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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

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

   repair

 

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.