检测数据块损坏(Block Corruption)

2024-04-02 19:04:59 795人浏览八月长安

摘要

数据块的损坏分两种情况，第一种是物理性的，第二种是逻辑性的。物理性一般指数据块头部不可以访问、数据块校验值不合法。逻辑性一般是在物理性结构完整的情况下，数据的内容在含义上不正确，比如保存了不允许的字段值。

数据块的损坏分两种情况，第一种是物理性的，第二种是逻辑性的。物理性一般指数据块头部不可以访问、数据块校验值不合法。逻辑性一般是在物理性结构完整的情况下，数据的内容在含义上不正确，比如保存了不允许的字段值。

下面分别用两种情况说明数据块的物理错误和数据的逻辑错误

一、数据块物理错误: physical bad block，物理性一般指数据块头部不可以访问、数据块校验值不合法
--创建表空间test
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DBdb/system01.dbf
/u01/app/oracle/oradata/DBdb/sysaux01.dbf
/u01/app/oracle/oradata/DBdb/undotbs01.dbf
/u01/app/oracle/oradata/DBdb/users01.dbf
/u01/app/oracle/oradata/DBdb/example01.dbf

SQL> create tablespace test datafile '/u01/app/oracle/oradata/DBdb/test.dbf' size 10m;

Tablespace created.

--创建表test,使用表空间test
SQL> create table scott.test tablespace test as select * from dba_objects where rownum <=100;

Table created.

sql> col name for a70
SQL> set lines 200 pages 999
SQL> select f.file#,
2         t.name tablespace,
3         f.name,
4         trunc(f.bytes / 1048576, 2) size_mb,
5         to_char(f.creation_time, 'yyyy-mm-dd') creation_time,
6         status
7    from v$datafile f, v$tablespace t
8   where f.ts# = t.ts#
9   order by f.creation_time;
FILE# TABLESPACE NAME                                          SIZE_MB CREATION_T STATUS
----- ---------- ------------------------------------------ ---------- ---------- -------
    1 SYSTEM     /u01/app/oracle/oradata/DBdb/system01.dbf        2800 2013-08-24 SYSTEM
    2 SYSAUX     /u01/app/oracle/oradata/DBdb/sysaux01.dbf         710 2013-08-24 ONLINE
    4 USERS      /u01/app/oracle/oradata/DBdb/users01.dbf      3058.75 2013-08-24 ONLINE
    3 UNDOTBS1   /u01/app/oracle/oradata/DBdb/undotbs01.dbf       2965 2013-08-24 ONLINE
    5 EXAMPLE    /u01/app/oracle/oradata/DBdb/example01.dbf     338.75 2017-04-27 ONLINE
    6 TEST       /u01/app/oracle/oradata/DBdb/test.dbf              10 2018-01-26 ONLINE

6 rows selected.

--test表从数据块128号开始的8个块(128-135)，数据文件是6号。
SQL> set lines 200
SQL> col name for a50
SQL> 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/DBdb/test.dbf

--test所有的行保存在131和132数据块中
SQL> select distinct dbms_rowid.rowid_block_number(rowid) from scott.test;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
132
131
--改变132数据块的内容
[oracle@wang ~]$ dd of=/u01/app/oracle/oradata/DBdb/test.dbf bs=8192 conv=notrunc seek=132 <<eof
> abcdefghijklmnopqrstuvwxyz
> EOF
0+1 records in
0+1 records out
27 bytes (27 B) copied, 8.484e-05 s, 318 kB/s
[oracle@wang~]$

                                                                           </eof

二、数据逻辑错误：logical bad block，逻辑性一般是在物理性结构完整的情况下，数据的内容在含义上不正确

--创建range分区表
SQL> create table scott.emp1 (empno number(4),ename varchar2(10),deptno number(2)) partition by range (deptno)
(partition p1 values less than (10) tablespace users,partition p2 values less than (20) tablespace users, partition p3 values less than (30)) tablespace users;

Table created.

SQL> conn scott/tiger;
Connected.
SQL>
SQL> select EMPNO,ENAME,DEPTNO from EMP;

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7499 ALLEN              30
      7521 WARD               30
      7566 JONES              20
      7654 MARTIN             30
      7698 BLAKE              30
      7782 CLARK              10
      7788 SCOTT              20
      7839 KING               10
      7844 TURNER             30
      7876 ADAMS              20
      7900 JAMES              30

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7902 FORD               20
      7934 MILLER             10

13 rows selected.

