Using Oracle Data Recovery Advisory for database backup and recovery

Read this tip from DBA Matthew Morris on using Oracle Data Recovery Advisory to identify and fix data recovery failures.

Oracle Data Recovery Advisor can automatically detect data failures, assess the extent and impact, and devise appropriate fixes. It can also perform the steps required to repair the damage. The recovery advisor is integrated with the Enterprise Manager Support Workbench, Health Monitor and RMAN. It can be accessed from either Enterprise Manager or RMAN. For databases in a RAC configuration, data recovery must be performed using the RMAN interface.

The following terms are associated with Oracle Data Recovery Advisor:

  • Health check -- The Health Monitor executes health checks to determine the state of the database. They can be invoked either reactively when an error occurs or manually by a database administrator.
  • Failure -- This is a persistent data corruption detected by a health check.
  • Repair -- This is an action that will fix one or more failures.

Failures are normally detected reactively when the database encounters data corruption during normal operations. A health check automatically executes as part of the error-handling process. The check will search the database for failures related to the error and record any findings in the Automatic Diagnostic Repository. Once failures have been detected and stored in the ADR, Oracle Data Recovery Advisor can generate repair advice and execute repair operations on demand. It can report on and repair failures such as inaccessible files, physical and logical block corruptions, and I/O failures. Every failure is assigned both a priority (LOW, HIGH or CRITICAL) and a status (OPEN or CLOSED).

  • CRITICAL priority failures make the whole database unavailable and require immediate attention. Critical failures generally are diagnosed during startup and cause the instance to abort.
  • HIGH priority failures make a database partially unavailable or unrecoverable. Normally they must to be repaired in a relatively short time frame.
  • LOW priority indicates that a failure can be ignored until more important failures are fixed.

Repairs can often be performed in more than one fashion, and Data Recovery Advisor allows you to view repair options. Repairs might involve the use of Oracle Flashback Database, block media recovery or data file media recovery. Normally both automated and manual repair options will be available. When an automated repair is used, Data Recovery Advisor performs the repair, verifies that it was successful and closes all relevant failures.

The recommended workflow for repairing data failures from RMAN is to run the following commands in sequence during an RMAN session: LIST FAILURE to display failures, ADVISE FAILURE to display repair options and REPAIR FAILURE to fix the failures.


The LIST FAILURE command displays failures against which you can run the ADVISE FAILURE and REPAIR FAILURE commands.


The ADVISE FAILURE command prints a summary of the failures identified by the Oracle Data Recovery Advisor, displays repair options and implicitly closes all open failures that are already fixed. The repair strategy suggested by the ADVISE FAILURE command is what the Data Recovery Advisor considers the optimal solution for the given failure or failures. The feasibility of performing the suggested repair is verified by Oracle Data Recovery Advisor. For example, before proposing a solution involving media recovery, it will check that all required backups and archived redo log files are available.

When there are multiple failures, the ADVISE command will consider all of them when creating an optimized set of repair steps. When possible, Data Recovery Advisor consolidates the required fixes into a single repair. For example, if the database has a missing control file, a corrupted data file, and has lost the current redo log group, Data Recovery Advisor would suggest a consolidated repair plan to restore the database and perform point-in-time recovery.


The CHANGE FAILURE command allows you to change the failure priority from HIGH to LOW, LOW to HIGH or to close it. You cannot change to or from CRITICAL priority.



Failures that the Oracle Data Recovery Advisor identifies can be fixed using the REPAIR FAILURE command. In order to use REPAIR FAILURE, the following three conditions must be met for the database to be repaired:

Only a single RMAN session should run the REPAIR FAILURE command. The only exception to this rule is REPAIR FAILURE ... PREVIEW, which can be executed in concurrent RMAN sessions. When performing an automated repair, Oracle Data Recovery Advisor may require specific backups and archived redo logs. If the files are not available, then the recovery will not be possible. REPAIR FAILURE will perform an implicit ADVISE FAILURE if one has not yet been executed in the current RMAN session. Before beginning the repair, RMAN verifies which failures are still relevant and automatically closes any that have been repaired. Once it has completed the repair steps, RMAN reevaluates any other open failures to determine if the repair fixed them as well.

Recovering from data corruption or lost files has traditionally been an activity that is both time-consuming and nerve-wracking. More than one database has been further damaged by a botched recovery. The Data Recovery Advisor has made this process faster, simpler and safer.

About the author:
Matthew Morris is a database engineer for Computer Sciences Corp. in Orlando, Fla. For over 17 years he has worked with the Oracle Database as a support engineer, database administrator, developer and architect.

Dig Deeper on Oracle database backup and recovery