Daily Archives: 2011 年 12 月 23 日

通过ROWID找回坏块数据

Posted on by

一.准备环境

C:\Users\XIFENFEI>sqlplus chf/xifenfei

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 12月 23 10:49:52 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> create tablespace t_xff datafile 'E:\ORACLE\ORADATA\XFF\t_xff01.dbf'
  2  size 10m autoextend on next 10m maxsize 1g;

表空间已创建。

SQL> create table t_xifenfei tablespace t_xff
  2  as
  3  select * from dba_objects;

表已创建。

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     73286

二.发现坏块
使用ULtraEdit破坏数据(关闭数据库执行)

SQL> select count(*) from t_xifenfei;
select count(*) from t_xifenfei
                     *
第 1 行出现错误:
ORA-01578: ORACLE 数据块损坏 (文件号 13, 块号 373)
ORA-01110: 数据文件 13: 'E:\ORACLE\ORADATA\XFF\T_XFF01.DBF'

三.查询坏块相关信息

The "LOW_RID" is the lowest rowid INSIDE the corrupt block:
SELECT dbms_rowid.rowid_create(1,<DATA_OBJECT_ID>,<RFN>,<BL>,0) LOW_RID
from DUAL;

The "HI_RID" is the first rowid AFTER the corrupt block:
SELECT dbms_rowid.rowid_create(1,<DATA_OBJECT_ID>,<RFN>,<BL>+1,0) HI_RID
from DUAL;

SQL> col tablespace_name for a30
SQL> col segment_type for a5
SQL> col owner for a10
SQL> col segment_name for a20
SQL> SELECT tablespace_name, segment_type, owner, segment_name
  2                 FROM dba_extents
  3                WHERE file_id =13
  4  AND 373 between block_id AND block_id + blocks - 1 ;

TABLESPACE_NAME                SEGME OWNER      SEGMENT_NAME
------------------------------ ----- ---------- --------------------
T_XFF                          TABLE CHF        T_XIFENFEI

SQL> SELECT data_object_id
  2            FROM dba_objects
  3   WHERE object_name = 'T_XIFENFEI'  and owner='CHF';

DATA_OBJECT_ID
--------------
         77759
--坏块的最小rowid
SQL> select dbms_rowid.rowid_create(1, 77759,13,373,0) from dual;

DBMS_ROWID.ROWID_C
------------------
AAAS+/AANAAAAF1AAA

坏块的最大rowid(block+1得到)
SQL> select dbms_rowid.rowid_create(1, 77759,13,374,0) from dual;

DBMS_ROWID.ROWID_C
------------------
AAAS+/AANAAAAF2AAA

四.根据rowid找回数据

SQL> SELECT /*+ ROWID(A) */ COUNT(*) FROM T_XIFENFEI A
  2  WHERE ROWID>='AAAS+/AANAAAAF2AAA';

  COUNT(*)
----------
     55858

SQL> SELECT /*+ ROWID(A) */ COUNT(*) FROM T_XIFENFEI A
  2  WHERE  ROWID<'AAAS+/AANAAAAF1AAA';

  COUNT(*)
----------
     17358

SQL> SELECT 77759-55858-17358 from dual;

77759-55858-17358
-----------------
             4543

SQL> CREATE TABLE T_XIFENFEI_BAK  TABLESPACE T_XFF
  2  AS
  3  SELECT /*+ ROWID(A) */ * FROM T_XIFENFEI A
  4  WHERE ROWID>='AAAS+/AANAAAAF2AAA';

表已创建。

SQL> INSERT INTO  T_XIFENFEI_BAK
  2  SELECT /*+ ROWID(A) */ * FROM T_XIFENFEI A
  3  WHERE  ROWID<'AAAS+/AANAAAAF1AAA';

已创建17358行。

SQL> COMMIT;

提交完成。

SQL> SELECT COUNT(*) FROM T_XIFENFEI_BAK;

  COUNT(*)
----------
     73216

五.和dbms_repair解决坏块对比

SQL> CONN / AS SYSDBA
已连接。
SQL> exec dbms_repair.skip_corrupt_blocks('CHF','T_XIFENFEI');

PL/SQL 过程已成功完成。

SQL>  select skip_corrupt from dba_tables where table_name='T_XIFENFEI';

SKIP_COR
--------
ENABLED

SQL> select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     73216

通过跳过坏块和rowid功能对比可以看出，两者丢失的数据是相同的，如果有index，同样利用rowid结合index，可能会找回部分数据。当然dbms_repair也提供了类此的功能。两种方法的使用看个人的爱好与习惯。