Thursday, September 24, 2009

Recovering Blocks Listed in V$DATABASE_BLOCK_CORRUPTION

The V$DATABASE_BLOCK_CORRUPTION view indicates which blocks in a datafile were marked corrupt since the most recent BACKUP or BACKUP VALIDATE command was run. After a corrupt block is repaired, the row identifying this block is deleted from the view.
You can check for logical corruption in the database by running the BACKUP (with or without VALIDATE option) with the CHECK LOGICAL command. If RMAN finds corrupt blocks, then it populates V$DATABASE_BLOCK_CORRUPTION. The backup will stop if the number of corrupt blocks exceeds MAXCORRUPT. A historical record of block corruptions in RMAN backups is kept in V$BACKUP_CORRUPTION and V$COPY_CORRUPTION.
In this scenario, you identify the blocks that require recovery by querying V$DATABASE_BLOCK_CORRUPTION, and then instruct RMAN to recover all blocks listed in this view by means of the CORRUPTION LIST keyword.
To recover datablocks while limiting the type of backup:
Query V$DATABASE_BLOCK_CORRUPTION to determine whether corrupt blocks exist in the most recent backups of the datafiles: SQL> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

Assuming that you have preallocated automatic channels, recover all blocks marked corrupt in V$DATABASE_BLOCK_CORRUPTION by running the BLOCKRECOVER CORRUPTION LIST command. For example, this command restores blocks from backups created more than 10 days ago: BLOCKRECOVER CORRUPTION LIST
RESTORE UNTIL TIME 'SYSDATE-10';

No comments:

Post a Comment

About Me

Working as Oracle DBA for Wipro Technologies, Chennai, India