E-mail：chf.dba@gmail.com
Title: oracle asm drop pdb recovery
Author: DATABASE SOS©All rights reserved [without my consent, it may not be reproduced in any form, otherwise there is the right to further legal responsibility.]
Recently, a new recovery method for asm disk data file loss has been analyzed and studied. Cod and acd are used for recovery. Our changes (creation, deletion, expansion, reduction, etc.) of files in the asm disk group will be reflected in cod and acd. There are some manifestations in the database. The changes in the data in the oracle database will be reflected in the redo and undo. You can confirm the distribution of the files in the asm disk group through their analysis to achieve the recovery of the data files. Create table spaces, insert data, delete table spaces (and delete files at the same time), and then analyze related cod and acd to achieve data file recovery.
Create tablespace
SQL> create tablespace xifenfei datafile '+data' size 1G; Tablespace created. SQL> alter tablespace xifenfei add datafile '+data' size 128M autoextend on; Tablespace altered.
Create mock table and insert data
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
drop tablespace
SQL> drop tablespace xifenfei including contents and datafiles; Tablespace dropped.
View alert log information
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
Here we can see that the asm numbers of the two deleted data files are 262 and 263. If you want to restore the table space data, you need to recover the data file first. Since the file is deleted, the file is stored in the asm and found to be related The data distribution relationship can be restored. Try to find the file extension mapping relationship
Try to read directly 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
The kfffdb.blkSize of 8192 here proves that it was probably a data file before, but the au and disk in kfffde are all set to f, indicating that the direct mapping table of the extent has been emptied, not to mention the indirect extent allocation mapping table, which is Say this path can’t get through. Change a way of thinking, since the extent mapping relationship of the file deleted from asm is cleared, then can the relevant data be found through the corresponding acd record. By analyzing the acd, it is found that drop Similar records related to the time point, by analyzing the corresponding acd records, it is found that the direct extent and extended extent allocation are all emptied and cannot be recovered through this idea
Try acd to restore 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
It is also not feasible to find the extent of the data file allocation directly by deleting the acd record. By analyzing the relevant acd block, I finally found the relevant record of the corresponding extent allocation
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
For records like this, all the au extent allocation records corresponding to the file number are obtained through the summary process, and the dd statement is generated, and then the file is generated
dbv Check recovery files
[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 Confirm to restore the data in the file
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>
By comparing the number of dbv and actual data, the data recovered by this kind of recovery is completely normal, and the data of the deleted data file in the asm can be recovered without using the underlying fragment scanning. In some special cases, this method cooperates with the underlying fragment recovery A more perfect recovery effect can be achieved. For the more typical oracle pdb is deleted (because there are multiple data files with the same file number, it cannot be directly recovered through the underlying fragmentation scan), and it can be recovered very well by such methods.
Similar article reference:
asm disk header complete damage recovery
ASM does not start normally, use dd to retrieve the data file
Asm disk group operation leads to data file loss recovery
If you unfortunately encounter asm data files deleted / lost, or accidentally deleted pdb and other related matters, if you need to restore, you can contact us to provide:E-Mail:chf.dba@gmail.com