19 | 08 | 2017
Latest Articles
Popular Articles

Administration

Detect Oracle Block Corruption

User Rating:  / 0
PoorBest 

Detect Oracle Block Corruption ( before it is too late )

 

See also  Validate online physical and logical consistency of the Oracle Database

 

This page is about what a DBA can do to detect database block corruption in time. Block recovery, one of RMAN' s option is not discussed here.

Database data block corruption and redo log block corruption can cause DBA nightmares since the earliest RDBMS releases. The real cause is and was often corruption of the IO sub system. It can be useful and it is possible to detect block corruption in time.

Some History 

LOG_BLOCK_CHECKSUM : an Oracle 7.2 parameter could be set to TRUE. If so a checksum was calculated prior to a redo write. I think to know LOG_BLOCK_CHECKSUM became obsolete in 8.1.x

DB_BLOCK_CHECKSUM : If I am not wrong was introduced in Oracle 8.1.7 and replaces LOG_BLOCK_CHECKSUM. When set to true a checksum is calculated for every block no matter it is a database data block or a redo log block. If set to false only the database data blocks belonging to the system tablespace were checked.

DB_BLOCK_CHECKSUM can be set to FALSE or TRUE in 8.1.x and in 9.x

DB_BLOCK_CHECKSUM can be set to OFF ( which is the same as FALSE ), TYPICAL ( which is the same as TRUE ) or FULL. FULL is a new option which at the price of some additional overhead will detect block corruption as fast as possible ( data block checksum calculated and verified in memory as fast as possible )

DB_BLOCK_CHECKING is meant for logical corruption detection and has been introduced in Oracle 8.1.7

DB_BLOCK_CHECKING can be set to FALSE or TRUE in 8.1.x and in 9.x.

DB_BLOCK_CHECKING can be set to OFF ( which is the same as FALSE ), FULL ( which is the same as TRUE ) or LOW ( after DML ) or MEDIUM ( after DML + for all non index structures )

DB_LOST_WRITE_PROTECT is introduced in 11G R1 and is meant to enable lost write detection. when a write is claimed to be physical but did not happen at the physical layer of the storage.

DB_LOST_WRITE_PROTECT can be set to NONE , TYPICAL ( only for read write tablespaces ) FULL ( also for read only tablespaces )

 

DB_ULTRA_SAFE is a one fits it all parameter.

DB_ULTRA_SAFE can be set to OFF, DATA_ONLY or DATA_AND_INDEX

If set to OFF then DB_LOST_WRITE_PROTECT, DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM are actif

If set to DATA_ONLY = {DB_BLOCK_CHECKING=MEDIUM + DB_BLOCK_CHECKSUM=FULL + DB_LOST_WRITE_PROTECT=TYPICAL}

if set to DATA_AND_INDEX = {DB_BLOCK_CHECKING=FULL + DB_BLOCK_CHECKSUM=FULL + DB_LOST_WRITE_PROTECT=TYPICAL}