oracle查看坏块属于表,坏块系列-表坏块及抽取数据方法测试

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的即可。然后打开数据库

--查看表中数据https://www.cndba.cn/Expect-le/article/2205

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'

https://www.cndba.cn/Expect-le/article/2205

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

https://www.cndba.cn/Expect-le/article/2205

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及其以上版本.https://www.cndba.cn/Expect-le/article/2205

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_TYPEOWNER SEGMENT_NAME

------------ ------------------ ---------- --------------------

LEI TABLELEI CNDBA

3.4

查看DATA_OBJECT_ID

https://www.cndba.cn/Expect-le/article/2205

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

https://www.cndba.cn/Expect-le/article/2205

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

可以通过SELECT,CATS,INSERTAS SELECT来转储表中的数据。这里不再演示。https://www.cndba.cn/Expect-le/article/2205

更详细的信息参考MOS:

Extracting Data from a Corrupt Table using ROWID Range Scans in Oracle8 and higher (文档ID 61685.1)

版权声明：本文为博主原创文章，未经博主允许不得转载。

表坏块