RMAN Data Recovery Advisor in Oracle 11g – scenario based explanation

By Kamran Agayev A.

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.

01 SQL> create tablespace users02 datafile 'C:\APP\ADMINISTRATOR\ORADATA\TT\USERS02.DBF' size 1m;
02  
03 Tablespace created.
04  
05 SQL> create tablespace users03 datafile 'C:\APP\ADMINISTRATOR\ORADATA\TT\USERS03.DBF' size 1m;
06  
07 Tablespace created.
08  
09 SQL> create table tbl_test01 (name varchar2(10)) tablespace users;
10  
11 Table created.
12  
13 SQL> create table tbl_test02 (name varchar2(10)) tablespace users02;
14  
15 Table created.
16  
17 SQL> insert into tbl_test01 values('my_test01');
18  
19 1 row created.
20  
21 SQL> insert into tbl_test02 values('my_test02');
22  
23 1 row created.
24  
25 SQL> commit;
26  
27 Commit complete.
28  
29 SQL>

Take backup of the database

1 RMAN> backup database plus archivelog;

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:

01 SQL> alter system flush buffer_cache;
02  
03 System altered.
04  
05 SQL> select * from tbl_test01;
06  
07 select * from tbl_test01
08  
09               *
10  
11 ERROR at line 1:
12  
13 ORA-01578: ORACLE data block corrupted (file # 4, block # 72)
14  
15 ORA-01110: data file 4: 'C:\APP\ADMINISTRATOR\ORADATA\TT\USERS01.DBF'
16  
17 SQL> alter system flush buffer_cache;
18  
19 System altered.
20  
21 SQL> select * from tbl_test02;
22  
23 select * from tbl_test02
24  
25               *
26  
27 ERROR at line 1:
28  
29 ORA-01578: ORACLE data block corrupted (file # 5, block # 16)
30  
31 ORA-01110: data file 5: 'C:\APP\ADMINISTRATOR\ORADATA\TT\USERS02.DBF'
32  
33 SQL> select * from v$database_block_corruption;
34  
35 no rows selected

Wait a while and run the command again:

01 SQL> select * from v$database_block_corruption;
02  
03      FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
04  
05 ---------- ---------- ---------- ------------------ ---------
06  
07          4         72          1                  0 CHECKSUM
08  
09          5         16          1                  0 CHECKSUM
10  
11 SQL> shut abort
12  
13 SQL> startup mount;
14  
15 SQL> alter database datafile 'C:\APP\ADMINISTRATOR\ORADATA\TT\USERS03.DBF' offline;
16  
17 Database altered.
18  
19 SQL> alter database open;
20  
21 Database altered.
22  
23 SQL>

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

001 C:\>rman target /
002  
003 RMAN> list failure;
004  
005 using target database control file instead of recovery catalog
006  
007 List of Database Failures
008  
009 =========================
010  
011 Failure ID Priority Status    Time Detected Summary
012  
013 ---------- -------- --------- ------------- -------
014  
015 328        HIGH     OPEN      20-MAY-10     One or more non-system datafiles are  missing
016  
017 308        HIGH     OPEN      20-MAY-10     Datafile 5: 'C:\APP\ADMINISTRATOR\ORADATA\TT\USERS02.DBF' contains one or more corrupt blocks
018  
019 122        HIGH     OPEN      20-MAY-10     Datafile 4: 'C:\APP\ADMINISTRATOR\ORADATA\TT\USERS01.DBF' contains one or more corrupt blocks
020  
021 You can get detailed information on any listed failure:
022  
023 RMAN> list failure 328 detail;
024  
025 List of Database Failures
026  
027 =========================
028  
029 Failure ID Priority Status    Time Detected Summary
030  
031 ---------- -------- --------- ------------- -------
032  
033 328        HIGH     OPEN      20-MAY-10     One or more non-system datafiles are
034  
035  missing
036  
037   Impact: See impact for individual child failures
038  
039   List of child failures for parent failure ID 328
040  
041   Failure ID Priority Status    Time Detected Summary
042  
043   ---------- -------- --------- ------------- -------
044  
045   331        HIGH     OPEN      20-MAY-10     Datafile 6: 'C:\APP\ADMINISTRATOR\ORADATA\TT\USERS03.DBF' is missing
046  
047     Impact: Some objects in tablespace USERS03 might be unavailable
048  
049 -          Now use ADVISE FAILURE command to get necessary advises and ready scripts to perform a recovery
050  
051 RMAN> advise failure all;
052  
053 List of Database Failures
054  
055 =========================
056  
057 Failure ID Priority Status    Time Detected Summary
058  
059 ---------- -------- --------- ------------- -------
060  
061 328        HIGH     OPEN      20-MAY-10     One or more non-system datafiles ar
062  
063  missing
064  
065 308        HIGH     OPEN      20-MAY-10     Datafile 5: 'C:\APP\ADMINISTRATOR\O
066  
067 ADATA\TT\USERS02.DBF' contains one or more corrupt blocks
068  
069 122        HIGH     OPEN      20-MAY-10     Datafile 4: 'C:\APP\ADMINISTRATOR\O
070  
071 ADATA\TT\USERS01.DBF' contains one or more corrupt blocks
072  
073 analyzing automatic repair options; this may take some time
074  
075 using channel ORA_DISK_1
076  
077 analyzing automatic repair options complete
078  
079 Mandatory Manual Actions
080  
081 ========================
082  
083 no manual actions available
084  
085 Optional Manual Actions
086  
087 =======================
088  
089 1. If file C:\APP\ADMINISTRATOR\ORADATA\TT\USERS03.DBF was unintentionally renaed or moved, restore it
090  
091 Automated Repair Options
092  
093 ========================
094  
095 Option Repair Description
096  
097 ------ ------------------
098  
099 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
100  
101   Strategy: The repair includes complete media recovery with no data loss
102  
103   Repair script: c:\app\administrator\diag\rdbms\tt\tt\hm\reco_3231280737.hm
104  
105 RMAN>

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:

01    # restore and recover datafile
02  
03    sql 'alter database datafile 6 offline';
04  
05    restore datafile 6;
06  
07    recover datafile 6;
08  
09    sql 'alter database datafile 6 online';
10  
11    # block media recovery
12  
13    recover datafile 5 block 16
14  
15    datafile 4 block 72;

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

01 RMAN>    sql 'alter database datafile 6 offline';
02  
03 RMAN>    restore datafile 6;
04  
05 RMAN>    recover datafile 6;
06  
07 RMAN>    sql 'alter database datafile 6 online';
08  
09 Now use ADVISE FAILURE command again.  It will diagnose the failures and update the result:
10  
11 RMAN> advise failure all;
12  
13 List of Database Failures
14  
15 =========================
16  
17 Failure ID Priority Status    Time Detected Summary
18  
19 ---------- -------- --------- ------------- -------
20  
21 308        HIGH     OPEN      20-MAY-10     Datafile 5: 'C:\APP\ADMINISTRATOR\OR
22  
23 ADATA\TT\USERS02.DBF' contains one or more corrupt blocks
24  
25 122        HIGH     OPEN      20-MAY-10     Datafile 4: 'C:\APP\ADMINISTRATOR\OR
26  
27 ADATA\TT\USERS01.DBF' contains one or more corrupt blocks
28  
29 analyzing automatic repair options; this may take some time
30  
31 using channel ORA_DISK_1
32  
33 analyzing automatic repair options complete
34  
35 Mandatory Manual Actions
36  
37 ========================
38  
39 no manual actions available
40  
41 Optional Manual Actions
42  
43 =======================
44  
45 no manual actions available
46  
47 Automated Repair Options
48  
49 ========================
50  
51 Option Repair Description
52  
53 ------ ------------------
54  
55 1      Perform block media recovery of block 16 in file 5; Perform block media recovery of block 72 in file 4
56  
57   Strategy: The repair includes complete media recovery with no data loss
58  
59   Repair script: c:\app\administrator\diag\rdbms\tt\tt\hm\reco_1778061078.hm

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:

01 RMAN> repair failure preview;
02  
03 Strategy: The repair includes complete media recovery with no data loss
04  
05 Repair script: c:\app\administrator\diag\rdbms\tt\tt\hm\reco_1778061078.hm
06  
07 contents of repair script:
08  
09    # block media recovery
10  
11    recover datafile 5 block 16
12  
13    datafile 4 block 72;
14  
15 RMAN> repair failure;
16  
17 Strategy: The repair includes complete media recovery with no data loss
18  
19 Repair script: c:\app\administrator\diag\rdbms\tt\tt\hm\reco_1778061078.hm
20  
21 contents of repair script:
22  
23    # block media recovery
24  
25    recover datafile 5 block 16
26  
27    datafile 4 block 72;
28  
29 Do you really want to execute the above repair (enter YES or NO)? YES
30  
31 executing repair script
32  
33 Starting recover at 20-MAY-10
34  
35 using channel ORA_DISK_1
36  
37 channel ORA_DISK_1: restoring block(s)
38  
39 channel ORA_DISK_1: specifying block(s) to restore from backup set
40  
41 restoring blocks of datafile 00005
42  
43 restoring blocks of datafile 00004
44  
45 <...output trimmed ....>
46  
47 <...output trimmed ....>
48  
49 starting media recovery
50  
51 media recovery complete, elapsed time: 00:00:07
52  
53 Finished recover at 20-MAY-10
54  
55 repair failure complete
56  
57 RMAN>

Now query the tables:

01 SQL> select * from tbl_test01;
02  
03 NAME
04  
05 ----------
06  
07 my_test01
08  
09 SQL> select * from tbl_test02;
10  
11 NAME
12  
13 ----------
14  
15 my_test02
16  
17 SQL>