首页 > 代码库 > 检测ORACLE数据块损坏的方法汇总
检测ORACLE数据块损坏的方法汇总
1:使用初始化参数
使用初始化参数db_block_checksum\db_block_checking可以设置数据库对块的物理一致性和逻辑一致性检查。
Db_block_checksum:物理一致性检查,默认开启。始终对system表空间数据进行检查,如果开启还会对日志进行检查,开启后会有1~2%的性能影响,建议开启。
Db_block_checking:逻辑一致性检查,默认未开启。始终对system表空间进行检查,如果开启会有1~10%的性能影响。DML越频繁,性能影响越大。不建议开启。
有关该参数的详细内容可以参考:
http://blog.csdn.net/yidian815/article/details/39932903
2:使用dbv工具
使用dbv可以进行物理和逻辑一致性检查,但是不会检测表数据和索引数据的匹配关系。使用该工具有以下特点:
只读方式打开数据文件
可以在线检查数据文件,数据库无需关闭
不可以检测控制文件和日志文件
可以检测asm文件
有时文件名称需要有后缀名,并且有时不可以检测超过2G大小的文件
3:使用analyze命令
语法格式:analyze table validate structure cascade online (offline).
该命令会同时进行物理和逻辑检查,同时还可以进行表数据和索引数据的匹配性检查,检查分区表的记录是否在正确的分区中,检测结果会存储在trace文件中。
如果没有使用cascade关键字,则只会进行表数据的检测,使用cascade后会进行表数据和索引数据的检测。
如果使用了online关键字,则可以对表数据进行在线检查,此时不会对表添加锁,DML语句可以继续进行,如果使用了offline关键字,则会对表加锁,此时用户不能对该表进行修改操作。
如果需要检测分区表记录是否在正确的分区内,可以使用如下语句:
Anlyze table table_name validte structure into invalid_rows.
使用该命令前,请使用utlvalid.sql 脚本创建相应的invalid_rows表。
4:使用rman工具
使用rman备份工具时,oracle会将数据先读入读缓冲区,然后将数据写入写缓冲区,最后写入磁盘,在从读缓冲区转移到写缓冲区时,rman工具会进行数据一致性检测。
语法格式:backup validate database.
使用backup validate 命令会对数据文件进行物理一致性检测(前提是db_block_checksum启用), 此时并不会生成备份文件。如:
RMAN> backup validate database; Starting backup at 2014-11-05 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 2014-11-05 15:44:23 RMAN> list backup; RMAN>
我们也可以添加check logical选项,以便rman进行逻辑一致性检测。注意:check logical是命令选项,如果单独使用check logical会生成备份:
RMAN> backup check logical validate database; Starting backup at 2014-11-05 15:45:59 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:03 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 2014-11-05 15:46:04 RMAN> list backup; RMAN> backup check logical database; Starting backup at 2014-11-05 15:46:14 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: starting piece 1 at 2014-11-05 15:46:14 channel ORA_DISK_1: finished piece 1 at 2014-11-05 15:46:39 piece handle=/home/app/oraten/flash_recovery_area/ORATEN/backupset/2014_11_05/o1_mf_nnndf_TAG20141105T154614_b5mog6kr_.bkp tag=TAG20141105T154614 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25 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: starting piece 1 at 2014-11-05 15:46:40 channel ORA_DISK_1: finished piece 1 at 2014-11-05 15:46:41 piece handle=/home/app/oraten/flash_recovery_area/ORATEN/backupset/2014_11_05/o1_mf_ncsnf_TAG20141105T154614_b5moh030_.bkp tag=TAG20141105T154614 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 Finished 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>
关于rman备份与恢复的验证,可以参照:
http://blog.csdn.net/yidian815/article/details/40824689
5:使用dbms_repair包
关于dbms_repaire包的使用,请参见:
http://blog.csdn.net/yidian815/article/details/40825511
检测ORACLE数据块损坏的方法汇总