SQL> insert into scott.emp1 select EMPNO,ENAME,DEPTNO from SCOTT.EMP where deptno<30;

7 rows created.

SQL> commit;

Commit

--EMP1表从数据块40576号开始的1024个块(40576+1024=41600)以及从数据块41600号开始的1024个块(41600+1024=42624），数据文件是4号。

SQL> 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      40576       1024 /u01/app/oracle/oradata/DBdb/users01.dbf
         4      41600       1024 /u01/app/oracle/oradata/DBdb/users01.dbf

--deptno是30的记录不能插入emp1表
SQL> 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


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

Table created.

SQL> insert into scott.emp2 values(1000,'SCOTT',30);

1 row created.

SQL> alter table scott.emp1 exchange partition p3 with table scott.emp2 without validation;

Table altered.

SQL>                                                                                                                                                      --deptno为30的记录已插入emp1表
SQL> select * from scott.emp1 partition (p3);

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      1000 SCOTT              30

SQL> select * from scott.emp1;

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7782 CLARK              10
      7839 KING               10
      7934 MILLER             10
      1000 SCOTT              30

SQL> select * from scott.emp2;

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7566 JONES              20
      7788 SCOTT              20
      7876 ADAMS              20
      7902 FORD               20

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

1.1 DBVERIFY工具，数据块的物理错误可以通过DBV命令检查出来
DBVerify - Identify Datafile Block Corruptions
    DBVERIFY identifies Physical and Logical Intra Block Corruptions by default. Dbverify cannot be run for the whole database in a single command. It does not need a database connection either:

[oracle@wang ~]$ dbv file=/u01/app/oracle/oradata/DBdb/test.dbf blocksize=8192

DBVERIFY: Release 11.2.0.4.0 - Production on Wed Nov 8 22:57:05 2017

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

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/DBdb/test.dbf
Page 132 is marked          ---检查test表空间的数据文件可以发现132块已经损坏。
Corrupt block relative dba: 0x01800084 (file 6, block 132)    --file 6, block 132,与我们自己手工破坏的块号匹配（scott.test表）
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: 0x658b0602
check value in block header: 0x7271
block checksum disabled

DBVERIFY - Verification complete

Total Pages Examined         : 1280
Total Pages Processed (Data) : 1
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 130
Total Pages Processed (Seg) : 0
Total Pages Failing   (Seg) : 0
Total Pages Empty            : 1148
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 4023695 (0.4023695)
[oracle@wang ~]$
                                                                                                                                                          --检查users表空间的数据文件没有发现逻辑错误
[oracle@wang ~]$ dbv file=/u01/app/oracle/oradata/DBdb/users01.dbf blocksize=8192

DBVERIFY: Release 11.2.0.4.0 - Production on Wed Nov 8 23:01:07 2017

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

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/DBdb/users01.dbf

DBVERIFY - Verification complete

Total Pages Examined         : 391520
Total Pages Processed (Data) : 290743
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 12935
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 67340
Total Pages Processed (Seg) : 0
Total Pages Failing   (Seg) : 0
Total Pages Empty            : 20502
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 4024489 (0.4024489)
[oracle@wang~]$
1.2 RMAN的backup命令
RMAN - Identify Datafile Block Corruptions

To identify both Physical and Logical Block Corruptions use the "CHECK LOGICAL" option. The next command checks the complete database for both corruptions without actually doing a backup:
添加check logical选项可以检查逻辑坏块；

$ rman target /
RMAN> backup check logical validate

The next command checks the complete database for both corruptions in a backup:

$ rman target /
RMAN> backup check logical database

检查坏块,示例：
backup check logical validate datafile 6;
validate check logical datafile 6;

validate database;                                --验证整个数据库
validate backupset 22;                            --验证某个备份文件
validate tablespace users;                        --验证某个表空间
validate datafile 1;                              --验证某个数据文件
validate datafile 1 block 10;                     --验证某个数据文件中的某个块
validate check logical datafile 1 BLOCK 5 TO 20; --检查数据文件 1 中的数据块 5 到 20

backup validate database;                         --验证所有文件，包括数据文件、控制文件、参数文件
backup validate datafile 6;                       --验证6号数据文件
backup validate datafile 4;                       --验证4号数据文件

注意:这个命令只是检查数据库的坏块，而不会真正进行备份。从 11g 开始可以省略 backup 子句，而直接使用命令"validate check logical database"。
如果由于缺失文件导致命令失败，可以增加 'SKIP INACCESSIBLE' 子句来避免这个问题,为了加快检查速度，可以设置 PARALLELISM 指定多个通道:

坏块信息会被记录在视图 V$DATABASE_BLOCK_CORRUPTION 中。使用如上validate 子句检查数据文件等后 RMAN 会生成一个 trace 文件，详细描述坏块信息

或者备份数据文件，也可以检查出坏块。
v$database_block_corruption查看backup操作发现的损坏
V$DATABASE_BLOCK_CORRUPTION displays infORMation about database blocks that were corrupted after the last backup. 此视图只显示上次备份后损坏的数据库块的信息。

参考：Http://www.linuxidc.com/Linux/2014-08/105897.htm

1.2.1使用rman备份检查坏块：
[oracle@wang ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Nov 8 23:07:46 2017

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

connected to target database: DBDB (DBID=3282897732)

RMAN> backup datafile 6;

Starting backup at 08-NOV-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/DBdb/test.dbf
channel ORA_DISK_1: starting piece 1 at 08-NOV-17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 11/08/2017 23:07:52
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/app/oracle/oradata/DBdb/test.dbf

RMAN>

验证说明；备份test表空间数据文件时报错，备份要求数据块0错误。可以使用视图v$database_block_corruption查看backup操作发现的损坏的块。
V$DATABASE_BLOCK_CORRUPTION displays information about database blocks that were corrupted after the last backup.

查询如下；
SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         6        132          1                  0 CORRUPT
物理坏块被发现

RMAN> backup datafile 4;

Starting backup at 26-JAN-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/DBdb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 26-JAN-18
channel ORA_DISK_1: finished piece 1 at 26-JAN-18
piece handle=/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/backupset/2018_01_26/o1_mf_nnndf_TAG20180126T065941_f6nrbxnx_.bkp tag=TAG20180126T065941 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 26-JAN-18

RMAN>

--再次检查视图：
SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         6        132          1                  0 CORRUPT

逻辑坏块未被检测出来；

1.2.2 使用 validate check logical子句检查逻辑及物理坏块
RMAN> validate check logical datafile 6;

Starting validate at 26-JAN-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00006 name=/u01/app/oracle/oradata/DBdb/test.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    FaiLED 0              1148         1280            4177988
File Name: /u01/app/oracle/oradata/DBdb/test.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              1
Index      0              0
Other      1              131

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_32387.trc for details
Finished validate at 26-JAN-18

RMAN> validate check logical datafile 4;

Starting validate at 26-JAN-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00004 name=/u01/app/oracle/oradata/DBdb/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:45
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              21386        391549          4178643
File Name: /u01/app/oracle/oradata/DBdb/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              241463
Index      0              55618
Other      0              73053

Finished validate at 26-JAN-18

RMAN>
未检查出逻辑坏块。。。。。。。。。。。。。

1.3 exp/expdp命令
exp/expdp命令导出数据库时会完全扫描每个数据块，所以也会检查出数据块的物理错误。
[oracle@wang ~]$ exp scott/tiger owner=scott

Export: Release 11.2.0.4.0 - Production on Thu Nov 9 00:16:08 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                          BONUS          0 rows exported
. . exporting table                           DEPT          4 rows exported
. . exporting table                            EMP         13 rows exported
. . exporting table                           EMP1
. . exporting partition                             P1          0 rows exported
. . exporting partition                             P2          3 rows exported
. . exporting partition                             P3          1 rows exported     --逻辑错误没检查出来。
. . exporting table                           EMP2          4 rows exported
. . exporting table                         GRADES          0 rows exported
. . exporting table                           JOBS         13 rows exported
. . exporting table                        STUDENT          0 rows exported
. . exporting table                           TEST
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 6, block # 132)                  --检测出6号数据文件的132号块损坏
ORA-01110: data file 6: '/u01/app/oracle/oradata/DBdb/test.dbf'
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

1.4 ANALYZE语句
analyze... validate staructure语句可以分析表和索引的逻辑完整性，所以能够检测出上面例子中分区表的逻辑错误。

--analyze语句要求有一张保存分析结果的表，执行utlvalid.sql脚本建立invalid_rows表保存分析结果。
QL> @?/rdbms/admin/utlvalid.sql

Table created.

SQL> desc invalid_rows
Name                                              Null?    Type
---------------------------------------------------------------
OWNER_NAME                                                 VARCHAR2(30)
TABLE_NAME                                                 VARCHAR2(30)
PARTITION_NAME                                             VARCHAR2(30)
SUBPARTITION_NAME                                          VARCHAR2(30)
HEAD_ROWID                                                 ROWID
ANALYZE_TIMESTAMP                                          DATE

SQL>

--查询表invalid_rows
SQL> select table_name,partition_name,head_rowid from invalid_rows;

no rows selected.

--验证表scott.emp1
SYS@orcl> analyze table scott.emp1 validate structure;

Table analyzed.

---查看analyze分析结果，可以发现一条rowid为AAASy7AAEAAAAIPAAA的记录，这条件记录就是emp1表p3分区中deptno为30的记录。
SQL> select table_name,partition_name,head_rowid from invalid_rows;

TABLE_NAME                     PARTITION_NAME                 HEAD_ROWID
------------------------------ ------------------------------ ------------------
EMP1                           P3                             AAAV/vAAEAAAAQlAAA

--根据rowid查询表对应的逻辑坏块：
SQL> select * from scott.emp1 where rowid='AAAV/vAAEAAAAQlAAA';

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      1000 SCOTT              30

--分析表scott.test
SQL> analyze table scott.test validate structure;

Table analyzed.

SQL> select table_name,partition_name,head_rowid from invalid_rows;

TABLE_NAME                     PARTITION_NAME                 HEAD_ROWID
------------------------------ ------------------------------ ------------------
EMP1                           P3                             AAAWERAAEAAAAIMAAA

未发现物理坏块。。。。。。。。。。。。。。。。。。。。

1.5 DBMS_REPAIR包
DBMS_REPAIR包可以检查表和索引对象的数据块损坏情况：

--首先需要先建立一张表保存检查结果,这里是repair_table表(repair是默认生成的表)
SQL> BEGIN
      DBMS_REPAIR.ADMIN_TABLES(TABLE_NAME => 'REPAIR_TABLE',
                           TABLE_TYPE => dbms_repair.repair_table,
                           ACTION     => dbms_repair.create_action,
                           TABLESPACE => 'USERS');
    END;
      /

PL/SQL procedure successfully completed.

SQL> desc repair_table
Name                                      Null?    Type
----------------------------------------- -------- -----
OBJECT_ID                                 NOT NULL NUMBER
TABLESPACE_ID                             NOT NULL NUMBER
RELATIVE_FILE_ID                          NOT NULL NUMBER
BLOCK_ID                                  NOT NULL NUMBER
CORRUPT_TYPE                              NOT NULL NUMBER
SCHEMA_NAME                               NOT NULL VARCHAR2(30)
OBJECT_NAME                               NOT NULL VARCHAR2(30)
BASEOBJECT_NAME                                    VARCHAR2(30)
PARTITION_NAME                                     VARCHAR2(30)
CORRUPT_DESCRIPTION                                VARCHAR2(2000)
REPAIR_DESCRIPTION                                 VARCHAR2(200)
MARKED_CORRUPT                            NOT NULL VARCHAR2(10)
CHECK_TIMESTAMP                           NOT NULL DATE
FIX_TIMESTAMP                                      DATE
REFORMAT_TIMESTAMP                                 DATE

SQL> select * from repair_table;

no rows selected

--执行check_object存储过程进行检测SCOTT.TEST表
SQL> SET SERVEROUTPUT ON
SQL> DECLARE num_corrupt INT;
      BEGIN
      num_corrupt :=0;
      DBMS_REPAIR.CHECK_OBJECT(
        SCHEMA_NAME =>'SCOTT',
        OBJECT_NAME =>'TEST',
        REPAIR_TABLE_NAME =>'REPAIR_TABLE',
        CORRUPT_COUNT =>num_corrupt);
        DBMS_OUTPUT.PUT_LINE('number corrupt:' || TO_CHAR(num_corrupt));
        END;
        /

number corrupt:1           --有1个块损坏

PL/SQL procedure successfully completed.

SQL>                                                                                                                                                      --查询repair_table检查是哪个块出错。
SQL> col REPAIR_DESCRIPTION for a50
SQL> col SCHEMA_NAME for a10
SQL> col OBJECT_NAME for a10
SQL>select OBJECT_ID,
       TABLESPACE_ID,
       RELATIVE_FILE_ID,
       BLOCK_ID,
       CORRUPT_TYPE,
       SCHEMA_NAME,
       OBJECT_NAME,
       REPAIR_DESCRIPTION
from repair_table;

OBJECT_ID TABLESPACE_ID RELATIVE_FILE_ID   BLOCK_ID CORRUPT_TYPE SCHEMA_NAM OBJECT_NAM REPAIR_DESCRIPTION
---------- ------------- ---------------- ---------- ------------ ---------- ---------- ------------------------------
     90090     7          6                 132      6148         SCOTT      TEST       mark block software
SYS> truncate tablerepair_table;

Table truncated.

SQL> select * from repair_table;

no rows selected

---接着检查scott.emp1的逻辑坏块，检测发现检查不来
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
        num_corrupt INT;
      BEGIN
        num_corrupt := 0;
        DBMS_REPAIR.CHECK_OBJECT(SCHEMA_NAME       => 'SCOTT',
                                 OBJECT_NAME       => 'EMP1',
                                 REPAIR_TABLE_NAME => 'REPAIR_TABLE',
                                 CORRUPT_COUNT     => num_corrupt);
        DBMS_OUTPUT.PUT_LINE('number corrupt:' || TO_CHAR(num_corrupt));
      END;
      /

number corrupt:0

PL/SQL procedure successfully completed.

SQL>

--检查表repair_table
SQL> select * from repair_table;

no rows selected

注意：DBMS_REPAIR包只能检查表或者索引上的数据块错误(物理坏块），如果是段的头部发生错误是无法检测出来

例如，如下：
--查出test表段头部在130号数据块。
SQL> select tablespace_id,header_file,header_block from sys_dba_segs where owner='SCOTT' and segment_name='TEST';

TABLESPACE_ID HEADER_FILE HEADER_BLOCK
------------- ----------- ------------
            7           6          130

--破坏130号数据块。
[oracle@wang ~]$ dd of=/u01/app/oracle/oradata/DBdb/test.dbf bs=8192 conv=notrunc seek=130 <<EOF<eof
> abcdefghijklmnopqrstuvwxyz
> EOF
0+1 records in
0+1 records out
29 bytes (29 B) copied, 0.00015142 s, 192 kB/s
[oracle@wang ~]$

SQL> SET SERVEROUTPUT ON
SQL> DECLARE num_corrupt INT;
      BEGIN
      num_corrupt :=0;
      DBMS_REPAIR.CHECK_OBJECT(
        SCHEMA_NAME =>'SCOTT',
        OBJECT_NAME =>'TEST',
        REPAIR_TABLE_NAME =>'REPAIR_TABLE',
        CORRUPT_COUNT =>num_corrupt);
        DBMS_OUTPUT.PUT_LINE('number corrupt:' || TO_CHAR(num_corrupt));
        END;
        /

number corrupt:1          --还是只有1个块损坏。

PL/SQL procedure successfully completed.

--查询repair_table检查只有132号块有错。
SQL>select OBJECT_ID,
       TABLESPACE_ID,
       RELATIVE_FILE_ID,
       BLOCK_ID,
       CORRUPT_TYPE,
       SCHEMA_NAME,
       OBJECT_NAME,
       REPAIR_DESCRIPTION
from repair_table;

OBJECT_ID TABLESPACE_ID RELATIVE_FILE_ID   BLOCK_ID CORRUPT_TYPE SCHEMA_NAM OBJECT_NAM REPAIR_DESCRIPTION
---------- ------------- ---------------- ---------- ------------ ---------- ----------
     90090             7                6        132         6148 SCOTT      TEST       mark block software corrupt

--而使用DBV命令检查test表空间数据文件可以看到结果中有2个数据块错误。
[oracle@wang ~]$ dbv file=/u01/app/oracle/oradata/DBdb/test.dbf blocksize=8192

DBVERIFY: Release 11.2.0.4.0 - Production on Thu Nov 9 01:11:39 2017

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

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/DBdb/test.dbf
Page 130 is marked corrupt
Corrupt block relative dba: 0x01800082 (file 6, block 130)            --file 6, block 130坏块
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: 0x658f2301
check value in block header: 0x7271
block checksum disabled

Page 132 is marked corrupt
Corrupt block relative dba: 0x01800084 (file 6, block 132)            --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: 0x658b0602
check value in block header: 0x7271
block checksum disabled

DBVERIFY - Verification complete

Total Pages Examined         : 1280
Total Pages Processed (Data) : 1
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 129
Total Pages Processed (Seg) : 0
Total Pages Failing   (Seg) : 0
Total Pages Empty            : 1148
Total Pages Marked Corrupt   : 2
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 4023692 (0.4023692)
[oracle@wang ~]$

MOS:处理 Oracle 块损坏 (文档 ID 1526911.1)