Introduction
Oracle 11g Database has been full of new and improved enhancements so it wasn’t a surprise to come across Data Recovery Advisor.
It is an amazing tool that analyzes, proposes and implements solutions for data failures.
It has command line and GUI interface. GUI interface can be accessed through Oracle EM Grid and DB Console while the RMAN command line interface includes DRA commands.
Oracle describes a basic five step process for fixings issues with DRA. These are;
1. List the failures
2. Validate the database (optional)
3. Establish the possible options
4. Consider an option
5. Verify or check for remaining failures
Now I will see how Data Recovery Advisor can perform on critical recoveries. We are considering two scenarios, first scenario is that the system datafile has been lost and the second scenario in which all the controlfiles have been lost. In both cases prior RMAN backups exist.
Scenario: System datafile has been lost
Recovery Manager: Release 11.1.0.6.0 - Production on Fri Jul 11 09:27:14 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN> connect target /
connected to target database: DEV11G (DBID=3891859867, not open)
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
42 CRITICAL OPEN 11-JUL-08 System datafile 1: ‘F:\APP\ADMINISTRATOR\ORADATA\DEV11G\SYSTEM01.DBF’ is missing
Obviously the database could not open at this point. We have used the command LIST FAILURE to review what the failures are.
Going a bit further seeing how this will impact the database we can use LIST FAILURE DETAIL as follows;
RMAN> list failure detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
42 CRITICAL OPEN 11-JUL-08 System datafile 1: ‘F:\APP\ADMINISTRATOR\ORADATA\DEV11G\SYSTEM01.DBF’ is missing
Impact: Database cannot be opened
As already mentioned the database cannot be opened which has made this as a CRITICAL priority.
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
42 CRITICAL OPEN 11-JUL-08 System datafile 1: ‘F:\APP\ADMINISTR
ATOR\ORADATA\DEV11G\SYSTEM01.DBF’ is missing
Impact: Database cannot be opened
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file F:\APP\ADMINISTRATOR\ORADATA\DEV11G\SYSTEM01.DBF was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
—— ——————
1 Restore and recover datafile 1
Strategy: The repair includes complete media recovery with no data loss
Repair script: f:\app\administrator\diag\rdbms\dev11g\dev11g\hm\reco_3619189128.hm
Now I have used ADVISE FAILURE to see as to how can I repair or fix this issue. A list of options have been presented these are MADATORY MANUAL ACTIONS but none are required in this case, OPTIONAL MANUNAL ACTIONS and AUTOMATED REPAIR OPTIONS. We will consider AUTOMATED REPAIR OPTIONS; this includes simply executing the following script;
f:\app\administrator\diag\rdbms\dev11g\dev11g\hm\reco_3619189128.hm
RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data loss
Repair script: f:\app\administrator\diag\rdbms\dev11g\dev11g\hm\reco_3619189128.hm
contents of repair script:
# restore and recover datafile
restore datafile 1;
recover datafile 1;
Now we have displayed the contents of the script with REPAIR FAILURE PREVIEW command. We are completely aware of the recovery process as it is transparent.
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: f:\app\administrator\diag\rdbms\dev11g\dev11g\hm\reco_3619189128hm
contents of repair script:
# restore and recover datafile
restore datafile 1;
recover datafile 1;
Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script
Starting restore at 11-JUL-08
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 F:\APP\ADMINISTRATOR\ORADATA\DE
11G\SYSTEM01.DBF
channel ORA_DISK_1: reading from backup piece F:\APP\ADMINISTRATOR\FLASH_RECOVE
Y_AREA\DEV11G\BACKUPSET\2008_07_11\O1_MF_NNNDF_TAG20080711T092430_47H2B04D_.BKP
channel ORA_DISK_1: piece handle=F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\DEV11
\BACKUPSET\2008_07_11\O1_MF_NNNDF_TAG20080711T092430_47H2B04D_.BKP tag=TAG20080
11T092430
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 11-JUL-08
Starting recover at 11-JUL-08
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 11-JUL-08
repair failure complete
Do you want to open the database (enter YES or NO)? YES
database opened
Finally we executed the fix using REPAIR FAILURE and the repair has been successful.
RMAN> list failure closed;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
42 CRITICAL CLOSED 11-JUL-08 System datafile 1: ‘F:\APP\ADMINISTR
ATOR\ORADATA\DEV11G\SYSTEM01.DBF’ is missing
After opening the database we check again for any failures by using LIST FAILURE command. We can see that the STATUS had been changed from OPEN to CLOSED as the issue has been resolved.
Now we see the loss of controlfiles recovery using DRA.
Scenario: All controlfiles have been lost
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
113 CRITICAL OPEN 11-JUL-08 Control file F:\APP\ADMINISTRATOR\ORADATA\DEV11G\CONTROL01.CTL is missing
110 CRITICAL OPEN 11-JUL-08 Control file F:\APP\ADMINISTRATOR\ORADATA\DEV11G\CONTROL02.CTL is missing
107 CRITICAL OPEN 11-JUL-08 Control file F:\APP\ADMINISTRATOR\ORADATA\DEV11G\CONTROL03.CTL is missing
This is an additional step only to review that the precise impact is being shown or it could even be considered as part of step one.
RMAN> list failure detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
113 CRITICAL OPEN 11-JUL-08 Control file F:\APP\ADMINISTRATOR\OR
ADATA\DEV11G\CONTROL01.CTL is missing
Impact: Database cannot be mounted
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
110 CRITICAL OPEN 11-JUL-08 Control file F:\APP\ADMINISTRATOR\OR
ADATA\DEV11G\CONTROL02.CTL is missing
Impact: Database cannot be mounted
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
107 CRITICAL OPEN 11-JUL-08 Control file F:\APP\ADMINISTRATOR\OR
ADATA\DEV11G\CONTROL03.CTL is missing
Impact: Database cannot be mounted
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
113 CRITICAL OPEN 11-JUL-08 Control file F:\APP\ADMINISTRATOR\OR
ADATA\DEV11G\CONTROL01.CTL is missing
Impact: Database cannot be mounted
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
110 CRITICAL OPEN 11-JUL-08 Control file F:\APP\ADMINISTRATOR\OR
ADATA\DEV11G\CONTROL02.CTL is missing
Impact: Database cannot be mounted
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
107 CRITICAL OPEN 11-JUL-08 Control file F:\APP\ADMINISTRATOR\OR
ADATA\DEV11G\CONTROL03.CTL is missing
Impact: Database cannot be mounted
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file F:\APP\ADMINISTRATOR\ORADATA\DEV11G\CONTROL01.CTL was unintentionally
renamed or moved, restore it
2. If file F:\APP\ADMINISTRATOR\ORADATA\DEV11G\CONTROL02.CTL was unintentionally
renamed or moved, restore it
3. If file F:\APP\ADMINISTRATOR\ORADATA\DEV11G\CONTROL03.CTL was unintentionally
renamed or moved, restore it
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: f:\app\administrator\diag\rdbms\dev11g\dev11g\hm\reco_4252270918.hm
RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data loss
Repair script: f:\app\administrator\diag\rdbms\dev11g\dev11g\hm\reco_4252270918.
hm
contents of repair script:
# restore control file
restore controlfile from autobackup;
sql ‘alter database mount’;
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: f:\app\administrator\diag\rdbms\dev11g\dev11g\hm\reco_4252270918.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)? YES
executing repair script
Starting restore at 11-JUL-08
using channel ORA_DISK_1
recovery area destination: F:\app\Administrator\flash_recovery_area
database name (or database unique name) used for search: DEV11G
channel ORA_DISK_1: AUTOBACKUP F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\DEV11G\A
UTOBACKUP\2008_07_11\O1_MF_S_659784297_47H2BTVC_.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 F:\APP\ADMINISTRATOR\
FLASH_RECOVERY_AREA\DEV11G\AUTOBACKUP\2008_07_11\O1_MF_S_659784297_47H2BTVC_.BKP
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=F:\APP\ADMINISTRATOR\ORADATA\DEV11G\CONTROL01.CTL
output file name=F:\APP\ADMINISTRATOR\ORADATA\DEV11G\CONTROL02.CTL
output file name=F:\APP\ADMINISTRATOR\ORADATA\DEV11G\CONTROL03.CTL
Finished restore at 11-JUL-08
sql statement: alter database mount
released channel: ORA_DISK_1
repair failure complete
At this point, the control files have been restored and the database reached in mount state.
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
437 CRITICAL OPEN 11-JUL-08 System datafile 1: ‘F:\APP\ADMINISTRATOR\ORADATA\DEV11G\SYSTEM01.DBF’ needs media recovery
305 CRITICAL OPEN 11-JUL-08 Control file needs media recovery
142 HIGH OPEN 11-JUL-08 One or more on-system datafiles need media recovery
This time new failures have been determined. So again we execute the ADVISE FAILURE.
RMAN> advise failure;
Starting implicit crosscheck backup at 11-JUL-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 11-JUL-08
Starting implicit crosscheck copy at 11-JUL-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 11-JUL-08
searching for all files in the recovery area
cataloging files…
cataloging done
List of Cataloged Files
=======================
File Name: F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\DEV11G\AUTOBACKUP\2008_07_11
\O1_MF_S_659786902_47H4W8C7_.BKP
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
437 CRITICAL OPEN 11-JUL-08 System datafile 1: ‘F:\APP\ADMINISTRATOR\ORADATA\DEV11G\SYSTEM01.DBF’ needs media recovery
305 CRITICAL OPEN 11-JUL-08 Control file needs media recovery
142 HIGH OPEN 11-JUL-08 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 restored the wrong version of data file F:\APP\ADMINISTRATOR\ORADATA\D
EV11G\SYSTEM01.DBF, then replace it with the correct one
2. If you have the correct version of the control file, then shutdown the database and replace the old control file
3. If you restored the wrong version of data file F:\APP\ADMINISTRATOR\ORADATA\DEV11G\SYSAUX01.DBF, then replace it with the correct one
4. If you restored the wrong version of data file F:\APP\ADMINISTRATOR\ORADATA\DEV11G\UNDOTBS01.DBF, then replace it with the correct one
5. If you restored the wrong version of data file F:\APP\ADMINISTRATOR\ORADATA\DEV11G\USERS01.DBF, then replace it with the correct one
Automated Repair Options
========================
Option Repair Description
—— ——————
1 Recover database
Strategy: The repair includes complete media recovery with no data loss
Repair script: f:\app\administrator\diag\rdbms\dev11g\dev11g\hm\reco_3650007651.hm
A new script has been created which can be reviewed as follows;
RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data loss
Repair script: f:\app\administrator\diag\rdbms\dev11g\dev11g\hm\reco_3650007651.hm
contents of repair script:
# recover database
recover database;
alter database open resetlogs;
Finally executing the repair script as follows;
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: f:\app\administrator\diag\rdbms\dev11g\dev11g\hm\reco_3650007651.
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)? YES
executing repair script
Starting recover at 11-JUL-08
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file F:\APP\ADMI
NISTRATOR\ORADATA\DEV11G\REDO01.LOG
archived log file name=F:\APP\ADMINISTRATOR\ORADATA\DEV11G\REDO01.LOG thread=1 s
equence=1
media recovery complete, elapsed time: 00:00:01
Finished recover at 11-JUL-08
database opened
repair failure complete
Repair has been successful once again.
In conclusion to the control files recovery we have seen that the DRA process was divided into two portions.
First script executed;
1. RESTORE CONTROLFILE FROM AUTOBACKUP
2. ALTER DATABSE MOUNT
Second script executed;
1. RECOVER DATABASE
2. ALTER DATABASE OPEN RESETLOGS
DRA seems to be a reliable and an effective tool for database recovery.
Additional Resources;
http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmrepai.htm
Posted by Saad 
