链接：http://blog.csdn.net/seertan/article/details/8501398
数据块的损坏分两种情况，第一种是物理性的，第二种是逻辑性的。物理性一般指数据块头部不可以访问、数据块校验值不合法。逻辑性一般是在物理性结构完整的情况下，数据的内容在含义上不正确，比如保存了不允许的字段值。
下面分别用两种情况说明数据块的物理错误和数据的逻辑错误。
数据块物理错误:
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: 0x55ac0602
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 : 1332654 (0.1332654)
---检查users表空间的数据文件没有发现错误
[oracle@ora ~]$ dbv file=/u01/app/oracle/oradata/orcl/users01.dbf blocksize=8192
DBVERIFY: Release 11.2.0.3.0 - Production on Mon Jan 14 13:58:22 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/users01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 1920
Total Pages Processed (Data) : 1170
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 39
Total Pages Failing (Index): 0
Total Pages Processed (Other): 416
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 295
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1334929 (0.1334929)
1.2RMAN的backup命令
RMAN的backup命令在备份时可以检查数据块的物理错误。
RMAN> backup datafile 6;
Starting backup at 14-JAN-13
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=00006 name=/u01/app/oracle/oradata/orcl/test01.dbf
channel ORA_DISK_1: starting piece 1 at 14-JAN-13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 01/14/2013 14:00:24
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/app/oracle/oradata/orcl/test01.dbf
RMAN> backup datafile 4;
Starting backup at 14-JAN-13
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/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 14-JAN-13
channel ORA_DISK_1: finished piece 1 at 14-JAN-13
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2013_01_14/o1_mf_nnndf_TAG20130114T140029_8h77qxwj_.bkp tag=TAG20130114T140029 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-JAN-13
1.3DBMS_HM包
DBMS_HM包的RUN_CHECK可以检查数据块物理错误。它可以只检测一个区(extents)。
SYS@orcl>begin
2 dbms_hm.run_check(
3 check_name => 'Data Block Integrity Check',
4 run_name => 'df6_check',
5 input_params => 'BLC_DF_NUM=6;BLC_BL_NUM=128');
6 end;
7 /
---检查6号数据文件test表的第一个区。
PL/SQL procedure successfully completed.
---查看检查报告可以发现6号数据文件132号块损坏。
SYS@orcl>select dbms_hm.get_run_report('df6_check') from dual;
DBMS_HM.GET_RUN_REPORT('DF6_CHECK')
--------------------------------------------------------------------------------
Basic Run Information
Run Name : df6_check
Run Id : 81
Check Name : Data Block Integrity Check
Mode : MANUAL
Status : COMPLETED
Start Time : 2013-01-14 14:15:46.708256 +08:00
End Time : 2013-01-14 14:15:47.378150 +08:00
Error Encountered : 0
Source Incident Id : 0
Number of Incidents Created : 0
DBMS_HM.GET_RUN_REPORT('DF6_CHECK')
--------------------------------------------------------------------------------
Input Paramters for the Run
BLC_DF_NUM=6
BLC_BL_NUM=128
Run Findings And Recommendations
Finding
Finding Name : Media Block Corruption
Finding ID : 85
Type : FAILURE
Status : OPEN
DBMS_HM.GET_RUN_REPORT('DF6_CHECK')
--------------------------------------------------------------------------------
Priority : HIGH
Message : Block 132 in datafile 6:
'/u01/app/oracle/oradata/orcl/test01.dbf' is media corrupt
Message : Object TEST owned by SCOTT might be unavailable
---检测users表空间数据文件没有发现数据块错误。
SYS@orcl>begin
2 dbms_hm.run_check(
3 check_name => 'Data Block Integrity Check',
4 run_name => 'df4_check',
5 input_params => 'BLC_DF_NUM=4;BLC_BL_NUM=520');
6 end;
7 /
PL/SQL procedure successfully completed.
SYS@orcl>select dbms_hm.get_run_report('df4_check') from dual;
DBMS_HM.GET_RUN_REPORT('DF4_CHECK')
--------------------------------------------------------------------------------
Basic Run Information
Run Name : df4_check
Run Id : 101
Check Name : Data Block Integrity Check
Mode : MANUAL
Status : COMPLETED
Start Time : 2013-01-14 14:19:43.058386 +08:00
End Time : 2013-01-14 14:19:43.306195 +08:00
Error Encountered : 0
Source Incident Id : 0
Number of Incidents Created : 0
DBMS_HM.GET_RUN_REPORT('DF4_CHECK')
--------------------------------------------------------------------------------
Input Paramters for the Run
BLC_DF_NUM=4
BLC_BL_NUM=520
exp/expdp命令导出数据库时会完全扫描每个数据块，所以也会检查出数据块的物理错误。
[oracle@ora ~]$ exp scott/oracle owner=scott
Export: Release 11.2.0.3.0 - Production on Mon Jan 14 14:23:38 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
. 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
EXP-00091: Exporting questionable statistics.
. . exporting table DEPT 4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table EMP 14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table EMP1
. . exporting partition P1 0 rows exported
. . exporting partition P2 0 rows exported
. . exporting partition P3 1 rows exported ---逻辑错误没检查出来。
. . exporting table EMP2 0 rows exported
. . exporting table SALGRADE 5 rows exported
EXP-00091: Exporting questionable statistics.
. . 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/orcl/test01.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.5ANALYZE语句
analyze... validate staructure语句可以分析表和索引的逻辑完整性，所以能够检测出上面例子中分区表的逻辑错误。
---analyze语句要求有一张保存分析结果的表，执行utlvalid.sql脚本建立invalid_rows表保存分析结果。
SYS@orcl>@?/rdbms/admin/utlvalid.sql
Table created.
SYS@orcl>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
SYS@orcl>analyze table scott.emp1 validate structure;
Table analyzed.
---查看analyze分析结果，可以发现一条rowid为AAASy7AAEAAAAIPAAA的记录，这条件记录就是emp1表p3分区中deptno为30的记录。
SYS@orcl>select table_name,partition_name,head_rowid from invalid_rows;
TABLE_NAME PARTITION_ HEAD_ROWID
---------- ---------- -------------------
EMP1 P3 AAASy7AAEAAAAIPAAA
SYS@orcl>select * from scott.emp1 where rowid='AAASy7AAEAAAAIPAAA';
EMPNO ENAME DEPTNO
---------- ---------- ----------
1000 SCOTT 30
1.6DBMS_REPAIR包
DBMS_REPAIR包可以检查表和索引对象的数据块损坏情况。
---首先需要先建立一张表保存检查结果,这里是repair_table表
SYS@orcl> 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.
---执行check_object存储过程进行检测
SYS@orcl>SET SERVEROUTPUT ON
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检查是哪个块出错。
SYS@orcl>select block_id,repair_description from repair_table;
BLOCK_ID REPAIR_DESCRIPTION
---------- --------------------------------------------------
132 mark block software corrupt
SYS@orcl>truncate table repair_table;
Table truncated.
---上面例子的逻辑错误检查不出来。
SYS@orcl>SET SERVEROUTPUT ON
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;
/
SYS@orcl>select count(*) from repair_table;
COUNT(*)
----------
0
DBMS_REPAIR包只能检查表或者索引上的数据块错误，如果是段的头部发生错误是无法检测出来，只能用上面的1.1-.14工具检查，例如：
---查出test表段头部在130号数据块。
SYS@orcl>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@ora ~]$ dd of=/u01/app/oracle/oradata/orcl/test01.dbf bs=8192 conv=notrunc seek=130 <<EOF
> abcdefghijklmnopqrstuvwxyz
> EOF
0+1 records in
0+1 records out
27 bytes (27 B) copied, 3.1988e-05 s, 844 kB/s
SYS@orcl>SET SERVEROUTPUT ON
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号块有错。
SYS@orcl>select block_id,repair_description from repair_table;
BLOCK_ID REPAIR_DESCRIPTION
---------- --------------------------------------------------
132 mark block software corrupt
---使用DBV命令检查test表空间数据文件可以看到结果中有2个数据块错误。
[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 15:58:45 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 130 is marked corrupt
Corrupt block relative dba: 0x01800082 (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: 0x55ae2301
check value in block header: 0x7271
block checksum disabled
Page 132 is marked corrupt
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: 0x55ac0602
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 : 1332652 (0.1332652)
数据块的损坏分两种情况，第一种是物理性的，第二种是逻辑性的。物理性一般指数据块头部不可以访问、数据块校验值不合法。逻辑性一般是在物理性结构完整的情况下，数据的内容在含义上不正确，比如保存了不允许的字段值。
下面分别用两种情况说明数据块的物理错误和数据的逻辑错误。
数据块物理错误:
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: 0x55ac0602
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 : 1332654 (0.1332654)
---检查users表空间的数据文件没有发现错误
[oracle@ora ~]$ dbv file=/u01/app/oracle/oradata/orcl/users01.dbf blocksize=8192
DBVERIFY: Release 11.2.0.3.0 - Production on Mon Jan 14 13:58:22 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/users01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 1920
Total Pages Processed (Data) : 1170
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 39
Total Pages Failing (Index): 0
Total Pages Processed (Other): 416
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 295
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1334929 (0.1334929)
1.2RMAN的backup命令
RMAN的backup命令在备份时可以检查数据块的物理错误。
RMAN> backup datafile 6;
Starting backup at 14-JAN-13
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=00006 name=/u01/app/oracle/oradata/orcl/test01.dbf
channel ORA_DISK_1: starting piece 1 at 14-JAN-13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 01/14/2013 14:00:24
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/app/oracle/oradata/orcl/test01.dbf
---备份test表空间数据文件时报错，备份要求数据块0错误。可以使用视图v$database_block_corruption查看backup操作发现的损坏的块。
RMAN> backup validate database; ---验证所有文件，包括数据文件、控制文件、参数文件
RMAN> backup validate datafile 4; ---验证4号数据文件
RMAN> backup datafile 4;
Starting backup at 14-JAN-13
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/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 14-JAN-13
channel ORA_DISK_1: finished piece 1 at 14-JAN-13
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2013_01_14/o1_mf_nnndf_TAG20130114T140029_8h77qxwj_.bkp tag=TAG20130114T140029 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-JAN-13
1.3DBMS_HM包
DBMS_HM包的RUN_CHECK可以检查数据块物理错误。它可以只检测一个区(extents)。
SYS@orcl>begin
2 dbms_hm.run_check(
3 check_name => 'Data Block Integrity Check',
4 run_name => 'df6_check',
5 input_params => 'BLC_DF_NUM=6;BLC_BL_NUM=128');
6 end;
7 /
---检查6号数据文件test表的第一个区。
PL/SQL procedure successfully completed.
---查看检查报告可以发现6号数据文件132号块损坏。
SYS@orcl>select dbms_hm.get_run_report('df6_check') from dual;
DBMS_HM.GET_RUN_REPORT('DF6_CHECK')
--------------------------------------------------------------------------------
Basic Run Information
Run Name : df6_check
Run Id : 81
Check Name : Data Block Integrity Check
Mode : MANUAL
Status : COMPLETED
Start Time : 2013-01-14 14:15:46.708256 +08:00
End Time : 2013-01-14 14:15:47.378150 +08:00
Error Encountered : 0
Source Incident Id : 0
Number of Incidents Created : 0
DBMS_HM.GET_RUN_REPORT('DF6_CHECK')
--------------------------------------------------------------------------------
Input Paramters for the Run
BLC_DF_NUM=6
BLC_BL_NUM=128
Run Findings And Recommendations
Finding
Finding Name : Media Block Corruption
Finding ID : 85
Type : FAILURE
Status : OPEN
DBMS_HM.GET_RUN_REPORT('DF6_CHECK')
--------------------------------------------------------------------------------
Priority : HIGH
Message : Block 132 in datafile 6:
'/u01/app/oracle/oradata/orcl/test01.dbf' is media corrupt
Message : Object TEST owned by SCOTT might be unavailable
---检测users表空间数据文件没有发现数据块错误。
SYS@orcl>begin
2 dbms_hm.run_check(
3 check_name => 'Data Block Integrity Check',
4 run_name => 'df4_check',
5 input_params => 'BLC_DF_NUM=4;BLC_BL_NUM=520');
6 end;
7 /
PL/SQL procedure successfully completed.
SYS@orcl>select dbms_hm.get_run_report('df4_check') from dual;
DBMS_HM.GET_RUN_REPORT('DF4_CHECK')
--------------------------------------------------------------------------------
Basic Run Information
Run Name : df4_check
Run Id : 101
Check Name : Data Block Integrity Check
Mode : MANUAL
Status : COMPLETED
Start Time : 2013-01-14 14:19:43.058386 +08:00
End Time : 2013-01-14 14:19:43.306195 +08:00
Error Encountered : 0
Source Incident Id : 0
Number of Incidents Created : 0
DBMS_HM.GET_RUN_REPORT('DF4_CHECK')
--------------------------------------------------------------------------------
Input Paramters for the Run
BLC_DF_NUM=4
BLC_BL_NUM=520
Run Findings And Recommendations
---删除所有的报告
SYS@orcl>exec dbms_hm.drop_schema();
PL/SQL procedure successfully completed.
exp/expdp命令导出数据库时会完全扫描每个数据块，所以也会检查出数据块的物理错误。
[oracle@ora ~]$ exp scott/oracle owner=scott
Export: Release 11.2.0.3.0 - Production on Mon Jan 14 14:23:38 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
. 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
EXP-00091: Exporting questionable statistics.
. . exporting table DEPT 4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table EMP 14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table EMP1
. . exporting partition P1 0 rows exported
. . exporting partition P2 0 rows exported
. . exporting partition P3 1 rows exported ---逻辑错误没检查出来。
. . exporting table EMP2 0 rows exported
. . exporting table SALGRADE 5 rows exported
EXP-00091: Exporting questionable statistics.
. . 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/orcl/test01.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.5ANALYZE语句
analyze... validate staructure语句可以分析表和索引的逻辑完整性，所以能够检测出上面例子中分区表的逻辑错误。
---analyze语句要求有一张保存分析结果的表，执行utlvalid.sql脚本建立invalid_rows表保存分析结果。
SYS@orcl>@?/rdbms/admin/utlvalid.sql
Table created.
SYS@orcl>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
SYS@orcl>analyze table scott.emp1 validate structure;
Table analyzed.
---查看analyze分析结果，可以发现一条rowid为AAASy7AAEAAAAIPAAA的记录，这条件记录就是emp1表p3分区中deptno为30的记录。
SYS@orcl>select table_name,partition_name,head_rowid from invalid_rows;
TABLE_NAME PARTITION_ HEAD_ROWID
---------- ---------- -------------------
EMP1 P3 AAASy7AAEAAAAIPAAA
SYS@orcl>select * from scott.emp1 where rowid='AAASy7AAEAAAAIPAAA';
EMPNO ENAME DEPTNO
---------- ---------- ----------
1000 SCOTT 30
1.6DBMS_REPAIR包
DBMS_REPAIR包可以检查表和索引对象的数据块损坏情况。
---首先需要先建立一张表保存检查结果,这里是repair_table表
SYS@orcl> 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.
---执行check_object存储过程进行检测
SYS@orcl>SET SERVEROUTPUT ON
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检查是哪个块出错。
SYS@orcl>select block_id,repair_description from repair_table;
BLOCK_ID REPAIR_DESCRIPTION
---------- --------------------------------------------------
132 mark block software corrupt
SYS@orcl>truncate table repair_table;
Table truncated.
---上面例子的逻辑错误检查不出来。
SYS@orcl>SET SERVEROUTPUT ON
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;
/
SYS@orcl>select count(*) from repair_table;
COUNT(*)
----------
0
DBMS_REPAIR包只能检查表或者索引上的数据块错误，如果是段的头部发生错误是无法检测出来，只能用上面的1.1-.14工具检查，例如：
---查出test表段头部在130号数据块。
SYS@orcl>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@ora ~]$ dd of=/u01/app/oracle/oradata/orcl/test01.dbf bs=8192 conv=notrunc seek=130 <<EOF
> abcdefghijklmnopqrstuvwxyz
> EOF
0+1 records in
0+1 records out
27 bytes (27 B) copied, 3.1988e-05 s, 844 kB/s
SYS@orcl>SET SERVEROUTPUT ON
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号块有错。
SYS@orcl>select block_id,repair_description from repair_table;
BLOCK_ID REPAIR_DESCRIPTION
---------- --------------------------------------------------
132 mark block software corrupt
---使用DBV命令检查test表空间数据文件可以看到结果中有2个数据块错误。
[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 15:58:45 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 130 is marked corrupt
Corrupt block relative dba: 0x01800082 (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: 0x55ae2301
check value in block header: 0x7271
block checksum disabled
Page 132 is marked corrupt
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: 0x55ac0602
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 : 1332652 (0.1332652)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31324783/viewspace-2123348/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- rac环境到单实例环境的dg配置--duplicate方式
- ocr磁盘组故障
- Oracle RAC 环境下的连接管理--崔华转载后修改总结
- v$session详解---http://blog.csdn.net/badly9/article/details/20302125
- oracle 11g数据库产品介绍
- ogg 复制--Oracle11G rac同步到Oracle11g（基于scn的数据初始化）
- GoldenGate介绍--http://blog.csdn.net/sunansheng/article/details/51854786
- ogg一些参数--http://blog.csdn.net/lemontree1123/article/details/46603549
- oratop工具的使用
- 10046事件
