本文的truncate恢复只针对于堆表(非lob)进行了测试，其实对于分区表和lob段的恢复原理是一样的。
根据之前对truncate原理的分析，truncate是不能通过闪回查询或者logminer的方式来恢复的，因为truncate操作不会对数据块进行任何操作。那么truncate应该如何恢复呢？下面列出几种常见的方法可供参考。
- 数据库闪回(要求flashback database开启，并且必要的闪回日志和归档日志不能丢失，因为闪回数据库不仅仅需要应用闪回日志，归档日志也是需要的)
- 异机恢复(要求有可用的备份以及必要的归档日志)
- TSPITR(要求有可用的备份以及必要的归档日志）
其中数据库闪回和TSPITR对数据库影响较大。假如数据库flashback database没有开启，并且无备份的情况下该如何恢复呢？本文介绍两种不常见的方法，重点介绍第二种修复元数据方式：
- odu(要求数据不被覆盖，如果数据被覆盖也可以最大程度的恢复数据)
- 通过修复元数据来实现恢复truncate(要求有truncate操作时的redo信息，并且数据不被覆盖，如果数据被覆盖也可以最大程度的恢复数据)
odu的方式：
odu是熊爷开发的一款专业而且强大的oracle恢复工具，适用于所有场景下的恢复，具体查看http://www.oracleodu.com/cn/，这里简单介绍一下odu恢复truncate的原理。odu恢复truncate的原理是通过scan数据文件生成一个ext.odu的文件，该文件是按照表的dataobj#扫描出具体的extent信息，然后通过ext.odu可以导出需要恢复的表的数据，最终再导入到数据库中。
修复元数据的方式：
根据之前对truncate原理的分析，truncate的实质是在不修改数据块的情况下，通过修改segment header的data_object_id,hwm,extent map，aux map等信息来实现清空表的目的，其中还涉及数据字典基表以及L1、L2位图块的修改，那么对于通过修改元数据的方式去恢复，首先需要确认哪些元数据块和数据字典是需要恢复的。通过10046的跟踪发现(可以去验证一下，需要flush shared pool和buffer cache)，全表扫描查询或者是通过rowid去查询一定会访问segment header，但是不会去访问任何L1、L2位图块的，访问的数据字典基表包括user$、obj$、tab$、tab_stats$、ts$、seg$、ind$、ind_stats$、col$、objauth$、cdef$、histgrm$、hist_head$,这里重点关注之前通过10046跟踪truncate操作有更改的基表obj$、tab$、seg$、tab_stats$(统计信息不用管)，其中seg$经过测试只要block#，file#，ts#不被更改就无需理会，而truncate操作是不会修改seg$的ts#、file#、block#的，具体测试过程如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
SYS@TEST(test):1>select obj#,dataobj# from obj$ where owner# in (select user# from user$ where name='TEST') and name='T1'; OBJ# DATAOBJ# ---------- ---------- 17284 17284 SYS@TEST(test):1>select TS#,FILE#,BLOCK# from tab$ where OBJ#=17284; TS# FILE# BLOCK# ---------- ---------- ---------- 9 4 290 SYS@TEST(test):1>update seg$ set blocks=1,extents=1,minexts=1,maxexts=1,extsize=1,bitmapranges=1,hwmincr=1,type#=1 where ts#=9 and file#=4 and block#=290; 1 row updated. SYS@TEST(test):1>commit; Commit complete. SYS@TEST(test):1>alter system flush shared_pool; System altered. SYS@TEST(test):1>select count(*) from test.t1; COUNT(*) ---------- 14164 SYS@TEST(test):1>delete from seg$ where ts#=9 and file#=4 and block#=290; 1 row deleted. SYS@TEST(test):1>commit; Commit complete. SYS@TEST(test):1>select count(*) from test.t1; select count(*) from test.t1 * ERROR at line 1: ORA-00600: internal error code, arguments: [ktsircinfo_num1], [9], [4], [290], [], [], [], [], [], [], [], [] |
所以需要恢复的元数据块和数据字典基表以及内容为：
- segment header(dataobj#、LHWM、HHWM、extent map、aux map以及extents个数)
- tab$(dataobj#)
- obj$(dataobj#)
下面提供segment header的信息对应的offset：
segment header dump：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 28 #blocks: 1664 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x01405b83 ext#: 27 blk#: 3 ext size: 128 #blocks in seg. hdr's freelists: 0 #blocks below: 1539 mapblk 0x00000000 offset: 27 Unlocked -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x01405b83 ext#: 27 blk#: 3 ext size: 128 #blocks in seg. hdr's freelists: 0 #blocks below: 1539 mapblk 0x00000000 offset: 27 Level 1 BMB for High HWM block: 0x01405b80 Level 1 BMB for Low HWM block: 0x01405b80 -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x01400769 Last Level 1 BMB: 0x01405b81 Last Level II BMB: 0x01400769 Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 28 obj#: 16840 flag: 0x10000000 Inc # 0 Extent Map ----------------------------------------------------------------- 0x01400768 length: 8 0x01402f48 length: 8 0x01402f50 length: 8 0x01402f58 length: 8 0x01402f60 length: 8 0x01402f68 length: 8 0x01402f70 length: 8 0x01402f78 length: 8 0x01402e80 length: 8 0x01402e88 length: 8 0x01402e90 length: 8 0x01402e98 length: 8 0x01402ea0 length: 8 0x01402ea8 length: 8 0x01402eb0 length: 8 0x01402eb8 length: 8 0x01403f80 length: 128 0x01404000 length: 128 0x01404080 length: 128 0x01404180 length: 128 0x01405800 length: 128 0x01405880 length: 128 0x01405900 length: 128 0x01405980 length: 128 0x01405a00 length: 128 0x01405a80 length: 128 0x01405b00 length: 128 0x01405b80 length: 128 Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x01400768 Data dba: 0x0140076b Extent 1 : L1 dba: 0x01400768 Data dba: 0x01402f48 Extent 2 : L1 dba: 0x01402f50 Data dba: 0x01402f51 Extent 3 : L1 dba: 0x01402f50 Data dba: 0x01402f58 Extent 4 : L1 dba: 0x01402f60 Data dba: 0x01402f61 Extent 5 : L1 dba: 0x01402f60 Data dba: 0x01402f68 Extent 6 : L1 dba: 0x01402f70 Data dba: 0x01402f71 Extent 7 : L1 dba: 0x01402f70 Data dba: 0x01402f78 Extent 8 : L1 dba: 0x01402e80 Data dba: 0x01402e81 Extent 9 : L1 dba: 0x01402e80 Data dba: 0x01402e88 Extent 10 : L1 dba: 0x01402e90 Data dba: 0x01402e91 Extent 11 : L1 dba: 0x01402e90 Data dba: 0x01402e98 Extent 12 : L1 dba: 0x01402ea0 Data dba: 0x01402ea1 Extent 13 : L1 dba: 0x01402ea0 Data dba: 0x01402ea8 Extent 14 : L1 dba: 0x01402eb0 Data dba: 0x01402eb1 Extent 15 : L1 dba: 0x01402eb0 Data dba: 0x01402eb8 Extent 16 : L1 dba: 0x01403f80 Data dba: 0x01403f82 Extent 17 : L1 dba: 0x01404000 Data dba: 0x01404002 Extent 18 : L1 dba: 0x01404080 Data dba: 0x01404082 Extent 19 : L1 dba: 0x01404180 Data dba: 0x01404182 Extent 20 : L1 dba: 0x01405800 Data dba: 0x01405802 Extent 21 : L1 dba: 0x01405880 Data dba: 0x01405882 Extent 22 : L1 dba: 0x01405900 Data dba: 0x01405902 Extent 23 : L1 dba: 0x01405980 Data dba: 0x01405982 Extent 24 : L1 dba: 0x01405a00 Data dba: 0x01405a02 Extent 25 : L1 dba: 0x01405a80 Data dba: 0x01405a82 Extent 26 : L1 dba: 0x01405b00 Data dba: 0x01405b02 Extent 27 : L1 dba: 0x01405b80 Data dba: 0x01405b82 -------------------------------------------------------- Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x01400769 |
下面是我认为比较重要的segment header每个offset对应的含义：
offset desc
36 total extents
40 total blocks
48 HWM所在的ext#
52 HWM所在的ext#的第几个block(从0开始)
56 HWM所在的ext#的ext blocks
60 HWM所在的dba地址
76 HWM下有多少个block
92 LHWM所在的ext#
96 LHWM所在的ext#的第几个block(从0开始)
100 LHWM所在的ext#的ext size
104 LHWM所在的dba地址
120 LHWM下有多少个block
124 Level 1 BMB for High HWM block
128 Level 1 BMB for Low HWM block
213 block size
220 L2 Array start offset
224 First Level 3 BMB
228 L2 Hint for inserts
236 Last Level 1 BMB
240 Last Level II BMB
244 Last Level III BMB
264 Map Header的extents
272 Map Header的obj#
276 Map Header的flag
280 ext#为0的block_id
284 ext#为0的extent blocks
288 ext#为1的block_id
292 ext#为1的extent blocks
……以此类推循环
2736 aux map信息，ext#为0的L1 dba
2740 aux map信息，ext#为0的data dba
2744 aux map信息，ext#为1的L1 dba
2748 aux map信息，ext#为1的data dba
……以此类推循环
5192 Second Level Bitmap block DBAs
确认了需要恢复的内容之后，还需要确认是否有对象占用了truncate释放的空间，依据是用从redo dump找到的truncate前的extent map和dba_extents对比。如果有对象占用需要先move对象到其他表空间。如何从redo dump找到extent map见后面段头块的extent map恢复。
无覆盖的TRUNCATE恢复
重要：假如确实失误truncate了表，需要马上停应用，最好将表空间设置为offline或者read only，避免数据被覆盖
通过之前对table full scan、segment header和前面truncate原理的分析，tfs不会读取L1,L2块，所以恢复的时候L1,L2块和具体存放数据的块都不用管，只需尝试通过修改段头块和基表信息来恢复truncate的数据，bbed修改段头块的具体offset含义见segment header章节。
1.修改段头块dataobj#(由于表可能不止一次被truncate，所以获取之前dataobj#最好的办法是通过logminer或者redo dump，这里我使用的redo dump)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
REDO RECORD - Thread:1 RBA: 0x000055.0000001b.0080 LEN: 0x00ac VLD: 0x01 SCN: 0x0000.003ed056 SUBSCN: 1 04/21/2018 12:25:34 CHANGE #1 TYP:0 CLS:34 AFN:6 DBA:0x018002ee OBJ:4294967295 SCN:0x0000.003ed054 SEQ:1 OP:5.1 ENC:0 RBL:0 ktudb redo: siz: 64 spc: 822 flg: 0x0022 seq: 0x017b rec: 0x33 xid: 0x0009.00f.00000353 ktubu redo: slt: 15 rci: 50 opc: 14.5 objn: 1 objd: 16840 tsn: 5 Undo type: Regular undo Undo type: Last buffer split: No Tablespace Undo: Yes 0x00000000 kteopu undo - undo operation on extent map segdba: 0x140076a class: 4 mapdba:0x140076a offset: 2 rbr extent - dba: 0x0 nbk: 0x0 kteop redo - redo operation on extent map CDOBJ: new object number:16840 CHANGE #2 TYP:0 CLS:4 AFN:5 DBA:0x0140076a OBJ:16840 SCN:0x0000.003ed050 SEQ:2 OP:14.4 ENC:0 RBL:0 kteop redo - redo operation on extent map CDOBJ: new object number:16860 |
从redo dump信息可以看到段头块0x0140076a的dataobj#从16840变成了16860，所以这里需要将段头块的dataobj#改回16840
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
BBED> set file 4 block 1898 FILE# 4 BLOCK# 1898 BBED> d offset 272 count 8 File: /u01/app/oracle/oradata/test/users01.dbf (4) Block: 1898 Offsets: 272 to 279 Dba:0x0100076a ------------------------------------------------------------------------ dc410000 00000010 <32 bytes per line> BBED> m /x c841 offset 272 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /u01/app/oracle/oradata/test/users01.dbf (4) Block: 1898 Offsets: 272 to 279 Dba:0x0100076a ------------------------------------------------------------------------ c8410000 00000010 <32 bytes per line> BBED> sum apply Check value for File 4, Block 1898: current = 0xe90e, required = 0xe90e |
仅仅修改段头块的dataobj#再次查询表会报ORA-08103: object no longer exists,原因是基表没有修改。查询的时候会通过表名去找到该表的dataobj#
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
SYS@TEST(test):1>select count(*) from test.truncate_table; select count(*) from test.truncate_table * ERROR at line 1: ORA-08103: object no longer exists SYS@TEST(test):1>UPDATE OBJ$ SET DATAOBJ#=16840 WHERE OBJ#=16840; 1 row updated. SYS@TEST(test):1>UPDATE TAB$ SET DATAOBJ#=16840 WHERE OBJ#=16840; 1 row updated. SYS@TEST(test):1>COMMIT; Commit complete. SYS@TEST(test):1>alter system flush buffer_cache; System altered. SYS@TEST(test):1>alter system flush shared_pool; System altered. SYS@TEST(test):1>select count(*) from test.truncate_table; COUNT(*) ---------- 0 |
2.恢复段头块HWM（HWM信息可以从redo dump中获取）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
REDO RECORD - Thread:1 RBA: 0x000055.00000016.0148 LEN: 0x01b8 VLD: 0x01 SCN: 0x0000.003ed050 SUBSCN: 5 04/21/2018 12:25:34 CHANGE #1 TYP:0 CLS:34 AFN:6 DBA:0x018002ee OBJ:4294967295 SCN:0x0000.003ed050 SEQ:3 OP:5.1 ENC:0 RBL:0 ktudb redo: siz: 164 spc: 1112 flg: 0x0022 seq: 0x017b rec: 0x30 xid: 0x0009.00f.00000353 ktubu redo: slt: 15 rci: 47 opc: 13.29 objn: 16840 objd: 16840 tsn: 5 Undo type: Regular undo Undo type: Last buffer split: No Tablespace Undo: No 0x00000000 Segment Header Undo Seghdr dba: 0x0140076a Mapblock dba: 0x00000000 Mapredo Offset: 4 scls: 4 mcls: 140733193388039 Both the HWMs Low HWM Highwater:: 0x01405b83 ext#: 27 blk#: 3 ext size: 128 #blocks in seg. hdr's freelists: 0 #blocks below: 1539 mapblk 0x00000000 offset: 27 lfdba: 0x01405b80 High HWM Highwater:: 0x01405b83 ext#: 27 blk#: 3 ext size: 128 #blocks in seg. hdr's freelists: 0 #blocks below: 1539 mapblk 0x00000000 offset: 27 lfdba: 0x01405b80 hint dba: 0x01400769 Lasts in Header LF: 20994945 LS: 20973417 LT: 0 FT: 0 CHANGE #2 TYP:0 CLS:4 AFN:5 DBA:0x0140076a OBJ:16840 SCN:0x0000.003ed050 SEQ:1 OP:13.28 ENC:0 RBL:0 Both the HWMs Low HWM Highwater:: 0x0140076b ext#: 0 blk#: 3 ext size: 8 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 lfdba: 0x01400768 High HWM Highwater:: 0x0140076b ext#: 0 blk#: 3 ext size: 8 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 lfdba: 0x01400768 hint dba: 0x01400769 Lasts in Header LF: 20973416 LS: 20973417 LT: 0 FT: 0 CHANGE #3 TYP:0 CLS:8 AFN:5 DBA:0x01400768 OBJ:16840 SCN:0x0000.003ed050 SEQ:2 OP:13.22 ENC:0 RBL:0 Redo on Level1 Bitmap Block Redo to set hwm Opcode: 32 Highwater:: 0x0140076b ext#: 0 blk#: 3 ext size: 8 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 |
可以看到段头块0x0140076a的LHWM信息和HHWM是一样的，都是从
Highwater:: 0x01405b83 ext#: 27 blk#: 3 ext size: 128
变成了
Highwater:: 0x0140076b ext#: 0 blk#: 3 ext size: 8
所以这里恢复HWM信息只需要根据redo dump改回去即可
|
1 2 3 4 5 6 7 8 9 10 |
m /x 1b offset 48 m /x 1b offset 92 m /x 03 offset 52 m /x 03 offset 96 m /x 80 offset 56 m /x 80 offset 100 m /x 835b offset 60 m /x 4001 offset 62 m /x 835b offset 104 m /x 4001 offset 106 |
3.恢复段头块extent map，Auxillary Map以及extent个数(extent信息和aux map信息同样可以从redo dump中获取)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
REDO RECORD - Thread:1 RBA: 0x000055.00000027.0180 LEN: 0x0138 VLD: 0x01 SCN: 0x0000.003ed061 SUBSCN: 3 04/21/2018 12:25:35 CHANGE #1 TYP:0 CLS:17 AFN:6 DBA:0x01800120 OBJ:4294967295 SCN:0x0000.003ed061 SEQ:1 OP:5.2 ENC:0 RBL:0 ktudh redo: slt: 0x0014 sqn: 0x00000000 flg: 0x0002 siz: 112 fbi: 248 uba: 0x018006ea.018c.1f pxid: 0x0000.000.00000000 CHANGE #2 TYP:0 CLS:18 AFN:6 DBA:0x018006ea OBJ:4294967295 SCN:0x0000.003ed061 SEQ:1 OP:5.1 ENC:0 RBL:0 ktudb redo: siz: 112 spc: 3358 flg: 0x0022 seq: 0x018c rec: 0x1f xid: 0x0001.014.00000297 ktubu redo: slt: 20 rci: 0 opc: 14.5 objn: 1 objd: 16860 tsn: 5 Undo type: Regular undo Undo type: Last buffer split: No Tablespace Undo: Yes 0x00000000 kteopu undo - undo operation on extent map segdba: 0x140076a class: 4 mapdba:0x140076a offset: 3 rbr extent - dba: 0x0 nbk: 0x0 kteop redo - redo operation on extent map ADD: dba:0x1405b80 len:128 at offset:27 --truncate前的extent信息 ADDAXT: offset:27 fdba:x01405b80 bdba:0x01405b82 --truncate前的aux map信息 SETSTAT: exts:28 blks:1664 lastmap:0x0 mapcnt:0 CHANGE #3 TYP:0 CLS:4 AFN:5 DBA:0x0140076a OBJ:16860 SCN:0x0000.003ed061 SEQ:2 OP:14.4 ENC:0 RBL:0 kteop redo - redo operation on extent map DELETE: entry:27 shift back: dba:0x0 len:0 SETSTAT: exts:27 blks:1536 lastmap:0x0 mapcnt:0 |
从redo dump可以发现truncate操作对于extent map和aux map是从最后一个extent开始逐一删除的，这里可以看到该表的extent总共有28个，ext#为27是该表最后一个extent，block_id为0x1405b80，该extent size为128个块，以此类推很容易可以通过简单的grep找出extent map；同理ext#为27的aux map的L1 dba为x01405b80，data dba为0x01405b82，以此类推很容易可以通过简单的grep找出aux map
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
extent map: [root@prim1-11g ~]# grep -i "ADD: dba" /u01/app/oracle/diag/rdbms/test/test/trace/test_ora_7611.trc ADD: dba:0x1405b80 len:128 at offset:27 ADD: dba:0x1405b00 len:128 at offset:26 ADD: dba:0x1405a80 len:128 at offset:25 ADD: dba:0x1405a00 len:128 at offset:24 ADD: dba:0x1405980 len:128 at offset:23 ADD: dba:0x1405900 len:128 at offset:22 ADD: dba:0x1405880 len:128 at offset:21 ADD: dba:0x1405800 len:128 at offset:20 ADD: dba:0x1404180 len:128 at offset:19 ADD: dba:0x1404080 len:128 at offset:18 ADD: dba:0x1404000 len:128 at offset:17 ADD: dba:0x1403f80 len:128 at offset:16 ADD: dba:0x1402eb8 len:8 at offset:15 ADD: dba:0x1402eb0 len:8 at offset:14 ADD: dba:0x1402ea8 len:8 at offset:13 ADD: dba:0x1402ea0 len:8 at offset:12 ADD: dba:0x1402e98 len:8 at offset:11 ADD: dba:0x1402e90 len:8 at offset:10 ADD: dba:0x1402e88 len:8 at offset:9 ADD: dba:0x1402e80 len:8 at offset:8 ADD: dba:0x1402f78 len:8 at offset:7 ADD: dba:0x1402f70 len:8 at offset:6 ADD: dba:0x1402f68 len:8 at offset:5 ADD: dba:0x1402f60 len:8 at offset:4 ADD: dba:0x1402f58 len:8 at offset:3 ADD: dba:0x1402f50 len:8 at offset:2 ADD: dba:0x1402f48 len:8 at offset:1 aux map: [root@prim1-11g ~]# grep -i "ADDAXT:" /u01/app/oracle/diag/rdbms/test/test/trace/test_ora_7611.trc ADDAXT: offset:27 fdba:x01405b80 bdba:0x01405b82 ADDAXT: offset:26 fdba:x01405b00 bdba:0x01405b02 ADDAXT: offset:25 fdba:x01405a80 bdba:0x01405a82 ADDAXT: offset:24 fdba:x01405a00 bdba:0x01405a02 ADDAXT: offset:23 fdba:x01405980 bdba:0x01405982 ADDAXT: offset:22 fdba:x01405900 bdba:0x01405902 ADDAXT: offset:21 fdba:x01405880 bdba:0x01405882 ADDAXT: offset:20 fdba:x01405800 bdba:0x01405802 ADDAXT: offset:19 fdba:x01404180 bdba:0x01404182 ADDAXT: offset:18 fdba:x01404080 bdba:0x01404082 ADDAXT: offset:17 fdba:x01404000 bdba:0x01404002 ADDAXT: offset:16 fdba:x01403f80 bdba:0x01403f82 ADDAXT: offset:15 fdba:x01402eb0 bdba:0x01402eb8 ADDAXT: offset:14 fdba:x01402eb0 bdba:0x01402eb1 ADDAXT: offset:13 fdba:x01402ea0 bdba:0x01402ea8 ADDAXT: offset:12 fdba:x01402ea0 bdba:0x01402ea1 ADDAXT: offset:11 fdba:x01402e90 bdba:0x01402e98 ADDAXT: offset:10 fdba:x01402e90 bdba:0x01402e91 ADDAXT: offset:9 fdba:x01402e80 bdba:0x01402e88 ADDAXT: offset:8 fdba:x01402e80 bdba:0x01402e81 ADDAXT: offset:7 fdba:x01402f70 bdba:0x01402f78 ADDAXT: offset:6 fdba:x01402f70 bdba:0x01402f71 ADDAXT: offset:5 fdba:x01402f60 bdba:0x01402f68 ADDAXT: offset:4 fdba:x01402f60 bdba:0x01402f61 ADDAXT: offset:3 fdba:x01402f50 bdba:0x01402f58 ADDAXT: offset:2 fdba:x01402f50 bdba:0x01402f51 ADDAXT: offset:1 fdba:x01400768 bdba:0x01402f48 |
这里可以写脚本根据segment header章节offset的含义来生成bbed命令，由于bbed命令较长省略一部分
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 |
修改exts信息： m /x 1c offset 36 m /x 1c offset 264 将grep出的数据作为一整列全部导入表中用sql生成bbed命令 如创建表：create table aux_map(a varchar2(2000);create table ext_map(a varchar2(2000);插入数据后就可以用下列sql生成命令 生成aux map的bbed命令 with aa as ( select replace(regexp_substr(a,'[^:]+',1,3),'fdba','') ext#, (replace(regexp_substr(a,'[^:]+',1,3),'fdba','')-1)*8+2744 offset, trim(replace(regexp_substr(a,'[^:]+',1,4),'bdba','')) l1, trim(regexp_substr(a,'[^:]+',1,5)) data from aux_map) select 'm /x '||a||' offset '||offset||chr(10)||'m /x '||b||' offset '||to_char(offset+2)||chr(10)||'m /x '||c||' offset '||to_char(offset+4)||chr(10)||'m /x '||d||' offset '||to_char(offset+6) from (select ext#,to_number(offset) offset,substr(l1,-2,2)||substr(l1,-4,2) a,substr(l1,-6,2)||substr(l1,-8,2) b,substr(data,-2,2)||substr(data,-4,2) c,substr(data,-6,2)||substr(data,-8,2) d from aa) 生成ext map的bbed命令 with aa as ( select trim(replace(replace(regexp_substr(a,'[^:]+',1,3),'len',''),'x','x0')) block_id, (regexp_substr(a,'[^:]+',1,5)-1)*8+288 offset, lpad(trim((to_char((replace(regexp_substr(a,'[^:]+',1,4),'at offset','')),'xxxx'))),2,0) blocks, regexp_substr(a,'[^:]+',1,5) ext# from ext_map) select 'm /x '||a||' offset '||offset||chr(10)||'m /x '||b||' offset '||to_char(offset+2)||chr(10)||'m /x '||blocks||' offset '||to_char(offset+4) from (select ext#,to_number(offset) offset,substr(block_id,-2,2)||substr(block_id,-4,2) a,substr(block_id,-6,2)||substr(block_id,-8,2) b,blocks from aa) bbed修改后extent map恢复成功： BBED> d /v offset 280 File: /u01/app/oracle/oradata/test/users01.dbf (4) Block: 1898 Offsets: 280 to 579 Dba:0x0100076a ------------------------------------------------------- 68074001 08000000 482f4001 08000000 l h.@.....H/@..... 502f4001 08000000 582f4001 08000000 l P/@.....X/@..... 602f4001 08000000 682f4001 08000000 l `/@.....h/@..... 702f4001 08000000 782f4001 08000000 l p/@.....x/@..... 802e4001 08000000 882e4001 08000000 l ..@.......@..... 902e4001 08000000 982e4001 08000000 l ..@.......@..... a02e4001 08000000 a82e4001 08000000 l ..@.......@..... b02e4001 08000000 b82e4001 08000000 l ..@.......@..... 803f4001 80000000 00404001 80000000 l .?@......@@..... 80404001 80000000 80414001 80000000 l .@@......A@..... 00584001 80000000 80584001 80000000 l .X@......X@..... 00594001 80000000 80594001 80000000 l .Y@......Y@..... 005a4001 80000000 805a4001 80000000 l .Z@......Z@..... 005b4001 80000000 805b4001 80000000 l .[@......[@..... 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 l ............ <16 bytes per line> aux map恢复成功： BBED> d /v offset 2736 File: /u01/app/oracle/oradata/test/users01.dbf (4) Block: 1898 Offsets: 2736 to 3035 Dba:0x0100076a ------------------------------------------------------- 68074001 6b074001 68074001 482f4001 l h.@.k.@.h.@.H/@. 502f4001 512f4001 502f4001 582f4001 l P/@.Q/@.P/@.X/@. 602f4001 612f4001 602f4001 682f4001 l `/@.a/@.`/@.h/@. 702f4001 712f4001 702f4001 782f4001 l p/@.q/@.p/@.x/@. 802e4001 812e4001 802e4001 882e4001 l ..@...@...@...@. 902e4001 912e4001 902e4001 982e4001 l ..@...@...@...@. a02e4001 a12e4001 a02e4001 a82e4001 l ..@...@...@...@. b02e4001 b12e4001 b02e4001 b82e4001 l ..@...@...@...@. 803f4001 823f4001 00404001 02404001 l .?@..?@..@@..@@. 80404001 82404001 80414001 82414001 l .@@..@@..A@..A@. 00584001 02584001 80584001 82584001 l .X@..X@..X@..X@. 00594001 02594001 80594001 82594001 l .Y@..Y@..Y@..Y@. 005a4001 025a4001 805a4001 825a4001 l .Z@..Z@..Z@..Z@. 005b4001 025b4001 805b4001 825b4001 l .[@..[@..[@..[@. 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 l ............ <16 bytes per line> |
最后恢复成功，这里由于L1,L2没有恢复，所以insert会有问题，但是可以通过CTAS重建表完全恢复
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SYS@TEST(test):1>select count(*) from test.truncate_table; COUNT(*) ---------- 113426 SYS@TEST(test):1>insert into test.truncate_table select * from dba_objects; insert into test.truncate_table select * from dba_objects * ERROR at line 1: ORA-00600: internal error code, arguments: [ktspgfblk3:kcbz_objdchk], [0], [0], [1], [], [], [], [], [], [], [], [] SYS@TEST(test):1>CREATE TABLE TEST.RECOVER_TABLE AS SELECT * FROM TEST.TRUNCATE_TABLE; Table created. SYS@TEST(test):1>INSERT INTO TEST.RECOVER_TABLE select * from dba_objects WHERE ROWNUM=1; 1 row created. SYS@TEST(test):1>COMMIT; Commit complete. |
最后恢复成功。