Steps for Fixing Corrupted Blocks

Steps to fix block corruption on datafile:

1. Verify datafile on command screen.

dbv file=/u04/oradata/sysaux01.dbf blocksize=8192

DBVERIFY: Release 10.2.0.4.0 - Production on Thu May 20 11:23:59 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

DBVERIFY - Verification starting : FILE = /u04/oradata/sysaux01.dbf

DBV-00200: Block, DBA 12584268, already marked corrupt


DBVERIFY - Verification complete

Total Pages Examined : 512000
Total Pages Processed (Data) : 152927
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 152369
Total Pages Failing (Index): 0
Total Pages Processed (Other): 26364
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 180340
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 1849116721 (0.1849116721)


2. Check which object is corrupted.

Select * from v$database_block_corruption
/

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
3 1356 1 0 CORRUPT

sys@testdb> select segment_type,owner||'.'||segment_name
from dba_extents
where file_id = 3 and 1356 between block_id and block_id+blocks -1;

SEGMENT_TYPE
------------------
OWNER||'.'||SEGMENT_NAME
----------------------------------------------------------------------------------------------------------------
INDEX
SYS.I_WRI$_OPTSTAT_H_ST

3. Recover the corrupted block using RMAN either with Catalog or not. You have to use no catalog if the corrupted
block is not in the backupset yet.

RMAN Blockrecover with catalog:

RMAN> BLOCKRECOVER DATAFILE 3 BLOCK 1356
FROM TAG = testdb_20100519_223001;

RMAN Blockrecover with no catalog:

rman target=/
RMAN> BLOCKRECOVER DATAFILE 3 BLOCK 1356;
Starting blockrecover at 20-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1792 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished blockrecover at 20-MAY-10

4. We need to clear the corrupted block record on v$database_block_corruption view.

rman target=/ catalog=xx/xx@rmandb

RMAN>backup validate check logical database archivelog all;

5. Once it's done query v$database_block_corruption.

sys@testdb> Select * from v$database_block_corruption;

no rows selected