Block Media Recovery with RMAN

Although datafile media recovery is the principal form of recovery, you can also use the RMAN BLOCKRECOVER command to perform block media recovery. Block media recovery recovers an individual corrupt datablock or set of datablocks within a datafile. In cases when a small number of blocks require media recovery, you can selectively restore and recover damaged blocks rather than whole datafiles.

Block media recovery provides several advantages over datafile media recovery. For example, block media recovery

Lowers the Mean Time to Recovery (MTTR) because only blocks needing recovery are restored and only necessary corrupt blocks undergo recovery. Block media recovery minimizes redo application time and avoids I/O overhead during recovery.
Allows affected datafiles to remain online during recovery of the blocks. Without block-level recovery, if even a single block is corrupt, then you must restore a backup of the entire datafile and apply all redo generated for that file after the backup was created.

Note these restrictions of block media recovery:

You can only perform block media recovery with RMAN. No SQL*Plus recovery interface is available.
You can only perform complete recovery of individual blocks. In other words, you cannot stop recovery before all redo has been applied to the block.
You can only recover blocks marked media corrupt. The V$DATABASE_BLOCK_CORRUPTION view indicates which blocks in a file were marked corrupt since the most recent BACKUP or BACKUP ... VALIDATE command was run against the file.
You must have a full RMAN backup. Incremental backups are not used by block media recovery. Proxy backups are also not used by block media recovery. Only full backups and archived log files are used.
Block media recovery is able to restore blocks from parent incarnation backups and recover the corrupted blocks through a RESETLOGS.
Blocks that are marked media corrupt are not accessible to users until recovery is complete. Any attempt to use a block undergoing media recovery results in an error message indicating that the block is media corrupt.

See Also:
"Performing Disaster Recovery" and Oracle Database Backup and Recovery Reference for BLOCKRECOVER syntax

When to Use Block Media Recovery

Block media recovery is not intended for cases where the extent of data loss or corruption is unknown and the entire datafile requires recovery. In such cases, datafile media recovery is the best solution. Block media recovery is not a replacement for traditional datafile media recovery, but a supplement to it.

In most cases, the database marks a block as media corrupt, invalidates the block in the instances (or all enabled instances in a Real Application Clusters configuration), and then writes it to disk when the corruption is first encountered. No subsequent read of the block will be successful until the block is recovered. You can only perform block recovery on blocks that are marked corrupt. This corrupt status effectively takes the block offline in all database instances and prevents user access during recovery.

Block media recovery is most useful for data losses that affect specific blocks. Block-level data loss usually results from intermittent, random I/O errors that do not cause widespread data loss, as well as memory corruptions that get written to disk. Typically, these types of block corruption are reported in the following locations:

Error messages in standard output
The alert log
User trace files
Results of the SQL commands ANALYZE TABLE and ANALYZE INDEX
Results of the DBVERIFY utility
Third-party media management output

For example, you may discover the following messages in a user trace file:

ORA-01578: ORACLE data block corrupted (file # 7, block # 3)
ORA-01110: data file 7: '/oracle/oradata/trgt/tools01.dbf'
ORA-01578: ORACLE data block corrupted (file # 2, block # 235)
ORA-01110: data file 2: '/oracle/oradata/trgt/undotbs01.dbf'

You can then specify the corrupt blocks in the BLOCKRECOVER command as follows:

BLOCKRECOVER
  DATAFILE 7 BLOCK 3 
  DATAFILE 2 BLOCK 235;

Block Media Recovery When Redo Is Missing

Like datafile media recovery, block media recovery cannot generally survive a missing or inaccessible archived log (although it will attempt restore failover when looking for usable copies of archived redo log files, as described in "Restore Failover"). Nevertheless, block media recovery can survive gaps in the redo stream if the missing or corrupt redo records do not affect the blocks being recovered. Whereas datafile recovery requires an unbroken series of redo changes from the beginning of recovery to the end, block media recovery only requires an unbroken set of redo changes for the blocks being recovered.

Note:

Each block is recovered independently during block media recovery, so recovery may be successful for a subset of blocks.

When RMAN first detects missing or corrupt redo records during block media recovery, it does not immediately signal an error because the block undergoing recovery may become a newed block later in the redo stream. When a block is newed all previous redo for that block becomes irrelevant because the redo applies to an old incarnation of the block. For example, the database can new a block when users delete all the rows recorded in the block or drop a table.

Assume that media recovery is performed on block 13 as depicted in Figure 3-2.

Figure 3-2 Performing RMAN Media Recovery

Description of "Figure 3-2 Performing RMAN Media Recovery"

After block recovery begins, RMAN discovers that change 120 is missing. RMAN does not terminate recovery in the hope that block 13 will be newed later in the redo stream. Assume that in change 140 a user drops the table EMPLOYEE stored in block 13. At this point, the database formats block 13 as a new block. Because the redo for block 13 in change 120 related to the EMPLOYEE table, and the EMPLOYEE table was dropped in change 140, RMAN can skip this missing change and apply the redo between changes 140 and 160.