Redo內部解析-Single Row insert （四）

七、表中insert一行記錄

具體測試步驟：

1、建立測試環境

SQL>select dbms_flashback.get_system_change_number scn from dual;
                     SCN
           --------------------
               56,399,212,054

SQL> create table test (id number, test varchar2(20));
SQL> insert into test values( 1, 'test');
SQL>commit;

SQL>select dbms_flashback.get_system_change_number scn from dual;
                 SCN
        --------------------
      56,399,212,082

SQL>alter system dump logfile
      '/home/xxxx/xxxx/oradata/xxxx/redo02.log'
     scn min 56399212054
     scn max 56399212082

2、解析trace檔案

REDO RECORD - Thread:1 RBA: 0x000f5f.0009d383.00c0 LEN: 0x0144 VLD: 0x01
SCN: 0x000d.21a7ae2d SUBSCN:  1 07/14/2009 11:06:22

CHANGE #1 TYP:0 CLS:33 AFN:2 DBA:0x00800089 OBJ:0 SCN:0x000d.21a7ad93 SEQ:  1 OP:5.2
ktudh redo: slt: 0x0012 sqn: 0x000318b2 flg: 0x0012 siz: 84 fbi: 0
            uba: 0x008018c9.54a5.1c    pxid:  0x0000.000.00000000

--更新undo header，所更新的undo段為(33-11)/2=11

CHANGE #2 TYP:0 CLS:34 AFN:2 DBA:0x008018c9 OBJ:0 SCN:0x000d.21a7ad92 SEQ:  1 OP:5.1
ktudb redo: siz: 84 spc: 1276 flg: 0x0012 seq: 0x54a5 rec: 0x1c
            xid:  0x0009.012.000318b2
ktubl redo: slt: 18 rci: 0 opc: 11.1 objn: 180594 objd: 180594 tsn: 8

--更改undo block資訊，記錄的物件是180594(表：test)；表空間#=8

Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x008018c9.54a5.1b
prev ctl max cmt scn:  0x000d.21a796dc  prev tx cmt scn:  0x000d.21a79721
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
op: Z
KDO Op code: DRP row dependencies Disabled

--對於insert 操作，undo操作是drop row記錄(DRP)

 xtype: XA flags: 0x00000000  bdba: 0x0200001f  hdba: 0x0200001b

--Undo記錄了資料塊dba資訊(0x0200001f),具體對應file_id和block_id，參看"Redo 內部解析(二)"

itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0)

--在row dictionary中指向的slot#是0(0x0)
--所以整個change vector意思是回滾將在資料塊=0200001f中，從字典中slot#=0刪除行資訊。

CHANGE #3 TYP:0 CLS: 1 AFN:8 DBA:0x0200001f OBJ:0 SCN:0x000d.21a7ae2d SEQ:  2 OP:11.2
--更改表段資料塊資訊

KTB Redo
op: 0x01  ver: 0x01
op: F  xid:  0x0009.012.000318b2    uba: 0x008018c9.54a5.1c
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0200001f  hdba: 0x0200001b
--資料塊地址(bdba)=0x0200001f,段頭地址(hdba)=0x0200001b

itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 11
--描述資料塊插入一行,在row dictionary中的slot編號；

fb: --H-FL-- lb: 0x1  cc: 2
null: --
col  0: [ 2]  c1 02
col  1: [ 4]  74 65 73 74  --對應test字元

REDO RECORD - Thread:1 RBA: 0x000f5f.0009d385.0010 LEN: 0x0054 VLD: 0x01
SCN: 0x000d.21a7ae30 SUBSCN:  1 07/14/2009 11:06:27
CHANGE #1 TYP:0 CLS:33 AFN:2 DBA:0x00800089 OBJ:0 SCN:0x000d.21a7ae2d SEQ:  1 OP:5.4
ktucm redo: slt: 0x0012 sqn: 0x000318b2 srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x008018c9.54a5.1c ext: 2 spc: 1190 fbi: 0
--表示commit;

總結：
  對於單行的插入操作，redo記錄資訊為：
  
修改undo header資訊(op=5.2) =>修改undo slot資訊(op=5.1) =>記錄資料塊的insert變化(op=11.2) => commit (op=5.4)
 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/354732/viewspace-607826/，如需轉載，請註明出處，否則將追究法律責任。