Recovery en cas de perte de: spfile, control, datafiles et redo logs. On a toujours la FRA par contre, avec l’autobackup et les derniers archivelogs.
On récupère le spfile à partir de l’autobackup, le controlfile à partir de son miroir, puis incomplete database recovery.
Un exemple sur une base de demo créée ici.
On supprime tout:
+ rm /u/DEMO/DATA/DEMO100/control01.ctl /u/DEMO/DATA/DEMO100/example01.dbf /u/DEMO/DATA/DEMO100/redo01bis.log /u/DEMO/DATA/DEMO100/redo01.log /u/DEMO/DATA/DEMO100/redo02bis.log /u/DEMO/DATA/DEMO100/redo02.log /u/DEMO/DATA/DEMO100/redo03bis.log /u/DEMO/DATA/DEMO100/redo03.log /u/DEMO/DATA/DEMO100/sysaux01.dbf /u/DEMO/DATA/DEMO100/system01.dbf /u/DEMO/DATA/DEMO100/temp01.dbf /u/DEMO/DATA/DEMO100/undotbs01.dbf /u/DEMO/DATA/DEMO100/users01.dbf /app/oracle/product/12c/dbs/spfileDEMO100.ora
Un shutdown abort pour arrêter l’instance:
SQL>> shutdown abort;
ORACLE instance shut down.
Recovery Manager: Release 12.1.0.1.0 - Production on Mon Nov 18 16:01:02 2013 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. connected to target database (not started)
RMAN> startup;
RMAN> startup; startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/app/oracle/product/12c/dbs/initDEMO100.ora' starting Oracle instance without parameter file for retrieval of spfile Oracle instance started RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 11/18/2013 16:01:06 ORA-00205: error in identifying control file, check alert log for more info
On n’a plus de init.ora ni spfile donc le startup a créé une instance DUMMY utile seulement pour restaurer le spfile.
Il faut préciser où le trouver en donnant le chemin de la FRA et le DB UNIQUE NAME.
RMAN> restore spfile from autobackup recovery area ‘/u/DEMO/FRA’ db_unique_name ‘DEMO100′;
RMAN> restore spfile from autobackup recovery area '/u/DEMO/FRA' db_unique_name 'DEMO100'; Starting restore at 18-NOV-13 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=424 device type=DISK recovery area destination: /u/DEMO/FRA database name (or database unique name) used for search: DEMO100 channel ORA_DISK_1: AUTOBACKUP /u/DEMO/FRA/DEMO100/autobackup/2013_11_17/o1_mf_s_831765902_98lcw1g4_.bkp found in the recovery area AUTOBACKUP search with format "%F" not attempted because DBID was not set channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u/DEMO/FRA/DEMO100/autobackup/2013_11_17/o1_mf_s_831765902_98lcw1g4_.bkp channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 18-NOV-13
On continue avec recovery advisor:
RMAN> list failure;
RMAN> list failure; List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 2 CRITICAL OPEN 17-NOV-13 Control file /app/oracle/product/12c/dbs/cntrlDEMO100.dbf is missing
On est toujours sur l’instance dummy. Il faut redémarrer pour prendre en compte le spfile restauré.
RMAN> startup force;
RMAN> startup force; Oracle instance started RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 11/18/2013 16:01:15 ORA-00205: error in identifying control file, check alert log for more info
RMAN> advise failure;
RMAN> advise failure; WARNING: new failures were found since last LIST FAILURE command List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 9843 CRITICAL OPEN 18-NOV-13 Control file /u/DEMO/DATA/DEMO100/control01.ctl is missing analyzing automatic repair options; this may take some time allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=123 device type=DISK analyzing automatic repair options complete Mandatory Manual Actions ======================== no manual actions available Optional Manual Actions ======================= no manual actions available Automated Repair Options ======================== Option Repair Description ------ ------------------ 1 Use a multiplexed copy to restore control file /u/DEMO/DATA/DEMO100/control01.ctl Strategy: The repair includes complete media recovery with no data loss Repair script: /u/DEMO/diag/rdbms/demo100/DEMO100/hm/reco_2201126679.hm
RMAN> repair failure;
RMAN> repair failure; Strategy: The repair includes complete media recovery with no data loss Repair script: /u/DEMO/diag/rdbms/demo100/DEMO100/hm/reco_2201126679.hm contents of repair script: # restore control file using multiplexed copy restore controlfile from '/u/DEMO/FRA/DEMO100/control02.ctl'; sql 'alter database mount'; Do you really want to execute the above repair (enter YES or NO)? executing repair script Starting restore at 18-NOV-13 using channel ORA_DISK_1 channel ORA_DISK_1: copied control file copy output file name=/u/DEMO/DATA/DEMO100/control01.ctl output file name=/u/DEMO/FRA/DEMO100/control02.ctl Finished restore at 18-NOV-13 sql statement: alter database mount released channel: ORA_DISK_1 repair failure complete
Maintenant qu’on a le spfile et controlfile, on continue:
RMAN> list failure;
RMAN> list failure; Database Role: PRIMARY List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 9933 CRITICAL OPEN 18-NOV-13 System datafile 1: '/u/DEMO/DATA/DEMO100/system01.dbf' is missing 9924 CRITICAL OPEN 18-NOV-13 Online log group 3 is unavailable 9915 CRITICAL OPEN 18-NOV-13 Online log group 2 is unavailable 9906 CRITICAL OPEN 18-NOV-13 Online log group 1 is unavailable 8 HIGH OPEN 18-NOV-13 One or more non-system datafiles are missing 9930 HIGH OPEN 18-NOV-13 Online log member /u/DEMO/DATA/DEMO100/redo03bis.log is missing 9927 HIGH OPEN 18-NOV-13 Online log member /u/DEMO/DATA/DEMO100/redo03.log is missing 9921 HIGH OPEN 18-NOV-13 Online log member /u/DEMO/DATA/DEMO100/redo02bis.log is missing 9918 HIGH OPEN 18-NOV-13 Online log member /u/DEMO/DATA/DEMO100/redo02.log is missing 9912 HIGH OPEN 18-NOV-13 Online log member /u/DEMO/DATA/DEMO100/redo01bis.log is missing 9909 HIGH OPEN 18-NOV-13 Online log member /u/DEMO/DATA/DEMO100/redo01.log is missing
RMAN> advise failure;
RMAN> advise failure; Database Role: PRIMARY List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 9933 CRITICAL OPEN 18-NOV-13 System datafile 1: '/u/DEMO/DATA/DEMO100/system01.dbf' is missing 9924 CRITICAL OPEN 18-NOV-13 Online log group 3 is unavailable 9915 CRITICAL OPEN 18-NOV-13 Online log group 2 is unavailable 9906 CRITICAL OPEN 18-NOV-13 Online log group 1 is unavailable 8 HIGH OPEN 18-NOV-13 One or more non-system datafiles are missing 9930 HIGH OPEN 18-NOV-13 Online log member /u/DEMO/DATA/DEMO100/redo03bis.log is missing 9927 HIGH OPEN 18-NOV-13 Online log member /u/DEMO/DATA/DEMO100/redo03.log is missing 9921 HIGH OPEN 18-NOV-13 Online log member /u/DEMO/DATA/DEMO100/redo02bis.log is missing 9918 HIGH OPEN 18-NOV-13 Online log member /u/DEMO/DATA/DEMO100/redo02.log is missing 9912 HIGH OPEN 18-NOV-13 Online log member /u/DEMO/DATA/DEMO100/redo01bis.log is missing 9909 HIGH OPEN 18-NOV-13 Online log member /u/DEMO/DATA/DEMO100/redo01.log is missing analyzing automatic repair options; this may take some time allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=7 device type=DISK analyzing automatic repair options complete Mandatory Manual Actions ======================== no manual actions available Optional Manual Actions ======================= 1. If file /u/DEMO/DATA/DEMO100/redo03bis.log was unintentionally renamed or moved, restore it 2. If file /u/DEMO/DATA/DEMO100/redo03.log was unintentionally renamed or moved, restore it 3. If file /u/DEMO/DATA/DEMO100/redo02bis.log was unintentionally renamed or moved, restore it 4. If file /u/DEMO/DATA/DEMO100/redo02.log was unintentionally renamed or moved, restore it 5. If file /u/DEMO/DATA/DEMO100/redo01bis.log was unintentionally renamed or moved, restore it 6. If file /u/DEMO/DATA/DEMO100/redo01.log was unintentionally renamed or moved, restore it 7. Try flush redo using ALTER SYSTEM FLUSH REDO TO 'standby name' command. Then perform a Data Guard role change (failover). Available standbys: DEMO110. Automated Repair Options ======================== Option Repair Description ------ ------------------ 1 Perform incomplete database recovery to SCN 2438795 Strategy: The repair includes point-in-time recovery with some data loss Repair script: /u/DEMO/diag/rdbms/demo100/DEMO100/hm/reco_2306086548.hm
RMAN> repair failure;
RMAN> repair failure; Strategy: The repair includes point-in-time recovery with some data loss Repair script: /u/DEMO/diag/rdbms/demo100/DEMO100/hm/reco_2306086548.hm contents of repair script: # database point-in-time recovery restore database until scn 2438795; recover database until scn 2438795; alter database open resetlogs; Do you really want to execute the above repair (enter YES or NO)? executing repair script Starting restore at 18-NOV-13 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /u/DEMO/DATA/DEMO100/system01.dbf channel ORA_DISK_1: restoring datafile 00002 to /u/DEMO/DATA/DEMO100/example01.dbf channel ORA_DISK_1: restoring datafile 00003 to /u/DEMO/DATA/DEMO100/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00004 to /u/DEMO/DATA/DEMO100/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00006 to /u/DEMO/DATA/DEMO100/users01.dbf channel ORA_DISK_1: reading from backup piece /u/DEMO/backup/1nop7c4r_1_1 channel ORA_DISK_1: piece handle=/u/DEMO/backup/1nop7c4r_1_1 tag=TAG20131117T210946 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:25 Finished restore at 18-NOV-13 Starting recover at 18-NOV-13 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 2 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_17/o1_mf_1_2_98l8v5jn_.arc archived log for thread 1 with sequence 1 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_17/o1_mf_1_1_98l90oc4_.arc archived log for thread 1 with sequence 2 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_17/o1_mf_1_2_98lb0yd7_.arc archived log for thread 1 with sequence 3 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_17/o1_mf_1_3_98lbrvkl_.arc archived log for thread 1 with sequence 1 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_17/o1_mf_1_1_98lcvcz2_.arc archived log for thread 1 with sequence 2 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_17/o1_mf_1_2_98lczm3t_.arc archived log for thread 1 with sequence 3 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_17/o1_mf_1_3_98lkwck9_.arc archived log for thread 1 with sequence 4 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_18/o1_mf_1_4_98lykw5o_.arc archived log file name=/u/DEMO/FRA/DEMO100/archivelog/2013_11_17/o1_mf_1_2_98l8v5jn_.arc thread=1 sequence=2 archived log file name=/u/DEMO/FRA/DEMO100/archivelog/2013_11_17/o1_mf_1_1_98l90oc4_.arc thread=1 sequence=1 archived log file name=/u/DEMO/FRA/DEMO100/archivelog/2013_11_17/o1_mf_1_2_98lb0yd7_.arc thread=1 sequence=2 archived log file name=/u/DEMO/FRA/DEMO100/archivelog/2013_11_17/o1_mf_1_3_98lbrvkl_.arc thread=1 sequence=3 archived log file name=/u/DEMO/FRA/DEMO100/archivelog/2013_11_17/o1_mf_1_1_98lcvcz2_.arc thread=1 sequence=1 archived log file name=/u/DEMO/FRA/DEMO100/archivelog/2013_11_17/o1_mf_1_2_98lczm3t_.arc thread=1 sequence=2 archived log file name=/u/DEMO/FRA/DEMO100/archivelog/2013_11_17/o1_mf_1_3_98lkwck9_.arc thread=1 sequence=3 archived log file name=/u/DEMO/FRA/DEMO100/archivelog/2013_11_18/o1_mf_1_4_98lykw5o_.arc thread=1 sequence=4 media recovery complete, elapsed time: 00:00:21 Finished recover at 18-NOV-13 Statement processed repair failure complete Recovery Manager complete.
On a tout récupéré jusqu’au dernier SCN disponible dans les archive log.
Grâce à la FRA qui contient l’autobackup, le restore/recover est assez simple. On a eu besoin de connaître le chemin de la FRA ainsi que le nom de la base.