Hello everyone!
During my career as an Oracle DBA, I saw many DBAs using this tool, the Data Recovery Advisor.
This is a tool that can assist with diagnosing media failure, providing RMAN commands to solved the problems.
Let’s start:
The commands used on Data Recovery Advisor are:
list failure
advise failure
repair failure
Well, you suspect that have a media failure in your database (perhaps a bad guy removed your data file). You will use the Data Recovery Advisor to list the media failure.
Log into RMAN prompt:
[oracle@srvdb12c ~]$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Thu May 26 23:31:17 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=886153789) RMAN> list failure; using target database control file instead of recovery catalog Database Role: PRIMARY List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 362 HIGH OPEN 26-MAY-16 One or more non-system datafiles are missing
Ohh!! You have a great problem there! This output indicates that one or more datafile is missing.
When you are using the Data Recovery Advisor, the first command that you should run is the list failure.
In case of you suspect a failure in your database that Data Recovery Advisor do not detect you can run the following command:
RMAN> validade database;
Take care, depending on size of your database this command could take a long time running.
To take more details about the failure you can run command with the ID of the failure 362
RMAN> list failure 362 detail;
Database Role: PRIMARY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
362 HIGH OPEN 26-MAY-16 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 362
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
620 HIGH OPEN 26-MAY-16 Datafile 24: '/u01/app/oracle/oradata/CDB1/PDB/APP01/dfapp01.dbf' is missing
Impact: Some objects in tablespace TBSAPP01 might be unavailable
Now run the advise failure:
RMAN> advise failure;
Database Role: PRIMARY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
362 HIGH OPEN 26-MAY-16 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 362
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
620 HIGH OPEN 26-MAY-16 Datafile 24: '/u01/app/oracle/oradata/CDB1/PDB/APP01/dfapp01.dbf' is missing
Impact: Some objects in tablespace TBSAPP01 might be unavailable
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 file /u01/app/oracle/oradata/CDB1/PDB/APP01/dfapp01.dbf was unintentionally renamed or moved, restore it
2. Automatic repairs may be available if you shutdown the database and restart it in mount mode
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 24
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/cdb1/CDB1/hm/reco_3458950717.hm
How you can see the tool already created the script for you, solve the problem, now run the repair failure:
RMAN> repair failure; Strategy: The repair includes complete media recovery with no data loss Repair script: /u01/app/oracle/diag/rdbms/cdb1/CDB1/hm/reco_3458950717.hm contents of repair script: # restore and recover datafile restore ( datafile 24 ); recover datafile 24; Do you really want to execute the above repair (enter YES or NO)? yes executing repair script Starting restore at 26-MAY-16 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 00024 to /u01/app/oracle/oradata/CDB1/PDB/APP01/dfapp01.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/PDBBKPS/backupset/2016_05_26/o1_mf_nnndf_TAG20160526T215516_cnhb847t_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/PDBBKPS/backupset/2016_05_26/o1_mf_nnndf_TAG20160526T215516_cnhb847t_.bkp tag=TAG20160526T215516 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 Finished restore at 26-MAY-16 Starting recover at 26-MAY-16 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 26-MAY-16 repair failure complete
The output shows that the command was run with success, you can check if the problem remains:
RMAN> list failure; Database Role: PRIMARY no failures found that match specification
Always run these commands in order, without exiting RMAN between each command:
list failure
advise failure
repair failure
I hope that you enjoy!
