Performing Oracle RMAN Block Media Recovery

You can detect and repair specified corrupted database blocks in a datafile, if you know that the extent of the corruption is not substantial.

The database and datafiles remain online.

Note

You cannot use this procedure to recover the block 1 datafile header.

A successful RMAN backup means there is no corruption.

You can use BLOCKRECOVER on Oracle Enterprise Edition databases in ARCHIVELOG mode, and there must be a full backup of the datafile you want to restore.

Before You Begin

Set the Oracle DB_BLOCK_CHECKSUM initialization parameter to True.

Note

When this parameter is set, Oracle states that there is additional overhead.

For a complete description of the DB_BLOCK_CHECKSUM parameter, go to the Oracle Help Center and search for "DB_BLOCK_CHECKSUM".

When this parameter is set to true, RMAN computes a checksum, and records the data file, and the block number of the corrupted data in the control file, and alert log.

Procedure

Perform a full backup by using the RMAN Command line interface, or through the CommCell Console.

Note

If you perform an RMAN backup, you can set the maxcorrupt parameter, to specify the maximum number of corrupt blocks allowed in a datafile for RMAN backups. The backup runs until it reaches this number of corrupted files.
Example:

Set the number of corrupted blocks to 500 for datafile 12.
```
run { 
allocate channel ch1 type sbt; 
 set maxcorrupt for datafile 12 to 500; 
 backup datafile 12; 
 }
```
Determine if there is corruption by checking the alert log or querying the following views:
- V$DATABASE_BLOCK_CORRUPTION
- V$BACKUP_CORRUPTION
- V$COPY_CORRUPTION
Optional: Use a query to access the corrupted data.

Use the following sample query and insert the values returned in the V$DATABASE_BLOCK_CORRUPTION view:
```
select * from dba_extents 
 where file_id = file# and block# 
 between block_id an block_id+blocks-1;
```
Connect to the target database by using RMAN.

Create and run a script to recover detected corruption. On the RMAN command line, run one of the following RMAN scripts:

To recover all blocks, use the following script:

run {
allocate channel ch1 type 'sbt_tape'
PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=1048576";
BLOCKRECOVER CORRUPTION LIST;
}

To recover individual blocks, use the following script, and enter all block numbers.

 
run { 
allocate auxiliary channel ch1 type SBT;
BLOCKRECOVER DATAFILE data_file_num BLOCK block_num1, block_num2;
}

What to Do Next

Run a validate command on the database to find any unmarked corrupt blocks. Additionally query the V$DATABASE_BLOCK_CORRUPTION view to verify the corruption is gone

Use the following sample query to verify the corrupted data is repaired.

select * from dba_extents 
where file_id = file# and block# 
between block_id an block_id+blocks-1;