How to deal with Block Corruption !!

This is pretty critical and once in a blue moon for Oracle DBAs. But it is really quite interesting if you have some backups available incase you are not able to Fix the Corrupt Block.

Oracle Database version is 9.2.0.4 and standby database is in 9.2.0.8 (Linux).

There is a below tables has got 1 block corrupt.

SQL> select count(1) from TAB1;
select count(1) from TAB1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 13, block # 477214)
ORA-01110: data file 13: '/usr/oracle/oradata/DATAFILE03.dbf'

It has generated a trace file as well. some glimpses of the trace as well.


Corrupt block relative dba: 0x0340586b (file 13, block 22635)
Bad check value found during backing up datafile
Data in bad block -
type: 6 format: 2 rdba: 0x0340586b
last change scn: 0x0011.6a3987cd seq: 0x1 flg: 0x06
consistency value in tail: 0x87cd0601
check value in block header: 0xd317, computed block checksum: 0x100
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of blocknum=22635, file=/usr/oracle/oradata/grcbonl/ONLINE03.dbf. found same corrupt data
Sun Feb 3 19:01:05 2008
Errors in file /home/oracle/admin/grcbonl/udump/onlstb_ora_24224.trc:
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory

=======================================================

Script to find out which object has got bad block.
13 is the file no. 22635 is the block occured in trace / alert.

select segment_name,segment_type,owner
from sys.dba_extents
where file_id=(13)
and (22635) between block_id and block_id + blocks -1;

SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE OWNER
------------------ ------------------------------
TAB1
TABLE USER1

**** First you need to create a table which will store object name and what block was corrupt.

Step 1 : exec DBMS_REPAIR.ADMIN_TABLES ('TB_REPAIR',1,1,null);

Step 2:

variable blockno number;

exec sys.DBMS_REPAIR.CHECK_OBJECT('USER1','TAB1',null, 1,'TB_REPAIR', null, null, null, null, :blockno );

print blockno;

Blockno will print how many blocks are corrupt in the object.

Now come to how you can fix the block.

Step 3 :exec DBMS_REPAIR.FIX_CORRUPT_BLOCKS('USER1','TAB1', NULL, 1, 'TB_REPAIR',NULL, :blockno );


print blockno;

It should print the same output as step 2;

If not then Package is not able to fix the corrupt block. Only way you can get data skipping this block or you can recover whole object by using previoud backups.

How to skip the corrupt block.

SQL> SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT,
CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION
FROM TB_REPAIR;
2 3
OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR
------------------------------ ---------- ------------ ----------
CORRUPT_DESCRIPTION
--------------------------------------------------------------------------------
REPAIR_DESCRIPTION
--------------------------------------------------------------------------------
TAB1 477214 6148 TRUE

mark block software corrupt

TAB1 477214 6148 TRUE

mark block software corrupt

OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR
------------------------------ ---------- ------------ ----------
CORRUPT_DESCRIPTION
--------------------------------------------------------------------------------
REPAIR_DESCRIPTION
--------------------------------------------------------------------------------



Step 4:

exec DBMS_REPAIR.skip_corrupt_blocks( 'USER1' , 'TAB1' , 1, 1);

Step 5:

exec DBMS_REPAIR.rebuild_freelists( 'USER1' , 'TAB1' , null, 1);

Step 5 will rebuild the freelists for the table

By above method if this does not fix the corrupt block problem. As i mentioned recover the object from the recent backup.