Oracle db12c : Récupérer une table via RMAN

Tout un chacun se souvient avoir eu une discussion de ce type :
– La base « CRITIQUE » est bien sauvegardées avec RMAN ?
– Oui
– Peux-tu restaurer la table « CRITIQUE » d’il y a 2 jours à 21h45?
– …
Je passerai les étapes pour recréer la base de données, appliquer le backup full, l’incrémental, le différentiel, les archivelogs; Faire un dump de la table, la restaurer, ou la recopier.
Avec l’arrivée de la version 12c, Oracle offre dans ses nouvelles fonctionalités (New Features) la possibilité de restaurer une table depuis un backup RMAN : le RMAN Table Point In Time Recovery (PITR).
Voyons comment fonctionne cette « nouveauté », ses avantages et ses limites.
1- Prérequis
Afin de pouvoir utiliser le PITR, il faut que :
– la base soit en archivelog
– la FRA soit paramétrée pour plus de facilité (bien que cela ne soit pas une obligation)
– un premier backup RMAN soit initié (incluant le controlfile)
Mise en place de la base en mode archivelog et vérification de la FRA

SQL> startup mount;
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2924928 bytes
Variable Size 520097408 bytes
Database Buffers 1073741824 bytes
Redo Buffers 13848576 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 32
Next log sequence to archive 34
Current log sequence 34
SQL> show parameter recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /app/oracle/fast_recovery_area
db_recovery_file_dest_size big integer 4815M

Mise en place de la sauvegarde automatique du controlfile et lancement de la sauvegarde RMAN Full

[oracle@ol71 ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Fri May 15 13:20:14 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA121 (DBID=4058994696)
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> backup database plus archivelog;
Starting backup at 15-MAY-15
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=262 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=34 RECID=1 STAMP=879772844
channel ORA_DISK_1: starting piece 1 at 15-MAY-15
channel ORA_DISK_1: finished piece 1 at 15-MAY-15
piece handle=/app/oracle/fast_recovery_area/ORA121/backupset/2015_05_15/o1_mf_annnn_TAG20150515T132045_boconfdx_.bkp tag=TAG20150515T132045 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-MAY-15
Starting Control File and SPFILE Autobackup at 15-MAY-15
piece handle=/app/oracle/fast_recovery_area/ORA121/autobackup/2015_05_15/o1_mf_s_879775370_bocr3c7j_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-MAY-15
Starting backup at 15-MAY-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/app/oracle/oradata/ORA121/easytest01.dbf
input datafile file number=00001 name=/app/oracle/oradata/ORA121/system01.dbf
input datafile file number=00002 name=/app/oracle/oradata/ORA121/sysaux01.dbf
input datafile file number=00003 name=/app/oracle/oradata/ORA121/undotbs01.dbf
input datafile file number=00004 name=/app/oracle/oradata/ORA121/users01.dbf
channel ORA_DISK_1: starting piece 1 at 15-MAY-15
channel ORA_DISK_1: finished piece 1 at 15-MAY-15
piece handle=/app/oracle/fast_recovery_area/ORA121/backupset/2015_05_15/o1_mf_nnndf_TAG20150515T132046_bocongr0_.bkp tag=TAG20150515T132046 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 15-MAY-15
channel ORA_DISK_1: finished piece 1 at 15-MAY-15
piece handle=/app/oracle/fast_recovery_area/ORA121/backupset/2015_05_15/o1_mf_ncsnf_TAG20150515T132046_boconpt6_.bkp tag=TAG20150515T132046 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-MAY-15
Starting backup at 15-MAY-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=35 RECID=2 STAMP=879772855
channel ORA_DISK_1: starting piece 1 at 15-MAY-15
channel ORA_DISK_1: finished piece 1 at 15-MAY-15
piece handle=/app/oracle/fast_recovery_area/ORA121/backupset/2015_05_15/o1_mf_annnn_TAG20150515T132055_boconr17_.bkp tag=TAG20150515T132055 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-MAY-15

2- Mise en situation
Créons notre environnement de test composé de :
– une table : EASYTABLE1
– une séquence : SEQ_EASY1
– une procédure d’alimentation de la table : PROC_INSERT_EASYTABLE1
– une planification de travaux (pour alimenter la table au fil de l’eau et parce que l’on aime bien utiliser le package DBMS_SCHEDULER)
Création de la table :

create table EASYTABLE1 ( datetime date, num_scn number, c1 number) ;

Création de la séquence :

create sequence SEQ_EASY1 increment by 1;

Création de la procédure d’alimentation de la table EASYTABLE1 qui tournera toutes les 15 minutes :

grant execute on DBMS_FLASHBACK to easytest;
create or replace procedure PROC_INSERT_EASYTABLE1 is
begin
insert into EASYTABLE1 select sysdate, DBMS_FLASHBACK.get_system_change_number, SEQ_EASY1.nextval from dual;
end;
/

Création de la planification :

grant execute on DBMS_SCHEDULER to easytest;
grant CREATE ANY JOB to easytest;
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'PROG_INSERT_EASYTEABLE1',
program_action => 'PROC_INSERT_EASYTABLE1',
program_type => 'STORED_PROCEDURE');
END;
/
PL/SQL procedure successfully completed.
exec dbms_scheduler.enable('PROG_INSERT_EASYTEABLE1');
PL/SQL procedure successfully completed.
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => 'all_15min_schedule',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY; INTERVAL=15',
end_date => NULL,
comments => 'Every 15 minutes');
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'Alim_table_PITR',
program_name => 'PROG_INSERT_EASYTEABLE1',
schedule_name => 'all_15min_schedule');
END;
/
PL/SQL procedure successfully completed.
exec dbms_scheduler.enable('Alim_table_PITR');
PL/SQL procedure successfully completed.

