Recovery en cas de perte de tous les controlfiles. d’un controlfile multiplexé. Un catalogue permet d’identifier le backup (le controlfile n’est plus là pour en avoir la trace), mais utiliser autobackup avec la destination par défaut (Fast Recovery Area) permet aussi de le trouver automatiquement.
Ici un exemple en utilisant Recovery Advisor, sur une base de demo créée ici.
Voici mes controlfiles:
RMAN> select status,name from v$controlfile;
RMAN> select status,name from v$controlfile; using target database control file instead of recovery catalog STATUS ------- NAME -------------------------------------------------------------------------------- /u/DEMO/DATA/DEMO100/control01.ctl /u/DEMO/FRA/DEMO100/control02.ctl Recovery Manager complete.
Je les supprime tous:
Failure simulation
+ rm /u/DEMO/DATA/DEMO100/control01.ctl /u/DEMO/FRA/DEMO100/control02.ctl + ls -l '/u/DEMO/DATA/DEMO100/control*.ctl' ls: cannot access /u/DEMO/DATA/DEMO100/control*.ctl: No such file or directory
Impossible de se connecter avec RMAN:
Recovery Manager: Release 12.1.0.1.0 - Production on Sat Nov 16 21:46:16 2013 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00554: initialization of internal recovery manager package failed RMAN-06003: ORACLE error from target database: ORA-00210: cannot open the specified control file ORA-00202: control file: '/u/DEMO/DATA/DEMO100/control01.ctl' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3
Je redémarre alors en nomount avec sqlplus:
SQL*Plus: Release 12.1.0.1.0 Production on Sat Nov 16 21:46:17 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> startup force; ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2289064 bytes Variable Size 327156312 bytes Database Buffers 79691776 bytes Redo Buffers 8409088 bytes ORA-00205: error in identifying control file, check alert log for more info SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Et voici la résolution automatique avec Recovery Advisor:
Recovery Manager: Release 12.1.0.1.0 - Production on Sat Nov 16 21:46:22 2013 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. connected to target database: DEMO100 (not mounted)
RMAN> list failure;
RMAN> list failure; using target database control file instead of recovery catalog List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 7004 CRITICAL OPEN 16-NOV-13 Control file /u/DEMO/FRA/DEMO100/control02.ctl is missing 7001 CRITICAL OPEN 16-NOV-13 Control file /u/DEMO/DATA/DEMO100/control01.ctl is missing
RMAN> advise failure;
RMAN> advise failure; List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 7004 CRITICAL OPEN 16-NOV-13 Control file /u/DEMO/FRA/DEMO100/control02.ctl is missing 7001 CRITICAL OPEN 16-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 ======================= 1. If file /u/DEMO/FRA/DEMO100/control02.ctl was unintentionally renamed or moved, restore it 2. If file /u/DEMO/DATA/DEMO100/control01.ctl was unintentionally renamed or moved, restore it 3. If this is a standby database, restore the controlfile for a standby database using RESTORE STANDBY CONTROLFILE FROM AUTOBACKUP command 4. If this is a primary database and a standby database is available, then perform a Data Guard failover initiated from the standby Automated Repair Options ======================== Option Repair Description ------ ------------------ 1 Restore a backup control file Strategy: The repair includes complete media recovery with no data loss Repair script: /u/DEMO/diag/rdbms/demo100/DEMO100/hm/reco_2694228121.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_2694228121.hm contents of repair script: # restore control file restore controlfile from autobackup; sql 'alter database mount'; Do you really want to execute the above repair (enter YES or NO)? executing repair script Starting restore at 16-NOV-13 using channel ORA_DISK_1 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_16/o1_mf_s_831675812_98hmwolx_.bkp found in the recovery area AUTOBACKUP search with format "%F" not attempted because DBID was not set channel ORA_DISK_1: restoring control file from AUTOBACKUP /u/DEMO/FRA/DEMO100/autobackup/2013_11_16/o1_mf_s_831675812_98hmwolx_.bkp channel ORA_DISK_1: control file restore from AUTOBACKUP complete output file name=/u/DEMO/DATA/DEMO100/control01.ctl output file name=/u/DEMO/FRA/DEMO100/control02.ctl Finished restore at 16-NOV-13 sql statement: alter database mount released channel: ORA_DISK_1 repair failure complete
Mais la base n’est pas encore ouverte. Il faut encore faire un recover using backup controlfile même si les datafiles sont à jour.
RMAN> list failure;
RMAN> list failure; Database Role: PRIMARY List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 7055 CRITICAL OPEN 16-NOV-13 System datafile 1: '/u/DEMO/DATA/DEMO100/system01.dbf' needs media recovery 7052 CRITICAL OPEN 16-NOV-13 Control file needs media recovery 760 HIGH OPEN 16-NOV-13 One or more non-system datafiles need media recovery
RMAN> advise failure;
RMAN> advise failure; Starting implicit crosscheck backup at 16-NOV-13 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=362 device type=DISK Crosschecked 2 objects Finished implicit crosscheck backup at 16-NOV-13 Starting implicit crosscheck copy at 16-NOV-13 using channel ORA_DISK_1 Finished implicit crosscheck copy at 16-NOV-13 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u/DEMO/FRA/DEMO100/archivelog/2013_11_16/o1_mf_1_3_98hp5f06_.arc File Name: /u/DEMO/FRA/DEMO100/archivelog/2013_11_16/o1_mf_1_2_98hp41w7_.arc File Name: /u/DEMO/FRA/DEMO100/archivelog/2013_11_16/o1_mf_1_4_98hpdox7_.arc File Name: /u/DEMO/FRA/DEMO100/autobackup/2013_11_16/o1_mf_s_831675812_98hmwolx_.bkp Database Role: PRIMARY List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 7055 CRITICAL OPEN 16-NOV-13 System datafile 1: '/u/DEMO/DATA/DEMO100/system01.dbf' needs media recovery 7052 CRITICAL OPEN 16-NOV-13 Control file needs media recovery 760 HIGH OPEN 16-NOV-13 One or more non-system datafiles need media recovery analyzing automatic repair options; this may take some time using channel ORA_DISK_1 analyzing automatic repair options complete Mandatory Manual Actions ======================== no manual actions available Optional Manual Actions ======================= 1. If you have the correct version of the control file, then shutdown the database and replace the old control file 2. If you restored the wrong version of data file /u/DEMO/DATA/DEMO100/system01.dbf, then replace it with the correct one 3. If you restored the wrong version of data file /u/DEMO/DATA/DEMO100/example01.dbf, then replace it with the correct one 4. If you restored the wrong version of data file /u/DEMO/DATA/DEMO100/sysaux01.dbf, then replace it with the correct one 5. If you restored the wrong version of data file /u/DEMO/DATA/DEMO100/undotbs01.dbf, then replace it with the correct one 6. If you restored the wrong version of data file /u/DEMO/DATA/DEMO100/users01.dbf, then replace it with the correct one 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 Recover database Strategy: The repair includes complete media recovery with no data loss Repair script: /u/DEMO/diag/rdbms/demo100/DEMO100/hm/reco_2263175920.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_2263175920.hm contents of repair script: # recover database recover database; alter database open resetlogs; Do you really want to execute the above repair (enter YES or NO)? executing repair script Starting recover at 16-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_16/o1_mf_1_2_98hp41w7_.arc archived log for thread 1 with sequence 3 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_16/o1_mf_1_3_98hp5f06_.arc archived log for thread 1 with sequence 4 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_16/o1_mf_1_4_98hpdox7_.arc archived log for thread 1 with sequence 5 is already on disk as file /u/DEMO/DATA/DEMO100/redo02.log archived log file name=/u/DEMO/FRA/DEMO100/archivelog/2013_11_16/o1_mf_1_2_98hp41w7_.arc thread=1 sequence=2 archived log file name=/u/DEMO/FRA/DEMO100/archivelog/2013_11_16/o1_mf_1_3_98hp5f06_.arc thread=1 sequence=3 archived log file name=/u/DEMO/FRA/DEMO100/archivelog/2013_11_16/o1_mf_1_4_98hpdox7_.arc thread=1 sequence=4 archived log file name=/u/DEMO/DATA/DEMO100/redo02.log thread=1 sequence=5 media recovery complete, elapsed time: 00:00:01 Finished recover at 16-NOV-13 Statement processed repair failure complete Recovery Manager complete.
Arrêt de la base le temps de restaurer le controlfile. Aucune perte de donnée mais ouverture en resetlogs. A noter qu’il a fallu lancer deux fois recovery advisor.