下面分别用两种情况说明数据块的物理错误和数据的逻辑错误
一、数据块物理错误: 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)