Afin de vérifier si le job fonctionne et voir l’état de ce dernier, nous pouvons utiliser les deux requêtes ci-dessous :

select * from USER_SCHEDULER_JOB_RUN_DETAILS where job_name = 'ALIM_TABLE_PITR';
select * from USER_SCHEDULER_JOB_LOG where job_name = 'ALIM_TABLE_PITR';

Il nous suffit d’attendre que la table s’alimente (avec une insertion toutes les 15 minutes).
Revenons un peu plus tard après avoir bu un grand verre d’eau par ces journées de forte chaleur.
Regardons la table, elle nous donne l’heure des inserts, le numéro de SCN et le numéro de l’insert :

SQL> select * from EASYTABLE1 order by datetime asc;
DATETIME NUM_SCN C1
------------------- ---------- ----------
15/05/2015 11:48:17 495962 1
15/05/2015 12:03:33 496534 2
15/05/2015 12:18:33 497067 3
15/05/2015 12:33:33 497448 4
15/05/2015 12:48:33 497827 5
15/05/2015 13:03:33 498344 6
15/05/2015 13:18:32 499229 7
15/05/2015 13:33:32 499879 8
15/05/2015 13:48:32 500295 9
15/05/2015 14:03:32 501305 10
15/05/2015 14:18:32 501869 11
15/05/2015 14:33:32 502295 12
15/05/2015 14:48:32 504943 13
15/05/2015 15:03:32 506915 14
15/05/2015 15:18:32 507640 15

Rentrons maintenant dans le vif du sujet. La demande tombe :
– La base « CRITIQUE » est bien sauvegardée avec RMAN ?
– Oui
– Le batch de Samedi a vérolé la table EASYTABLE1. Peux-tu restorer la table EASYTABLE1 au 15/05/2015 à 14:18:32 ?
– Oui
Nous allons donc restorer la table EASYTEST.EASYTABLE1 dans la table EASYTEST.EASYTABLE2 en nous basant sur le SCN 501869 qui correspond à la date souhaitée.
Le répertoire de travail sera : /app/oracle/restore.

