Repair block corruptions using Data Recovery Advisor in Oracle 11g

The data recovery advisor introduced in Oracle 11g and using this tool you check the block corruptions and loss of data files and control files. The GUI feature also available from Enterprise manager.

By default RMAN checks for physically corrupted blocks automatically with every backup (backup set or image copy) it creates. You can check the logical corruption using CHECK LOGICAL command. Using VALIDATE keyword it checks all blocks and it will not create any backup.

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;
Starting backup at 28-SEP-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
. . .

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
16 OK 0 194907 2048000 1045096118
File Name: /dbs1/orcl/users.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 877717
Index 0 201038
Other 0 774338

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
21 FAILED 0 169474 2048000 1046708826
File Name: /dbs1/orcl/tools.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 847248
Index 0 358482
Other 1 672796

The above command populates the information of all the corrupted blocks in v$database_block_corruption view with and you can query this view to find all corrupted blocks.

Here we are using Data Recovery Advisor to find the possible repair options

Use LIST FAILURE command to find the any failures with a status OPEN and a priority of CRITICAL or HIGH in order of importance. You can use CHANGE FAILURE command to change the priority of a failure or close an open failure.

RMAN> LIST FAILURE
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1041 HIGH OPEN 23-SEP-11 Datafile 21: '/dbs1/orcl/tools.dbf ' contains one or more corrupt blocks

Use ADVISE FAILURE command to for repair advise based up on failures listed by LIST FAILURE command.

RMAN> ADVISE FAILURE;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1041 HIGH OPEN 23-SEP-11 Datafile 21:''/dbs1/orcl/tools.dbf ' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=319 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
1. No backup of block 986359 in file 21 was found. Drop and re-create the associated object (if possible), or use the DBMS_REPAIR package to repair the block corruption

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
1 Recover multiple corrupt blocks in datafile 21
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/home/oracle/diag/rdbms/orcl/orcl/hm/reco_3435353455.hm
In this case there are no manual actions available, if any actions available then you can use to repair the database.

Use automated repair options to fix the block corruptions and use the repair script generated by RMAN. You can run the script manually or you can use REPAIR FAILURE command.

Use REPAIR FAILURE PREVIEW command to list the contents of repair script without applying it.
RMAN> Repair failure preview;

Use REPAIR FAILURE command to apply the repair script generated by ADVISE FAILURE command and fix the block corruptions.
RMAN> REPAIR FAILURE;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/home/oracle/diag/rdbms/orcl/orcl/hm/reco_3435353455.hm

contents of repair script:
# block media recovery for multiple blocks
recover datafile 21 block 120;

The REPAIR FAILURE command promts you to confirm the preair and if you want to prevent that use NOPROMPT keyword.

RMAN> REPAIR FAILURE NO PROMPT;

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com