标签归档：recover drop pdb

oracle asm中drop pdb恢复方法

发表于 2020 年 4 月 28 日由惜分飞

最近分析研究了一种asm disk 数据文件丢失的新恢复方法,通过cod和acd进行恢复,我们对于asm 磁盘组中的文件的改变(创建,删除,扩大,缩小等)操作会体现在cod和acd中有一些体现,类似oracle 数据库中数据的变化都会体现在redo和undo中类似.可以通过对他们的分析,确认文件在asm磁盘组中的分配关系,从而实现数据文件的恢复.我这里通过模拟创建表空间,插入数据,删除表空间(同时也删除文件),然后相关cod和acd分析,实现数据文件恢复.
创建表空间

SQL> create tablespace xifenfei datafile '+data' size 1G;

Tablespace created.

SQL> alter tablespace xifenfei add datafile '+data' size 128M autoextend on;

Tablespace altered.

创建模拟表并插入数据

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

Table created.

SQL> insert into t_xifenfei select * from t_xifenfei;

73013 rows created.

…………

SQL> insert into t_xifenfei select * from t_xifenfei;

18691328 rows created.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(1) FROM T_XIFENFEI;

  COUNT(1)
----------
  37382656

SQL> alter system checkpoint;

System altered.

SQL> select bytes/1024/1024/1024,TABLESPACE_NAME FROM USER_SEGMENTS  where segment_name='T_XIFENFEI';

BYTES/1024/1024/1024 TABLESPACE_NAME
-------------------- ------------------------------
          5.56738281 XIFENFEI

删除表空间

SQL> drop tablespace xifenfei including contents and datafiles;

Tablespace dropped.

查看alert日志信息

2020-04-23T18:23:43.088997-04:00
drop tablespace xifenfei including contents and datafiles
2020-04-23T18:23:46.226654-04:00
Deleted Oracle managed file +DATA/ORA18C/DATAFILE/xifenfei.262.1035571131
Deleted Oracle managed file +DATA/ORA18C/DATAFILE/xifenfei.263.1038507123
Completed: drop tablespace xifenfei including contents and datafiles

这里我们可以看到被删除的两个数据文件的asm number为262和263,如果要恢复该表空间数据需要先恢复出来该数据文件.由于文件被删除,文件对应存储在asm里面的找出来相关的数据分配关系才可以对其恢复出来.尝试找该文件的分配extent映射关系
尝试直接读取extent map

[root@rac18c2 ~]#  kfed read /dev/xifenfei-sdb  aus=4194304 blkn=0|grep f1b1locn
kfdhdb.f1b1locn:                     10 ; 0x0d4: 0x0000000a
[root@rac18c2 ~]#  kfed read /dev/xifenfei-sdb  aus=4194304 aun=10 blkn=262|more
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            4 ; 0x002: KFBTYP_FILEDIR
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                     262 ; 0x004: blk=262
kfbh.block.obj:                       1 ; 0x008: file=1
kfbh.check:                  4132734069 ; 0x00c: 0xf6548475
kfbh.fcn.base:                     6741 ; 0x010: 0x00001a55
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfffdb.node.incarn:          1035571132 ; 0x000: A=0 NUMM=0x1edcc7de
kfffdb.node.frlist.number:          264 ; 0x004: 0x00000108
kfffdb.node.frlist.incarn:            0 ; 0x008: A=0 NUMM=0x0
kfffdb.hibytes:                       0 ; 0x00c: 0x00000000
kfffdb.lobytes:              1073750016 ; 0x010: 0x40002000
kfffdb.xtntcnt:                       0 ; 0x014: 0x00000000
kfffdb.xtnteof:                     257 ; 0x018: 0x00000101
kfffdb.blkSize:                    8192 ; 0x01c: 0x00002000
kfffdb.flags:                         1 ; 0x020: O=1 S=0 S=0 D=0 C=0 I=0 R=0 A=0
kfffdb.fileType:                      2 ; 0x021: 0x02
…………
kfffdb.mxshad:                        0 ; 0x498: 0x0000
kfffdb.mxprnt:                        0 ; 0x49a: 0x0000
kfffdb.fmtBlks:                  131073 ; 0x49c: 0x00020001
kfffde[0].xptr.au:           4294967295 ; 0x4a0: 0xffffffff
kfffde[0].xptr.disk:              65535 ; 0x4a4: 0xffff
kfffde[0].xptr.flags:                 0 ; 0x4a6: L=0 E=0 D=0 S=0 R=0 I=0
kfffde[0].xptr.chk:                  42 ; 0x4a7: 0x2a
kfffde[1].xptr.au:           4294967295 ; 0x4a8: 0xffffffff
kfffde[1].xptr.disk:              65535 ; 0x4ac: 0xffff
kfffde[1].xptr.flags:                 0 ; 0x4ae: L=0 E=0 D=0 S=0 R=0 I=0
kfffde[1].xptr.chk:                  42 ; 0x4af: 0x2a

这里的kfffdb.blkSize为8192证明以前很可能是数据文件,但是kfffde中的au和disk全部被置为f,说明extent的直接映射表已经被置空,更不用说间接extent分配映射表,也就是说这条路无法走通.变换一种思路,既然文件从asm中删除掉的extent映射关系被清空,那是否可以通过对应的acd记录来找到相关数据.通过对acd进行分析,发现在删除drop的时间点相关类似记录,通过分析对应的acd记录,发现直接extent和扩展extent分配全部被置空,无法通过该思路进行恢复
尝试acd恢复extent map

kfracdb2.lge[2].bcd[2].kfbl.blk:    262 ; 0x1cc: blk=262
kfracdb2.lge[2].bcd[2].kfbl.obj:      1 ; 0x1d0: file=1
kfracdb2.lge[2].bcd[2].kfcn.base:  6216 ; 0x1d4: 0x00001848
kfracdb2.lge[2].bcd[2].kfcn.wrap:     0 ; 0x1d8: 0x00000000
kfracdb2.lge[2].bcd[2].oplen:        20 ; 0x1dc: 0x0014
kfracdb2.lge[2].bcd[2].blkIndex:    262 ; 0x1de: 0x0106
kfracdb2.lge[2].bcd[2].flags:        28 ; 0x1e0: F=0 N=0 F=1 L=1 V=1 A=0 C=0 R=0
kfracdb2.lge[2].bcd[2].opcode:      162 ; 0x1e2: 0x00a2
kfracdb2.lge[2].bcd[2].kfbtyp:        4 ; 0x1e4: KFBTYP_FILEDIR
kfracdb2.lge[2].bcd[2].redund:       17 ; 0x1e5: SCHE=0x1 NUMB=0x1
kfracdb2.lge[2].bcd[2].pad:       63903 ; 0x1e6: 0xf99f
kfracdb2.lge[2].bcd[2].KFFFD_PEXT.xtntcnt:0 ; 0x1e8: 0x00000000
kfracdb2.lge[2].bcd[2].KFFFD_PEXT.xtntblk:0 ; 0x1ec: 0x0000
kfracdb2.lge[2].bcd[2].KFFFD_PEXT.xnum:0 ; 0x1ee: 0x0000
kfracdb2.lge[2].bcd[2].KFFFD_PEXT.xcnt:1 ; 0x1f0: 0x0001
kfracdb2.lge[2].bcd[2].KFFFD_PEXT.setflg:0 ; 0x1f2: 0x00
kfracdb2.lge[2].bcd[2].KFFFD_PEXT.flags:0 ; 0x1f3: O=0 S=0 S=0 D=0 C=0 I=0 R=0 A=0
kfracdb2.lge[2].bcd[2].KFFFD_PEXT.xptr[0].au:4294967292 ; 0x1f4: 0xfffffffc
kfracdb2.lge[2].bcd[2].KFFFD_PEXT.xptr[0].disk:0 ; 0x1f8: 0x0000
kfracdb2.lge[2].bcd[2].KFFFD_PEXT.xptr[0].flags:0 ; 0x1fa: L=0 E=0 D=0 S=0 R=0 I=0
kfracdb2.lge[2].bcd[2].KFFFD_PEXT.xptr[0].chk:41 ; 0x1fb: 0x29
kfracdb2.lge[2].bcd[2].au[0]:        10 ; 0x1fc: 0x0000000a
kfracdb2.lge[2].bcd[2].disks[0]:      0 ; 0x200: 0x0000


