签到成功

知道了

CNDBA社区CNDBA社区

坏块系列-表坏块及抽取数据方法测试

2017-08-31 18:17 3202 0 原创 Oracle 故障处理
作者： Expect-乐
本文链接：http://www.cndba.cn/Expect-le/article/2205

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

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

--查看数据文件

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

 

--查看表中数据

http://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'

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  确定损坏的块

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

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和损坏类型

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

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

http://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.

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

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

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

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查询表中数据

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

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)http://www.cndba.cn/Expect-le/article/2205

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

表坏块

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
Expect-乐

Expect-乐

关注

Without the continuous bitter cold, there can be no fragrant plum blossom

  • 336
    原创
  • 6
    翻译
  • 100
    转载
  • 41
    评论
  • 访问：1552620次
  • 积分：1957
  • 等级：核心会员
  • 排名：第4名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