RMAN backup is failing due to “corrupt blocks”

Last week, I was not able to complete a backup because of the ORA-19566 error: “exceeded limit of 0 corrupt blocks”. Here is what you can do to fix it.

Starting Point: backup error

Here is the starting point of this case. The following error is found in the RMAN Backup log file:

allocated channel: ch1
channel ch1: sid=25 devtype=DISK
Starting backup at 27-APR-11
channel ch1: starting incremental level 0 datafile backupset
channel ch1: specifying datafile(s) in backupset
input datafile fno=00006 name=D:MXXRTXIDXXRIDX01.DBF
input datafile fno=00003 name=D:MXXRTXLXRL01.DBF
input datafile fno=00002 name=D:MXXRTXSXRS01.DBF
input datafile fno=00007 name=D:MXXRTXTMPXRTEMP01.DBF
input datafile fno=00004 name=D:MXXRTXBLOBXRBLOB01.DBF
channel ch1: starting piece 1 at 27-APR-11
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ch1 channel at 04/27/2011 08:53:37
ORA-19566: exceeded limit of 0 corrupt blocks for file D:MXXRTXLXRL01.DBF

Validation of the database

First of all, verify your database with the RMAN validate command to find out which blocks are corrupted.

The RMAN command below will check for physical as well as for logical corruption of the database. Per default, the VALIDATE command check only physical corruptions.

But what is the difference between a logical and a physical corruption?

• Physical corruption: the block is not recognized.
• Logical corruption: the contents of the block is logically inconsistent.

For your information: With Oracle 11.2, please use the new command:

RMAN> validate database check logical;

In my case, it was Oracle 9.2, so the command usage is slightly different:

RMAN> backup validate check logical database ;
Starting backup at 06-MAY-11
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00008 name=D:MXXRTXSYSRBS01.DBF
. . .
channel ORA_DISK_1: backup set complete, elapsed time: 00:14:15
Finished backup at 06-MAY-11
RMAN>

Once the validation command is finished, RMAN populates the findings in the V$DATABASE_BLOCK_CORRUPTION view.
In my case, I then thourougly analyzed the corrupted block reported into the view presented below:

SQL> select * from v$database_block_corruption;
FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
 3     100261          1                  0 FRACTURED

Only one block was reported as fractured. I then tried to create a backup with a number of authorized corrupted blocks.

run
{
ALLOCATE CHANNEL ch1 TYPE DISK;
set maxcorrupt for datafile 3 to 10;
. . .
RELEASE CHANNEL ch1;
}

Find the corrupted blocks

the backup run successfully and no corrupted block are found anymore, the next step is to identify which information is saved on the corrupted block.

Using the command below, it is possible to find out which object uses this block.

SQL> select segment_name,owner,segment_type from dba_extents
     where file_id=3
     and 100261 between block_id and block_id + blocks -1;
no rows selected
SQL>

Our corrupted block is an empty block reported as fractured into the view v$database_block_corruption.
Now, what is the definition of a fractured block?

Fractured: Block header looks reasonable, but the front and back of the block are different versions.
Since Oracle 9.2, it is possible to make a block recovery in case a single block is corrupted.

How to fix the corruption

I therefore tried to fix the corrupted block using the command below:

RMAN> blockrecover datafile 3 block 100261;
Starting blockrecover at 06-MAY-11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 05/06/2011 14:33:44
RMAN-05009: Block Media Recovery requires Enterprise Edition

 
Unfortunately blockrecover is not usable because it requires an Oracle Enterprise Edition and we are running an Oracle Standard Edition.
I tried also to repair this block with the package dbms_repair, but dbms_repair doesn’t have any option to fix a empty corrupted block. 😕
After some deeper analyzes, I decided to leave this fractured empty block into the database, as a corrupted unused block is not harmful. When Oracle will reuse this block to assign it to an segment, Oracle will automatically reformat this block, and the problem will be solved.

For your information: We have also the possibility to fix this error, but the Database will need a maintenance windows to export and reimport Data into a new empty created tablespace.

Conclusion

Since Oracle 10.2, RMAN also skips currently unused blocks – as opposed to never used block only in Oracle 9.2. In my case the block was recognized as corrupted, because I was running an Oracle 9.2 database. But in newer Oracle versions, this problem would not have been reported. Oracle would have ignored this corruption and your backup would be running successfully…