Tuesday, December 6, 2011

Physical (block) corruption in database

Block corruption or physical corruption is when a block on a physical disk becomes unreadable
or inconsistent to the state that the data is unusable

Reasons for Block corruption:

  1. human error through use of software or firmwares
  2. OS/hardware housing the oracle software
  3. bugs/patches
  4. etc.
Methods to detect and resolve block corruption:
  1. DBVERIFY utility
  2. ANALYZE table command
  3. DB_BLOCK_CHECKING parameter
  4. DBMS_REPAIR package
I have discussed first three methods in the article

DBVERIFY utility:

This is normally run on the database backup or when the database is not up and running.However, it can be used otherwise.The output of the this utility tells if there is any corrpution exists at block or index level.

The Oracle DBVERIFY utility is executed by entering dbv at the command prompt. In oracle 10g, this utility has nine parameters that can be specified at execution as show below

C:\oracle\product\10.1.0\db_1\database>dbv help=y

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)

[oracle@testlab dbs]$ dbv blocksize=8192 file=/u03/oradata/testdb/datafiles/REPORTING_DATA1.dbf

DBVERIFY: Release - Production on Tue Dec 6 13:21:49 2011

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

DBVERIFY - Verification starting : FILE =/u03/oradata/testdb/datafiles/REPORTING_DATA1.dbf

DBVERIFY - Verification complete

Total Pages Examined         : 989696
Total Pages Processed (Data) : 525013
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 95767
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 5183
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 363733
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2666423995 (3.2666423995)

If you want to put the output of the dbv command to be placed into some logfile then add the logfile clause as 

[oracle@testlab dbs]$ dbv blocksize=8192 file=/u03/oradata/testdb/datafiles/REPORTING_DATA1.dbf logfile=u02/temp/reporting_data1.log


The ANALYZE TABLE table_name VALIDATE STRUCTURE command validates the integrity of 
the object being analyzed. This command is either successful or not successful at the object level. 
If the command returns error then the object that is being analyzed has to be recreated else if the no error is returned then the object has no corruption at all as show below

SQL> analyze table my_table validate structure;
ERROR at line 1:
ORA-01498: block check failure - see trace file

SQL> analyze table my_table validate structure;
Table analyzed.


By default is FALSE for all tablespaces except for the system tablespaces. This parameter checks the block corruption at the database level and can be set as below without bouncing the instance

SQL> alter system set db_block_checking = TRUE;

Once set to true, a checksum occurs every time a block is modified in the database

DBMS_REPAIR package:

I will write a separate post for detecting and fixing block corruption using the dbms_repair package. stay tuned.

No comments:

Post a Comment