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

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.0 to 11.2.0.2 - Release: 8.1.7 to 11.2
Information in this document applies to any platform.

Goal

How to identify all the corrupted segments in the database using RMAN.

Solution

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;

This command is not doing a backup but checking the database for corruption. From 11g and beyond the backup clause can be omitted and use "validate check logical database".To make it faster, RMAN 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.

Notes:

The CHECK LOGICAL option checks for both PHYSICAL and LOGICAL Block corruptions. See Note 840978.1 for an explanation of these corruption types.

For a single or specific datafiles use "check logical validate datafile 1, 2".

To monitor the progress of the VALIDATE command run the next query:

 
select sid, serial#, context, sofar, 

totalwork,

round(sofar/totalwork*100,2) "%_complete"

from 

v$session_longops

where opname like 'RMAN%'

and opname not like 

'%aggregate%'

and totalwork != 0

and sofar <> totalwork;

For a NOARCHIVELOG mode database using 10g version and lower, the database must be in MOUNT state; otherwise error ORA-19602 is produced. If it is not possible to close the database, use dbverify instead. This restriction is lifted in 11g.
From 11g and beyond: the validation of a single datafile can be made parallel by using the section clause. RMAN divides the file into sections and processes each file section in parallel. The next example divides the datafile 5 into 1gb sections when multiple channels are configured or allocated and each section is run in parallel (reference The Oracle Database Backup and Recovery User's Guide for more information):

backup validate check logical datafile 5 SECTION SIZE 1024M;

From 11g onwards a range of blocks can be checked within a datafile using the BLOCK TO clause. The next command check blocks from 5 to 20 of datafile 1:

validate check logical datafile 1 BLOCK 5 TO 20;

In Oracle8i corruptions found with the RMAN validate command are only reported in the alert log. Oracle8i users must search the alert.log for corruption errors in the time range during which the validate command was started and when it finished. Corruptions found are NOT reported back to the RMAN interface. In Oracle9i and beyond the view V$DATABASE_BLOCK_CORRUPTION can be queried to determine what corruption, if any, was found by RMAN. As in Oracle8i, corruptions found are NOT reported back to the RMAN interface.
Note that corruption reported in V$DATABASE_BLOCK_CORRUPTION is updated with each RMAN backup validate run. To understand what is reported in this view, see the description of the view as shown in the manual titled Database Reference.

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