oracle data trans,深入解析Oracle 数据块(type: 0x06=trans data)结构

一、概念介绍

Oracle 数据块(type: 0x06=trans data)实际存储业务数据，数据块的内部结构由Cache layer、Transaction layer及Data layer三层结构组织，本文主要使用dd、Oracle dump、bbed研究数据块的内部结构，最后使用C语言对业务数据进行unload脱机。

图1 Data Block Structure

二、解析Cache layer层

Oracle的所有数据块的头部都有该结构，存储数据块的格式、类型等信息，

图2 Oracle Data Block Layout

Cache layer的C结构体kcbh.h，

struct kcbh

{

ub1  type_kcbh;   * Block type*

ub1 frmt_kcbh;         * #define KCBH_FRMT11 a2 */

ub1 spare1_kcbh:

ub1 spare2_kcbh;

ub4 rdba_kcbh;   * relative DBA

ub4 bas_kcbh;    * base of SCN */

ub2 wrp_kcbh;    * wrap of scn */

ub1 seq_kcbh;    * sequence # of changes at same scn */

ub1 flg_kcbh;

ub2 chkval_kcbh;

};

解析dd结构，

图3 Cache layer

bbed结构，

BBED> p kcbh

struct kcbh, 20 bytes                       @0

ub1 type_kcbh   @0        0x06    --块类型type: 0x06=trans  data

ub1 frmt_kcbh   @1        0xa2    --块格式

ub1 spare1_kcbh @2        0x00

ub1 spare2_kcbh @3        0x00

ub4 rdba_kcbh   @4        0x01401e1b --块地址rdba: 0x01401e1b (5/7707)

ub4 bas_kcbh    @8        0x002d34f1 --SCN base

ub2 wrp_kcbh    @12       0x0000   -- SCN wrap

ub1 seq_kcbh    @14       0x10 --seq: 0x10 sequence number  incremented for each change made to the block at the same SCN

ub1 flg_kcbh    @15       0x04 (KCBHFCKV)

ub2 chkval_kcbh  @16       0x5cb5   --检验值

ub2 spare3_kcbh  @18       0x0000

BBED>

flg_kcbh解释：

#define KCBHFNEW 0x01 *new block - zeroeddata area   */

#define KCBHFDLC 0x02 *delayed loggingchange advance scn/seq */

#define KCBHFCKV 0x04 * check valuesaved-block xor's zero */

#define KCBHFTMP 0X08/* TEMPORARY BLOCK */

段头块类型：

Decimal Hex Type10x01 undo segment header110x0b data file header120x0c data segment header with FLG blocks140x0e unlimited undo segment header150x0f unlimited save undo segment header160x10 unlimited data segment header170x11 unlimited data segment header with FLG blocks180x12 extent map block230x17 bitmapped segment header290x1d bitmapped file space header320x20 first level bitmap block330x21 second level bitmap block340x22 third level bitmap block350x23 Pagetable segment header block360x24 Pagetable extent map block370x25 System Managed Undo Extent Map Bloc

oracle dump:

Block dump from disk:

buffer tsn: 6 rdba: 0x01401e1b (5/7707)

scn: 0x0000.002d34f1 seq: 0x10 flg: 0x04tail: 0x34f10610

frmt: 0x02 chkval: 0x5cb5 type: 0x06=transdata

tail 值计算规则:

34f1(last two bytes of scn base)+ 06(type)+10(seq)

三、解析Transaction layer层

Transaction layer由Transaction layer: fixed、Transaction layer: variable两部分组织，

图4 Transaction layer

图5 struct ktbbh

dd解析：

图6 ktbb二进制

bbed解析：

BBED> p ktbbh

struct ktbbh, 72 bytes                      @20

ub1 ktbbhtyp             @20       0x01 (KDDBTDATA)--

union ktbbhsid, 4 bytes                 @24

ub4 ktbbhsg1                         @24       0x00015792

ub4 ktbbhod1                         @24       0x00015792

struct ktbbhcsc, 8 bytes                @28

