ENG DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM

This article discusses mechanisms to detect and prevent the occurrence of corrupted data.

Table Of Contents

Introduction
DB_BLOCK_CHECKING – logical test
DB_BLOCK_CHECKSUM – check based on checksum
DB_ULTRA_SAFE – new option in 11g
DB_LOST_WRITE_PROTECT – check for DataGuard
Another literature

Entry

There is often a situation when there is a corrupted database block (bad block). This usually occurs due to corruption of data storage (i.e., disk). The same unit can be corrupted in memory (eg, bug) or deteriorate when input-output.

The problem here is that this block is usually found only at the time referred to it, ie, after some time after the occurrence of such a block. And this time may be long enough.

In order to detect and prevent the occurrence of corrupted data in Oracle, there are several mechanisms. These mechanisms allow to prevent data corruption or time to warn about it.

WARNING : By default, these mechanisms are disabled or are using a minimum level of checks (only tablespace SYSTEM).

WARNING : DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM – these are two different mechanisms!

So consider these mechanisms.

DB_BLOCK_CHECKING – This is the logical test unit integrity. This mechanism verifies the blocks in the memory information in the header blocks and identify logical errors, such as incorrect field length, etc. Blocks with logical fallacies are available for access and Oracle is trying to restore the integrity of the unit, if it fails to do that immediately caused the error ORA-01578: ORACLE data block corrupted.

The overhead of this mechanism for the 1% – 10%. The more DML operations, the more overhead (for the duration of individual operations can turn off this mechanism if the overhead is very high).

The documentation is written – Use a level check – FULL, if the overhead of your system is acceptable. There is an article on the MOS – Health Check Alert: Consider setting DB_BLOCK_CHECKING to the recommended value (Doc ID 957453.1) which is recommended to use only the lowest level of inspection. Although this article is from 2011 and it is possible that it is already outdated. It is also recommended prior to inclusion in the industrial database – check out all the database files using DBVERIFY.

Team

ALTER SYSTEM SET DB_BLOCK_CHECKING = {FALSE | OFF | LOW | MEDIUM | TRUE | FULL} SCOPE = {MEMORY | SPFILE | BOTH};

Levels of testing

(DEFAULT) OFF or FALSE – Check the blocks are not carried out (disabled). (For the SYSTEM tablespace, this mechanism is always enabled, regardless of the value DB_BLOCK_CHECKING).

LOW – Minimum check in the event of a change in the memory unit (eg, after an UPDATE or INSERT, block read from disk, the transfer unit to interconnect in Oracle RAC).

MEDIUM – All the test level LOW + full semantic checking blocks for any database objects except for the index (in the case of data corruption in the indexes, they can recreate through drop + rebuild).

FULL or TRUE – All the test level LOW and MEDIUM + full semantic checking blocks for any database objects.

Known bugs

The full list, see the – Init.ora Parameter “DB_BLOCK_CHECKING” Reference Note (Doc ID 68483.1). There are only interesting to me.

10.1.0.4 (fix 10.2.0.1) – Bug 4622960 – Less resource intensive block checking options needed (Doc ID 4622960.8) – Description of hidden parameters that allow fewer checks, and therefore less impact on performance.

11.2.0.2 (fix 11.2.03 + PSU) – Bug 9350204 – Spurious ORA-600 [kddummy_blkchk] .. [6145] during CR operations on tables with ROWDEPENDENCIES (Doc ID 9350204.8).

DB_BLOCK_CHECKSUM – This is a test blocks based on checksum (checksum). Ensures that you have read what was written. Calculates the checksum for a dirty block (dirty) and is recorded in the header block. The block is written to disk. When the next block is read from the disk, its checksum is again calculated and compared with the checksum stored in the block header. If the checksum is calculated does not match the checksum that was recorded in the block header – called the error ORA-01578: ORACLE data block corrupted. At the level of FULL (starting with 10gR2) checksum verification is done during each change unit during update / delete – it allows you to identify bad blocks in memory without writing to disk. If you are using a lower level FULL – mechanism does not determine the situation when the unit is already damaged in memory – there will db_block_checking, so it’s best to use just two mechanism db_block_checking and db_block_checksum simultaneously.

Commands

ALTER SESSION SET DB_BLOCK_CHECKSUM = {OFF | FALSE | TYPICAL | TRUE | FULL};
ALTER SYSTEM SET DB_BLOCK_CHECKSUM = {OFF | FALSE | TYPICAL | TRUE | FULL} SCOPE = {MEMORY | SPFILE | BOTH}; [ 1999007] Levels of testing

OFF or FALSE – Check the blocks are not carried out (disabled). (For the SYSTEM tablespace, this mechanism is always enabled, regardless of the value DB_BLOCK_CHECKSUM). Checksum for the redo logs blocks are not considered.

