Monday, February 11, 2013

Corruption In Oracle Database

There are two types of corruption in Oracle database physical and logical what is the difference between them,Database written to blocks if this write failed then Database Corruption happened Sometimes because I/O Problems, Power Problem ... etc which cause no time for header to been updated, usually Oracle Corruption will not effecting on your work until you try to read this block. There are two types of Corruption:

1- Physical Corruption  (Media Corruption)

2- Logical Corruption (Soft Corruption) 

The Details can be Find in Alertlog
ORA-01578:ORACLE data block corrupted (file # string, block # string)


Physical Corruption :

this kind of corruption can be happened when I/O Problems, Memory Failure, Server Controller

Regarding to Oracle documentation the corruption could be happened by:

  • Bad header:the beginning of the block (cache header) is corrupt with invalid values.
  • Block is Fractured/Incomplete:Information from the block header does not match the block tail
  • Block checksum is invalid
  • Block is misplaced
you can DB_BLOCK_CHECKSUM init parameter are used to identify if the block was changed by something external to Oracle and after the block was last written by Oracle. You can check the Parameter documentation Here.

Logical Corruption:



this kind of corruption you will not find it alertlog only by DBVerify utility, also be noted if db_block_checking Parameter is enabled, it may produce the internal error ORA-600 [kddummy_blkchk]


So , the difference between both kind is clear , Logical Corruption is Header/Footer and database will try to read that block , In the physical Corruption Something prevent us to read that block.

How can i know i have Corruption:


1- RMAN
2- DBVerify

DBVerify :

DBVERIFY is an external command-line utility that performs a physical data structure integrity check on an offline database. It can be used against backup files and online files (or pieces of files). You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored or as a diagnostic aid when you have encountered data corruption problems.

Documentation is here.

RMAN

Rman could Detect both corruption logical/physical , check the below script that could check if the database contains corruption without even take backup:

RMAN> configure device type disk parallelism 4;
RMAN> backup validate check logical database;
OR
RMAN> run {
allocate channel diskl1 type disk;
allocate channel disk2 type disk;
allocate channel disk3 type disk;
allocate channel disk4 type disk;
backup validate check logical database;
}
The above script to check Database Validation.


Which Is Better :

  1. RMAN can be run with PARALLELISM option
  2. DB Verfiy Check Empty Block.
  3. Both Tools Can check for Rang Blocks.
RMAN:
VALIDATE DATAFILE 1 BLOCK .... to ......

DBV:
start=... end=....

Some Other Blogs Could Be useful :
1- Database Corruption
2-  Other Tools To Check Corruption


Thank you
Osama mustafa


 

1 comment:

  1. Well (organized, to the point, easy language to understand, supported by references) post.
    Keep it up :-)

    ReplyDelete