Understanding of the Oracle database bad block

1. Physical bad blocks and logical bad blocks

There is a concept in the database called data block consistency, Oracle's data block consistency includes two levels: physical consistency and logical consistency, if a data block at these two levels of inconsistency, it corresponds to the physical and logical bad blocks we want to say today.

There is a checksum field at the head of each block, and Oracle recalculates the checksum of the block every time the block is written back to the disk, and logs the field to the final write disk.    The next time the data block is read into memory, Oracle recalculates the checksum of the chunk and compares it to the field of the block, and if there is a difference, Oracle knows that the block has errors and will report a ORA-1578 error. A check by a checksum field is a physical consistency check that allows Oracle to check for all underlying errors, including disk, storage, and IO subsystems, so it is called a physical consistency check.

In Oracle, whenever a block of data is to be modified, a consistency check is made on the contents of the block (such as checking that the records are locked by food that does not exist, whether the records and indexes correspond, etc.). If this consistency check fails, a ORA-600 internal error is thrown. When Oracle checks the logical consistency of the data block, it attempts to Cache the block of data, and if the block is not restored to a consistent state, Oracle will flag the block as software corrupt, and when a query accesses the block, the Recovery The previous ORA-1578 error will also be thrown.

The physical consistency check uses the checksum field to focus primarily on checking for hardware failures and not about the correctness of the content, while the logical consistency check is focused on content checking, which is much more complex than checksum checking. Logical consistency checks are more content than physical consistency checks.

2. Causes of bad blocks

There are a variety of reasons for data block corruption, but due to physical reasons, it can also be caused by human-caused or Oracle bugs. More common human-caused abnormal shutdown, power-down, termination of the service so that the process terminates abnormally, thus destroying the integrity of the data, resulting in the generation of bad blocks. When the cause of block corruption occurs, the most typical behavior is encountering ORA-01578 errors (such as when a full table is scanned), and sometimes ORA-600 internal errors.

3. Check the consistency of the data blocks

(1) Check data block consistency with initialization parameters

Enable, turn off physical consistency checks by setting the initialization parameter db_block_checksum=true/false, enabling this parameter increases the load on 1%~2%, which is recommended by Oracle. In Oracle9i, 10g, the default is enabled.

By setting the parameter db_block_checking=true, you can enable logical consistency checking of the data block. Enabling this parameter increases the load on the 1%~10%, especially if the DML operation is more dense and the performance impact is greater. The default value of this parameter in oracle10g is false, which means that the database only checks the system table space for logical consistency.

(2) DBV Tool for Data block physical/logical check

Details: http://book.51cto.com/art/201108/287893.htm

(3) Analyze command Check data block consistency

Analyze command has a lot of usage, can complete a lot of functions, specifically, refer to: http://blog.csdn.net/tianlesoftware/article/details/7055164

Analyze command Check object consistency: Analyze table tablename Validate structure cascade online (offline);

The command also performs physical and logical consistency checks. In addition, it checks the matching of table and index entries, checks if the records of partitioned tables are in the correct partition, and checks for problems that are placed in the trace file of the User_dump_dest directory.

Use this command to note a few questions:

* When checking the partition table records are in the correct partition, you can record the rowid of the records in a special table, this table is called invalid_rows, the table needs to be established in advance with Utlvalid.sql script, check the syntax: Analyze tablename Validate structure into invalid_rows;

* If the index is checked, this command checks the data block and index consistency, but does not confirm that each record has a corresponding index entry or that the index has corresponding records, if this check is required, the CASCADE keyword is used.

* If you use the online clause, you can check for consistency on-line during DML operations, but the statistics for the object are not collected at this point, and if you use the offline table it will be locked.

(4) Use the Rman tool to check

* Check Data file
Backup check logical validate [database];

* Check individual data files
Backup check logical Validate DataFile 1, 2;

* Check the entire library
The results of the backup check logical validate database check are placed in the v$database_block_corruption dynamic view.

* This command has a certain effect on the system performance.

(5) Perform the check using the Dbms_repair.check_object method
For more information, refer to: http://blog.itpub.net/8494287/viewspace-1357457/

(6) Export entire database with EXP tool to detect bad blocks

The Export command will report a ORA-01578 error in execution, which will prompt the file number in the error prompt and which block in the file is damaged, such as Ora-01578:oracle block corruption (file number 4, block number 35). The use of the Exp tool to check the bad block is also limited, for some types of bad blocks can not be detected such as: Hwm above the bad block is not found, the index of the existence of the bad block is not found, the data dictionary of bad blocks will not be found.

-----to Be Continued

Understanding of the Oracle database bad block