0

深入解析:TRUNCATE TABLE 的内部原理解析与恢复思路

李翔宇 2018-08-01
185

关注我们获得更多精彩


作者 | 李翔宇,云和恩墨西区交付技术顾问,长期服务移动运营商行业客户

精通 oracle 性能优化,故障诊断,特殊恢复领域。


摘要

众所周知,truncate table 是一种快速清空表内数据的一种方式,与 delete 方式不同,truncate 只产生非常少的 redo 和 undo,就实现了清空表数据并降低表  HWM 的功能。本文主要围绕 truncate table 的实现原理和 truncate table 的恢复来展开。


 TRUNCATE 原理

环境准备


构造测试环境,通过 10046 以及 redo dump 去分析 truncate 的整个操作过程。

1) 10046 用于观察 truncate 对于字典基表的操作;

2) redo dump 用于观察 truncate 对于 segment header 以及 L1、L2 位图块的操作。


基于 ASSM 测试环境如下:

OS: redhat 6.5

db:11.2.0.4

segment&extent info:

SYS@:>select owner,segment_name,header_file,header_block from dba_segments where segment_name='TRUNCATE_TABLE' and owner='TEST';

OWNER                SEGMENT_NAME         HEADER_FILE     HEADER_BLOCK

-------------------- -------------------- ----------- ------------- ----------- ------------- ----------- ------

TEST                    TRUNCATE_TABLE                 5                              1898

 

SYS@:>select extent_id,file_id,block_id,blocks from dba_extents where segment_name='TRUNCATE_TABLE' and owner='TEST' order by 1;

EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS

---------- ---------- ---------- ----------

0          5       1896          8

1          5      12104          8

2          5      12112          8

3          5      12120          8

4          5      12128          8

5          5      12136          8

6          5      12144          8

7          5      12152          8

8          5      11904          8

9          5      11912          8

10          5      11920          8

11          5      11928          8

12          5      11936          8

13          5      11944          8

14          5      11952          8

15          5      11960          8

16          5      16256        128

17          5      16384        128

18          5      16512        128

19          5      16768        128

20          5      22528        128

21          5      22656        128

22          5      22784        128

23          5      22912        128

24          5      23040        128

25          5      23168        128

26          5      23296        128

27          5      23424        128


truncate 对数据字典基表的操作


SYS@TEST(test):1>select count(*) from test.truncate_table;

COUNT(*)

----------

113426

SYS@:>alter system flush SHARED_POOL;

System altered.

SYS@:>alter system flush BUFFER_CACHE;

System altered.

SYS@:>alter system switch logfile;

System altered.

SYS@:>select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME         NEXT_CHANGE# NEXT_TIME

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------

1          1         85   52428800        512          1 NO  CURRENT                4116465 21-APR-18            2.8147E+14

2          1         83   52428800        512          1 NO  INACTIVE               4092314 20-APR-18               4116301 21-APR-18

3          1         84   52428800        512          1 NO  INACTIVE               4116301 21-APR-18               4116465 21-APR-18

SYS@:>oradebug setmypid;

Statement processed.

SYS@:>oradebug tracefile_name

/u01/app/oracle/diag/rdbms/test/test/trace/test_ora_7091.trc

SYS@:>oradebug event 10046 trace name context forever,level 12;

Statement processed.

SYS@:>truncate table test.truncate_table;

Table truncated.

SYS@:>oradebug event 10046 trace name context off;

Statement processed.

SYS@TEST(test):1>alter system dump logfile '/u01/app/oracle/oradata/test/redo01.log';

System altered.

 

从 10046 trace 里搜出对基表的 dml 操作:

update:

[root@prim1-11g ~]# grep -i "^update"  /u01/app/oracle/diag/rdbms/test/test/trace/test_ora_7091.trc

update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14 where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null

update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, deletes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags + :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where obj# = :objn

update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9),audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15,rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21,analyzetime=:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1,null,:26),instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29,flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34,spare6=:35 where obj#=:1

update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3

delete:

[root@prim1-11g ~]# grep -i "^delete"  /u01/app/oracle/diag/rdbms/test/test/trace/test_ora_7091.trc

delete from superobj$ where subobj# = :1

delete from tab_stats$ where obj#=:1

 

通过上述跟踪,可以看到对基表的修改主要是:

1) 修改 obj$,tab$ 的 dataobj#

2) 修改 seg$ 的对应信息如(extents,blocks,hwmincr等等)

3) 删除 tab_stats$ 对应对象的统计信息


truncate 对元数据块的操作


对于 segment header 以及 L1、L2 位图块的操作,只能通过 redo dump 去观察。

为什么不使用 logminer 进行分析?


因为在 logminer 中只会记录数据块的变更,而对于 segment header 和 L1、L2 位图块的操作在 logminer 里只记录操作类型为 internal 或者 unsupported,没有什么有价值的信息。

 

通过对 redo dump 的分析,发现 truncate 操作只对 segment header,L2位图块,第一个 L1 位图块和 HWM block 所属的 L1 位图块进行了修改。


对于 segment header 的修改内容:

1) 修改块的 dataobj#

2) 修改 LHWM 和 HHWM

3) 修改 extent map、aux map 以及 extents 个数

 

对于 L2 位图块的修改内容:

1) 删除 L1 ranges

