表坏块处理方法

1 说明

首先要明白，在没有有效的备份的情况下，表，CLUSTER或者LOGSEGMENT如果有坏块，那么一定会有数据丢失。所以当出现坏块时，抢救有效数据是最重要的。有很多种方法，可以从包含坏块的表中把数据导出来。这些方法都是在没有备份的情况下使用，有备份就用备份来恢复。

2 方法1-DBMS_REPAIR

2.1 模拟测试数据

SQL> conn lei/lei
Connected.

SQL> create table cndba as select * from dba_tables;
Table created.

SQL> select count(*) from dba_tables;
  COUNT(*)
----------
      2108

SQL> select header_file,header_block from dba_segments where segment_name='CNDBA' and owner='LEI';
HEADER_FILE HEADER_BLOCK
----------- ------------
  2 130

–查看数据文件

SQL> select file_id,file_name from dba_data_files where file_id=2;
   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------
 2 /u01/app/oracle/oradata/CNDBA_P/datafile/o1_mf_lei_dthj7jo6_.dbf

关闭数据库，然后使用UE修改2号文件的下图中的大于0的即可。然后打开数据库

–查看表中数据

SQL> select count(*) from cndba;

select count(*) from cndba
       *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 2, block # 166)
ORA-01110: data file 2:
'/u01/app/oracle/oradata/CNDBA_P/datafile/o1_mf_lei_dthj7jo6_.dbf'

2.2 用dbv检查一下

[oracle@12cdg-p ~]$ dbv file=/u01/app/oracle/oradata/CNDBA_P/datafile/o1_mf_lei_dthj7jo6_.dbf

DBVERIFY: Release 12.2.0.1.0 - Production on Thu Aug 31 16:01:36 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/CNDBA_P/datafile/o1_mf_lei_dthj7jo6_.dbf

Page 166 is marked corrupt
Corrupt block relative dba: 0x008000a6 (file 2, block 166)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x008000a6
last change scn: 0x0000.0000.001f2397 seq: 0x2 flg: 0x04
spare3: 0x0
consistency value in tail: 0x23970602
check value in block header: 0xcc62
computed block checksum: 0x2020
DBVERIFY - Verification complete

Total Pages Examined         : 12800
Total Pages Processed (Data) : 92
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 136
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 12571
Total Pages Marked Corrupt : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2040735 (0.2040735)

–也可以通过analyze命令来查看

SQL> analyze table lei.cndba validate structure;
analyze table lei.cndba validate structure
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 2, block # 166)
ORA-01110: data file 2:
'/u01/app/oracle/oradata/CNDBA_P/datafile/o1_mf_lei_dthj7jo6_.dbf'

2.3 通过dbms_repair来处理

使用方法详见MOS:DBMS_REPAIR SCRIPT (文档 ID 556733.1)

2.3.1 创建REPAIR TABLE

SQL> BEGIN
  DBMS_REPAIR.ADMIN_TABLES (
  TABLE_NAME => 'REPAIR_TABLE',
  TABLE_TYPE => dbms_repair.repair_table,
  ACTION => dbms_repair.create_action,
  TABLESPACE => 'LEI');
END;
/

PL/SQL procedure successfully completed.

2.3.2 确定损坏的块

SQL> set serveroutput on
DECLARE num_corrupt INT;
BEGIN
  num_corrupt := 0;
  DBMS_REPAIR.CHECK_OBJECT (
  SCHEMA_NAME => 'LEI',
  OBJECT_NAME => 'CNDBA',
  REPAIR_TABLE_NAME => 'REPAIR_TABLE',
  corrupt_count => num_corrupt);
  DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/

number corrupt: 1 --坏块个数

PL/SQL procedure successfully completed.

2.3.3 查看损坏的BLOCK_ID和损坏类型

