检查数据块损坏(Block Corruption)_综合

    数据块的损坏分两种情况，第一种是物理性的，第二种是逻辑性的。物理性一般指数据块头部不可以访问、数据块校验值不合法。逻辑性一般是在物理性结构完整的情况下，数据的内容在含义上不正确，比如保存了不允许的字段值。
   下面分别用两种情况说明数据块的物理错误和数据的逻辑错误。
   数据块物理错误:
SYS@orcl>create tablespace test datafile '/u01/app/oracle/oradata/orcl/test01.dbf' size 10m;
Tablespace created.

SYS@orcl>create table scott.test tablespace test as select * from dba_objects where rownum <=100;
Table created.

---test表从数据块128号开始的8个块(128-135)，数据文件是6号。
SYS@orcl>select a.file_id,a.block_id,a.blocks,b.name from dba_extents a,v$datafile b where a.file_id=b.file# and a.owner='SCOTT' and a.segment_name='TEST';
   FILE_ID   BLOCK_ID      BLOCKS NAME
---------- ---------- ---------- ---------------------------------------------
   6      128           8 /u01/app/oracle/oradata/orcl/test01.dbf

---test所有的行保存在131和132数据块中。
SYS@orcl>select distinct dbms_rowid.rowid_block_number(rowid) from scott.test;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
               132
               131

---改变132数据块的内容
[oracle@ora ~]$ dd of=/u01/app/oracle/oradata/orcl/test01.dbf bs=8192 conv=notrunc seek=132 <<EOF
> abcdefghijklmnopqrstuvwxyz
> EOF
0+1 records in
0+1 records out
27 bytes (27 B) copied, 3.2686e-05 s, 826 kB/s

   数据逻辑错误：
---创建range分区表
SYS@orcl>create table scott.emp1 (empno number(4),ename varchar2(10),deptno number(2))
2 partition by range (deptno)
3 (partition p1 values less than (10) tablespace users,
4   partition p2 values less than (20) tablespace users,
5 partition p3 values less than (30)) tablespace users;
Table created.

---test表从数据块520号开始的8个块(520-527)，数据文件是4号。
SYS@orcl>select a.file_id,a.block_id,a.blocks,b.name from dba_extents a,v$datafile b where a.file_id=b.file# and a.owner='SCOTT' and a.segment_name='EMP1';
   FILE_ID   BLOCK_ID      BLOCKS NAME
---------- ---------- ---------- ---------------------------------------------
   4      520           8 /u01/app/oracle/oradata/orcl/users01.dbf

---deptno是30的记录不能插入emp1表。
SYS@orcl>insert into scott.emp1 values(1000,'SCOTT',30);
insert into scott.emp1 values(1000,'SCOTT',30)
                  *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

---使用交换分区的方式让test表接受deptno为30的行。
SYS@orcl>create table scott.emp2 (empno number(4),ename varchar2(10),deptno number(2)) tablespace users;
Table created.

SYS@orcl>insert into scott.emp2 values(1000,'SCOTT',30);
1 row created.

SYS@orcl>alter table scott.emp1 exchange partition p3 with table scott.emp2 without validation;
Table altered.

---deptno为30的记录已插入emp1表
SYS@orcl>select * from scott.emp1 partition (p3);
     EMPNO ENAME      DEPTNO
---------- ---------- ----------
      1000 SCOTT          30

   oracle提供了很多工具用来检查数据块是否损坏，有的可以从物理层面上检查，有的可以从逻辑层面上检查。

   1.1DBVERIFY工具
   数据块的物理错误可以通过DBV命令检查出来。
[oracle@ora ~]$ dbv file=/u01/app/oracle/oradata/orcl/test01.dbf blocksize=8192
DBVERIFY: Release 11.2.0.3.0 - Production on Mon Jan 14 13:58:15 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/test01.dbf
Page 132 is marked corrupt                       ---检查test表空间的数据文件可以发现132块已经损坏。
Corrupt block relative dba: 0x01800084 (file 6, block 132)
Bad header found during dbv:
Data in bad block:
type: 97 format: 2 rdba: 0x68676665
last change scn: 0x6e6d.6c6b6a69 seq: 0x6f flg: 0x70
spare1: 0x63 spare2: 0x64 spare3: 0x7473
consistency value in tail: 0x55