使用dbms_repair修复坏块

李志强OracleLeave a comment

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
–查看数据文件

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
1
2
3
4
关闭数据库，然后使用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’
1
2
3
4
5
6
7
8
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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
–也可以通过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’
1
2
3
4
5
6
7
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.
1
2
3
4
5
6
7
8
9
10
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

1
2
3
4
5
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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.
1
2
3
4
5
6
7
8
9
10
2.3.6 再次查询表中数据
SQL> select count(*) from lei.cndba;

COUNT(*)

  2085  --原来的数据条数是2108，说明有20条左右的数据丢失了。

1
2
3
4
到此表中的数据就抢救回来了。

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’
1
2
3
4
5
6
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
1
2
3
4
5
6
7
8
9
10
11
3.4 查看DATA_OBJECT_ID
SQL> SELECT data_object_id
FROM dba_objects
WHERE object_name = ‘CNDBA’ and owner=’LEI’;

DATA_OBJECT_ID

73615
1
2
3
4
5
6
3.5 查看坏块对应的坏块ROWID
SQL> select dbms_rowid.rowid_create(1, 73615,2,461,0) from dual;

DBMS_ROWID.ROWID_C

AAAR+PAACAAAAHNAAA
1
2
3
4
5
3.6 坏块ID+1的ROWID
SQL> select dbms_rowid.rowid_create(1, 73615,2,462,0) from dual;

DBMS_ROWID.ROWID_C

AAAR+PAACAAAAHOAAA
1
2
3
4
5
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
1
2
3
4
5
6
7
8
9
10
11
12
13
可用数据总条数=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)

————————————————
版权声明：本文为CSDN博主「Expect-乐」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qianglei6077/article/details/90311527

Leave a Reply