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”
Bon travail, bien expliqué. Impeccable surtout avec les points d’attention.
Très professionnel. Impeccable et bonne continuation.
Zoungou
Les commentaires sont fermés.