Dev's Weblog

Reading time: 3 - 5 minutes

I happened to had Block corruption in my database yesterday and i used the following steps to resolve it.

I had full export available before the corruption occurred.

Steps followed to clear Data Block Corruption.

• Find how many blocks corrupted.

You can use RMAN's following command to get the list of corrupted blocks.

RMAN>backup validate check logical database;

The above command will populate the v$database_block_corruption view.

SQL>select * from v$database_block_corruption;

FILE#AAAA BLOCK#AAAAA BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5AAA 46863AAAAAAAAAA 1AAAAAAA 4777483 LOGICAL
5AAA 47311AAAAAAAAAA 1AAAAAAA 4777362 LOGICAL
5AAA 52751AAAAAAAAAA 1AAAAAAA 4777313 LOGICAL
5AAA 50127AAAAAAAAAA 1AAAAAAA 4777378 LOGICAL
5AAA 51855AAAAAAAAAA 1AAAAAAA 4777321 LOGICAL
5AAA 45647AAAAAAAAAA 1AAAAAAA 4777434 LOGICAL
5AAA 48463AAAAAAAAAA 1AAAAAAA 4777499 LOGICAL
5AAA 51087AAAAAAAAAA 2AAAAAAA 4777329 LOGICAL
5AAA 51471AAAAAAAAAA 1AAAAAAA 4777386 LOGICAL
5AAA 46671AAAAAAAAAA 1AAAAAAA 4777475 LOGICAL
5AAA 51279AAAAAAAAAA 1AAAAAAA 4777337 LOGICAL

FILE#AAAA BLOCK#AAAAA BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5AAA 46543AAAAAAAAAA 1AAAAAAA 4777443 LOGICAL
5AAA 46927AAAAAAAAAA 1AAAAAAA 4777491 LOGICAL
5AAA 47119AAAAAAAAAA 1AAAAAAA 4777345 LOGICAL
5AAA 48143AAAAAAAAAA 1AAAAAAA 4777459 LOGICAL
5AAA 47247AAAAAAAAAA 1AAAAAAA 4777354 LOGICAL
5AAA 49999AAAAAAAAAA 1AAAAAAA 4777370 LOGICAL
5AAA 52239AAAAAAAAAA 1AAAAAAA 4777418 LOGICAL
5AAA 51663AAAAAAAAAA 1AAAAAAA 4777394 LOGICAL
5AAA 52047AAAAAAAAAA 1AAAAAAA 4777402 LOGICAL
5AAA 45519AAAAAAAAAA 1AAAAAAA 4777426 LOGICAL
5AAA 48015AAAAAAAAAA 1AAAAAAA 4777451 LOGICAL

FILE#AAAA BLOCK#AAAAA BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5AAA 48207AAAAAAAAAA 1AAAAAAA 4777467 LOGICAL
5AAA 52175AAAAAAAAAA 1AAAAAAA 4777409 LOGICAL

• Find out the objects which are stored in corrupted blocks.

SQL>SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = &AFN and &BL between block_id AND block_id + blocks - 1;

Enter value for afn : 5

oldAA 3:AAAAA WHERE file_id = &AFN
newAA 3:AAAAA WHERE file_id = 5

Enter value for bl: 52175

oldAA 4:AAAAAAA and &BL between block_id AND block_id + blocks - 1
newAA 4:AAAAAAA and 52175 between block_id AND block_id + blocks - 1

TABLESPACE_NAMEAAAAAAAAAAA SEGMENT_TYPEAAAAA OWNER
------------------------------ ------------------ ------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
CS_OWNER AAAAAAAAAA TABLEAAAAAAAA OWNER
GROUPING_CD

Run the above query for all the corrupted blocks.

• Drop all the objects.

drop table GROUPING_CD;

• Import the affected objected using the export dump file.

[oracle01@lala]$ imp system/password file=full_exp.dmp FROMUSER=del_owner touser=del_owner statistics=none tables=GROUPING_CD

• Verify the Datafile using DBVERIFY utility

[oracle01@test DEL]$ dbv file=cs_del_owner_01.dbf blocksize=8192

DBVERIFY: Release 10.2.0.4.0 - Production on Wed May 6 19:15:50 2009

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

DBVERIFY - Verification starting : FILE = del.dbf

DBV-00201: Block, DBA 21017039, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21017167, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21018063, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21018191, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21018383, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21018447, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21018639, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21018767, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21018831, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21019535, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21019663, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21019727, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21019983, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21021519, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21021647, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21022607, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21022608, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21022799, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21022991, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21023183, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21023375, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21023567, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21023695, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21023759, marked corrupt for invalid redo application

DBV-00201: Block, DBA 21024271, marked corrupt for invalid redo application

DBVERIFY - Verification complete

Total Pages ExaminedAAAAAAAA : 75392
Total Pages Processed (Data) : 1511
Total Pages FailingAA (Data) : 0
Total Pages Processed (Index): 1113
Total Pages FailingAA (Index): 0
Total Pages Processed (Other): 24524
Total Pages Processed (Seg)A : 0
Total Pages FailingAA (Seg)A : 0
Total Pages EmptyAAAAAAAAAAA : 48244
Total Pages Marked CorruptAA : 25
Total Pages InfluxAAAAAAAAAA : 0
Highest block SCNAAAAAAAAAAA : 4846657 (0.4846657)

• Still you will see blocks are corrupted because we have re-created objects but the old blocks will remain corrupt.To clean it follow the following step.
• Drop the user with cascade option and drop the tablespace ( dont use including contents and datafiles claus ) which has corrupt block.
• After tablespace is dropped , pls do not remove the datafile because it has corrupt block and if u remove it and re-create the datafile then it may happen that new datafile gets the same block ,so rename old datafile and create new datafile with old name.
• Re-create the tablespace and user.
• Import the user's objects to new tablespace.
• Again run DBVERIFY to confirm that blocks are not corrupt.