[20170419]bbed探究数据块.txt
--//bbed 是一个瑞士军刀,也是了解oracle内部数据块结构的好工具。我自己开始使用基本是看别人的帖子,对oracle数据块的内部也不是很了解。
--//使用多了,也就了解大概,里面的结构体名字实在太难记。自己通过1个数据块加深理解。
--//注解部分参考链接:http://www.dbaqhs.com/archives/1680
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table deptx as select * from dept ;
Table created.
SCOTT@book> @ &r/qq deptx 1
ROWID OBJECT FILE BLOCK ROW ROWID_DBA DEPTNO DNAME LOC
------------------ ---------- ---------- ---------- ---------- -------------------- ---------- -------------- -------------
AAAWE5AAEAAAAIjAAA 90425 4 547 0 0x1000223 10 ACCOUNTING NEW YORK
SCOTT@book> @ &r/rowid AAAWE5AAEAAAAIjAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
90425 4 547 0 0x1000223 4,547 alter system dump datafile 4 block 547 ;
SCOTT@book> alter system checkpoint ;
System altered.
2.使用bbed探查dba = 4,547.
BBED> map dba 4,547
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 547 Dba:0x01000223
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0 -- cache layer 占20 bytes
struct ktbbh, 96 bytes @20 -- transaction layer (前24 bytes固定,事务信息)
struct kdbh, 14 bytes @124 -- data header layer
struct kdbt[1], 4 bytes @138 -- table directory layer
sb2 kdbr[4] @142 -- row directory layer
ub1 freespace[7946] @150 -- free space layer
ub1 rowdata[92] @8096 -- row data layer
ub4 tailchk @8188 -- tailchk layer = {(bas_kcbh(低2字节))+(type_kcbh)+(seq_kcbh)}
--//oracle 数据块是分层的cache layer,transaction layer,data header layer,table directory layer,row directory layer,free
--//space layer,row data layer,tailchk layer.
3.cache layer:
BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06 -- header block type
ub1 frmt_kcbh @1 0xa2 -- v7,v8
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x01000223 -- RDBA (relative data block address)
ub4 bas_kcbh @8 0x4279f41e -- scn base
ub2 wrp_kcbh @12 0x0003 -- scn wrap
ub1 seq_kcbh @14 0x02 -- incremental for every change made to the block at the same SCN.
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0x1d3c -- check sum
ub2 spare3_kcbh @18 0x0000 -- 如果做rman backup, 备份文件这里记录的是1.参考
-- //http://blog.itpub.net/267265/viewspace-2128392/
--//对于kcbh.seq_kcbh如何增加,我一直没有什么好方法。也许拿索引来测可以测试出来。
--//其中:header block type 不同类型如下:
01 undo segment header
02 undo data block
03 save undo header
04 save undo data block
05 data segment header (temp,index,data and so on )
06 KTB managed data block (with ITL)
07 temp table data block (no ITL)
08 sort key
09 sort run
10 segment free list block
11 data file header
--//注上面仅仅是其中一部分,使用10进制。其他还有:
Decimal Hex Type
1 0x01 undo segment header
11 0x0b data file header
12 0x0c data segment header with FLG blocks
14 0x0e unlimited undo segment header
15 0x0f unlimited save undo segment header
16 0x10 unlimited data segment header
17 0x11 unlimited data segment header with FLG blocks
18 0x12 extent map block
23 0x17 bitmapped segment header
29 0x1d bitmapped file space header
32 0x20 first level bitmap block
33 0x21 second level bitmap block
34 0x22 third level bitmap block
35 0x23 Pagetable segment header block
36 0x24 Pagetable extent map block
37 0x25 System Managed Undo Extent Map Block
--//比较全面的可以参考,注奇怪9.2之前11 Unknown
http://www.juliandyke.com/Internals/BlockTypes.php
4. transaction layer:
--//(前24 bytes固定,事务信息)
BBED> p ktbbh
struct ktbbh, 96 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA) -- data type (1=data; 2=index)
union ktbbhsid, 4 bytes @24 -- segment/object id
ub4 ktbbhsg1 @24 0x00016139 --
ub4 ktbbhod1 @24 0x00016139 -- object_id
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0x4279f41c -- scn at last block cleanout
ub2 kscnwrp @32 0x0003
sb2 ktbbhict @36 3 -- number of ITL SLOTS
ub1 ktbbhflg @38 0x32 (NONE) -- 0= on the freelist
ub1 ktbbhfsl @39 0x00 -- ITL tx freelist slot
ub4 ktbbhfnx @40 0x01000220 -- DBA of next block on the freelist
struct ktbbhitl[0], 24 bytes @44 -- ITL列表信息 blow:
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0xffff
ub2 kxidslt @46 0x0000
ub4 kxidsqn @48 0x00000000
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00000000
ub2 kubaseq @56 0x0000
ub1 kubarec @58 0x00
ub2 ktbitflg @60 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 3
ub2 _ktbitwrp @62 0x0003
ub4 ktbitbas @64 0x4279f41c
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0000
ub2 kxidslt @70 0x0000
ub4 kxidsqn @72 0x00000000
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00000000
ub2 kubaseq @80 0x0000
ub1 kubarec @82 0x00
ub2 ktbitflg @84 0x0000 (NONE)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x00000000
struct ktbbhitl[2], 24 bytes @92
struct ktbitxid, 8 bytes @92
ub2 kxidusn @92 0x0000
ub2 kxidslt @94 0x0000
ub4 kxidsqn @96 0x00000000
struct ktbituba, 8 bytes @100
ub4 kubadba @100 0x00000000
ub2 kubaseq @104 0x0000
ub1 kubarec @106 0x00
ub2 ktbitflg @108 0x0000 (NONE)
union _ktbitun, 2 bytes @110
sb2 _ktbitfsc @110 0
ub2 _ktbitwrp @110 0x0000
ub4 ktbitbas @112 0x00000000
--//ITL信息有一列是ktbitflg,其不同值的含义如下 :
---- = transaction is active, or committedpending cleanout
C--- = transaction has been committed and locks cleaned out
-B-- = this undo record contains the undo for this ITL entry
--U- = transaction committed (maybe longago); SCN is an upper bound
---T = transaction was still active atblock cleanout SCN
C-U- = Block cleaned by delayed block cleanout, and rollback segment info is overwritten.
--//也就是第1位为1表示C, 第2位为1表示B, 第3位为1表示U,第4位为1表示T.
5.data header layer:
BBED> p kdbh
struct kdbh, 14 bytes @124
ub1 kdbhflag @124 0x00 (NONE)
sb1 kdbhntab @125 1 -- number of tables(>1 in clusters)
sb2 kdbhnrow @126 4 -- number of rows
sb2 kdbhfrre @128 -1
sb2 kdbhfsbo @130 26 -- freespace begin offset
sb2 kdbhfseo @132 7972 -- freespace end offset (和上一个相减是freespace)
sb2 kdbhavsp @134 7946 -- available space in the block
sb2 kdbhtosp @136 7946 -- total available space when all TXs commit
6. table directory layer 和 row directory layer:
BBED> p kdbt
struct kdbt[0], 4 bytes @138
sb2 kdbtoffs @138 0
sb2 kdbtnrow @140 4
BBED> p kdbr
sb2 kdbr[0] @142 8038
sb2 kdbr[1] @144 8016
sb2 kdbr[2] @146 7996
sb2 kdbr[3] @148 7972
--//kdbr[4] 是一个数组,每个占2个字节。行目录,记录了记录的相对偏移,要算绝对偏移要加+kdbh的偏移量(这里是124,一般如果2个ITL槽正好是100)
--//如果参考前面的显示:
BBED> map
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 547 Dba:0x01000223
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 96 bytes @20
struct kdbh, 14 bytes @124
struct kdbt[1], 4 bytes @138
sb2 kdbr[4] @142
ub1 freespace[7946] @150
ub1 rowdata[92] @8096
ub4 tailchk @8188
struct kdbh, 14 bytes @124
struct kdbt[1], 4 bytes @138
sb2 kdbr[4] @142
--// 14+4+4*2=26,kdbhfsbo(freespace begin offset) = 26 .
7.剩下free space layer,row data layer,tailchk layer.
ub1 freespace[7946] @150 -- free space layer
ub1 rowdata[92] @8096 -- row data layer
ub4 tailchk @8188 -- tailchk layer = {(bas_kcbh(低2字节))+(type_kcbh)+(seq_kcbh)}
--//从这里也可以看出数据是从块底部开始插入,这样可以充分利用free space的空间。
BBED> p tailchk
ub4 tailchk @8188 0xf41e0602
BBED> p kcbh.bas_kcbh
ub4 bas_kcbh @8 0x4279f41e
--//注意看scn号的低2字节 f41e,用来填充tailchk的一部分。
--//最后关注row data layer。
BBED> p kdbr
sb2 kdbr[0] @142 8038
sb2 kdbr[1] @144 8016
sb2 kdbr[2] @146 7996
sb2 kdbr[3] @148 7972
BBED> x /rncc *kdbr[0]
rowdata[66] @8162
-----------
flag@8162: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8163: 0x00
cols@8164: 3
col 0[2] @8165: 10
col 1[10] @8168: ACCOUNTING
col 2[8] @8179: NEW YORK
--//你可以注意kdbr[0]=8038,而使用x命令显示偏移是8162,8162-8038=124,也就是kdbr记录的是相对偏移要加上kdbh的偏移量(124)才是绝对偏移。