Oracle Data Recovery Advisor

Oracle 11g come up with one new cool feature for Database Backup & Recovery. This is 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 repaires at user requrests.

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

1. List Failure

2. Advise Failure

4. Repair Failure

5. Change Failure

1. List Failure

List failure command give 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 give 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.

Let see some pratical

1. Suppose I lost my system datafile.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: 'C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF'

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

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

1. Using list failure command checking the failure description

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 advise 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 lossRepair 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

repair failure complete

Do you want to open the database (enter YES or NO)? yes

database opened

Database is now recovered and open for normal operation. So it is very easy to restore and recover database using Data Recovery Advisor feature.

same like above procedure for the system datafile we can restore or recover "undo file" & other "application datafiles".

If we lost one of controlfile or all controlfiles then use the following procedure.

ORA-00205: error in identifying control file, check alert log for more info

I am getting above error during startup database then I connect to rman and check "list failure".

RMAN> list failure;
using target database control file instead of recovery catalog

List of Database Failures

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

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

732 CRITICAL OPEN 26-JUL-08 Control file C:\APP\M.TAJ\ORADATA\TEST\CONTROL01.CTL is missing

Then I check "advise" from rman regarding above error

RMAN> advise failure;
List of Database Failures

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

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

732 CRITICAL OPEN 26-JUL-08 Control file C:\APP\M.TAJ\ORADATA\TEST\CONTROL01.CTL is missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=151 device type=DISK

RMAN-06495: must explicitly specify DBID with SET DBID command

analyzing automatic repair options complete
Mandatory Manual Actions

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

no manual actions available
Optional Manual Actions

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

no manual actions available
Automated Repair Options

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

Option Repair Description

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

1 Use a multiplexed copy to restore control file C:\APP\M.TAJ\ORADATA\TEST\CONTROL01.CTL

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

Then I check repair preview for the above failure.

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_2234784495.hm

contents of repair script:

# restore control file using multiplexed copy

restore controlfile from 'C:\APP\M.TAJ\ORADATA\TEST\CONTROL02.CTL';

sql 'alter database mount';

And finally I apply the above suggested script by RMAN.

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_2234784495.hm

contents of repair script:

# restore control file using multiplexed copy

restore controlfile from 'C:\APP\M.TAJ\ORADATA\TEST\CONTROL02.CTL';

sql 'alter database mount';

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: copied control file copy

output file name=C:\APP\M.TAJ\ORADATA\TEST\CONTROL01.CTL

output file name=C:\APP\M.TAJ\ORADATA\TEST\CONTROL02.CTL

output file name=C:\APP\M.TAJ\ORADATA\TEST\CONTROL03.CTL

Finished restore at 26-JUL-08
sql statement: alter database mount

released channel: ORA_DISK_1

repair failure complete

Do you want to open the database (enter YES or NO)? YES

database opened

Now if we lost all or one of redolog file then use the following procedure

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: 'C:\APP\M.TAJ\ORADATA\TEST\REDO01.LOG'

During startup I am getting above error then after I connect to RMAN and perform 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

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

781 CRITICAL OPEN 26-JUL-08 Redo log group 2 is unavailable

775 CRITICAL OPEN 26-JUL-08 Redo log group 1 is unavailable

784 HIGH OPEN 26-JUL-08 Redo log file C:\APP\M.TAJ\ORADATA\TEST\REDO02.LOG is missing

778 HIGH OPEN 26-JUL-08 Redo log file C:\APP\M.TAJ\ORADATA\TEST\REDO01.LOG is missing

After that I check advise from RMAN regarding above failure list.

RMAN> advise failure;
List of Database Failures

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

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

781 CRITICAL OPEN 26-JUL-08 Redo log group 2 is unavailable

775 CRITICAL OPEN 26-JUL-08 Redo log group 1 is unavailable

784 HIGH OPEN 26-JUL-08 Redo log file C:\APP\M.TAJ\ORADATA\TEST\REDO02.LOG is missing

778 HIGH OPEN 26-JUL-08 Redo log file C:\APP\M.TAJ\ORADATA\TEST\REDO01.LOG is missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=148 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\REDO02.LOG was unintentionally renamed or moved, restore it

2. If file C:\APP\M.TAJ\ORADATA\TEST\REDO01.LOG was unintentionally renamed or moved, restore it
Automated Repair Options

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

Option Repair Description

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

1 Recover database

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

Then finally I apply the suggested RMAN advise means "repair failure".

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_4228591735.hm
contents of repair script:

# recover database until cancel and open resetlogs

sql 'alter database recover database until cancel';

alter database open resetlogs;
Do you really want to execute the above repair (enter YES or NO)? YES

executing repair script
sql statement: alter database recover database until cancel
database opened

repair failure complete

So what you say, it is one of cool feature with 11g :)

now up to we see how to perform recovery against "controlfile" & "datafile" & "redolog" file suppose if we lost "tempfiles" then what Oracle suggest. let see

Oracle 11g

SQL> conn sys as sysdba

Enter password:

Connected.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> host del C:\app\m.taj\oradata\test\TEMP*.dbf
SQL> --In above statement I deleted the TEMPORARY files from filesystem.

SQL> startup

ORACLE instance started.
Total System Global Area 535662592 bytes

Fixed Size 1334380 bytes

Variable Size 201327508 bytes

Database Buffers 327155712 bytes

Redo Buffers 5844992 bytes

Database mounted.

Database opened.

Database is open without any error but when i checked alertlog file then i found following entries.

Re-creating tempfile C:\APP\M.TAJ\ORADATA\TEST\TEMP01.DBF

Above line meaning is Oracle Automatically Created Missing Tempfile during startup but this is not happen with Oracle 10gr1 (tested), Don't know about 10gr2 (Didn't test)

When I deleted tempfile manually in 10gr1 then after database open without error during startup I found following entires in alertlog file

Oracle10gr1

Sat Jul 26 19:27:04 2008

Errors in file f:\oracle\product\10.1.0\admin\test\bdump\test_dbw0_3292.trc:

ORA-01186: file 202 failed verification tests

ORA-01157: cannot identify/lock data file 202 - see DBWR trace file

ORA-01110: data file 202: 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\TEMPFILE01.DBF'

File 202 not verified due to error ORA-01157

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