Summary of methods for detecting ORACLE data block corruption, and oracle Summary

Source: Internet
Author: User

Summary of methods for detecting ORACLE data block corruption, and oracle Summary

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:

Http://blog.csdn.net/yidian815/article/details/39932903

 

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.

 

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 2014-11-05 15:44:21using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/home/app/oraten/oradata/oraten/system01.dbfinput datafile fno=00003 name=/home/app/oraten/oradata/oraten/sysaux01.dbfinput datafile fno=00002 name=/home/app/oraten/oradata/oraten/undotbs01.dbfinput datafile fno=00005 name=/home/app/oraten/oradata/oraten/test01.dbfinput datafile fno=00004 name=/home/app/oraten/oradata/oraten/users01.dbfchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding current control file in backupsetincluding current SPFILE in backupsetchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 2014-11-05 15:44:23 RMAN> list backup;  RMAN> 

You can also add the check logical option to enable rman to perform logical consistency detection. Note: check logical is a command option. If you use check logical separately, a backup is generated:

RMAN> backup check logical validate database; Starting backup at 2014-11-05 15:45:59using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/home/app/oraten/oradata/oraten/system01.dbfinput datafile fno=00003 name=/home/app/oraten/oradata/oraten/sysaux01.dbfinput datafile fno=00002 name=/home/app/oraten/oradata/oraten/undotbs01.dbfinput datafile fno=00005 name=/home/app/oraten/oradata/oraten/test01.dbfinput datafile fno=00004 name=/home/app/oraten/oradata/oraten/users01.dbfchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:03channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding current control file in backupsetincluding current SPFILE in backupsetchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 2014-11-05 15:46:04 RMAN> list backup;  RMAN> backup check logical database; Starting backup at 2014-11-05 15:46:14using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/home/app/oraten/oradata/oraten/system01.dbfinput datafile fno=00003 name=/home/app/oraten/oradata/oraten/sysaux01.dbfinput datafile fno=00002 name=/home/app/oraten/oradata/oraten/undotbs01.dbfinput datafile fno=00005 name=/home/app/oraten/oradata/oraten/test01.dbfinput datafile fno=00004 name=/home/app/oraten/oradata/oraten/users01.dbfchannel ORA_DISK_1: starting piece 1 at 2014-11-05 15:46:14channel ORA_DISK_1: finished piece 1 at 2014-11-05 15:46:39piece handle=/home/app/oraten/flash_recovery_area/ORATEN/backupset/2014_11_05/o1_mf_nnndf_TAG20141105T154614_b5mog6kr_.bkp tag=TAG20141105T154614 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:25channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding current control file in backupsetincluding current SPFILE in backupsetchannel ORA_DISK_1: starting piece 1 at 2014-11-05 15:46:40channel ORA_DISK_1: finished piece 1 at 2014-11-05 15:46:41piece handle=/home/app/oraten/flash_recovery_area/ORATEN/backupset/2014_11_05/o1_mf_ncsnf_TAG20141105T154614_b5moh030_.bkp tag=TAG20141105T154614 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 2014-11-05 15:46:41 List of Backup Sets=================== BS Key  Type LV Size       Device Type Elapsed Time Completion Time    ------- ---- -- ---------- ----------- ------------ -------------------59      Full    545.84M    DISK        00:00:17     2014-11-05 15:46:31        BP Key: 41   Status: AVAILABLE  Compressed: NO  Tag: TAG20141105T154614        Piece Name: /home/app/oraten/flash_recovery_area/ORATEN/backupset/2014_11_05/o1_mf_nnndf_TAG20141105T154614_b5mog6kr_.bkp  List of Datafiles in backup set 59  File LV Type Ckp SCN    Ckp Time            Name  ---- -- ---- ---------- ------------------- ----  1       Full 870906     2014-11-05 15:46:14 /home/app/oraten/oradata/oraten/system01.dbf  2       Full 870906     2014-11-05 15:46:14 /home/app/oraten/oradata/oraten/undotbs01.dbf  3       Full 870906     2014-11-05 15:46:14 /home/app/oraten/oradata/oraten/sysaux01.dbf  4       Full 870906     2014-11-05 15:46:14 /home/app/oraten/oradata/oraten/users01.dbf  5       Full 870906     2014-11-05 15:46:14 /home/app/oraten/oradata/oraten/test01.dbf BS Key  Type LV Size       Device Type Elapsed Time Completion Time    ------- ---- -- ---------- ----------- ------------ -------------------60      Full    6.80M      DISK        00:00:01     2014-11-05 15:46:40        BP Key: 42   Status: AVAILABLE  Compressed: NO  Tag: TAG20141105T154614        Piece Name: /home/app/oraten/flash_recovery_area/ORATEN/backupset/2014_11_05/o1_mf_ncsnf_TAG20141105T154614_b5moh030_.bkp  Control File Included: Ckp SCN: 870915       Ckp time: 2014-11-05 15:46:39  SPFILE Included: Modification time: 2014-11-05 15:16:33<span style="font-size: 12px; font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);"> </span>

For verification of rman backup and recovery, refer:

Http://blog.csdn.net/yidian815/article/details/40824689

 

 

5: Use the dbms_repair package

For how to use the dbms_repaire package, see:

Http://blog.csdn.net/yidian815/article/details/40825511

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.