Summary of methods for detecting broken Oracle data blocks

1 : Using initialization parameters

Use the initialization parameter db_block_checksum\db_block_checking to set the physical and logical consistency checks for a database block.

Db_block_checksum: Physical consistency check, enabled by default. The system tablespace data is always checked, and if the log is checked when turned on, there will be a performance impact of 1~2% after opening, it is recommended to open.

Db_block_checking: Logical consistency check, default not turned on. the system table Space is always checked for 1to10% performance impact if turned on. The more frequently DML, the greater the performance impact. It is not recommended to open.

For more information on this parameter, refer to:

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

2: Using the dbv tool

Physical and logical consistency checks can be performed using DBV, but the matching relationship between table data and index data is not detected. Using this tool has the following features:

Open data file as read-only mode

Data files can be checked online and databases need not be shut down

Control files and log files cannot be detected

Can detect asm Files

Sometimes file names need to have a suffix, and sometimes it is not possible to detect files larger than 2G in size

3: Use the analyze command

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

The command also checks both physical and logical, as well as matching the table data to the index data, checking that the partition table records are in the correct partition, and the test results are stored in the trace file.

If you do not use the cascade keyword, only the detection of the table data will be performed, and the table data and index data will be detected after using cascade.

If the online keyword is used, the table data can be checked on-line, no locks are added to the table, andDML Statements can proceed if offline is used keyword, the table is locked and the user cannot modify the table.

If you need to detect if a partitioned table record is within the correct partition, you can use the following statement:

ANLYZE Table TABLE_NAME VALIDTE structure into invalid_rows.

Use the utlvalid.sql script to create the appropriate invalid_rows table before using this command .

4: Using the rman tool

When using the Rman Backup tool,Oracle reads the data into the read buffer, writes the data to the write buffer, and finally writes to the disk, and when it transfers from the read buffer to the write buffer,theRman The tool will perform data consistency detection.

Syntax format:backup validate database.

Using the backup validate command will detect the physical consistency of the data file (provided db_block_checksum is enabled ), The backup file is not generated at this time. such as:

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 Datafi Le (s) in backupsetincluding current control file in backupsetincluding current SPFILE in Backupsetchannel ora_disk_1:back  Up set complete, elapsed time:00:00:01finished backup at 2014-11-05 15:44:23 rman> list backup; Rman>

We can also add the check logical option to enable Rman for logical consistency detection. Note:check Logical is a command option, and if you use check logical alone, 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 Datafi Le (s) in backupsetincluding current control file in backupsetincluding current SPFILE in Backupsetchannel ora_disk_1:back  Up 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/fla SH_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:4 0channel 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--------------------------------------- --------------------------545.84M DISK 00:00:17 2014-11-05 15:46:31 BP key:41 Sta Tus:available compressed:no tag:tag20141105t154614 Piece Name:/home/app/oraten/flash_recovery_area/oraten/bac    KUPSET/2014_11_05/O1_MF_NNNDF_TAG20141105T154614_B5MOG6KR_.BKP List of Datafiles in backup set-A-File LV Type CKP SCN CKP time Name-------------------------------------------1 full 870906 2014-11-05 15:46:14/home/app/oraten/oradata/oraten/system     01.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/o RATEN/ORADATA/ORATEN/USERS01.DBF 5 full 870906 2014-11-05 15:46:14/home/app/oraten/oradata/oraten/test01.dbf B S Key type LV Size Device Type Elapsed Time completion time---------------------------------------------- -------------------6.80M DISK 00:00:01 2014-11-05 15:46:40 BP key:42 Status:ava ilable compressed:no tag:tag20141105t154614 Piece Name:/HOME/APP/ORATEN/FLASH_RECOVERY_AREA/ORATEN/BACKUPSET/2 014_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 The validation of Rman backup and recovery, you can refer to:

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

5: Using the dbms_repair Package

For The use of the Dbms_repaire package, see:

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

Summary of methods for detecting broken Oracle data blocks