Summary of methods for detecting Oracle data block corruption

Summary of methods for detecting Oracle data block corruption

1: Use initialization parameters

You can use the initialization parameter db_block_checksum \ db_block_checking to set the database's physical consistency and logical consistency check for blocks.

Db_block_checksum: Physical consistency check, which is enabled by default. Always check the system tablespace data. If enabled, logs are also checked. Once enabled, 1 ~ 2% performance impact. We recommend that you enable this feature.

Db_block_checking: logical consistency check, which is disabled by default. Always check the system tablespace. If it is enabled, 1 ~ 10% performance impact. The more DML, the greater the impact on performance. It is not recommended to enable this function.

For details about this parameter, refer:

2: Use the dbv Tool

Dbv can be used for physical and logical consistency checks, but does not detect the matching relationship between table data and index data. The tool has the following features:

Open Data Files in read-only mode

You can check data files online without shutting down the database.

Control Files and log files cannot be detected.

Detects asm files.

Sometimes the file name must have a suffix, and sometimes it cannot detect files larger than 2 GB.

3: Use the analyze command

Syntax format: analyze table validate structure cascade online (offline ).

This command performs both physical and logical checks. It also checks the matching of table data and index data to check whether the records of the partition table are in the correct partition, the detection results are stored in the trace file.

If the cascade keyword is not used, only table data is detected. After cascade is used, table data and index data are detected.

If the online keyword is used, you can check the table data online without adding a lock to the table. The DML statement can continue. If the offline keyword is used, the table is locked, you cannot modify the table.

To check whether the Partition Table records are in the correct partition, use the following statement:

Anlyze table table_name validte structure into invalid_rows.

Before using this command, use the utlvalid. SQL script to create the corresponding invalid_rows table.

-------------------------------------- Recommended reading --------------------------------------

RMAN: Configure an archive log deletion policy

Basic Oracle tutorial-copying a database through RMAN

Reference for RMAN backup policy formulation

RMAN backup learning notes

Oracle Database Backup encryption RMAN Encryption

-------------------------------------- Split line --------------------------------------

4: Use the rman Tool

When using the rman backup tool, oracle reads data into the read buffer, writes the data to the write buffer, and finally writes the data to the disk. when the data is transferred from the read buffer to the write buffer, the rman tool checks data consistency.

Syntax format: backup validate database.

Use the backup validate command to check the physical consistency of data files (provided that db_block_checksum is enabled). At this time, no backup file is generated. For example:

RMAN> backup validate database;
 
Starting backup at 15:44:21
Using channel ORA_DISK_1
Channel ORA_DISK_1: starting full datafile backupset
Channel ORA_DISK_1: specifying datafile (s) in backupset
Input datafile fno = 00001 name =/home/app/oraten/oradata/oraten/system01.dbf
Input datafile fno = 00003 name =/home/app/oraten/oradata/oraten/sysaux01.dbf
Input datafile fno = 00002 name =/home/app/oraten/oradata/oraten/undotbs01.dbf
Input datafile fno = 00005 name =/home/app/oraten/oradata/oraten/test01.dbf
Input datafile fno = 00004 name =/home/app/oraten/oradata/oraten/users01.dbf
Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Channel ORA_DISK_1: starting full datafile backupset
Channel ORA_DISK_1: specifying datafile (s) in backupset
Including current control file in backupset
Including current SPFILE in backupset
Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15:44:23
 
RMAN> list backup;
 
 
RMAN>

For more details, please continue to read the highlights on the next page:

1 2 Next Page