数据块恢复实例

1. 准备数据,
SQL> create table test as select * from all_objects;

Table created.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select blocks from user_tables where table_name='TEST';

    BLOCKS
----------
       826

select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from test


2.bbed破坏数据
[oracle@deer lib]$ ./bbed parfile=bbed.par
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Sun Jun 10 13:31:05 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set dba 4,9644
        DBA             0x010025ac (16786860 4,9644)
BBED> find /c LATCH
 File: /u01/oradata/deer/users01.dbf (4)
 Block: 9644             Offsets: 6239 to 6750           Dba:0x010025ac
------------------------------------------------------------------------
 4c415443 485f4d49 53534553 ff03c20f 03ff0753 594e4f4e 594d0778 6d080f01
 12240778 6d080f01 12241332 3030392d 30382d31 353a3030 3a31373a 33350556
 414c4944 014e014e 014e02c1 022c000e 06505542 4c49430d 56244c41 54434848
 4f4c4445 52ff02c2 0fff0753 594e4f4e 594d0778 6d080f01 12240778 6d080f01
 12241332 3030392d 30382d31 353a3030 3a31373a 33350556 414c4944 014e014e
 014e02c1 022c000e 06505542 4c49430b 56244c41 5443484e 414d45ff 03c20e63
 ff075359 4e4f4e59 4d07786d 080f0112 2407786d 080f0112 24133230 30392d30
 382d3135 3a30303a 31373a33 35055641 4c494401 4e014e01 4e02c102 2c000e06
 5055424c 49430e56 244c4154 43485f50 4152454e 54ff03c2 0e61ff07 53594e4f
 4e594d07 786d080f 01122407 786d080f 01122413 32303039 2d30382d 31353a30
 303a3137 3a333505 56414c49 44014e01 4e014e02 c1022c00 0e065055 424c4943
 1056244c 41544348 5f434849 4c445245 4eff03c2 0e5fff07 53594e4f 4e594d07
 786d080f 01122407 786d080f 01122413 32303039 2d30382d 31353a30 303a3137
 3a333505 56414c49 44014e01 4e014e02 c1022c00 0e065055 424c4943 0756244c
 41544348 ff03c20e 5dff0753 594e4f4e 594d0778 6d080f01 12240778 6d080f01
 12241332 3030392d 30382d31 353a3030 3a31373a 33350556 414c4944 014e014e

 <32 bytes="" per="" line="">
 
BBED> dump /v dba 4,9644 offset 6239 count 64
 File: /u01/oradata/deer/users01.dbf (4)
 Block: 9644    Offsets: 6239 to 6302  Dba:0x010025ac
-------------------------------------------------------
 4c415443 485f4d49 53534553 ff03c20f l LATCH_MISSES..R
 03ff0753 594e4f4e 594d0778 6d080f01 l ...SYNONYM.xm...
 12240778 6d080f01 12241332 3030392d l .$.xm....$.2009-
 30382d31 353a3030 3a31373a 33350556 l 08-15:00:17:35.V

 <16 bytes="" per="" line="">

BBED> f                                                             -------------------------------》寻找下一个
 File: /u01/oradata/deer/users01.dbf (4)
 Block: 9644             Offsets: 6686 to 6749           Dba:0x010025ac
------------------------------------------------------------------------
 4c415443 48ff03c2 0e5dff07 53594e4f 4e594d07 786d080f 01122407 786d080f
 01122413 32303039 2d30382d 31353a30 303a3137 3a333505 56414c49 44014e01

 <32 bytes="" per="" line="">

BBED> modify 10000 dba 4,9644
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oradata/deer/users01.dbf (4)
 Block: 9644             Offsets: 6686 to 6749           Dba:0x010025ac
------------------------------------------------------------------------
 27105443 48ff03c2 0e5dff07 53594e4f 4e594d07 786d080f 01122407 786d080f
 01122413 32303039 2d30382d 31353a30 303a3137 3a333505 56414c49 44014e01

 <32 bytes="" per="" line="">

3.DBC 检查
[oracle@deer lib]$ dbv file=/u01/oradata/deer/users01.dbf

DBVERIFY: Release 11.2.0.1.0 - Production on Sun Jun 10 13:38:49 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/oradata/deer/users01.dbf
Page 9644 is marked corrupt
Corrupt block relative dba: 0x010025ac (file 4, block 9644)
Bad check value found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x010025ac
 last change scn: 0x0000.00af076f seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x076f0601
 check value in block header: 0xaf97
 computed block checksum: 0x516b



DBVERIFY - Verification complete

Total Pages Examined         : 13760
Total Pages Processed (Data) : 3723
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 8966
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 537
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 533
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 11470764 (0.11470764)



select * from test                                    ------------------------------>报告查询错误
ERROR:
ORA-01578: ORACLE data block corrupted (file # 4, block # 9644)
ORA-01110: data file 4: '/u01/oradata/deer/users01.dbf'


3.如上，跳出 ORA-01578,说明数据块已经被标识为'software corrupt' ,
   如果遇到其他错误,ora-0600这时就需要把数据块标志为'software corrupt'
   需要使用dbms_repair家标记。
   先利用dbms_repair创建两个表
   declare
   begin
         dbms_repair.admin_tables(
                   table_name=>'repair_table',
                   table_type=>dbms_repaire.repaire_table,
                  action=>dbms_repaire.create_action,
                  tablespace=>'USER' )
    end;
    在创建orphan key table:
   declare
   begin
         dbms_repair.admin_tables(
                   table_name=>'orphan_table',
                   table_type=>dbms_repaire.orphan_table,
                  action=>dbms_repaire.create_action,
                  tablespace=>'USER' )
    end;

标记坏块：
 declare
         fix_count int;
  begin
        fix_count:=0
        dbms_repaire.fix_corrupt_blocks(
          schema_name=>'SCOTT',
          object_name=>'TEST',
          object_type=>dbms_repaire.table_objects,
          repaire_table_name=>'repaire_table',
          fix_count=>fix_count);
        dbms_output.put_line('fix count:'||to_char(fix_count)');
   end;
之后查询数据块，会抛出ORA-01578错误。


4.重建表

    使用dbms_repaire.skip_corrupt_blocks设置为skip,或者使用10231事件，屏蔽ora-1578错误
    alter session set events '10231 trace name context forever ,level 10'
    执行这个之后发现user_table中skip_corrupt，还是disable，不知道为什么这个样（11g）
   但是可以全表执行全表扫描。
   利用CTAS重建table和相关index。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24237320/viewspace-732382/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24237320/viewspace-732382/