(DEFAULT) TYPICAL or TRUE – Powered checking blocks based on checksum (checksum) for any data blocks. Just considered checksum for redo logs blocks.

FULL – Checking the TYPICAL or TRUE + test blocks in the memory during the update / delete.

The overhead for this mechanism to level TYPICAL 1% – 2% to 4% FULL – 5%. The more DML operations, the more overhead (for the duration of individual operations can turn off this mechanism if the overhead is very high).

The documentation is written – Use a level check – TYPICAL.

Known bugs

11.2.0.1 – If the version 11.2.0.2 or higher and DB_BLOCK_CHECKSUM = FULL, be sure to read about _verify_fg_log_checksum – Bug 9717227 – Internal fix affecting DB_BLOCK_CHECKSUM = FULL behaviour (Doc ID 9717227.8).

10.2.0.3, 11.1.0.6 – Bug 6814520 – Solaris: Poor performance with DB_BLOCK_CHECKSUM = TRUE (Doc ID 6814520.8).

In 11g introduced a new parameter DB_ULTRA_SAFE . Article – New Parameter DB_ULTRA_SAFE Introduced In 11g (Doc ID 465130.1) to talk about integrated protection mechanisms. But in my opinion it is just a setting that allows you to control other parameters responsible for the mechanisms of protection against corrupted data DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, and DB_LOST_WRITE_PROTECT and nothing else.

WARNING : This parameter can not be changed dynamically, ie must restart the database. (I believe that this is a serious shortcoming of this parameter).

In my opinion it is more convenient not to use this option, but to manage those parameters that were DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, and DB_LOST_WRITE_PROTECT.

Team

ALTER SYSTEM SET DB_ULTRA_SAFE = {OFF | DATA_ONLY | DATA_AND_INDEX} SCOPE = {SPFILE};

The parameter values

OFF – If you explicitly set the parameters DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, or DB_LOST_WRITE_PROTECT – no changes are made. Ie DB_ULTRA_SAFE option in this case does not affect the other parameters.

DATA_ONLY – Sets the parameters:

DB_BLOCK_CHECKING = MEDIUM
DB_BLOCK_CHECKSUM = FULL
DB_LOST_WRITE_PROTECT = TYPICAL

DATA_AND_INDEX – Sets the parameters:

DB_BLOCK_CHECKING = FULL
DB_BLOCK_CHECKSUM = FULL
DB_LOST_WRITE_PROTECT = TYPICAL

When writing unit, input-output subsystem can report that the recording is completed, when in fact it is not. This mechanism is used in the configuration DataGuard to detect such situations.

Details on setting up this mechanism better read – Best Practices for Corruption Detection, Prevention, and Automatic Repair – in a Data Guard Configuration (Doc ID 1302539.1).

Known bugs

11.2.0.2, 11.2.0.3 (fix 11.2.0.4, 12.1.0.1) – Bug 13928657 – ORA-600 [kcbz_zib_simulation_1] when cache is resized or following switchover (Doc ID 13928657.8) – associated with DB_ULTRA_SAFE.

Another literature:

1. TECH: Database Block Checking Features (Doc ID 32969.1) – Good and useful article. An overview of some features in Oracle to identify corrupted data.

2. Best Practices for Avoiding and Detecting Corruption (Doc ID 428570.1). A small overview of methods for finding corrupted data.

3. Performance Impact On Compressed Tables In a DB Utilizing DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM (Doc ID 1538563.1) – Impact on the inclusion DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM for Advanced Compression tables.

4. Init.ora Parameter “DB_BLOCK_CHECKSUM” Reference Note (Doc ID 30706.1) – There is a related bugs.

5. Enhanced Block Integrity Checking in Memory Using DB_BLOCK_CHECKSUM (Doc ID 336194.1) – Checking the blocks in memory FULL, starting with 10gR2.
Dmitry Bobrovsky
6. Init.ora Parameter “DB_ULTRA_SAFE” Reference Note (Doc ID 567096.1) – There is a related bugs.
Dmitry Bobrovsky
7. Init.ora Parameter “DB_BLOCK_CHECKING” Reference Note (Doc ID 68483.1) – There is a related bugs.

(Visited 3,142 times, 1 visits today)
adminTips
ENG DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM This article discusses mechanisms to detect and prevent the occurrence of corrupted data. Table Of Contents Introduction DB_BLOCK_CHECKING - logical test DB_BLOCK_CHECKSUM - check based on checksum DB_ULTRA_SAFE - new option in 11g DB_LOST_WRITE_PROTECT - check for DataGuard Another literature Entry There is often a situation...

Do you want to be notified about new DBA updates, releases, jobs and free tips? Join our email newsletter. It's fast and easy. You will be among the first to know about hot new DBA updates and stuff, it will help you enhance your DBA skills.
We take your privacy very seriously