One of the great new features of RMAN in Oracle 11g is the new tool named Data Recovery Advisor. This tool automates the diagnose of the data failure and corruption, advises the recovery steps and performs automatic recovery of the database failure.  This tool could be used from GUI as well as from CLI.

In the following scenario we’ll use Data Recovery Advisor to recover the lost data

–          We have three tablespaces (USERS, USERS02, USERS03)

–          We create two tables on two tablespaces (tbl_test01 on USERS and tbl_test02 on USERS02)

–          We corrupt the datafiles of USERS and USERS02 tablespace and delete the USERS03 datafile

–          Using LIST FAILURE command we see list three data failure (two data block corruption and one missing datafile)

–          We get advice for all these problems and manually restore the third datafile and recover it

–          We use REPAIR FAILURE command to make RMAN automatically repair the data block corruption

Let’s start performing the above scenario

–          Create two new tablespaces (USERS02, USER03) and create two tables on USERS and USERS02 tablespaces.

[sourcecode] SQL> create tablespace users02 datafile ‘C:\APP\ADMINISTRATOR\ORADATA\TT\USERS02.DBF’ size 1m;

Tablespace created.

SQL> create tablespace users03 datafile ‘C:\APP\ADMINISTRATOR\ORADATA\TT\USERS03.DBF’ size 1m;

Tablespace created.

SQL> create table tbl_test01 (name varchar2(10)) tablespace users;

Table created.

SQL> create table tbl_test02 (name varchar2(10)) tablespace users02;

Table created.

SQL> insert into tbl_test01 values(‘my_test01’);

1 row created.

SQL> insert into tbl_test02 values(‘my_test02’);

1 row created.

SQL> commit;

Commit complete.

SQL>

[/sourcecode]

–          Take backup of the database

[sourcecode] RMAN> backup database plus archivelog; [/sourcecode]

–          Corrupt the datafiles using techniques that are described in the Performing Block Media Recovery with RMAN video tutorial. Then flush the buffer cache and query the table. You’ll get “ORA-01578: ORACLE data block corrupted” error. Query the V$DATABASE_BLOCK_CORRUPTION view. You can get an empty result, however after a while Oracle automatically detects and updates the view. Then shutdown the database, delete the datafile that belongs to the USERS03 tablespace, mount the database and use ALTER DATABASE DATAFILE ‘path_of_the_users03.dbf’ OFFLINE; command to make it offline and start the database:

[sourcecode]

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from tbl_test01;

select * from tbl_test01

              *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 4, block # 72)

ORA-01110: data file 4: ‘C:\APP\ADMINISTRATOR\ORADATA\TT\USERS01.DBF’

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from tbl_test02;

select * from tbl_test02

              *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 16)

ORA-01110: data file 5: ‘C:\APP\ADMINISTRATOR\ORADATA\TT\USERS02.DBF’

SQL> select * from v$database_block_corruption;

no rows selected

[/sourcecode]

Wait a while and run the command again:

[sourcecode]

SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO

———- ———- ———- —————— ———

         4         72          1                  0 CHECKSUM

         5         16          1                  0 CHECKSUM

SQL> shut abort

SQL> startup mount;

SQL> alter database datafile ‘C:\APP\ADMINISTRATOR\ORADATA\TT\USERS03.DBF’ offline;

Database altered.

SQL> alter database open;

Database altered.

SQL>

[/sourcecode]

–          Now use LIST FAILURE command to let RMAN gather the data failures you have:

[sourcecode] C:\>rman target /

RMAN> list failure;

using target database control file instead of recovery catalog

List of Database Failures

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

Failure ID Priority Status    Time Detected Summary

———- ——– ——— ————- ——-

328        HIGH     OPEN      20-MAY-10     One or more non-system datafiles are  missing

308        HIGH     OPEN      20-MAY-10     Datafile 5: ‘C:\APP\ADMINISTRATOR\ORADATA\TT\USERS02.DBF’ contains one or more corrupt blocks

122        HIGH     OPEN      20-MAY-10     Datafile 4: ‘C:\APP\ADMINISTRATOR\ORADATA\TT\USERS01.DBF’ contains one or more corrupt blocks

You can get detailed information on any listed failure:

RMAN> list failure 328 detail;

List of Database Failures

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

Failure ID Priority Status    Time Detected Summary

———- ——– ——— ————- ——-

328        HIGH     OPEN      20-MAY-10     One or more non-system datafiles are

 missing

  Impact: See impact for individual child failures

  List of child failures for parent failure ID 328

  Failure ID Priority Status    Time Detected Summary

  ———- ——– ——— ————- ——-

  331        HIGH     OPEN      20-MAY-10     Datafile 6: ‘C:\APP\ADMINISTRATOR\ORADATA\TT\USERS03.DBF’ is missing

    Impact: Some objects in tablespace USERS03 might be unavailable

–          Now use ADVISE FAILURE command to get necessary advises and ready scripts to perform a recovery

RMAN> advise failure all;

List of Database Failures

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

Failure ID Priority Status    Time Detected Summary

———- ——– ——— ————- ——-

328        HIGH     OPEN      20-MAY-10     One or more non-system datafiles ar

 missing

308        HIGH     OPEN      20-MAY-10     Datafile 5: ‘C:\APP\ADMINISTRATOR\O

ADATA\TT\USERS02.DBF’ contains one or more corrupt blocks

122        HIGH     OPEN      20-MAY-10     Datafile 4: ‘C:\APP\ADMINISTRATOR\O

ADATA\TT\USERS01.DBF’ contains one or more corrupt blocks

analyzing automatic repair options; this may take some time

using channel ORA_DISK_1

analyzing automatic repair options complete

Mandatory Manual Actions

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

no manual actions available

Optional Manual Actions

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

1. If file C:\APP\ADMINISTRATOR\ORADATA\TT\USERS03.DBF was unintentionally renaed or moved, restore it

Automated Repair Options

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

Option Repair Description

—— ——————

1      Restore and recover datafile 6; Perform block media recovery of block 16 in file 5; Perform block media recovery of block 72 in file 4

  Strategy: The repair includes complete media recovery with no data loss

  Repair script: c:\app\administrator\diag\rdbms\tt\tt\hm\reco_3231280737.hm

RMAN>

[/sourcecode]

So we have a detailed information on what we have and how we can perform a recovery. We need to restore and recover the datafile 6 and perform block media recovery on datafile 4 and 5. RMAN created a script which could be run to perform the whole recovery. Here’s the source of the script:

[sourcecode]

   # restore and recover datafile

   sql ‘alter database datafile 6 offline’;

   restore datafile 6;

   recover datafile 6;

   sql ‘alter database datafile 6 online’;

   # block media recovery

   recover datafile 5 block 16

   datafile 4 block 72;

[/sourcecode]

Let’s perform the first action manually. So run the following commands in RMAN

[sourcecode] RMAN>    sql ‘alter database datafile 6 offline’;

RMAN>    restore datafile 6;

RMAN>    recover datafile 6;

RMAN>    sql ‘alter database datafile 6 online’;

Now use ADVISE FAILURE command again.  It will diagnose the failures and update the result:

RMAN> advise failure all;

List of Database Failures

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

Failure ID Priority Status    Time Detected Summary

———- ——– ——— ————- ——-

308        HIGH     OPEN      20-MAY-10     Datafile 5: ‘C:\APP\ADMINISTRATOR\OR

ADATA\TT\USERS02.DBF’ contains one or more corrupt blocks

122        HIGH     OPEN      20-MAY-10     Datafile 4: ‘C:\APP\ADMINISTRATOR\OR

ADATA\TT\USERS01.DBF’ contains one or more corrupt blocks

analyzing automatic repair options; this may take some time

using channel ORA_DISK_1

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      Perform block media recovery of block 16 in file 5; Perform block media recovery of block 72 in file 4

  Strategy: The repair includes complete media recovery with no data loss

  Repair script: c:\app\administrator\diag\rdbms\tt\tt\hm\reco_1778061078.hm

[/sourcecode]

–          Now let’s preview the repair plan of RMAN and repair all data. For this, use REPAIR FAILURE PREVIEW command and REPAIR FAILURE as follows:

[sourcecode]

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss

Repair script: c:\app\administrator\diag\rdbms\tt\tt\hm\reco_1778061078.hm

contents of repair script:

   # block media recovery

   recover datafile 5 block 16

   datafile 4 block 72;

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script: c:\app\administrator\diag\rdbms\tt\tt\hm\reco_1778061078.hm

contents of repair script:

   # block media recovery

   recover datafile 5 block 16

   datafile 4 block 72;

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

executing repair script

Starting recover at 20-MAY-10

using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)

channel ORA_DISK_1: specifying block(s) to restore from backup set

restoring blocks of datafile 00005

restoring blocks of datafile 00004

<…output trimmed ….>

<…output trimmed ….>

starting media recovery

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

Finished recover at 20-MAY-10

repair failure complete

RMAN>

[/sourcecode]

–          Now query the tables:

[sourcecode]

SQL> select * from tbl_test01;

NAME

———-

my_test01

SQL> select * from tbl_test02;

NAME

———-

my_test02

SQL>

[/sourcecode]