Online Fixing Data Block Corruption

RMAN starting from 9i has features to recover only corrupted blocks in the database while it is up and running without the need to shutdown the database.

Steps:

1. Error reported by user pointing to block corruption.

ORA-01578: ORACLE data block corrupted (file # 48, block # 142713)
ORA-01110: data file 48: 'D:\oracle\product\10.2.0\oradata\ORCL\USERS03.dbf'
ORA-02063: preceding 2 lines from MODSL_MACSL_LINK
File name : D:\oracle\product\10.2.0\oradata\ORCL\USERS03.dbf

2. Use DBVERIFY utility to check if there is only one corrupted blocks or more.

D:\> dbv file=D:\oracle\product\10.2.0\oradata\ORCL\USERS03.dbf BLOCKSIZE=8192 LOGFILE=dbv_log.log

3. Open the dbv_log.log file you will find data like this:

DBVERIFY: Release 10.2.0.1.0 - Production on Sun Mar 9 16:40:30 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = D:\oracle\product\10.2.0\oradata\ORCL\USERS03.dbf

DBVERIFY - Verification complete
Total Pages Examined : 262144
Total Pages Processed (Data) : 218615
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 22422
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 21107
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 2550111754 (1040.2550111754)

in the log above you will find "Total Pages Marked Corrupt", and it indicates to the corrupted blocks in the datafile and the value for it is 1 this means you have only one block corrupted in the datafile.

4. Get the BLOCK# for the corrupted block from the v$database_block_corruption view

SQL> Select * from v$database_block_corruption;

for example 142875

5. Now we can use RMAN to recover the block online as we know its BLOCK#.

D:\> rman target /

6. From RMAN Prompt issue the BLOCKRECOVER command to recover the corrupted block

RMAN> blockrecover datafile 48 block 142875;

7. Data block corruption has been fixed online while the database was up and running within minutes.

8. If you have more than one corrupted blocks, You can use single command to recover them.

RMAN> BLOCKRECOVER corruption list;