Performing Block Media Recovery with RMAN

The BLOCKRECOVER command can restore and recover individual datablocks within a datafile. This procedure is useful when a trace file or standard output reveals that a small number of blocks within a datafile are corrupt.

Block media recovery is not useful in cases where the extent of data loss or corruption is not known; in this case, use datafile recovery instead.

Block Media Recovery Using All Available Backups

In this scenario, you identify the blocks that require recovery and then use any available backup to perform the restore and recovery of these blocks.

To recover datablocks by using all available backups:

Obtain the datafile numbers and block numbers for the corrupted blocks. Typically, you obtain this output from the standard output, the alert.log, trace files, or a media management interface. For example, you may see the following in a trace file:

ORA-01578: ORACLE data block corrupted (file # 8, block # 13)
ORA-01110: data file 8: '/oracle/oradata/trgt/users01.dbf'
ORA-01578: ORACLE data block corrupted (file # 2, block # 19)
ORA-01110: data file 2: '/oracle/oradata/trgt/undotbs01.dbf'

Assuming that you have preallocated automatic channels, run the BLOCKRECOVER command at the RMAN prompt, specifying the file and block numbers for the corrupted blocks as in the following example:
```
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19;
```

Block Media Recovery Using Specific Backups

In this scenario, you identify the blocks that require recovery, and then use only selected backups to perform the restore and recovery of these blocks.

To recover datablocks while limiting the type of backup:

ORA-01578: ORACLE data block corrupted (file # 8, block # 13)
ORA-01110: data file 8: '/oracle/oradata/trgt/users01.dbf'
ORA-01578: ORACLE data block corrupted (file # 2, block # 19)
ORA-01110: data file 2: '/oracle/oradata/trgt/undotbs01.dbf'

Assuming that you have preallocated automatic channels, execute the BLOCKRECOVER command at the RMAN prompt, specifying the file and block numbers for the corrupted blocks and limiting the backup candidates by means of the available options. For example, you can specify what type of backup should be used to restore the blocks:

# restore from backupset
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 FROM BACKUPSET;
# restore from datafile image copy
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 
      FROM DATAFILECOPY;

You can indicate the backup by specifying a tag:

# restore from backupset with tag "mondayam"
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 199 
      FROM TAG = mondayam;

You can limit the backup candidates to those made before a certain point:

# restore using backups made before one week ago
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 
  RESTORE UNTIL 'SYSDATE-7';
# restore using backups made before SCN 100
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 
  RESTORE UNTIL SCN 100;
# restore using backups made before log sequence 7024
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 
  RESTORE UNTIL SEQUENCE 7024;

Note that if you limit the restore of datablocks with the UNTIL clause, then RMAN must perform more recovery on the blocks, and the recovery phase must scan all logs for changes to the specified blocks.

Block Media Recovery of 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';
```

See Oracle Database Backup and Recovery Reference for more details on block media recovery in RMAN.