ub4 kscnbas                           @28       0x002cf2ab

ub2 kscnwrp                          @32       0x0000

sb2 ktbbhict                            @36       2

ub1 ktbbhflg                            @38       0x32 (NONE)

ub1 ktbbhfsl                            @39       0x00

ub4 ktbbhfnx                            @40       0x01401e18

struct ktbbhitl[0], 24 bytes            @44

struct ktbitxid, 8 bytes             @44

ub2 kxidusn                        @44       0x0009

ub2 kxidslt                       @46       0x0010

ub4 kxidsqn                       @48       0x00000956

struct ktbituba, 8 bytes             @52

ub4 kubadba                        @52      0x00c00418

ub2 kubaseq                       @56       0x025f

ub1 kubarec                       @58       0x5f

ub2 ktbitflg                         @60       0x8000 (KTBFCOM)

union _ktbitun, 2 bytes               @62

sb2 _ktbitfsc                     @62       0

ub2 _ktbitwrp                     @62       0x0000

ub4 ktbitbas                         @64       0x002b5f48

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

BBED>

oracle dump解析：

图7 oracle dump Transaction layer

Rdba: Relative data block address

Seg/Obj: object_id

Csc: SCN at last block cleanout

Itc: Number of itl slots

Flg： 0=on the freelist

Typ: 1=DATA; 2=INDEX

Fsl: ITL TX freelist slot

Fnx: DBA of next block on freelist

Itl: Interested transaction list index(ITLsdetermined by INITRANS and MAXTRANS)

Xid: Transaction ID(UndoSeg.Slot.Wrap

Uba: Undo address(UndoDBA.SeqNo.RecordNo)

Flg: C=Committed;U=Commit Upper Bound;T=Active at CSC

Lck: Number of rows affected by thistransaction

Scn/Fsc: Scn=SCN of commited TX; Fsc= Freespace credit(bytes)

四、解析Data layer层

Data layer由Table directory、Rowdirectory、Free space和Row data四部分组成。包括以下部分，

struct kdbh, 14 bytes   --头部信息

struct kdbt[1], 4 bytes   --Table directory

sb2 kdbr[269]         --Row directory

ub1 freespace[807]    --Free space

ub1 rowdata[6725]    --Row data

图8 Data Layer

dd解析：

图9 table directory

图10 row directory

图11 row directory(bbed)

图12 row directory(二进制)

图13 Free Space(bbed)

图14 Row data(二进制)

图15 Row data of top (bbed)

图16 Row data of bottom (bbed)

p *kdbr[268]行offset在头部，p*kdbr[0]行offset在尾部，说明oracle在块内存储数据规则“bottom/up”。

图17 Block Usage example

行存储格式:

图18 Row Format

第一个byte, row flag：由8位bit表示，“2c”：--HFL--；

Thevalues for the row flag are:#defineKDRHFK 0x80 ClusterKey#defineKDRHFC 0x40Clustered table member#defineKDRHFH 0x20Head piece of row#defineKDRHFD 0x10Deleted row#defineKDRHFF 0x08First data piece|#defineKDRHFL 0x04Last data piece#defineKDRHFP 0x02 First column continues fromPreviouspiece#defineKDRHFN 0x01 Last column continues inNext piece

第二个byte ,lock byte: 如果该记录被update操作，则lock指击事务itl槽。

第三个byte, cols: 该行记录的列数

第四个byte,cluster key idx,如果是集群表，则记录cluster keyidx。

after byte: Column length |column data| Column length |column data

五、unload业务数据

使用C语言unload业务数据，输出到操作系统文件f:\unload.dmp，结果如下所示，

操作系统生成文件：

图19 unload file

unload业务数据：

图20 unload data

后台日志：

图21 unload log

六、说明

1、以上内容为个人多次测试结果，由于个人原因，如有分析不足之处还请见谅及指正。

2、文章涉及内容，请勿在生产环境模拟。

积土成山，风雨兴焉；积水成渊，蛟龙生焉。——荀子《劝学》