How to identify all the Corrupted Objects in the Database reported by RMAN

 

Step 1: Identify the corrupt blocks

Populate the v$database_block_corruption view with  information of all the corrupted blocks by executing the following command from RMAN:

RMAN> backup validate check logical database; 

To make it faster, itt can be configured to use PARALLELISM with multiple channels:

RMAN> configure device type disk parallelism 4;

RMAN> backup validate check logical database; 

OR

RMAN> run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
backup validate check logical database;
}

The corrupted blocks are listed in the view v$database_block_corruption:

SQL> select * from v$database_block_corruption; 

FILE#          BLOCK#          BLOCKS CORRUPTION_CHANGE# CORRUPTIO
————— ————— ————— —————— ———
6              10               1      8183236781662 LOGICAL
6              42               1                  0 FRACTURED
6              34               2                  0 CHECKSUM
6              50               1      8183236781952 LOGICAL
6              26               4                  0 FRACTURED

5 rows selected.

Step 2: Identify the corrupt segments

The next query can be run to map each block to a segment in the database.  It will map each block from v$database_block_corruption to either a segment or if the block is free.

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#;


An output example is:

OWNER SEGMENT_TYPE       SEGMENT_NAME PARTITION_ FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
----- ------------------ ------------ ---------- ----- ----------------- --------------- ---------------- -------------
SCOTT TABLE              EMP                         6                10              10                1
SCOTT TABLE PARTITION    ORDER        ORDER_JAN      6                26              28                3
6                29              29                1 Free Block
SCOTT TABLE              BONUS                       6                34              34                1
6                35              35                1 Free Block
SCOTT TABLE              DEPT                        6                42              42                1 Segment Header
SCOTT TABLE              INVOICE                     6                50              50                1

Notes:

• If a corrupt block is in a dictionary managed tablespace and if the segment header block is corrupt, the above query may display the same block twice.
• If a segment header block is corrupt in an ASSM tablespace, the above query displays the segment header block but subsequent corrupt blocks for the same object may not be displayed.