SQL> col CORRUPT_DESCRIPTION for a30
SQL> select BLOCK_ID, CORRUPT_TYPE, CORRUPT_DESCRIPTION from REPAIR_TABLE;
  BLOCK_ID CORRUPT_TYPE CORRUPT_DESCRIPTION
---------- ------------ ------------------------------
       166	   6148

2.3.4 标记坏块

SQL> DECLARE num_fix INT;
BEGIN
  num_fix := 0;
  DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
  SCHEMA_NAME => 'LEI',
  OBJECT_NAME=> 'CNDBA',
  OBJECT_TYPE => dbms_repair.table_object,
  REPAIR_TABLE_NAME => 'REPAIR_TABLE',
  FIX_COUNT=> num_fix);
  DBMS_OUTPUT.PUT_LINE( 'num fix: ' || to_char(num_fix));
END;
/

num fix: 0
PL/SQL procedure successfully completed.

2.3.5 允许DML操作跳过坏块

SQL> BEGIN
  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
  SCHEMA_NAME => 'LEI',
  OBJECT_NAME => 'CNDBA',
  OBJECT_TYPE => dbms_repair.table_object,
  FLAGS => dbms_repair.SKIP_FLAG);
END;
/

PL/SQL procedure successfully completed.

2.3.6 再次查询表中数据

SQL> select count(*) from lei.cndba;
  COUNT(*)
----------
      2085  --原来的数据条数是2108，说明有20条左右的数据丢失了。

到此表中的数据就抢救回来了。

3 方法2-使用ROWID range scan

下面的方法使用Oracle8/8i及其以上版本.

3.1 创建测试数据

使用上面的方法，然后在table_name列上创建一个索引，需要用到

3.2 查询表数据

SQL> select count(*) from cndba;
select count(*) from cndba
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 2, block # 461)
ORA-01110: data file 2: '/u01/app/oracle/oradata/CNDBA_P/datafile/o1_mf_lei_dthj7jo6_.dbf'

3.3 查看坏块对象详细信息

SQL> col tablespace_name for a12
SQL> col owner for a10
SQL> col segment_name for a20
SQL> SELECT tablespace_name, segment_type, owner, segment_name
  FROM dba_extents
WHERE file_id =2
AND 461 between block_id AND block_id + blocks - 1 ;

TABLESPACE_N SEGMENT_TYPE	OWNER	   SEGMENT_NAME
------------ ------------------ ---------- --------------------
LEI	     TABLE	LEI	   CNDBA

3.4 查看DATA_OBJECT_ID

SQL> SELECT data_object_id
       FROM dba_objects
      WHERE object_name = 'CNDBA'  and owner='LEI';
DATA_OBJECT_ID
--------------
 73615

3.5 查看坏块对应的坏块ROWID

SQL> select dbms_rowid.rowid_create(1, 73615,2,461,0) from dual;

DBMS_ROWID.ROWID_C
------------------
AAAR+PAACAAAAHNAAA

3.6 坏块ID+1的ROWID

SQL> select dbms_rowid.rowid_create(1, 73615,2,462,0) from dual;

DBMS_ROWID.ROWID_C
------------------
AAAR+PAACAAAAHOAAA

3.7 通过ROWID查询表中数据

SQL> SELECT /*+ ROWID(A) */ count(*)
       FROM LEI.CNDBA A
     	     WHERE rowid < 'AAAR+PAACAAAAHNAAA'; --坏块对应的ROWID
  COUNT(*)
----------
      2092

SQL> SELECT /*+ ROWID(A) */ count(*)
       FROM LEI.CNDBA A
     	      WHERE rowid >='AAAR+PAACAAAAHOAAA'; --坏块下一个块对应的ROWID
  COUNT(*)
----------
 0

可用数据总条数=2092+0=2092条。

可以通过SELECT,CATS,INSERTAS SELECT来转储表中的数据。这里不再演示。

更详细的信息参考MOS:
Extracting Data from a Corrupt Table using ROWID Range Scans in Oracle8 and higher (文档 ID 61685.1)