Block Corruption in Oracle

There are many possible causes of a block corruption including:

- Bad IO hardware / firmware

- OS problems

- Oracle problems

               

The point in time when an Oracle error is raised may be much later than when any corruption initially occurred.

Determine the Extent of the Corruption Problem

Whenever a corruption error occurs note down the FULL error message/s and look in the instance's alert log and trace files for any associated errors. It is important to do this first to assess whether this is a single block corruption, an error due to an UNRECOVERABLE operation or a more severe issue.

  It is a good idea to scan affected files (and any important files)  with DBVERIFY or RMAN to check for other corruptions in order to determine  the extent of the problem.

1) DBVERIFY

DBV can be run against datafiles which are currently opened by a database instance - there is no need to shutdown the database. 

Datafiles are opened read-only by DBV so it cannot corrupt the contents.

/opt/oracle > dbv help=y

DBVERIFY: Release 11.2.0.1.0 - Production on Thu Mar 31 02:22:27 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Keyword     Description                    (Default)
----------------------------------------------------
FILE        File to Verify                 (NONE)
START       Start Block                    (First Block of File)
END         End Block                      (Last Block of File)
BLOCKSIZE   Logical Block Size             (8192)
LOGFILE     Output Log                     (NONE)
FEEDBACK    Display Progress               (0)
PARFILE     Parameter File                 (NONE)
USERID      Username/Password              (NONE)
SEGMENT_ID  Segment ID (tsn.relfile.block) (NONE)
HIGH_SCN    Highest Block SCN To Verify    (NONE)
            (scn_wrap.scn_base OR scn)       

The following script from sqlplus which will generate the script for DBV

set feedback off  
set head off  
set echo off  
set linesize 200  
set pagesize 2500  
spool /tmp/dbvlogs/dbvchk.sh  
select 'dbv file=' || name || ' blocksize='|| block_size || ' logfile=' ||    
substr(name, instr(name, '/', -1, 1) +1) ||
'.' || file# ||  '.log'  from v$datafile  
/  
spool off 

Now you need to run the dbv commands for all the datafiles, which is spooled to dbvchk.sh. 

$ cd /tmp/dbvlogs

$ chmod 755 dbvchk.sh 

$ ./dbvchk.sh 

2) RMAN:

$ rman target / nocatalog

      From the RMAN> prompt issue the validate command with the "check logical"

      clause:

      The following example shows how to validate all datafiles:

             run {

             allocate channel d1 type disk;

             backup check logical validate database;

             release channel d1;

             }

Note : Before running it in PROD or Critical environments test on DEV or Non Critical Environments.

~~Cheers..