jump to navigation

DB_ULTRA_SAFE – A superficial parameter introduced in Oracle database 11g June 6, 2009

Posted by sendtoshailesh in Uncategorized.
trackback

I saw a new parameter introduced in Oracle Database 11g. This controls behavior of DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, or DB_LOST_WRITE_PROTECT parameters.
Lets tall first about these parameters:

DB_LOST_WRITE_PROTECT is also introduced in 11g. it 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.When the parameter is set to TYPICAL on the primary database, the instance logs buffer cache reads for read-write tablespaces in the redo log, which is necessary for detection of lost writes.When the parameter is set to FULL on the primary database, the instance logs reads for read-only tablespaces as well as read-write tablespaces.When the parameter is set to TYPICAL or FULL on the standby database or on the primary database during media recovery, the instance performs lost write detection.When the parameter is set to NONE on either the primary database or the standby database, no lost write detection functionality is enabled.

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

Values:

*

OFF or FALSE

No block checking is performed for blocks in user tablespaces. However, semantic block checking for SYSTEM tablespace blocks is always turned on.
*

LOW

Basic block header checks are performed after block contents change in memory (for example, after UPDATE or INSERT statements, on-disk reads, or inter-instance block transfers in Oracle RAC).
*

MEDIUM

All LOW checks are performed, as well as semantic block checking for all non-index-organized table blocks.
*

FULL or TRUE

All LOW and MEDIUM checks are performed, as well as semantic checks for index blocks (that is, blocks of subordinate objects that can actually be dropped and reconstructed when faced with corruption).

Oracle checks a block by going through the data in the block, making sure it is logically self-consistent. Block checking can often prevent memory and data corruption. Block checking typically causes 1% to 10% overhead, depending on workload and the parameter value. The more updates or inserts in a workload, the more expensive it is to turn on block checking. You should set DB_BLOCK_CHECKING to FULL if the performance overhead is acceptable.

For backward compatibility the use of FALSE (implying OFF) and TRUE (implying FULL) is preserved.

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. Checksums are verified when a block is read – only if this parameter is TYPICAL or FULL and the last write of the block stored a checksum. In FULL mode, Oracle also verifies the checksum before a change application from update/delete statements and recomputes it after the change is applied. In addition, Oracle gives every log block a checksum before writing it to the current log.

Starting with Oracle Database 11g, most of the log block checksum is done by the generating foreground processes, while the LGWR performs the rest of the work, for better CPU and cache efficiency. Prior to Oracle Database 11g, the LGWR solely performed the log block checksum. When this parameter is set to FULL, the LGWR verifies the checksum of each log block generated by the foreground processes before writing it to disk.

If this parameter is set to OFF, DBWn calculates checksums only for the SYSTEM tablespace, but not for user tablespaces. In addition, no log checksum is performed when this parameter is set to OFF.

Checksums allow Oracle to detect corruption caused by underlying disks, storage systems, or I/O systems. If set to FULL, DB_BLOCK_CHECKSUM also catches in-memory corruptions and stops them from making it to the disk. Turning on this feature in TYPICAL mode causes only an additional 1% to 2% overhead. In the FULL mode it causes 4% to 5% overhead. Oracle recommends that you set DB_BLOCK_CHECKSUM to TYPICAL.

For backward compatibility the use of TRUE (implying TYPICAL) and FALSE (implying OFF) values is preserved.
Finally our discussion has reached to point where I will discuss about DB_ULTRA_SAFE.

DB_ULTRA_SAFE sets the default values for other parameters that control protection levels.

Values:

*

OFF

When any of DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, or DB_LOST_WRITE_PROTECT are explicitly set, no changes are made.
*

DATA_ONLY
o

DB_BLOCK_CHECKING will be set to MEDIUM.
o

DB_LOST_WRITE_PROTECT will be set to TYPICAL.
o

DB_BLOCK_CHECKSUM will be set to FULL.
*

DATA_AND_INDEX
o

DB_BLOCK_CHECKING will be set to FULL.
o

DB_LOST_WRITE_PROTECT will be set to TYPICAL.
o

DB_BLOCK_CHECKSUM will be set to FULL

Advertisements

Comments»

1. ck wee - October 13, 2010

thanks for sharing. much appreciated.

2. sendtoshailesh - October 13, 2010

Thank you


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: