ORA-01122 ORA-01210 故障恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-01122 ORA-01210 故障恢复

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有朋友数据文件头出现错误ORA-01122和ORA-01210等错误，数据库无法正常open。
ORA-01210

因为平台是win，他们找我咨询win bbed,因为回老家电脑没有带,无法提供win的bbed.我通过dd部分文件头,然后在linux平台分析发现是该文件的文件头block大量坏块

bbed分析坏块情况

BBED> show all
        FILE#           0
        BLOCK#          1
        OFFSET          0
        DBA             0x00000000 (0 0,1)
        FILENAME        /tmp/30.dbf
        BIFILE          bifile.bbd
        LISTFILE
        BLOCKSIZE       8192
        MODE            Browse
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No
BBED> set count 64
        COUNT           64
BBED> map
 File: /tmp/30.dbf (0)
 Block: 1                                     Dba:0x00000000
------------------------------------------------------------
BBED-00400: invalid blocktype (27)
BBED> d
 File: /tmp/30.dbf (0)
 Block: 1                Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 03004400 bffd8a1d 0000000c acba0000 008f4500 00003455 fc020000
 02040000 00000000 00008001 04000000 00000000 00000000 949400b4 94514005
 <32 bytes per line>
BBED> set block +1
        BLOCK#          2
BBED> map
 File: /tmp/30.dbf (0)
 Block: 2                                     Dba:0x00000000
------------------------------------------------------------
BBED-00400: invalid blocktype (27)
BBED> d
 File: /tmp/30.dbf (0)
 Block: 2                Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 04004400 bffd8a1d 0000000c a6e00000 008f4500 00003455 fc020000
 0204e81f 00000000 0000241e 05000000 00000000 00000000 11fc297f b426fe2b
 <32 bytes per line>
BBED> set block +1
        BLOCK#          3
BBED> d
 File: /tmp/30.dbf (0)
 Block: 3                Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 05004400 bffd8a1d 0000000c 780a0000 008f4500 00003455 fc020000
 0204e81f 00000000 0000c001 06000000 00000000 00000000 2969a0d2 d30168a2
 <32 bytes per line>
BBED> set block +1
        BLOCK#          4
BBED> d
 File: /tmp/30.dbf (0)
 Block: 4                Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 06004400 bffd8a1d 0000000c 6c5a0000 008f4500 00003455 fc020000
 0204e81f 00000000 0000f81d 07000000 00000000 00000000 7b51d409 6dc7ca4d
 <32 bytes per line>
BBED> set block +1
        BLOCK#          5
BBED> d
 File: /tmp/30.dbf (0)
 Block: 5                Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 07004400 bffd8a1d 0000000c c5600000 008f4500 00003455 fc020000
 02040000 00000000 0000c001 08000000 00000000 00000000 14514005 25145200
 <32 bytes per line>
BBED> set block +1
        BLOCK#          6
BBED> d
 File: /tmp/30.dbf (0)
 Block: 6                Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 08004400 bffd8a1d 0000000c 60480000 008f4500 00003455 fc020000
 0204e81f 00000000 0000c301 09000000 00000000 00000000 c2a1606a 7615130a
 <32 bytes per line>
BBED> set block +1
        BLOCK#          7
BBED> d
 File: /tmp/30.dbf (0)
 Block: 7                Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 09004400 bffd8a1d 0000000c e3430000 008f4500 00003455 fc020000
 0204e81f 00000000 00000002 0a000000 00000000 00000000 00a28a28 00a28a28
 <32 bytes per line>
BBED> set block +1
        BLOCK#          8
BBED> d
 File: /tmp/30.dbf (0)
 Block: 8                Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 0a004400 07fe8a1d 0000000c fc000000 008f4500 00003455 fc020000
 0205e81f 00000000 0000f41d 00000000 00000000 00000000 ffd8ffe0 00104a46
 <32 bytes per line>
BBED> set block +1
        BLOCK#          9
BBED> d
 File: /tmp/30.dbf (0)
 Block: 9                Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 0b004400 07fe8a1d 0000000c 48da0000 008f4500 00003455 fc020000
 0205e81f 00000000 0000c601 01000000 00000000 00000000 b47d69d3 7fa96a6f
 <32 bytes per line>
BBED> set block +1
        BLOCK#          10
BBED> d
 File: /tmp/30.dbf (0)
 Block: 10               Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 0c004400 07fe8a1d 0000000c be0f0000 008f4500 00003455 fc020000
 0205e81f 00000000 0000181d 02000000 00000000 00000000 9de3e868 4782d83a
 <32 bytes per line>
