本文的truncate复原只针对于堆表(非lob)进行了测试，其实对于分区表和lob段的复原原理是一样的。

依据之前对truncate原理的剖析，truncate是不能通过闪回查问或者logminer的形式来复原的，因为truncate操作不会对数据块进行任何操作。那么truncate应该如何复原呢？上面列出几种常见的办法可供参考。

数据库闪回(要求flashback database开启，并且必要的闪回日志和归档日不能失落，因为闪回数据库不仅仅须要利用闪回日志，归档日志也是须要的。)

异机复原(要求有可用的备份以及必要的归档日志)

TSPITR(要求有可用的备份以及必要的归档日志）

其中数据库闪回和TSPITR对数据库影响较大。

如果数据库flashback database没有开启，并且无备份的状况下该如何复原呢？

本文介绍两种不常见的办法，重点介绍第二种修复元数据形式：

ODU(要求数据不被笼罩，如果数据被笼罩也能够最大水平的复原数据)

通过修复元数据来实现复原truncate(要求有truncate操作时的redo信息，并且数据不被笼罩，如果数据被笼罩也能够最大水平的复原数据)

01、ODU的形式

ODU是前Oracle ACED 熊军开发的一款业余而且弱小的Oracle复原工具，实用于所有场景下的复原，具体查看http://www.oracleodu.com/cn/，这里简略介绍一下ODU复原truncate的原理。

ODU复原truncate的原理是通过scan数据文件生成一个ext.odu的文件，该文件是依照表的dataobj#扫描出具体的extent信息，而后通过ext.odu能够导出须要复原的表的数据，最终再导入到数据库中。

02、修复元数据的形式

依据之前对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#的，具体测试过程如下：

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      17284SYS@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：

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)

REDO RECORD - Thread:1 RBA: 0x000055.0000001b.0080 LEN: 0x00ac VLD: 0x01SCN: 0x0000.003ed056 SUBSCN:  1 04/21/2018 12:25:34CHANGE #1 TYP:0 CLS:34 AFN:6 DBA:0x018002ee OBJ:4294967295 SCN:0x0000.003ed054 SEQ:1 OP:5.1 ENC:0 RBL:0ktudb 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: 5Undo type:  Regular undo       Undo type:  Last buffer split:  NoTablespace Undo:  Yes             0x00000000kteopu undo - undo operation on extent map       segdba: 0x140076a  class: 4  mapdba:0x140076a  offset: 2rbr extent - dba: 0x0  nbk: 0x0kteop redo - redo operation on extent map   CDOBJ: new object number:16840CHANGE #2 TYP:0 CLS:4 AFN:5 DBA:0x0140076a OBJ:16840 SCN:0x0000.003ed050 SEQ:2 OP:14.4 ENC:0 RBL:0kteop redo - redo operation on extent map   CDOBJ: new object number:16860

从redo dump信息能够看到段头块0x0140076a的dataobj#从16840变成了16860，所以这里须要将段头块的dataobj#改回16840。

BBED> set file 4 block 1898        FILE#           4        BLOCK#          1898 BBED> d offset 272 count 8File: /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 272Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) yFile: /u01/app/oracle/oradata/test/users01.dbf (4)Block: 1898             Offsets:  272 to  279           Dba:0x0100076a------------------------------------------------------------------------c8410000 00000010 <32 bytes per line> BBED> sum applyCheck value for File 4, Block 1898:current = 0xe90e, required = 0xe90e

仅仅批改段头块的dataobj#再次查问表会报ORA-08103: object no longer exists，起因是基表没有批改。查问的时候会通过表名去找到该表的dataobj#。

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中获取）

REDO RECORD - Thread:1 RBA: 0x000055.00000016.0148 LEN: 0x01b8 VLD: 0x01SCN: 0x0000.003ed050 SUBSCN:  5 04/21/2018 12:25:34CHANGE #1 TYP:0 CLS:34 AFN:6 DBA:0x018002ee OBJ:4294967295 SCN:0x0000.003ed050 SEQ:3 OP:5.1 ENC:0 RBL:0ktudb 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: 5Undo type:  Regular undo       Undo type:  Last buffer split:  NoTablespace Undo:  No             0x00000000Segment Header UndoSeghdr dba:  0x0140076a Mapblock dba:  0x00000000 Mapredo Offset: 4 scls: 4 mcls: 140733193388039Both the HWMsLow 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:  0x01405b80High 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:  0x01400769Lasts in HeaderLF: 20994945 LS: 20973417 LT: 0 FT: 0CHANGE #2 TYP:0 CLS:4 AFN:5 DBA:0x0140076a OBJ:16840 SCN:0x0000.003ed050 SEQ:1 OP:13.28 ENC:0 RBL:0Both the HWMsLow 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:  0x01400768High 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:  0x01400769Lasts in HeaderLF: 20973416 LS: 20973417 LT: 0 FT: 0CHANGE #3 TYP:0 CLS:8 AFN:5 DBA:0x01400768 OBJ:16840 SCN:0x0000.003ed050 SEQ:2 OP:13.22 ENC:0 RBL:0Redo on Level1 Bitmap BlockRedo to set hwmOpcode: 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改回去即可。

m /x 1b offset 48m /x 1b offset 92m /x 03 offset 52m /x 03 offset 96m /x 80 offset 56m /x 80 offset 100m /x 835b offset 60m /x 4001 offset 62m /x 835b offset 104m /x 4001 offset 106

3.复原段头块extent map，Auxillary Map以及extent个数(extent信息和aux map信息同样能够从redo dump中获取)

REDO RECORD - Thread:1 RBA: 0x000055.00000027.0180 LEN: 0x0138 VLD: 0x01SCN: 0x0000.003ed061 SUBSCN:  3 04/21/2018 12:25:35CHANGE #1 TYP:0 CLS:17 AFN:6 DBA:0x01800120 OBJ:4294967295 SCN:0x0000.003ed061 SEQ:1 OP:5.2 ENC:0 RBL:0ktudh redo: slt: 0x0014 sqn: 0x00000000 flg: 0x0002 siz: 112 fbi: 248            uba: 0x018006ea.018c.1f    pxid:  0x0000.000.00000000CHANGE #2 TYP:0 CLS:18 AFN:6 DBA:0x018006ea OBJ:4294967295 SCN:0x0000.003ed061 SEQ:1 OP:5.1 ENC:0 RBL:0ktudb 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: 5Undo type:  Regular undo       Undo type:  Last buffer split:  NoTablespace Undo:  Yes             0x00000000kteopu undo - undo operation on extent map       segdba: 0x140076a  class: 4  mapdba:0x140076a  offset: 3rbr extent - dba: 0x0  nbk: 0x0kteop 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:0CHANGE #3 TYP:0 CLS:4 AFN:5 DBA:0x0140076a OBJ:16860 SCN:0x0000.003ed061 SEQ:2 OP:14.4 ENC:0 RBL:0kteop 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。

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命令较长省略一部分。

批改exts信息：m /x 1c offset 36m /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)) datafrom 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) dfrom 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,blocksfrom aa) bbed批改后extent map复原胜利：BBED> d /v offset 280File: /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 2736File: /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重建表完全恢复。

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.

最初复原胜利。

对于作者

李翔宇，云和恩墨西区交付技术顾问，长期服务挪动运营商行业客户，相熟Oracle性能优化，故障诊断，非凡复原。
往年的数据技术嘉年华大会上，李翔宇老师将带来题为《在通过案例深刻解析Oracle外部原理》的演讲，与大家一起摸索CBO和ASM rebalance的一些外部机制，精彩不容错过！