kfracdb2.lge[20].bcd[1].kfbl.blk:2147483648 ; 0xe54: blk=0 (indirect)
kfracdb2.lge[20].bcd[1].kfbl.obj:   262 ; 0xe58: file=262
kfracdb2.lge[20].bcd[1].kfcn.base: 3280 ; 0xe5c: 0x00000cd0
kfracdb2.lge[20].bcd[1].kfcn.wrap:    0 ; 0xe60: 0x00000000
kfracdb2.lge[20].bcd[1].oplen:       16 ; 0xe64: 0x0010
kfracdb2.lge[20].bcd[1].blkIndex:     0 ; 0xe66: 0x0000
kfracdb2.lge[20].bcd[1].flags:       28 ; 0xe68: F=0 N=0 F=1 L=1 V=1 A=0 C=0 R=0
kfracdb2.lge[20].bcd[1].opcode:     163 ; 0xe6a: 0x00a3
kfracdb2.lge[20].bcd[1].kfbtyp:      12 ; 0xe6c: KFBTYP_INDIRECT
kfracdb2.lge[20].bcd[1].redund:      17 ; 0xe6d: SCHE=0x1 NUMB=0x1
kfracdb2.lge[20].bcd[1].pad:      63903 ; 0xe6e: 0xf99f
kfracdb2.lge[20].bcd[1].KFFIX_PEXT.xtntblk:0 ; 0xe70: 0x0000
kfracdb2.lge[20].bcd[1].KFFIX_PEXT.xnum:0 ; 0xe72: 0x0000
kfracdb2.lge[20].bcd[1].KFFIX_PEXT.xcnt:1 ; 0xe74: 0x0001
kfracdb2.lge[20].bcd[1].KFFIX_PEXT.ub2spare:0 ; 0xe76: 0x0000
kfracdb2.lge[20].bcd[1].KFFIX_PEXT.xptr[0].au:4294967292 ; 0xe78: 0xfffffffc
kfracdb2.lge[20].bcd[1].KFFIX_PEXT.xptr[0].disk:0 ; 0xe7c: 0x0000
kfracdb2.lge[20].bcd[1].KFFIX_PEXT.xptr[0].flags:0 ; 0xe7e: L=0 E=0 D=0 S=0 R=0 I=0
kfracdb2.lge[20].bcd[1].KFFIX_PEXT.xptr[0].chk:41 ; 0xe7f: 0x29
kfracdb2.lge[20].bcd[1].au[0]:      296 ; 0xe80: 0x00000128
kfracdb2.lge[20].bcd[1].disks[0]:     0 ; 0xe84: 0x0000

直接通过删除的acd记录来找出来数据文件分配的extent也行不通,通过分析相关acd block,终于找到了对应的extent分配的相关记录

