資料塊恢復例項

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。