Identify all the Corrupted Objects in the Database with 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;

Notes:

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”.

If this command fails due to missing files, the ‘SKIP INACCESSIBLE’ clause can be used to avoid the failure.

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.

When a logical corruption is found the alert log is updated with:

  • Error backing up file <file#>, block <block#>: logical corruption

    In 11g a trace file is created with the corruption description.

  • When a physical corruption is found the alert log is updated with:
Corrupt block relative dba: 0x01000009 (file 4, block 9)
Bad check value found during validation
Data in bad block:
type: 16 format: 2 rdba: 0x01000009
last change scn: 0x0000.00000000 seq: 0xff flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x000010ff
check value in block header: 0xb4e0
computed block checksum: 0xa800
Reread of blocknum=9, file=/ade/b/1071806200/oracle/dbs/tc.f. found same corrupt data
  • 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 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.
  • 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

About Sher khan

Senior Oracle DBA, Oracle 10g and 11g OCE and OCS, Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g and 11g. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in Database Corruption. Bookmark the permalink.

2 Responses to Identify all the Corrupted Objects in the Database with RMAN

  1. Mehmood says:

    nice post
    Thanks

  2. siripala66 says:

    Nice article.Please keep posting

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s