DB_LOST_WRITE_PROTECT,DB_BLOCK_CHECKING,DB_BLOCK_CHECKSUM

Scope
This document is intended for Database Administrators wanting to learn how to prevent and detect from various data block corruptions. 
A corrupt block is a block that has been changed so that it differs from what Oracle Database expects to find. This note covers three data block corruption types:

• In a physical block corruption, which is also called a media corruption, the database does not recognize the block at all: the checksum is invalid, the block contains all zeros, the header and footer of the block do not match or one of the key data block data structure is incorrect such as the data block address (DBA).
• In a logical block corruption, the contents of the block are physically sound and pass the physical block checks; however the block can be logically inconsistent. Examples of logical corruption include corruption of a row piece or index entry.
• Block corruptions caused by stray writes, lost writes or misdirected writes can also cause havoc to your database availability. The data block may be physically or logically correct but in this case the block’s content is older or stale or in the wrong location.

Block corruptions can also be divided into interblock corruption and intrablock corruption:

• In intrablock corruption, the corruption occurs in the block itself and can be either a physical or a logical corruption.
• In an interblock corruption, the corruption occurs between blocks and can only be a logical corruption.

Details
Causes of corrupted blocks
Block corruptions can be caused by various failures including, but not limited to the following:

• Faulty disks and disk controllers
• Faulty memory
• Faulty network components
• Firmware, operating system, volume manager, NFS or third party software defects
• Oracle Database software defects

Block corruptions can be also be caused by operator errors such as copying backups over existing data files or restoring inconsistent database backups.

Configuration Details
Configure at Primary Database:

• DB_BLOCK_CHECKSUM=FULL
• DB_BLOCK_CHECKING=FULL or MEDIUM
• DB_LOST_WRITE_PROTECT=TYPICAL
• Enable Flashback Technologies for fast point-in-time recovery from human errors (e.g. drop table, inadvertent or malicious data changes) and for fast reinstatement of a primary database following failover.

Configure at Data Guard Standby Database:

• DB_BLOCK_CHECKSUM=FULL
• DB_BLOCK_CHECKING=FULL or MEDIUM
• DB_LOST_WRITE_PROTECT=TYPICAL
• Enable Flashback Technologies for fast point-in-time recovery from human errors (e.g. drop table, inadvertent or malicious data changes) and for fast reinstatement of a primary database following failover.
• Use Active Data Guard to enable Automatic Block Repair (Data Guard 11.2 onward).

Review the additional background on each of these settings provided in the sections below, especially if tests show that any of the above recommendations have a greater than acceptable impact on the performance of your application.

DB_ULTRA_SAFE Parameter

DB_ULTRA_SAFE is a new parameter introduced in 11g. It provides an integrated mechanism to offer protection from various possible data corruptions. and provides critical high availability benefits for Oracle Database. Setting DB_ULTRA_SAFE initialization parameter will configure the appropriate data protection block checking level in the database. It will control DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, and DB_LOST_WRITE_PROTECT.

DB_BLOCK_CHECKING controls whether or not Oracle performs block checking for database blocks.

DB_BLOCK_CHECKSUM determines whether DBWn and the direct loader will calculate a checksum (a number calculated from all the bytes stored in the block) and store it in the cache header of every data block when writing it to disk.

DB_LOST_WRITE_PROTECT enables or disables lost write detection. A data block lost write occurs when an I/O subsystem acknowledges the completion of the block write, while in fact the write did not occur in the persistent storage.

DB_ULTRA_SAFE Parameter can be set to 3 different values: OFF, DATA_ONLY and DATA_AND_INDEX. Default value is OFF.

Here are the descriptions of these values:

OFF: It will not change values of DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM and DB_LOST_WRITE_PROTECT if they are explicitly set, otherwise all of them will set to default values.

DATA_ONLY: It will set DB_BLOCK_CHECKING to medium, DB_LOST_WRITE_PROTECT to typical and DB_BLOCK_CHECKSUM to full.

DATA_AND_INDEX: It will set DB_BLOCK_CHECKING to full, DB_LOST_WRITE_PROTECT to typical, and DB_BLOCK_CHECKSUM to full.

The only difference between DATA_AND_INDEX and DATA_ONLY is DB_BLOCK_CHECKING. When DB_BLOCK_CHECKING is set to full, Oracle will do semantic checks for index blocks.