Corrupt database blocks II

After you have validated database as shown in previous post any corrupt database blocks will be reported in the view V$DATABASE_BLOCK_CORRUPTION. Now you want to find out the tables that used to have data in these blocks; those data have typically been lost if you have no backup of them or do not posses special tools like DUDE.

Before you start I recommend to purge the DBA Recycle Bin if it is in use, as reported here certain queries  seem to take longer time when it is not empty. Execute as SYSDBA:

purge dba_recyclebin;

Then create a temporary table with information on the corrupt blocks:

create table segments_with_corruption
as select FILE_ID,BLOCK_ID,blocks,OWNER,SEGMENT_NAME,
  start_corrupt,corrupt_blocks
from (
  select e.FILE_ID,e.BLOCK_ID,e.blocks,e.OWNER,e.SEGMENT_NAME,
    c.block# start_corrupt,c.blocks corrupt_blocks
  from DBA_EXTENTS E, V$DATABASE_BLOCK_CORRUPTION C
  where E.BLOCK_ID <= C.block# 
    and E.BLOCK_ID + E.blocks - 1 >= C.block#
  and e.file_id=c.file#
  union all
  select e.FILE_ID,e.BLOCK_ID,e.blocks,e.OWNER,e.SEGMENT_NAME,
    c.block# start_corrupt,c.blocks corrupt_blocks
  from DBA_EXTENTS E, V$DATABASE_BLOCK_CORRUPTION C
  where E.BLOCK_ID >= C.block# 
    and E.BLOCK_ID <= C.block# + C.blocks -1
  and e.file_id=c.file#
);

This query may take a long time to run, which is a reason to materialize it in a table while you are working on this. You can find a more advanced query on MOS in article 472231.1 To create this query I made a drawing for myself with all different scenarios on how interval of corrupt blocks could possible overlap with extents belonging to some object. Then I wrote down the criteria and merged them together.

Use this query to find owner, name and type of the segments you have to work with:

select distinct c.owner,c.segment_name, c.segment_type
from segments_with_corruption c, dba_segments s
where c.segment_name=c.segment_name and c.owner=s.owner;

For segment_type=INDEX you can fix it by simply rebuilding the index (hopefully none of the rows it indexes are in corrupt blocks).

The next post will be on how to salvage as much as possibly from the good blocks belonging to a table that has some corrupt blocks. I'll show a query you will run to generate other statements that will be used to extract data from blocks around the corrupt blocks.