Four Methods for checking data block consistency

(I) What is data block consistency?

Each data block header has a "checksum" field.
Before the data block is written back to the disk, Oracle recalculates the checksum.
Record this field and write it back to the disk.
The next time a data block is read into the memory, Oracle recalculates the data block checksum.
And compare with the value in the checksum Field
If there is a difference, Oracle throws a ORA-1578
That is, the entire verification process:
When writing back, computation and save
Calculate and compare the read data

Checking through the checksum field is called the physical consistency check, which focuses on hardware faults and does not care whether the content is correct or not.
The logical consistency check takes over this task, for example, whether the record corresponds to the index, and whether the record is locked by a non-existent transaction.

Db_block_checksum: Physical consistency check
When the value is true, in addition to checking the data blocks in all tablespaces, Oracle also performs the checksum on the redo log blocks.
If this parameter is set to false, only the data blocks in the system tablespace are verified.
We recommend that you enable this parameter for Oracle.

Db_block_checking: logical consistency check
When the value is false, only the system tablespace is checked for logical consistency.
The impact on performance is relatively high, and the DBA must weigh the impact on performance.

(Ii) Four Tools for verification

① DBV

Check the consistency between physical and logical data files.
However, it does not check the matching of indexes.
For more information, see my previous blog:
Introduction to the Oracle tool DBV

② Analyze

Same physical and logical consistency check
Able to check the matching between Indexes
If the check fails, the problem will be placed in the TRC file of user_dump_dest.
Analyze checks object consistency Syntax:
Analyze table table_name validate Structure Cascade online (offline)

Note:

(1) cascade: you can confirm that each record has an index.
(2) Online: Checks online consistency, but does not collect object statistics.
(3) offline: Collects object statistics, but the table is locked.
(4) When checking whether the records in the Partition Table are correctly partitioned, you can put the checked rowid in the special table invalid_rows.
Run $ ORACLE_HOME/rdbms/admin/utlvalid. SQL script before this operation.
Syntax:
Analyze table table_name validate structure into invalid_rows;

As this command is also important, I will write a blog to introduce it!


③ RMAN

When using RMAN backup, data blocks are read to the read buffer zone of RMAN, then copied to the write buffer zone of RMAN, and then written to the physical media from the write buffer zone.
During the copy process from the read buffer to the write buffer, RMAN checks the data block consistency.
Syntax:
Backup check logical validate;
Note:
This command only performs consistency check and does not back up
Put the check result in V $ database_block_corruption.
Logical: Checks logical consistency.
Validate: checks the physical consistency.
Example:
Backup check logical validate datafile 1;

In addition to checking data files, RMAN can also check the files that have been backed up.
Syntax:
Restore validate
Example:
Check Database Backup
Restore validate database;
Check the backup control file
Restore validate controlfile to '/u01 /....';
Check archived log files
Restore validate archivelog from sequence X until sequence y;


④ Dbms_repair

Discover, identify, and modify Bad blocks in data files
However, using this package may cause data loss, inconsistent data returned from tables and indexes, and damage to integrity constraints.
Therefore, dbms_repair is only a method used without backup. This method usually causes data loss.
The working principle of the dbms_repair package is relatively simple. It is to mark the detected bad block so that subsequent DML operations can skip this block.
At the same time, the dbms_repair package also provides the process of saving the key values marked as bad blocks in the index and repairing freelist and segment bitmap.

Note that the dbms_repair package is not authorized, and only sys users can execute

This package is complicated to use. I will also write a special blog to introduce it!