Block Corruption and Recovery

Step 1: Identify the corrupt blocks
-----------------------------------
1. Run below command to populate v$database_block_corruption view with information of all the corrupted blocks.

RMAN> backup validate check logical database;

“CHECK LOGICAL" option is used to identify both Physical and Logical Block Corruptions.

Select the view to identify the corrupted blocks detected by RMAN.
SQL> select * from v$database_block_corruption;

Use below query to find the object name and object type
SQL> SELECT owner,segment_type, segment_name FROM dba_extents
WHERE file_id = &file_no
AND &block_no BETWEEN block_id AND block_id + blocks -1;

Please note, After a corrupt block is repaired, the row identifying the block is deleted from the view.

2. Check alert. log file for corrupted blocks, data file list.
For ex:- ORA-01578: ORACLE data block corrupted (file # 5, block # 15)
ORA-01110: data file 5: '/oracle/oradata/trgt/users01.dbf'

3. You can also use dbverify utility to identify Physical and Logical Intra Block Corruptions.
dbv file=datafile_name blocksize=datafile_block_size

Step 2: Recovering Data blocks
-------------------------------
1. Recovering Data blocks By Using All Available Backups

Run the BLOCKRECOVER command at the RMAN prompt, specifying the file and block numbers for the corrupted blocks

RMAN>BLOCKRECOVER DATAFILE 5 BLOCK 15;

Recover multiple blocks in single command
RMAN>BLOCKRECOVER DATAFILE 5 BLOCK 15 DATAFILE 2 BLOCK 10;

2. Recovering Data blocks Using Selected Backups

Run the BLOCKRECOVER command at the RMAN prompt, Specifying the data file and block numbers for the corrupted blocks and limiting the backup candidates by means of the available options. For example, specify what type of backup should be used to restore the blocks.

# restore from backupset
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 FROM BACKUPSET;

# restore from datafile image copy
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 FROM DATAFILECOPY;

# restore from backup set with tag "Sunday"
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 FROM TAG = Sunday;

# restore using backups created before log sequence 100
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 RESTORE UNTIL SEQUENCE 100;

# restore using one week ago backups
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 RESTORE UNTIL 'SYSDATE-7';

# restore using backups until SCN 100
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 RESTORE UNTIL SCN 100;

3 . Recovering blocks listed in V$DATABASE_BLOCK_CORRUPTION view

Run the below command to recover all blocks marked corrupt in V$DATABASE_BLOCK_CORRUPTION
RMAN> BLOCKRECOVER CORRUPTION LIST;

Restores blocks from backup sets created more than 7 days ago
RMAN> BLOCKRECOVER CORRUPTION LIST FROM BACKUPSET RESTORE UNTIL TIME 'SYSDATE-7';

Note:-Block corruptions in RMAN backups and copies is kept in V$BACKUP_CORRUPTION and V$COPY_CORRUPTION

Step 3: Allow Recovery to corrupt blocks
-----------------------------------------
During recovery database finds corrupt blocks then recovery stops. Run the below command in order to skip the corrupt blocks and proceed with recovery.

SQL>RECOVER DATABASE ALLOW n CORRUPTION;

Where n is the number of allowable corrupt blocks

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