联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有朋友oracle数据库文件被加密,后缀名为:.id-09C1B27D.[3441546223@qq.com].ncov,
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
最近分析研究了一种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语句,然后生成文件
[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 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
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
接到客户恢复请求:把oracle asm datagroup中的一个磁盘增加到vg中,现在磁盘组无法mount,数据库无法正常启动.远程登录现场进行分析发现情况如下:
操作系统层面分析
history操作记录
--查看pv信息
[root@xff1 ~]# pvdisplay
--- Physical volume ---
PV Name /dev/sda2
VG Name VolGroup
PV Size 277.98 GiB / not usable 3.00 MiB
Allocatable yes (but full)
PE Size 4.00 MiB
Total PE 71161
Free PE 0
Allocated PE 71161
PV UUID F6QO3f-065n-mwTW-Xbq2-Xx2y-c8HD-Tkr7V7
--- Physical volume ---
PV Name /dev/sdg <----新加入的磁盘
VG Name VolGroup
PV Size 200.00 GiB / not usable 4.00 MiB
Allocatable yes
PE Size 4.00 MiB
Total PE 51199
Free PE 255
Allocated PE 50944
PV UUID i69vUG-nCIK-dtxL-FvpD-2WZd-bvLv-n7lwrb
[root@xff1 ~]# lvdisplay
--- Logical volume ---
LV Path /dev/VolGroup/lv_root
LV Name lv_root
VG Name VolGroup
LV UUID JUNnkN-m4zq-D0gh-h42b-cUM1-Wh1q-ZMtQE4
LV Write Access read/write
LV Creation host, time localhost.localdomain, 2017-07-19 20:08:47 +0800
LV Status available
# open 1
LV Size 50.00 GiB
Current LE 12800
Segments 1
Allocation inherit
Read ahead sectors auto
- currently set to 256
Block device 253:0
--- Logical volume ---
LV Path /dev/VolGroup/lv_home
LV Name lv_home
VG Name VolGroup
LV UUID eZTkLt-cNGX-371i-m8Bd-VdD9-q6Hz-wYDRIJ
LV Write Access read/write
LV Creation host, time localhost.localdomain, 2017-07-19 20:08:54 +0800
LV Status available
# open 1
LV Size 422.97 GiB <-----lv大小编程422G,应该是被扩了199G后结果
Current LE 108281
Segments 2
Allocation inherit
Read ahead sectors auto
- currently set to 256
Block device 253:2
--- Logical volume ---
LV Path /dev/VolGroup/lv_swap
LV Name lv_swap
VG Name VolGroup
LV UUID 54P9ok-VpwO-zM68-hvwY-9GBf-89yb-8xQAMn
LV Write Access read/write
LV Creation host, time localhost.localdomain, 2017-07-19 20:09:23 +0800
LV Status available
# open 1
LV Size 4.00 GiB
Current LE 1024
Segments 1
Allocation inherit
Read ahead sectors auto
- currently set to 256
Block device 253:1
[root@xff1 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
50G 3.9G 43G 9% /
tmpfs 63G 509M 63G 1% /dev/shm
/dev/sda1 477M 44M 408M 10% /boot
/dev/mapper/VolGroup-lv_home
417G 226G 170G 58% /home <----增加了199g空间,剩余只剩170G,证明增加空间之后最少使用了30G以上
基于这样的情况,基本上可以确定sdg盘加入VolGroup中并且被分配给 lv_home中,而且还写入了数据(/home空闲空间只剩余170G,lv_home当时扩了199G).
asm层面分析
asm磁盘组无法mount,提示缺少一块磁盘
SQL> ALTER DISKGROUP DATA MOUNT /* asm agent *//* {1:12056:279} */
NOTE: cache registered group DATA number=1 incarn=0xa1dbff16
NOTE: cache began mount (first) of group DATA number=1 incarn=0xa1dbff16
NOTE: Assigning number (1,2) to disk (/dev/asmdisk3)
NOTE: Assigning number (1,1) to disk (/dev/asmdisk2)
Sat Apr 25 13:04:58 2020
ERROR: no read quorum in group: required 1, found 0 disks
NOTE: cache dismounting (clean) group 1/0xA1DBFF16 (DATA)
NOTE: messaging CKPT to quiesce pins Unix process pid: 81552, image: oracle@rac2db1 (TNS V1-V3)
NOTE: dbwr not being msg'd to dismount
NOTE: lgwr not being msg'd to dismount
NOTE: cache dismounted group 1/0xA1DBFF16 (DATA)
NOTE: cache ending mount (fail) of group DATA number=1 incarn=0xa1dbff16
NOTE: cache deleting context for group DATA 1/0xa1dbff16
GMON dismounting group 1 at 19 for pid 30, osid 81552
NOTE: Disk DATA_0001 in mode 0x9 marked for de-assignment
NOTE: Disk DATA_0002 in mode 0x9 marked for de-assignment
ERROR: diskgroup DATA was not mounted
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DATA" cannot be mounted
ORA-15040: diskgroup is incomplete
ERROR: ALTER DISKGROUP DATA MOUNT /* asm agent *//* {1:12056:279} */
kfed分析磁盘信息
root@xff1:/home/oracle11g$kfed read /dev/asmdisk1 aun=10000 kfbh.endian: 51 ; 0x000: 0x33 kfbh.hard: 55 ; 0x001: 0x37 kfbh.type: 32 ; 0x002: *** Unknown Enum *** kfbh.datfmt: 42 ; 0x003: 0x2a kfbh.block.blk: 1329801248 ; 0x004: blk=1329801248 kfbh.block.obj: 1128615502 ; 0x008: file=347726 kfbh.check: 1094999892 ; 0x00c: 0x41445f54 kfbh.fcn.base: 675103060 ; 0x010: 0x283d4154 kfbh.fcn.wrap: 1448232275 ; 0x014: 0x56524553 kfbh.spare1: 1598374729 ; 0x018: 0x5f454349 kfbh.spare2: 1162690894 ; 0x01c: 0x454d414e 7F7843EAD400 2A203733 4F432820 43454E4E 41445F54 [37 * (CONNECT_DA] 7F7843EAD410 283D4154 56524553 5F454349 454D414E [TA=(SERVICE_NAME] 7F7843EAD420 6361723D 29626432 44494328 5250283D [=rac2db)(CID=(PR] 7F7843EAD430 4152474F 3A443D4D 4341505C DFCF3153 [OGRAM=D:\PACS1..] 7F7843EAD440 B3BEB7BB 6369445C 65536D6F 72657672 [....\DicomServer] 7F7843EAD450 445C524D 6D6F6369 76726553 524D7265 [MR\DicomServerMR] 7F7843EAD460 6578652E 4F482829 573D5453 362D4E49 [.exe)(HOST=WIN-6] 7F7843EAD470 51414C38 54553645 28294A30 52455355 [8LAQE6UT0J)(USER] 7F7843EAD480 6D64413D 73696E69 74617274 2929726F [=Administrator))] 7F7843EAD490 202A2029 44444128 53534552 5250283D [) * (ADDRESS=(PR] 7F7843EAD4A0 434F544F 743D4C4F 28297063 54534F48 [OTOCOL=tcp)(HOST] 7F7843EAD4B0 2E30313D 2E303831 30332E31 4F502829 [=10.180.1.30)(PO] 7F7843EAD4C0 343D5452 37333539 2A202929 74736520 [RT=49537)) * est] 7F7843EAD4D0 696C6261 2A206873 63617220 20626432 [ablish * rac2db ] 7F7843EAD4E0 3231202A 0A343135 2D534E54 31353231 [* 12514.TNS-1251] 7F7843EAD4F0 54203A34 6C3A534E 65747369 2072656E [4: TNS:listener ] 7F7843EAD500 73656F64 746F6E20 72756320 746E6572 [does not current] 7F7843EAD510 6B20796C 20776F6E 7320666F 69767265 [ly know of servi] 7F7843EAD520 72206563 65757165 64657473 206E6920 [ce requested in ] 7F7843EAD530 6E6E6F63 20746365 63736564 74706972 [connect descript] ……………… 7F7843EAE300 6F636944 7265536D 4D726576 69445C52 [DicomServerMR\Di] 7F7843EAE310 536D6F63 65767265 2E524D72 29657865 [comServerMR.exe)] 7F7843EAE320 534F4828 49573D54 4F302D4E 314B304A [(HOST=WIN-0OJ0K1] 7F7843EAE330 4955304E 55282954 3D524553 696D6441 [N0UIT)(USER=Admi] 7F7843EAE340 7473696E 6F746172 29292972 28202A20 [nistrator))) * (] 7F7843EAE350 52444441 3D535345 4F525028 4F434F54 [ADDRESS=(PROTOCO] 7F7843EAE360 63743D4C 48282970 3D54534F 312E3031 [L=tcp)(HOST=10.1] 7F7843EAE370 312E3038 2930332E 524F5028 35353D54 [80.1.30)(PORT=55] 7F7843EAE380 29383632 202A2029 61747365 73696C62 [268)) * establis] 7F7843EAE390 202A2068 32636172 2A206264 35323120 [h * rac2db * 125] 7F7843EAE3A0 540A3431 312D534E 34313532 4E54203A [14.TNS-12514: TN] 7F7843EAE3B0 696C3A53 6E657473 64207265 2073656F [S:listener does ] 7F7843EAE3C0 20746F6E 72727563 6C746E65 6E6B2079 [not currently kn] 7F7843EAE3D0 6F20776F 65732066 63697672 65722065 [ow of service re] 7F7843EAE3E0 73657571 20646574 63206E69 656E6E6F [quested in conne] 7F7843EAE3F0 64207463 72637365 6F747069 34320A72 [ct descriptor.24] KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][32]
通过上述kfed可以看到第10000 au的位置被写入的是数据库异常之后listener.log的信息(该数据库安装在/home目录中),进一步证明覆盖,通过以下信息证明sdg就是asmdisk1
[root@xff1 dev]# ls -l sdg brw-rw---- 1 root disk 8, 96 Apr 25 00:05 sdg [root@xff1 dev]# ls -l asmdisk1 brw-rw---- 1 grid asmadmin 8, 96 Apr 25 00:05 asmdisk1
基于现在的情况,data磁盘组是由三块 200G的磁盘组成,第一块磁盘被意外加入vg,并且写入数据大于30G,无法从asm层面直接通过kfed修复磁盘组,然后直接mount,只能通过oracle asm磁盘数据块重组技术(asm disk header 彻底损坏恢复)实现没有覆盖数据的恢复.
E-Mail:dba@xifenfei.com联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
接到客户恢复请求,数据库启动报ORA-600 2662错误
Fri Apr 24 19:52:58 2020 alter database open resetlogs RESETLOGS is being done without consistancy checks. This may result in a corrupted database. The database should be recreated. RESETLOGS after incomplete recovery UNTIL CHANGE 15491509441794 Resetting resetlogs activation ID 1460987657 (0x5714e709) Fri Apr 24 19:52:59 2020 Setting recovery target incarnation to 3 Fri Apr 24 19:52:59 2020 Assigning activation ID 1566342598 (0x5d5c7dc6) Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: Y:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Fri Apr 24 19:52:59 2020 SMON: enabling cache recovery Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_3860.trc (incident=8561): ORA-00600: 内部错误代码, 参数: [2662], [3606], [3857372426], [3606], [3857377059], [12583040], [], [], [], [], [], [] Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_8561\orcl_ora_3860_i8561.trc Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_3860.trc: ORA-00600: 内部错误代码, 参数: [2662], [3606], [3857372426], [3606], [3857377059], [12583040], [], [], [], [], [], [] Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_3860.trc: ORA-00600: 内部错误代码, 参数: [2662], [3606], [3857372426], [3606], [3857377059], [12583040], [], [], [], [], [], [] Error 600 happened during db open, shutting down database USER (ospid: 3860): terminating the instance due to error 600 Instance terminated by USER, pid = 3860 ORA-1092 signalled during: alter database open resetlogs...
这个错误比较常见,通过对数据库scn进行调整,顺利规避该错误,继续启动报如下错误
SQL> startup mount pfile='d:/pfile.txt'; ORACLE 例程已经启动。 Total System Global Area 1.3696E+10 bytes Fixed Size 2188768 bytes Variable Size 6878661152 bytes Database Buffers 6777995264 bytes Redo Buffers 37044224 bytes 数据库装载完毕。 SQL> alter database open; alter database open * 第 1 行出现错误: ORA-03113: 通信通道的文件结尾 进程 ID: 5884 会话 ID: 66 序列号: 3
Fri Apr 24 20:57:49 2020 SMON: enabling cache recovery Successfully onlined Undo Tablespace 2. Dictionary check beginning Dictionary check complete Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is ZHS16GBK No Resource Manager plan active Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x898ADE43] [PC:0x9287D88, kgegpa()+38] Dump file d:\app\administrator\diag\rdbms\orcl\orcl\trace\alert_orcl.log Fri Apr 24 20:57:49 2020 ORACLE V11.2.0.1.0 - 64bit Production vsnsta=0 vsnsql=16 vsnxtr=3 Windows NT Version V6.1 CPU : 16 - type 8664, 16 Physical Cores Process Affinity : 0x0x0000000000000000 Memory (Avail/Total): Ph:21429M/32767M, Ph+PgF:54255M/65533M Fri Apr 24 20:57:49 2020 Errors in file ORA-07445: caught exception [ACCESS_VIOLATION] at [kgegpa()+38] [0x0000000009287D88] Fri Apr 24 20:57:52 2020 PMON (ospid: 2496): terminating the instance due to error 397 Instance terminated by PMON, pid = 2496
这里的主要错误是由于ORA-07445 kgegpa,根据以前恢复经验,该问题很可能和undo有关,对undo进行处理之后启动库
SQL> startup mount pfile='d:/pfile.txt' ; ORACLE 例程已经启动。 Total System Global Area 1.3696E+10 bytes Fixed Size 2188768 bytes Variable Size 6878661152 bytes Database Buffers 6777995264 bytes Redo Buffers 37044224 bytes 数据库装载完毕。 SQL> recover database; 完成介质恢复。 SQL> alter database open; 数据库已更改。
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
SMON: Restarting fast_start parallel rollback
Fri Apr 24 21:01:28 2020
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_p000_4360.trc (incident=13377):
ORA-00600: internal error code, arguments: [4198], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_13377\orcl_p000_4360_i13377.trc
Stopping background process MMNL
Doing block recovery for file 3 block 296
Resuming block recovery (PMON) for file 3 block 296
Block recovery from logseq 3, block 25 to scn 15491947056761
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
Mem# 0: Y:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
Block recovery completed at rba 3.25.16, scn 3607.20090
Doing block recovery for file 6 block 165592
Resuming block recovery (PMON) for file 6 block 165592
Block recovery from logseq 3, block 33 to scn 15491947056769
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
Mem# 0: Y:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
Block recovery completed at rba 3.58.16, scn 3607.20098
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_smon_4912.trc (incident=13321):
ORA-00600: internal error code, arguments: [4198], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_13321\orcl_smon_4912_i13321.trc
SMON: Parallel transaction recovery slave got internal error
SMON: Downgrading transaction recovery to serial
Stopping background process MMON
Fri Apr 24 21:01:29 2020
Trace dumping is performing id=[cdmp_20200424210129]
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_smon_4912.trc (incident=13322):
ORA-00600: internal error code, arguments: [4137], [12.30.1712324], [0], [0], [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_13322\orcl_smon_4912_i13322.trc
ORACLE Instance orcl (pid = 14) - Error 600 encountered while recovering transaction (12, 30).
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_smon_4912.trc:
ORA-00600: internal error code, arguments: [4137], [12.30.1712324], [0], [0], [], [], [], [], [], [], [], []
Completed: alter database open upgrade
Fri Apr 24 21:01:30 2020
MMON started with pid=16, OS id=4980
Fri Apr 24 21:01:31 2020
Sweep [inc][13322]: completed
Corrupt block relative dba: 0x00c395ee (file 3, block 234990)
Fractured block found during buffer read
Data in bad block:
type: 2 format: 2 rdba: 0x00c395ee
last change scn: 0x0e16.e5ead38b seq: 0x2b flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xdb720232
check value in block header: 0xebe2
computed block checksum: 0xb60b
Reading datafile'Y:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF'for corruption at rdba: 0x00c395ee (file 3,block 234990)
Reread (file 3, block 234990) found same corrupt data
Corrupt Block Found
TSN = 2, TSNAME = UNDOTBS1
RFN = 3, BLK = 234990, RDBA = 12817902
OBJN = 0, OBJD = -1, OBJECT = , SUBOBJECT =
SEGMENT OWNER = , SEGMENT TYPE =
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_m001_4852.trc (incident=13641):
ORA-01578: ORACLE data block corrupted (file # 3, block # 234990)
ORA-01110: data file 3: 'Y:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF'
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_13641\orcl_m001_4852_i13641.trc
SQL> create undo tablespace undotbs2 datafile 2 'Y:\APP\ADMINISTRATOR\ORADATA\ORCL\undo_xff02.dbf' size 128M autoextend on; 表空间已创建。 SQL> drop tablespace undotbs1 including contents and datafiles; 表空间已删除。 SQL> shutdown immediate; 数据库已经关闭。 已经卸载数据库。 ORACLE 例程已经关闭。 SQL> create spfile from pfile='d:/pfile.txt'; 文件已创建。 SQL> startup mount ORACLE 例程已经启动。 Total System Global Area 1.3696E+10 bytes Fixed Size 2188768 bytes Variable Size 6878661152 bytes Database Buffers 6777995264 bytes Redo Buffers 37044224 bytes 数据库装载完毕。 SQL> alter database open; 数据库已更改。
数据库启动之后继续报出来的ORA-600 4198和ORA-600 4137以及undo坏块均证明是由于undo异常引起的问题,通过重建新undo,数据库open正常,安排客户进行数据导出导入到新库
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有客户找到我们,由于操作不当把原本应该在备库上执行的drop table语句放到生产库上来执行了,导致生产库的表全部被干掉
 |
|