{"id":2582,"date":"2012-06-08T11:15:00","date_gmt":"2012-06-08T09:15:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/protecting-an-oracle-database-from-block-corruption\/"},"modified":"2012-06-08T11:15:00","modified_gmt":"2012-06-08T09:15:00","slug":"protecting-an-oracle-database-from-block-corruption","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/protecting-an-oracle-database-from-block-corruption\/","title":{"rendered":"Protecting an Oracle database from block corruption"},"content":{"rendered":"

Data can be corrupted in different ways, for example an I\/O issue when reading data from the storage or a memory issue when updating data. The db_block_checking parameter allows to change the behavior of an Oracle database concerning block checking.<\/p>\n

This posting will present this parameter and some tests to measure its impact on the database.<\/p>\n

According to the documentation, the db_block_checking parameter specifies if Oracle performs a block checking or not. Block checking can prevent data corruption in the memory but it does not prevent from human corruption if a user inserts or updates wrong data.<\/p>\n

Settings<\/h3>\n

db_block_checking is a dynamic parameter, it can be set at session or system level :\u00a0alter system\/session set\u00a0db_block_checking =\u00a0Value<\/em>
\nFrom Oracle 8.1.6 to 10g, only two choices were possible, TRUE or FALSE. Siince Oracle 10gR2, 4 values are possible OFF, LOW, MEDIUM, and FULL.<\/p>\n\n\n\n\n\n\n\n
Value<\/strong><\/td>\nEffect<\/strong><\/td>\n<\/tr>\n
OFF\/FALSE<\/td>\nNo checks on blocks in user tablespaces. Semantic block checking is always enabled on SYSTEM tablespace.<\/td>\n<\/tr>\n
LOW<\/td>\nBasic block header checks after block changes in memory.<\/td>\n<\/tr>\n
MEDIUM<\/td>\nSame as LOW plus semantic block checking for all table blocks (except index organized tables).<\/td>\n<\/tr>\n
FULL\/TRUE<\/td>\nSame as MEDIUM plus semantic block checking on index blocks.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Semantic check consists in Oracle going through the data in the block to make sure it is logically self-consistent.<\/p>\n

As checks are performed when changes are performed on a block, the main interest of this feature is to get an internal error as soon as a corrupted block is detected to avoid wide-spread corruption from occurring: corrupted blocks are identified before they are marked corrupted.<\/p>\n

The main disadvantage is the potential overhead on the system, from 1% and up to 10% according to Oracle. In fact, it depends on the amount of update\/insert in the workload.<\/p>\n

This parameter can be set explicitly by a statement but it can be set by through the db_ultra_safe init parameter. Enabling db_ultra_safe will set db_block_checking automatically to MEDIUM or FULL depending on the value of\u00a0db_ultra_safe.<\/p>\n