2) 修改L2块的dataobj#

 

对于第一个 L1 位图块的修改内容:

1) 修改第一个 L1 块的 dataobj#

2) set hwm 为 ext# 为 0 的第 3+1 个块(即段头块+1)

 

对于 HWM block 所属 L1 位图块的修改内容:

1) clear HWM flag

 

原理总结


truncate的实质是在不修改数据块的情况下,通过修改segment header的data_object_id,hwm,逐条清空 extent map,aux map 来实现清空表的目的,其中还涉及数据字典基表以及 L1、L2 位图块的修改,因此也可以说明 truncate 操作只是存储数据的数据块没有产生任何 redo 和 undo,但是 segment header,位图块,数据字典基表还是会产生 redo 和 undo。


TRUNCATE的恢复


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


根据之前对 truncate  原理的分析,truncate是不能通过闪回查询或者 logminer 的方式来恢复的,因为 truncate 操作不会对数据块进行任何操作,那么 truncate 应该如何恢复呢?下面列出几种常见的方法可供参考。

  • 数据库闪回(要求 flashback database 开启,并且必要的闪回日志和归档日志不能丢失,因为闪回数据库不仅仅需要应用闪回日志,归档日志也是需要的)

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

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

  • 特殊恢复工具恢复,如:odu(要求数据不被覆盖,如果数据被覆盖也可以最大程度的恢复数据)

  • 通过修复元数据来实现恢复 truncate(要求有 truncate 操作时的 redo 信息,并且数据不被覆盖,如果数据被覆盖也可以最大程度的恢复数据)

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


最后两种方式可以在数据库 flashback database 没有开启,并且无备份的情况下进行恢复,但是这两种方式的技术难度大且容易制造出更大的麻烦,强烈建议不要轻易地在生产环境中进行尝试,最好还是请专业人士进行恢复(小编强行植入:云和恩墨 24h 等待各位召唤师召唤,如有紧急 case 直接在微信任意一篇文章留言会立即帮忙联系公司专家对接,嘎嘎),本文只是为了演示最后这两种不常见的方法,重点介绍第二种修复元数据方式。


odu 的方式


odu 是 oracle 技术大咖 老熊(熊爷)开发的一款专业而且强大的 oracle 恢复工具,适用于所有场景下的恢复,具体功能介绍可以查看产品BLOG  http://www.oracleodu.com/cn/


odu 恢复 truncate 的原理是通过 scan 数据文件生成一份名为 ext.odu 的文件,该文件按照表的 dataobj# 扫描出具体的 extent 信息,然后通过 ext.odu 可以导出需要恢复的表的数据,最终再导入到数据库中,具体情况可以参看“利用 ODU 在 ASM 中恢复被 Truncate 掉的表的实例”(http://www.oracleodu.com/cn/recovery-truncated-table-in-asm-using-odu.html)


修复元数据的方式


重点介绍下通过修复元数据的方式来恢复 truncate table 的操作。


根据之前对 truncate 原理的分析,truncate 的实质是在不修改数据块的情况下,通过修改 segment header 的 data_object_id,hwm,逐条清空 extent map,aux map 等信息来实现清空表的目的,其中还涉及数据字典基表以及 L1、L2 位图块的修改,那么对于通过修改元数据的方式去恢复,大致思路如下:

  • 确认需要恢复的数据字典基表和元数据块以及内容

  • 确认truncate释放的空间是否被覆盖

  • 根据前两点的分析开始进行恢复

 

第一个问题:如何确认哪些元数据块和数据字典是需要恢复的?

通过 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#(其实就是 segment header 地址)不被更改就无需理会,且 truncate 操作并不会修改 seg$ 的 ts#、file#、block#,因为 segment header 地址不会改变,验证过程如下:

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

--修改 seg$ 的 blocks,extents,extsize,hwmincr 等信息

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.

--并不影响 t1 的查询

SYS@TEST(test):1>select count(*) from test.t1;

COUNT(*)

----------

14164

--删除或修改 ts#,file#,block#

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

extents

272

obj#  

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 恢复。


具体恢复验证,请期待下一操作文章。


作者:李翔宇

投稿:有投稿意向技术人请在微信对话框留言。

转载:意向文章下方留言。

更多精彩请关注 “数据和云” 微信


资源下载

关注微信:数据和云(OraNews)回复关键字获取

2018DTCC , 数据库大会PPT

2017DTC,2017 DTC 大会 PPT

DBALIFE ,“DBA 的一天”海报

DBA04 ,DBA 手记4 电子书

122ARCH ,Oracle 12.2体系结构图

2017OOW ,Oracle OpenWorld 资料

PRELECTION ,大讲堂讲师课程资料

近期文章

仅仅使用AWR做报告? 性能优化还未入门

实战课堂:一则CPU 100%的故障分析

杨廷琨:如何编写高效SQL(含PPT)

一份高达555页的技术PPT会是什么样子?

大象起舞:用PostgreSQL解海盗分金问题

ProxySQL!像C罗一样的强大

高手过招:用SQL解决环环相扣刑侦推理问题

最后修改时间:2020-05-07 23:50:02
「喜欢文章,快来给作者赞赏墨值吧」
文章转载自李翔宇,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

数据库资讯
最新 热门 更多
本月热门
近期活动
全部
暂无活动,敬请期待...
相关课程
全部