kfracdb2.lge[21].bcd[0].kfbl.blk:     2 ; 0x918: blk=2
kfracdb2.lge[21].bcd[0].kfbl.obj:2147483648 ; 0x91c: disk=0
kfracdb2.lge[21].bcd[0].kfcn.base: 2820 ; 0x920: 0x00000b04
kfracdb2.lge[21].bcd[0].kfcn.wrap:    0 ; 0x924: 0x00000000
kfracdb2.lge[21].bcd[0].oplen:       28 ; 0x928: 0x001c
kfracdb2.lge[21].bcd[0].blkIndex:     2 ; 0x92a: 0x0002
kfracdb2.lge[21].bcd[0].flags:       28 ; 0x92c: F=0 N=0 F=1 L=1 V=1 A=0 C=0 R=0
kfracdb2.lge[21].bcd[0].opcode:      73 ; 0x92e: 0x0049
kfracdb2.lge[21].bcd[0].kfbtyp:       3 ; 0x930: KFBTYP_ALLOCTBL
kfracdb2.lge[21].bcd[0].redund:      18 ; 0x931: SCHE=0x1 NUMB=0x2
kfracdb2.lge[21].bcd[0].pad:      63903 ; 0x932: 0xf99f
kfracdb2.lge[21].bcd[0].KFDAT_ALLOC2.vaa.curidx:2416 ; 0x934: 0x0970
kfracdb2.lge[21].bcd[0].KFDAT_ALLOC2.vaa.nxtidx:8 ; 0x936: 0x0008
kfracdb2.lge[21].bcd[0].KFDAT_ALLOC2.vaa.prvidx:8 ; 0x938: 0x0008
kfracdb2.lge[21].bcd[0].KFDAT_ALLOC2.vaa.asz:0 ; 0x93a: KFDASZ_1X
kfracdb2.lge[21].bcd[0].KFDAT_ALLOC2.vaa.frag:0 ; 0x93b: 0x00
kfracdb2.lge[21].bcd[0].KFDAT_ALLOC2.vaa.total:0 ; 0x93c: 0x0000
kfracdb2.lge[21].bcd[0].KFDAT_ALLOC2.vaa.free:0 ; 0x93e: 0x0000
kfracdb2.lge[21].bcd[0].KFDAT_ALLOC2.vaa.fnum:262 ; 0x940: 0x00000106
kfracdb2.lge[21].bcd[0].KFDAT_ALLOC2.vaa.xnum:0 ; 0x944: 0x00000000
kfracdb2.lge[21].bcd[0].KFDAT_ALLOC2.vaa.flags:8388608 ; 0x948: 0x00800000
kfracdb2.lge[21].bcd[0].KFDAT_ALLOC2.lxnum:3 ; 0x94c: 0x03
kfracdb2.lge[21].bcd[0].KFDAT_ALLOC2.spare1:0 ; 0x94d: 0x00
kfracdb2.lge[21].bcd[0].KFDAT_ALLOC2.spare2:0 ; 0x94e: 0x0000
kfracdb2.lge[21].bcd[0].au[0]:        0 ; 0x950: 0x00000000
kfracdb2.lge[21].bcd[0].au[1]:       11 ; 0x954: 0x0000000b
kfracdb2.lge[21].bcd[0].disks[0]:     0 ; 0x958: 0x0000
kfracdb2.lge[21].bcd[0].disks[1]:     0 ; 0x95a: 0x0000
kfracdb2.lge[21].bcd[1].kfbl.blk:   262 ; 0x95c: blk=262
kfracdb2.lge[21].bcd[1].kfbl.obj:     1 ; 0x960: file=1
kfracdb2.lge[21].bcd[1].kfcn.base: 3018 ; 0x964: 0x00000bca
kfracdb2.lge[21].bcd[1].kfcn.wrap:    0 ; 0x968: 0x00000000
kfracdb2.lge[21].bcd[1].oplen:       20 ; 0x96c: 0x0014
kfracdb2.lge[21].bcd[1].blkIndex:   262 ; 0x96e: 0x0106
kfracdb2.lge[21].bcd[1].flags:       28 ; 0x970: F=0 N=0 F=1 L=1 V=1 A=0 C=0 R=0
kfracdb2.lge[21].bcd[1].opcode:     162 ; 0x972: 0x00a2
kfracdb2.lge[21].bcd[1].kfbtyp:       4 ; 0x974: KFBTYP_FILEDIR
kfracdb2.lge[21].bcd[1].redund:      17 ; 0x975: SCHE=0x1 NUMB=0x1
kfracdb2.lge[21].bcd[1].pad:      63903 ; 0x976: 0xf99f
kfracdb2.lge[21].bcd[1].KFFFD_PEXT.xtntcnt:0 ; 0x978: 0x00000000
kfracdb2.lge[21].bcd[1].KFFFD_PEXT.xtntblk:0 ; 0x97c: 0x0000
kfracdb2.lge[21].bcd[1].KFFFD_PEXT.xnum:0 ; 0x97e: 0x0000
kfracdb2.lge[21].bcd[1].KFFFD_PEXT.xcnt:1 ; 0x980: 0x0001
kfracdb2.lge[21].bcd[1].KFFFD_PEXT.setflg:0 ; 0x982: 0x00
kfracdb2.lge[21].bcd[1].KFFFD_PEXT.flags:0 ; 0x983: O=0 S=0 S=0 D=0 C=0 I=0 R=0 A=0
kfracdb2.lge[21].bcd[1].KFFFD_PEXT.xptr[0].au:297 ; 0x984: 0x00000129
kfracdb2.lge[21].bcd[1].KFFFD_PEXT.xptr[0].disk:0 ; 0x988: 0x0000
kfracdb2.lge[21].bcd[1].KFFFD_PEXT.xptr[0].flags:0 ; 0x98a: L=0 E=0 D=0 S=0 R=0 I=0
kfracdb2.lge[21].bcd[1].KFFFD_PEXT.xptr[0].chk:2 ; 0x98b: 0x02
kfracdb2.lge[21].bcd[1].au[0]:       10 ; 0x98c: 0x0000000a
kfracdb2.lge[21].bcd[1].disks[0]:     0 ; 0x990: 0x0000
kfracdb2.lge[21].bcd[2].kfbl.blk:     2 ; 0x994: blk=2
kfracdb2.lge[21].bcd[2].kfbl.obj:     4 ; 0x998: file=4
kfracdb2.lge[21].bcd[2].kfcn.base: 3019 ; 0x99c: 0x00000bcb
kfracdb2.lge[21].bcd[2].kfcn.wrap:    0 ; 0x9a0: 0x00000000
kfracdb2.lge[21].bcd[2].oplen:        8 ; 0x9a4: 0x0008
kfracdb2.lge[21].bcd[2].blkIndex:     2 ; 0x9a6: 0x0002
kfracdb2.lge[21].bcd[2].flags:       28 ; 0x9a8: F=0 N=0 F=1 L=1 V=1 A=0 C=0 R=0
kfracdb2.lge[21].bcd[2].opcode:     211 ; 0x9aa: 0x00d3
kfracdb2.lge[21].bcd[2].kfbtyp:      16 ; 0x9ac: KFBTYP_COD_DATA
kfracdb2.lge[21].bcd[2].redund:      17 ; 0x9ad: SCHE=0x1 NUMB=0x1
kfracdb2.lge[21].bcd[2].pad:      63903 ; 0x9ae: 0xf99f
kfracdb2.lge[21].bcd[2].KFRCOD_DATA.offset:60 ; 0x9b0: 0x003c
kfracdb2.lge[21].bcd[2].KFRCOD_DATA.length:4 ; 0x9b2: 0x0004
kfracdb2.lge[21].bcd[2].KFRCOD_DATA.data[0]:1 ; 0x9b4: 0x01
kfracdb2.lge[21].bcd[2].KFRCOD_DATA.data[1]:0 ; 0x9b5: 0x00
kfracdb2.lge[21].bcd[2].KFRCOD_DATA.data[2]:0 ; 0x9b6: 0x00
kfracdb2.lge[21].bcd[2].KFRCOD_DATA.data[3]:0 ; 0x9b7: 0x00
kfracdb2.lge[21].bcd[2].au[0]:       16 ; 0x9b8: 0x00000010
kfracdb2.lge[21].bcd[2].disks[0]:     0 ; 0x9bc: 0x0000