[oracle@ol71 restore]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Fri May 15 13:55:20 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA121 (DBID=4058994696)
RMAN> RECOVER TABLE 'EASYTEST'.'EASYTABLE1'
UNTIL SCN 501869
AUXILIARY DESTINATION '/app/oracle/restore'
REMAP TABLE 'EASYTEST'.'EASYTABLE1':'EASYTABLE2';
Starting recover at 15-MAY-15
using target database control file instead of recovery catalog
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=263 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with SID='fxbd'
initialization parameters used for automatic instance:
db_name=ORA121
db_unique_name=fxbd_pitr_ORA121
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/app/oracle
_system_trig_enabled=FALSE
sga_target=1536M
processes=200
db_create_file_dest=/app/oracle/restore
log_archive_dest_1='location=/app/oracle/restore'
#No auxiliary parameter file used
starting up automatic instance ORA121
Oracle instance started
Total System Global Area 1610612736 bytes
Fixed Size 2924928 bytes
Variable Size 402656896 bytes
Database Buffers 1191182336 bytes
Redo Buffers 13848576 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until scn 501869;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET until clause
Starting restore at 15-MAY-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=12 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /app/oracle/fast_recovery_area/ORA121/autobackup/2015_05_15/o1_mf_s_879775370_bocr3c7j_.bkp
channel ORA_AUX_DISK_1: piece handle=/app/oracle/fast_recovery_area/ORA121/autobackup/2015_05_15/o1_mf_s_879775370_bocr3c7j_.bkp tag=TAG20150515T140250
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/app/oracle/restore/ORA121/controlfile/o1_mf_boct4m1c_.ctl
Finished restore at 15-MAY-15
sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of Memory Script:
{
# set requested point in time
set until scn 501869;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 2 to new;
set newname for clone tempfile 1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 3, 2;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /app/oracle/restore/ORA121/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 15-MAY-15
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /app/oracle/restore/ORA121/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /app/oracle/restore/ORA121/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /app/oracle/restore/ORA121/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /app/oracle/fast_recovery_area/ORA121/backupset/2015_05_15/o1_mf_nnndf_TAG20150515T132046_bocongr0_.bkp
channel ORA_AUX_DISK_1: piece handle=/app/oracle/fast_recovery_area/ORA121/backupset/2015_05_15/o1_mf_nnndf_TAG20150515T132046_bocongr0_.bkp tag=TAG20150515T132046
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 15-MAY-15
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=879777472 file name=/app/oracle/restore/ORA121/datafile/o1_mf_system_boct4ry0_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=879777472 file name=/app/oracle/restore/ORA121/datafile/o1_mf_undotbs1_boct4ryc_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=879777472 file name=/app/oracle/restore/ORA121/datafile/o1_mf_sysaux_boct4ry8_.dbf
contents of Memory Script:
{
# set requested point in time
set until scn 501869;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 2 online
Starting recover at 15-MAY-15
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 35 is already on disk as file /app/oracle/fast_recovery_area/ORA121/archivelog/2015_05_15/o1_mf_1_35_boconqvv_.arc
archived log for thread 1 with sequence 36 is already on disk as file /app/oracle/fast_recovery_area/ORA121/archivelog/2015_05_15/o1_mf_1_36_bocp5xtj_.arc
archived log for thread 1 with sequence 37 is already on disk as file /app/oracle/fast_recovery_area/ORA121/archivelog/2015_05_15/o1_mf_1_37_bocr39kr_.arc
archived log for thread 1 with sequence 38 is already on disk as file /app/oracle/fast_recovery_area/ORA121/archivelog/2015_05_15/o1_mf_1_38_boct41lt_.arc
archived log file name=/app/oracle/fast_recovery_area/ORA121/archivelog/2015_05_15/o1_mf_1_35_boconqvv_.arc thread=1 sequence=35
archived log file name=/app/oracle/fast_recovery_area/ORA121/archivelog/2015_05_15/o1_mf_1_36_bocp5xtj_.arc thread=1 sequence=36
archived log file name=/app/oracle/fast_recovery_area/ORA121/archivelog/2015_05_15/o1_mf_1_37_bocr39kr_.arc thread=1 sequence=37
archived log file name=/app/oracle/fast_recovery_area/ORA121/archivelog/2015_05_15/o1_mf_1_38_boct41lt_.arc thread=1 sequence=38
media recovery complete, elapsed time: 00:00:02
Finished recover at 15-MAY-15
sql statement: alter database open read only
contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''/app/oracle/restore/ORA121/controlfile/o1_mf_boct4m1c_.ctl'' comment=
''RMAN set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script
sql statement: create spfile from memory
database closed
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1610612736 bytes
Fixed Size 2924928 bytes
Variable Size 419434112 bytes
Database Buffers 1174405120 bytes
Redo Buffers 13848576 bytes
sql statement: alter system set control_files = ''/app/oracle/restore/ORA121/controlfile/o1_mf_boct4m1c_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1610612736 bytes
Fixed Size 2924928 bytes
Variable Size 419434112 bytes
Database Buffers 1174405120 bytes
Redo Buffers 13848576 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until scn 501869;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 5 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 5;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 15-MAY-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=12 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /app/oracle/restore/FXBD_PITR_ORA121/datafile/o1_mf_easytest_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /app/oracle/fast_recovery_area/ORA121/backupset/2015_05_15/o1_mf_nnndf_TAG20150515T132046_bocongr0_.bkp
channel ORA_AUX_DISK_1: piece handle=/app/oracle/fast_recovery_area/ORA121/backupset/2015_05_15/o1_mf_nnndf_TAG20150515T132046_bocongr0_.bkp tag=TAG20150515T132046
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 15-MAY-15
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=879777531 file name=/app/oracle/restore/FXBD_PITR_ORA121/datafile/o1_mf_easytest_boct6not_.dbf
contents of Memory Script:
{
# set requested point in time
set until scn 501869;
# online the datafiles restored or switched
sql clone "alter database datafile 5 online";
# recover and open resetlogs
recover clone database tablespace "EASYTEST", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 5 online
Starting recover at 15-MAY-15
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 35 is already on disk as file /app/oracle/fast_recovery_area/ORA121/archivelog/2015_05_15/o1_mf_1_35_boconqvv_.arc
archived log for thread 1 with sequence 36 is already on disk as file /app/oracle/fast_recovery_area/ORA121/archivelog/2015_05_15/o1_mf_1_36_bocp5xtj_.arc
archived log for thread 1 with sequence 37 is already on disk as file /app/oracle/fast_recovery_area/ORA121/archivelog/2015_05_15/o1_mf_1_37_bocr39kr_.arc
archived log for thread 1 with sequence 38 is already on disk as file /app/oracle/fast_recovery_area/ORA121/archivelog/2015_05_15/o1_mf_1_38_boct41lt_.arc
archived log file name=/app/oracle/fast_recovery_area/ORA121/archivelog/2015_05_15/o1_mf_1_35_boconqvv_.arc thread=1 sequence=35
archived log file name=/app/oracle/fast_recovery_area/ORA121/archivelog/2015_05_15/o1_mf_1_36_bocp5xtj_.arc thread=1 sequence=36
archived log file name=/app/oracle/fast_recovery_area/ORA121/archivelog/2015_05_15/o1_mf_1_37_bocr39kr_.arc thread=1 sequence=37
archived log file name=/app/oracle/fast_recovery_area/ORA121/archivelog/2015_05_15/o1_mf_1_38_boct41lt_.arc thread=1 sequence=38
media recovery complete, elapsed time: 00:00:00
Finished recover at 15-MAY-15
database opened
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/app/oracle/restore''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/app/oracle/restore''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/app/oracle/restore''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/app/oracle/restore''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_fxbd_ypwm":
EXPDP> Estimate in progress using BLOCKS method...
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 64 KB
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> . . exported "EASYTEST"."EASYTABLE1" 6.085 KB 10 rows
EXPDP> Master table "SYS"."TSPITR_EXP_fxbd_ypwm" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_fxbd_ypwm is:
EXPDP> /app/oracle/restore/tspitr_fxbd_79112.dmp
EXPDP> Job "SYS"."TSPITR_EXP_fxbd_ypwm" successfully completed at Fri May 15 14:39:17 2015 elapsed 0 00:00:14
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_fxbd_snuw" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_fxbd_snuw":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "EASYTEST"."EASYTABLE2" 6.085 KB 10 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYS"."TSPITR_IMP_fxbd_snuw" successfully completed at Fri May 15 14:39:38 2015 elapsed 0 00:00:16
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /app/oracle/restore/ORA121/datafile/o1_mf_temp_boct52qn_.tmp deleted
auxiliary instance file /app/oracle/restore/FXBD_PITR_ORA121/onlinelog/o1_mf_3_boct6xwk_.log deleted
auxiliary instance file /app/oracle/restore/FXBD_PITR_ORA121/onlinelog/o1_mf_2_boct6xj7_.log deleted
auxiliary instance file /app/oracle/restore/FXBD_PITR_ORA121/onlinelog/o1_mf_1_boct6x29_.log deleted
auxiliary instance file /app/oracle/restore/FXBD_PITR_ORA121/datafile/o1_mf_easytest_boct6not_.dbf deleted
auxiliary instance file /app/oracle/restore/ORA121/datafile/o1_mf_sysaux_boct4ry8_.dbf deleted
auxiliary instance file /app/oracle/restore/ORA121/datafile/o1_mf_undotbs1_boct4ryc_.dbf deleted
auxiliary instance file /app/oracle/restore/ORA121/datafile/o1_mf_system_boct4ry0_.dbf deleted
auxiliary instance file /app/oracle/restore/ORA121/controlfile/o1_mf_boct4m1c_.ctl deleted
auxiliary instance file tspitr_fxbd_79112.dmp deleted
Finished recover at 15-MAY-15

L’opération est terminée. Vérifions le contenu de la table EASYTABLE2 :

[oracle@ol71 ~]$ sqlplus easytest/easytest
SQL*Plus: Release 12.1.0.2.0 Production on Fri May 15 14:43:52 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Fri May 15 2015 14:43:32 +02:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select * from EASYTABLE2 order by datetime asc
DATETIME NUM_SCN C1
------------------- ---------- ----------
15/05/2015 11:48:17 495962 1
15/05/2015 12:03:33 496534 2
15/05/2015 12:18:33 497067 3
15/05/2015 12:33:33 497448 4
15/05/2015 12:48:33 497827 5
15/05/2015 13:03:33 498344 6
15/05/2015 13:18:32 499229 7
15/05/2015 13:33:32 499879 8
15/05/2015 13:48:32 500295 9
15/05/2015 14:03:32 501305 10
15/05/2015 14:18:32 501869 11

Les données sont bien présentes et correspondent bien à ce que nous attendions.
Mais alors, que s’est-il passé ? Comment a fait Oracle pour nous faciliter la tâche.
Regardons la log pour comprendre les grandes étapes :
Côté base de données :
1- Création d’une instance temporaire
2- Clonage de la base source
3- Export de la table source
4- Import du dump
5- Suppression de l’instance temporaire
Coté système :

[oracle@ol71 ~]$ cd /app/oracle/restore/
[oracle@ol71 restore]$ ll
total 0
drwxr-x---. 3 oracle oinstall 24 May 15 14:37 ORA121
[oracle@ol71 restore]$ ll
total 0
drwxr-x---. 5 oracle oinstall 55 May 15 14:37 ORA121
[oracle@ol71 restore]$ du -sm *
1476 ORA121
[oracle@ol71 restore]$ du -sm *
0 FXBD_PITR_ORA121
0 ORA121

Tiens … Ces étapes doivent vous rappeler quelque chose.
Nous avons vu que nous pouvions utiliser cette méthode avec le numéro de SCN mais il n’est pas toujours aisé de le récupérer.
De la même façon, il est possible d’utiliser une date comme paramètre de restauration comme suit :

RMAN> RECOVER TABLE 'EASYTEST'.'EASYTABLE1'
UNTIL TIME "to_date('15/05/2015 14:03:32','DD/MM/YYYY HH24:MI:SS')"
AUXILIARY DESTINATION '/app/oracle/restore'
REMAP TABLE 'EASYTEST'.'EASYTABLE1':'EASYTABLE3';

Pour le même résultat:

SQL> select * from easytable3 order by datetime asc;
DATETIME NUM_SCN C1
------------------- ---------- ----------
15/05/2015 11:48:17 495962 1
15/05/2015 12:03:33 496534 2
15/05/2015 12:18:33 497067 3
15/05/2015 12:33:33 497448 4
15/05/2015 12:48:33 497827 5
15/05/2015 13:03:33 498344 6
15/05/2015 13:18:32 499229 7
15/05/2015 13:33:32 499879 8
15/05/2015 13:48:32 500295 9
15/05/2015 14:03:32 501305 10

3- Génération d’un dump de table
En ce basant sur le SCN :

RMAN> RECOVER TABLE 'EASYTEST'.'EASYTABLE1'
UNTIL SCN 504943
AUXILIARY DESTINATION '/app/oracle/restore'
DATAPUMP DESTINATION '/app/oracle/export'
DUMP FILE 'EASYTEST_EASYTABLE1_504943.dmp'
NOTABLEIMPORT;
[oracle@ol71 export]$ ll /app/oracle/export
total 160
-rw-r-----. 1 oracle oinstall 163840 May 15 15:26 EASYTEST_EASYTABLE1_504943.dmp

En ce basant sur une date :

RMAN> RECOVER TABLE 'EASYTEST'.'EASYTABLE1'
UNTIL TIME "to_date('15/05/2015 14:50:28','DD/MM/YYYY HH24:MI:SS')"
AUXILIARY DESTINATION '/app/oracle/restore'
DATAPUMP DESTINATION '/app/oracle/export'
DUMP FILE 'EASYTEST_EASYTABLE1_15052015145028.dmp'
NOTABLEIMPORT;
[oracle@ol71 export]$ ll /app/oracle/export
total 320
-rw-r-----. 1 oracle oinstall 163840 May 15 15:31 EASYTEST_EASYTABLE1_15052015145028.dmp
-rw-r-----. 1 oracle oinstall 163840 May 15 15:26 EASYTEST_EASYTABLE1_504943.dmp

4- Points d’attention
Bien qu’Oracle nous facilite grandement la tâche, il faut tout de même faire attention à certains points :
– la taille de la base (n’oublions pas que celle-ci est restaurée dans la FRA)
– avoir une sauvegarde du controlfile antérieure à la date de restauration sous peine d’être puni du message suivant :

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/15/2015 13:56:29
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06024: no backup or copy of the control file found to restore

5- Conclusion
Oracle a su nous faciliter une tâche d’administration fastidieuse avec cette « nouvelle fonctionnalité », il faut juste ne pas oublier que nous sommes tout de même DBA.

1 réflexion sur “Oracle db12c : Récupérer une table via RMAN”

  1. Bon travail, bien expliqué. Impeccable surtout avec les points d’attention.
    Très professionnel. Impeccable et bonne continuation.
    Zoungou

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *