检测ORACLE方法汇总数据块损坏

2023-07-21 13:32:52

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文件里。

假设没有使用cascadekeyword。则仅仅会进行表数据的检測。使用cascade后会进行表数据和索引数据的检測。

假设使用了onlinekeyword，则能够对表数据进行在线检查。此时不会对表加入锁，DML语句能够继续进行。假设使用了offlinekeyword，则会对表加锁。此时用户不能对该表进行改动操作。

假设须要检測分区表记录是否在正确的分区内，能够使用例如以下语句：

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

码农公寓

相关文章