Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle Data Recovery Advisor

Oracle Database Tips by Donald Burleson

Question:  What is the 11g data recovery advisor?  Is the data recovery advisor a part of RMAN?

Answer: As part of the enhanced intelligent data protection and repair present in Oracle 11g, the Data Recovery Advisor is a new feature. With this advisor, data failures can be automatically diagnosed along with reporting appropriate options for repair. The advisor can also help reduce the amount of time needed to recover.

Also read this article on RMAN data recovery advisor.


Like many other advisors, the two interfaces used are OEM and a command-line API. The command-line API in this case is a bit different than the others because it operates inside of RMAN. Since the commands run in RMAN, this implies that the database is most likely running in archivelog mode, so that can help ensure less data loss.


The basic commands for the Data Recovery Advisor are LIST FAILURE, ADVISE FAILURE, REPAIR FAILURE, and CHANGE FAILURE. Due to the nature of what this advisor is helping the DBA with, it is best to test the functionality on a test database. The database needs to experience a media failure or some type of data loss (corruption) to trigger or put this advisor into action.


Failures have two characteristics. Their status is either OPEN or CLOSED, and their priority is one of CRITICAL, HIGH, or LOW. The Data Recovery Advisor assigns a priority of  CRITICAL or HIGH to diagnosed failures. A priority of CRITICAL is significant; it implies the whole database is at risk. Loss of a control file or a SYSTEM tablespace data file would certainly qualify for this rating.


A HIGH rating means the database can continue on. An example of this would be where a non-system tablespace datafile becomes unavailable. The status in such a case could be downgraded to LOW.


The OEM interface may be slow to present failure information. As an experiment, create a 1MB datafile in its own tablespace on an attached flash drive and then pull the drive. This may terminate the instance. One's access to the database via OEM becomes quite limited. Using host credentials, connect to the database. More precisely, connect to files associated with the database, such as those in the ADR. This means, as an example, that the alert log can be viewed via OEM. The alert log records the following entry.


Figure 8.11:  Alert Log Output


In a scenario such as this, the only viable recourse is to recover the database via a command-line API such as RMAN or via traditional means such as startup mount. As this is now a backup and recovery situation, it goes beyond the scope of this chapter. After restoring the drive, starting the instance, and logging back in to OEM, the advisor now shows the critical failure, but also records it as closed for obvious reasons.


Figure 8.12:  Advisor Output in OEM


After registering the database, or configuring it for use with RMAN, and repeating the drive failure, the instance stays open and the advisor reports the problem.


Figure 8.13:  More Advisor Output


Click on Advise, and the advisor offers details for a manual action.


Figure 8.14:  Manual Actions Shown in OEM


The RMAN version of this interface is shown below. The scenario is the same, meaning the flash drive was pulled to simulate media failure. A "startup mount" is issued. In an RMAN session, connect to the target because the rman user cannot be connected to if using the controlfile as the catalog. Once in RMAN, issue LIST FAILURE and ADVISE FAILURE commands. Note: line returns were added for formatting purposes.


RMAN> list failure;


using target database control file instead of recovery catalog

List of Database Failures



Failure ID Priority Status    Time Detected Summary

---------- -------- --------- ------------- -------

62         HIGH     OPEN      22-SEP-08     One or more non-system

                                            datafiles are missing


RMAN> advise failure;


List of Database Failures



Failure ID Priority Status    Time Detected Summary

---------- -------- --------- ------------- -------

62         HIGH     OPEN      22-SEP-08     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=155 device type=DISK

analyzing automatic repair options complete


Mandatory Manual Actions


1. If file E:\FDRIVE01.DBF was unintentionally renamed or moved,

   restore it

2. Contact Oracle Support Services if the preceding recommendations

   cannot be used, or if they do not fix the failures selected for



Optional Manual Actions


no manual actions available


Automated Repair Options


no automatic repair options available


With this release of Oracle, the Data Recovery Advisor only operates on a single instance. In other words, it is not supported in a RAC environment. However, it can be used on a single instance, so that remains an option.


Overall, the Data Recovery Advisor can help recover a database during a time of crisis. Failure scenarios are stressing, to say the least, and having advice served up by the server, telling what needs to happen, can save time. If the database is in archivelog mode, and backups are available and once the media problem has been addressed, the advisor can preview what will take place during the repair. In an RMAN session, issue a REPAIR FAILURE PREVIEW command and view the output. If satisfactory, initiate the repair with just REPAIR FAILURE. There is also the choice of applying the repair oneself as one can have a repair script generated for oneself, which is essentially what the preview shows anyway.


WARNING: As always with recovery operations, this is something to have practiced beforehand. Learning how to perform recovery during a live event is not the most conducive learning environment and is a recipe for failure. The symbol for this warning is appropriate - the DBA does not want any bombs going off in the recovery process.


Get the Complete
Oracle Utility Information 

The landmark book "Advanced Oracle Utilities The Definitive Reference"  contains over 600 pages of filled with valuable information on Oracle's secret utilities. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.