AskDba.org Weblog

Recovering from physical corruption:

1. Using BMR when RMAN backups are available
When small set of blocks are corrupted, Block Media Recovery (BMR) can be used as a recovery technique. The advantage of BMR is that it can be performed without taking the datafiles offline but the block undergoing BMR is not accessible to users. RMAN BLOCKRECOVER command is used for BMR.

1)How to identify and recover the corrupted blocks needing media recovery:
To find the corrupted blocks we can use v$database_block_corruption OR v$backup_corruption view
We can use RMAN to populate the he v$backup_corruption view, which will tell us the corruptedblocks are the datafile number on which they resides:

RMAN > backup validate database;

This command will check the datafiles for physical and logical corruption. If the backup validation discovers corrupt blocks,then RMAN updates the V$DATABASE_BLOCK_CORRUPTION and v$backup_corruption views.
Now Run :

SQL> select * from V$backup_corruption;

SQL> select * from V$database_block_corruption;

to find out the corrupted blocks.
It will give you the output like :

FILE# BLOCK BLOCKS CORRUPTION_CHANGE# MAR

15 215 1 0 YES

Now we have the list of corrupted blocks file#=15 and block#=215
We will use the following command to recover corrupted blocks:

RMAN > BLOCKRECOVER DATAFILE 15 BLOCK 215;
RMAN > BLOCKRECOVER CORRUPTION LIST;

The corrupted block will be recovered from last RMAN backup and RMAN will take care of applying archives to make the block current.

2. Using BMR when RMAN backups are not taken ?
If we have a OS level backup of corrupted datafile, it can be cataloged with RMAN to perform BMR.
Find the location of Backup of datafiles which have corruption in it.
Let us suppose the backup of corrupted datafile is at /u01/app/oracle/backup/data01.dbf

Now use the following command to catalog the backup as:
RMAN> catalog datafilecopy ‘/u01/app/oracle/backup/data01.dbf’;

Find the archive log:

RMAN will use the archive logs which are present in V$ARCHIVED_LOG, If the archivelogs are moved to some other location or they are aged out of controlfilethen they need to be cataloged as well:

RMAN> catalog archivelog ‘/u02/app/oracle/backup/archoracle_1_40.dbf’;

Once the datafiles and the required archive logs are cataloged, we can use the BMR as:

RMAN > BLOCKRECOVER datafile 15 block 215;

Restrictions on BLOCKRECOVER

1. The database needs to be in mounted or open stage.

2. Only complete media recovery of block is possible.

3. Only full backups of datafiles containing corrupted blocks can be used to perform block media recovery.

4. If the archivelogs are missing or not accessible, BMR will fail.

5. Header blocks cannot be recovered.

6. Database needs to be ARCHIVELOG mode for performing a BMR.

I will be discussing more on Recovery and data salvaging techniques under Corruption Part-3.