对于类似这样的记录,通过汇总处理获得所有的file number对应的au extent分配记录,并且生成dd语句,然后生成文件

dbv检查恢复文件

[oracle@rac18c2 tmp]$ dbv file=262.dbf

DBVERIFY: Release 18.0.0.0.0 - Production on Tue Apr 28 09:45:37 2020

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

DBVERIFY - Verification starting : FILE = /tmp/262.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 131072
Total Pages Processed (Data) : 123400
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 631
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 7041
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 10001146011 (2.1411211419)

[oracle@rac18c2 tmp]$ dbv file=263.dbf

DBVERIFY: Release 18.0.0.0.0 - Production on Tue Apr 28 09:51:05 2020

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

DBVERIFY - Verification starting : FILE = /tmp/263.dbf



DBVERIFY - Verification complete

Total Pages Examined         : 643584
Total Pages Processed (Data) : 595146
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 821
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 36865
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 10001153042 (2.1411218450)
[oracle@rac18c2 tmp]$

dul确认恢复文件中数据

DUL>  scan database
start scan database in parallel 1...
scan database completed.
DUL> sample all segment 
start get segment info: data_obj#: 74635
finish get segment info: data_obj#: 74635
guess col def: 22
write segment info: 74635, 1, 8, 22
write sample rows: 10000
DUL> unload object 74635
 2020-04-24 22:32:11 unloading table segment 74635...
 2020-04-24 22:35:36 unloaded 37382656 rows.
DUL>

通过dbv和实际数据条数对比,此种恢复恢复的数据完全正常,不用使用底层碎片扫描,亦可恢复asm中被删除数据文件数据.在某些特殊情况下,此类方法配合底层碎片恢复,可以实现更加完美的恢复效果.对于比较典型的oracle pdb被删除(因为有多个数据文件的文件号是一样的,无法直接通过底层碎片扫描恢复),通过此类方法可以非常好的恢复出来.
类似文章参考:
asm disk header 彻底损坏恢复
 ASM未正常启动,使用dd找回数据文件
 asm磁盘组操作不当导致数据文件丢失恢复
如果你不幸遭遇asm 数据文件被删除/丢失,或者误删除pdb等相关事宜,如果需要恢复可以联系我们,提供专业数据库恢复服务
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com