4 Methods of data block consistency check

㈠ What is data block consistency.

Each block header has a "checksum" field
Oracle recalculates this checksum before the data block is written back to the disk
and record this field, and finally write back to the disk
The next time a block of data is read into memory, Oracle recalculates the checksum of the data block
and compare the values in the checksum field
If there are differences, Oracle throws ORA-1578
That is, the entire validation process:
When writing back, compute and save
Read, calculate and compare

Checking with a checksum field is called a physical consistency check, which focuses on hardware failures and does not care about the correct content
The logical consistency check takes over the task, such as whether the records and indexes correspond, whether the records are locked by nonexistent transactions, and so on.

Db_block_checksum: Physical consistency check
When True, Oracle verifies and checks the redo log block in addition to the data blocks for all tablespaces
If False, only the data blocks of the system table space are validated
Oracle recommends opening this parameter

Db_block_checking: Logical Consistency check
When false, only logical consistency checks are made on the system table space
Greater performance impact, requiring DBA to weigh

㈡4 Tool Verification

①dbv

You can check the physical and logical consistency of a data file
But the table and index matches are not checked
Specifically, see my previous blog:
Introduction to the use of Oracle tool DBV

②analyze

Also perform physical and logical consistency checks
Ability to check table and index compatibility
If you check for problems, you will put the problem in the User_dump_dest TRC file.
Analyze Check object consistency syntax:
Analyze table TABLE_NAME Validate structure cascade online (offline)

Comments:

⑴cascade: You can confirm that each record has a corresponding index
⑵online: Online Consistency check, just do not collect object statistics
⑶offline: Can collect object statistics, only the table will be locked
⑷ you can place the rowid of a checked record in a special table invalid_rows when checking that the records in the partitioned table are partitioned correctly
This needs to run before: $ORACLE _home/rdbms/admin/utlvalid.sql script
The corresponding syntax:
Analyze table table_name validate structure into invalid_rows;

Because this command is also more important, I will write a blog introduction.


③rman

When an Rman backup is used, the data block is read first to the read buffer of the Rman, and then to the write buffer of Rman, and finally from the write buffer to the physical media
In the copy process from the read buffer to the write buffer, Rman checks the data block for consistency
Grammar:
Backup check logical validate;
Comments:
This command is only checked for consistency and is not backed up
The test results are placed in the V$database_block_corruption
Logical: Conducting a logical consistency check
Validate: Physical consistency check
Example:
Backup check logical validate DataFile 1;

In addition to checking the data file, Rman can also check for files that have been backed up
Grammar:
Restore Validate
Example:
Check Database backup
Restore validate database;
Check the backup control files
Restore validate Controlfile to '/u01/... ';
Checking archive log files
Restore validate Archivelog from sequence x until sequence y;


④dbms_repair

Discover, identify, and modify bad blocks in data files
But using this package will also result in data loss, inconsistent table and index return data, integrity constraints, and other issues
As a result, Dbms_repair is just a means to use without backup, which generally results in loss of data
The Dbms_repair package works fairly simply by labeling the bad blocks that are checked out so that subsequent DML operations skip the Block
The Dbms_repair package also provides a procedure for saving key values contained in the index as bad blocks, as well as repairing freelist and segment bitmap

One thing to note is that the Dbms_repair package is not authorized and only the SYS user can perform

The use of this package is more complex, I will also write a blog to introduce.