Data Recovery Advisor(DRA)

Oracle 11g come up with one new cool feature for Database Backup & recovery called DRA (Data Recovery Advisor) which help us to recover the database without any trouble with few RMAN commands.

What is Data Recovery Advisor?

DRA is an oracle database tool that automatically diagnoses data failures, determines and presents appropriate repair options and executes repairs at user requests.

The following RMAN commands are use to perform Data Recovery Advisor.

1. List Failure

2. Advise Failure

3. Repair Failure

4. Change Failure

1. List Failure:     List failure command gives us information regarding failures and the effect of these on database operations. Each failures uniquely identified by failure number.

2. Advise Failure:   Advise failure give us advise for how to deal with failure against database means advise failure give us solution for particular failure.

3. Repair failure:  Repair failure command gives us "rman generated scrits" which restore and recover database from backup.

4. Change failure:  Change failure is RMAN command which change the failure status or priority.

Like there is two status : OPEN or CLOSED and Priority is HIGH or LOW.

If some failure is rman showing HIGH and we want to change it to LOW then using change failure command we can change it.

1. Suppose I lost my system data file.

2. Now I am not worry because I have database backup with 11g database.

3. I need to just connect with RMAN, after connect with RMAN I do the following task.

RMAN>      List failure;

Using target database control file instead of recovery catalog

List of Database Failures

=========================
Failure ID Priority Status Time Detected Summary

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

602 CRITICAL OPEN 26-JUL-08 System datafile 1: 'C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF' is missing

After got the failure description we can get "advise" from oracle about failure through advice failure command.

RMAN>     Advise failure;

List of Database Failures

=========================
Failure ID Priority Status Time Detected Summary

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

602 CRITICAL OPEN 26-JUL-08 System datafile 1: 'C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF' is missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=152 device type=DISK

analyzing automatic repair options complete
Mandatory Manual Actions

========================

no manual actions available
Optional Manual Actions

=======================

1. If file C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF was unintentionally renamed or moved, restore it
Automated Repair Options

========================

Option Repair Description

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

1 Restore and recover datafile 1

Strategy: The repair includes complete media recovery with no Data loss Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_2508517227.hm

Above is rman advise regarding particular failure if above suggested repair option is helpful and fix the current problem then ok otherwise need to call oracle support services.

now check oracle suggested repair options or scripts.

RMAN>   repair failure preview;

Strategy: The repair includes complete media recovery with no data loss Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_2508517227.hm

contents of repair script:

# restore and recover datafile

restore datafile 1;

recover datafile 1;

Above is suggested script from RMAN to restore and recover database for particular failure, if suppose we want to use above script then again run "repair failure" command without 'preview' keyword.

RMAN>   repair failure ;
Strategy: The repair includes complete media recovery with no data loss

Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_2508517227.hm

contents of repair script:

# restore and recover datafile

restore datafile 1;

recover datafile 1;

Do you really want to execute the above repair (enter YES or NO)? YES

executing repair script
Starting restore at 26-JUL-08

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 00001 to C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF

channel ORA_DISK_1: reading from backup piece C:\APP\M.TAJ\PRODUCT\11.1.0\DB_1\DATABASE\05JMEU48_1_1

channel ORA_DISK_1: piece handle=C:\APP\M.TAJ\PRODUCT\11.1.0\DB_1\DATABASE\05JMEU48_1_1 tag=TAG20080726T124808

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:05:25

Finished restore at 26-JUL-08

Starting recover at 26-JUL-08

using channel ORA_DISK_1
starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished recover at 26-JUL-08

If we lost "tempfiles"  in 10gr1 we need to manually RE-CREATE temporary tablespace but in 11g it is automatically done by ORACLE .