Thursday, June 10, 2010

RMAN 11G : Data Recovery Advisor - RMAN command line example

The Data Recovery Advisor is a tool that helps you to diagnose and repair data failures and corruptions. The Data Recovery Advisor analyzes failures based on symptoms and intelligently determines optimal repair strategies. The tool can also automatically repair diagnosed failures.

The Data Recovery Advisor is available from Enterprise Manager (EM) Database Control and Grid Control. You can also use it via the RMAN command-line.

In this example you will see examples of via the RMAN command line utilising the DRA commands:

Three DRA commands are available within RMAN:

List Failure - lists the results of previously executed failure assessments. Revalidates existing failures and closes them, if possible.

Advise Failure - presents manual and automatic repair options

Repair Failure - automatically fix failures by running optimal repair option, suggested by ADVISE FAILURE. Revalidates existing failures when completed.

Below is a demonstration of DRA.

1. Take Full RMAN backup;

2. RMAN> validate check logical datafile 2;
Show that the datafile is free from corruption.

RMAN> validate check logical datafile 2;

Starting validate at 2008/11/10 09:52:36
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=132 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=/oradata/V11/sysaux01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:25
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 28279 104896 2885460
File Name: /oradata/V11/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 21440
Index 0 24664
Other 0 30513

Finished validate at 2008/11/10 09:53:02

3. Corrupt several Blocks within the sysaux tablespace

SQL> @corrupt_blocks.sql

4. RMAN> validate check logical datafile 2;

This should show the corruption and indicate the # of blocks corrupted.

RMAN> validate check logical datafile 2;

Starting validate at 2008/11/10 09:57:05
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=/oradata/V11/sysaux01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 FAILED 0 28273 104896 2894632
File Name: /oradata/V11/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 21440
Index 0 24663
Other 10 30520


validate found one or more corrupt blocks
See trace file /u01/app/oracle/product/11.1.0/db_1/diag/rdbms/v11/V11/trace/V11_ora_22522.trc for details
Finished validate at 2008/11/10 09:57:12


Review: /u01/app/oracle/product/11.1.0/db_1/diag/rdbms/v11/V11/trace/V11_ora_22522.trc
Open the trace file to show the errors that have been picked up.


Corrupt block relative dba: 0x00800024 (file 2, block 36)
Completely zero block found during validation
Reread of blocknum=36, file=/oradata/V11/sysaux01.dbf. found same corrupt data
Reread of blocknum=36, file=/oradata/V11/sysaux01.dbf. found same corrupt data
Reread of blocknum=36, file=/oradata/V11/sysaux01.dbf. found same corrupt data
Reread of blocknum=36, file=/oradata/V11/sysaux01.dbf. found same corrupt data
Reread of blocknum=36, file=/oradata/V11/sysaux01.dbf. found same corrupt data
Hex dump of (file 2, block 37)
We can now issue list failure & list failure detail to get more about the issue.

5. RMAN> list failure;
RMAN> list failure ### detail; ( where ### equlas the failure number)


RMAN> list failure;

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
351261 HIGH OPEN 2008/11/10 09:56:33 Datafile 2: '/oradata/V11/sysa
ux01.dbf' contains one or more corrupt blocks

RMAN> list failure 351261 detail;

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
351261 HIGH OPEN 2008/11/10 09:56:33 Datafile 2: '/oradata/V11/sysa
ux01.dbf' contains one or more corrupt blocks
Impact: Some objects in tablespace might be unavailable
List of child failures for parent failure ID 351261
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
351261 HIGH OPEN 2008/11/10 09:56:33 Datafile 2: '/oradata/V11/sysa
ux01.dbf' contains one or more corrupt blocks
Impact: Some objects in tablespace might be unavailable
List of child failures for parent failure ID 351261
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
352028 HIGH OPEN 2008/11/10 09:56:35 Block 37 in datafile 2: '/or
adata/V11/sysaux01.dbf' is media corrupt

At this point you are able to issue the advise keyword which will prompt DBA will be advise on how to solve the current scenario.

6. RMAN> Advise Failure;

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
351261 HIGH OPEN 2008/11/10 09:56:33 Datafile 2: '/oradata/V11/sysa
ux01.dbf' contains one or more corrupt blocks
Impact: Some objects in tablespace might be unavailable
List of child failures for parent failure ID 351261
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
352028 HIGH OPEN 2008/11/10 09:56:35 Block 37 in datafile 2: '/or
adata/V11/sysaux01.dbf' is media corrupt
..
..
..
Impact: Object I_SMB$CONFIG_PKEY owned by SYS might be unavailable
351974 HIGH OPEN 2008/11/10 09:56:33 Block 28 in datafile 2: '/or
adata/V11/sysaux01.dbf' is media corrupt
Impact: Object I_SMB$CONFIG_PKEY owned by SYS might be unavailable

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 a standby database is available, then consider a Data Guard switchover or failover

Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Recover multiple corrupt blocks in datafile 2

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

Repair script: /u01/app/oracle/product/11.1.0/db_1/diag/rdbms/v11/V11/hm/reco_189110358.hm

RMAN>

Review File: : /u01/app/oracle/product/11.1.0/db_1/diag/rdbms/v11/V11/hm/reco_189110358.hm

[oracle@aulnx7 hm]$ vi reco_189110358.hm

# block media recovery for multiple blocks
recover datafile 2 block 28 to 37;


As can be seen above one option is to perform rman block recovery. The next step for the DBA is to proceed with the repair preview.


7. RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/product/11.1.0/db_1/diag/rdbms/v11/V11/hm/reco_42
19629556.hm

contents of repair script:
# block media recovery for multiple blocks
recover datafile 2 block 28 to 37;
Then you are ready to perform the fix.

8. RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/product/11.1.0/db_1/diag/rdbms/v11/V11/hm/reco_42
19629556.hm

contents of repair script:
# block media recovery for multiple blocks
recover datafile 2 block 28 to 37;

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

Starting recover at 2008/11/10 10:18:48
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 00002
channel ORA_DISK_1: reading from backup piece /recovery_area/V11/backupset/2008_
11_10/o1_mf_nnndf_TAG20081110T093808_4kgslm1w_.bkp
channel ORA_DISK_1: piece handle=/recovery_area/V11/backupset/2008_11_10/o1_mf_n
nndf_TAG20081110T093808_4kgslm1w_.bkp tag=TAG20081110T093808
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:35

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

Finished recover at 2008/11/10 10:19:27
repair failure complete

RMAN>
At this point the the blocks have been fully recovered.

No comments: