oracle database block corruption scripts.

script to get corrupted block list.

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
     , greatest(e.block_id, c.block#) corr_start_block#
     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) 
       - greatest(e.block_id, c.block#) + 1 blocks_corrupted
     , null description
  FROM dba_extents e, v$database_block_corruption c
 WHERE e.file_id = c.file#
   AND e.block_id <= c.block# + c.blocks - 1
   AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
     , header_block corr_start_block#
     , header_block corr_end_block#
     , 1 blocks_corrupted
     , 'Segment Header' description
  FROM dba_segments s, v$database_block_corruption c
 WHERE s.header_file = c.file#
   AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
     , greatest(f.block_id, c.block#) corr_start_block#
     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) 
       - greatest(f.block_id, c.block#) + 1 blocks_corrupted
     , 'Free Block' description
  FROM dba_free_space f, v$database_block_corruption c
 WHERE f.file_id = c.file#
   AND f.block_id <= c.block# + c.blocks - 1
   AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;

check the corrupted block on database level using db verify & rman validate.

RMAN> backup validate check logical datafile  #

dbv userid=system/****** file= file path  blocksize=8192

DBVERIFY - Verification complete




Total Pages
Examined         : 3968000

Total Pages Processed (Data) :
2113698

Total Pages Failing  
(Data) : 0

Total Pages Processed (Index):
1311235

Total Pages Failing  
(Index): 0

Total Pages Processed (Other):
502143

Total Pages Processed
(Seg)  : 3553

Total Pages Failing  
(Seg)  : 0

Total Pages
Empty            : 37371

Total Pages Marked
Corrupt   : 0

Total Pages
Influx           : 0

Highest block
SCN            :
1095073332 (1.1095073332)