Diagnosing and Repairing Failures with Data Recovery Advisor
Data Recovery Advisor is an Oracle Database tool that automatically diagnoses data failures, determines and presents appropriate repair options, and executes repairs at the user's request.
The easiest way to determine problems that your database is encountering is to use the LIST FAILURE command.
Step-1 : Check LIST FAILURE
RMAN> LIST FAILURE;
List of Database Failures
=================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- --------------------------------------------------------------
862 HIGH OPEN 16-DEC-13 One or more non-system datafiles are missing
RMAN>
If one or more failures exist, then you should typically use LIST FAILURE to show information about the failures and then use ADVISE FAILURE in the same RMAN session to obtain a report of your repair options.
Step-2 : Check ADVISE FAILURE
RMAN> ADVISE FAILURE;
List of Database Failures
=================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- ---------------------------------------------
862 HIGH OPEN 16-DEC-13 One or more non-system datafiles are missing
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
===================
no manual actions available
Optional Manual Actions
=================
1. If file +DATA/orcl/datafile/users.259.818891057 was unintentionally renamed or moved, restore it
Automated Repair Options
==================
Option Repair Description
------ ------------------
1 Restore and recover datafile 4
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/ORCL/hm/reco_452126040.hm
If ADVISE FAILURE suggests manual repairs, then try these first. If manual repairs are not possible,
or if they do not repair all failures, then you can use REPAIR FAILURE to automatically fix failures suggested in the most recent
ADVISE FAILURE command in your current RMAN session.
Execute REPAIR FAILURE PREVIEW which previews the first repair options displayed by the previous ADVISE FAILURE command in the RMAN session.
Step-3 : Check REPAIR FAILURE PREVIEW
RMAN> REPAIR FAILURE PREVIEW;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/ORCL/hm/reco_452126040.hm
contents of repair script:
# restore and recover datafile
restore datafile 4;
recover datafile 4;
sql 'alter database datafile 4 online';
Step-4 : Check REPAIR FAILURE
RMAN> REPAIR FAILURE;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/ORCL/hm/reco_452126040.hm
contents of repair script:
# restore and recover datafile
restore datafile 4;
recover datafile 4;
sql 'alter database datafile 4 online';
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
Starting restore at 16-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=6 STAMP=834322042 file name=+RECO/orcl/datafile/users.290.834321573
destination for restore of datafile 00004: +DATA/orcl/datafile/users.259.818891057
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=+DATA/orcl/datafile/users.259.834324019 RECID=0 STAMP=0
Finished restore at 16-DEC-13
Starting recover at 16-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: +DATA/orcl/datafile/users.259.834324019
channel ORA_DISK_1: reading from backup piece +RECO/orcl/backupset/2013_12_16/nnndn1_incr_update_0.306.834322071
channel ORA_DISK_1: piece handle=+RECO/orcl/backupset/2013_12_16/nnndn1_incr_update_0.306.834322071 tag=INCR_UPDATE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
archived log for thread 1 with sequence 39 is already on disk as file +RECO/orcl/archivelog/2013_12_16/thread_1_seq_39.281.834323373
archived log for thread 1 with sequence 40 is already on disk as file +RECO/orcl/archivelog/2013_12_16/thread_1_seq_40.283.834323373
archived log file name=+RECO/orcl/archivelog/2013_12_16/thread_1_seq_39.281.834323373 thread=1 sequence=39
archived log file name=+RECO/orcl/archivelog/2013_12_16/thread_1_seq_40.283.834323373 thread=1 sequence=40
media recovery complete, elapsed time: 00:00:01
Finished recover at 16-DEC-13
sql statement: alter database datafile 4 online
repair failure complete
Do you want to open the database (enter YES or NO)? yes
database opened
RMAN>