BBED> set block +1
        BLOCK#          11
BBED> d
 File: /tmp/30.dbf (0)
 Block: 11               Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 0d004400 07fe8a1d 0000000c 9cd00000 008f4500 00003455 fc020000
 0205e81f 00000000 0000241e 03000000 00000000 00000000 dead1259 5919e385
 <32 bytes per line>
BBED> set block +1
        BLOCK#          12
BBED> d
 File: /tmp/30.dbf (0)
 Block: 12               Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 0e004400 07fe8a1d 0000000c df450000 008f4500 00003455 fc020000
 0205e81f 00000000 00004001 04000000 00000000 00000000 31d9a292 9698828a
 <32 bytes per line>
BBED> set block +1
        BLOCK#          13
BBED> d
 File: /tmp/30.dbf (0)
 Block: 13               Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 0f004400 07fe8a1d 0000000c 18790000 008f4500 00003455 fc020000
 02050000 00000000 00000002 05000000 00000000 00000000 b93f8235 5ea063b7
 <32 bytes per line>

拿block 1的rdba(04004400–倒序存储)分析[win文件拷贝到linux后使用bbed查看相差1 block]可以的出来block信息为file=1, block=262148,明显错误.

通过dul分析文件头损坏情况

Data UnLoader: 10.2.0.6.9 - Internal Only - on Tue Sep 29 22:15:22 2015
with 64-bit io functions
Copyright (c) 1994 2015 Bernard van Duijnen All rights reserved.
 Strictly Oracle Internal Use Only
DUL: Warning: Recreating file "dul.log"
Reading SCANNEDLOBPAGE.dat 1204 entries loaded and sorted 1204 entries
Reading SEG.dat 0 entries loaded
Reading EXT.dat 44 entries loaded and sorted 44 entries
Reading COMPATSEG.dat 0 entries loaded
DUL: Warning: Wrong DBA  0X00440004 (file=1, block=262148) (Ignored)
DUL: Error: While processing file# 30 block# 1
DUL: Warning: Found mismatch while checking file E:\TEMP\shebao\30.dbf
DUL: Warning: DUL osd_parameter or control.dul configuration error
DUL: Warning: Given file number(30) in control file does not match file# in dba(1)
DUL: Warning: Wrong DBA  0X00440004 (file=1, block=262148) (Ignored)
DUL: Error: While processing file# 30 block# 1
DUL>

通过bbed和dul证明文件头大量损坏,而且尚未有任何该文件的物理备份,因此恢复起来难道较大。

分析Oracle Database Recovery Check Result
通过对Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)的分析结果,我们意外的发现,人品不错，发现异常的文件创建时间为2015-09-26 19:39:33，进一步和客户沟通，这个文件存储为图片，少量丢失可以允许，优先恢复业务
Oracle-Database-Recovery-Check

有了这个结论,那处理起来就easy了,直接offline异常文件,然后分析丢失的表
从而确定时lob字典的少量extent数据分配到了file 30上
lob

为了避免查询对应lob之时出现错误,通过update 对应lob为空规避该问题

create table corrupt_lobs (corrupt_rowid rowid,table_name varchar2(100));
declare
  n number;
begin
  for cursor_lob in (select rowid r, xff_lob from xff.t_xifenfei) loop
  begin
    n:=dbms_lob.instr(cursor_lob.xff_lob,hextoraw('889911'));
  exception
    when others then
      insert into corrupt_lobs values (cursor_lob.r,'xff.t_xifenfei');
      commit;
    end;
  end loop;
end;
/
update xff.t_xifenfei
     set xff_lob = empty_blob()
     where rowid in (select corrupted_rowid from corrupt_lobs);

本次恢复是由于运气好,遇到异常文件刚好是最近加入,而且都是图片,客户允许少量丢失,如果是不允许丢失的数据文件,可能需要通过找历史的该文件的备份(Oracle 12C的第一次异常恢复—文件头坏块),在某些情况下,如果也没有此类备份,只能通过bbed重构block 1(如果有其他异常块一次处理,如果太多无法处理,最少也需要重构block 1）,然后尝试open数据库或者使用dul之类工具处理（因为文件头损坏,工具可能不能识别文件无法恢复）

发表评论

邮箱地址不会被公开。 必填项已用*标注

7 + 16 =