commit操作是否一定會被記錄到redo等問題的研究

網上已有不少介紹與分析redolog dump的技術討論，關於這部分基礎知識可以參考網上內容。
本文的測試案例旨在回答以下幾個問題：
DDL語句對應的更改在Redo裡是以何種形式進行記錄的？
merge命令產生的修改在Redo裡是以何種形式進行記錄的？
commit操作一定會被記錄在redo裡嗎？


//////////////////
// redolog如何記載DDL語句所產生的修改
//////////////////
create table scott.t0107_1 tablespace test1 as select * from all_users where 1=2;


col member format a50
set linesize 100
select lf.member,l.status,l.sequence# from v$log l,v$logfile lf where l.group#=lf.group#;
MEMBER                                             STATUS            SEQUENCE#
-------------------------------------------------- ---------------- ----------
/odata/redo/ORA55/onlinelog/o1_mf_1_d4wdjm7w_.log  CURRENT                  52
/odata/redo/ORA55/onlinelog/o1_mf_2_d4wdjmmx_.log  INACTIVE                 50
/odata/redo/ORA55/onlinelog/o1_mf_3_d4wdjn3k_.log  INACTIVE                 51


select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
                                  1878119


alter table scott.t0107_1 modify (username varchar2(31));


select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
                                  1878126


select value from v$diag_info where name='Default Trace File';
VALUE
----------------------------------------------------------------------------------------------------
/oracle/app/oracle/diag/rdbms/ora55/ora55/trace/ora55_ora_29884476.trc


alter system dump logfile '/odata/redo/ORA55/onlinelog/o1_mf_1_d4wdjm7w_.log' scn MIN 1878119 scn MAX 1878126;


***ora55_ora_29884476.trc檔案裡摘錄的相關內容，
REDO RECORD - Thread:1 RBA: 0x000034.000065a0.0010 LEN: 0x0100 VLD: 0x05    <---第一條redo record
SCN: 0x0000.001ca868 SUBSCN:  1 12/19/2016 11:18:33
(LWN RBA: 0x000034.000065a0.0010 LEN: 0002 NST: 0001 SCN: 0x0000.001ca868)
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.001ca864 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0007 sqn: 0x000007e9 flg: 0x0011 siz: 80 fbi: 0
            uba: 0x00c0026c.01d4.11    pxid:  0x0000.000.00000000
CHANGE #2 TYP:0 CLS:36 AFN:3 DBA:0x00c0026c OBJ:4294967295 SCN:0x0000.001ca862 SEQ:2 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 80 spc: 6286 flg: 0x0010 seq: 0x01d4 rec: 0x11
            xid:  0x000a.007.000007e9  
ktubl redo: slt: 7 rci: 0 opc: 5.7 [objn: 0 objd: 0 tsn: 0]
Undo type:  Regular undo        Begin trans    Last buffer split:  No 
。。。。。
 
REDO RECORD - Thread:1 RBA: 0x000034.000065a0.0110 LEN: 0x0188 VLD: 0x01    <---第二條redo record
SCN: 0x0000.001ca869 SUBSCN:  1 12/19/2016 11:18:33
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:24.1 ENC:0
 
REDO RECORD - Thread:1 RBA: 0x000034.000065a2.0010 LEN: 0x0390 VLD: 0x05    <---第三條redo record
SCN: 0x0000.001ca86c SUBSCN:  1 12/19/2016 11:18:33
(LWN RBA: 0x000034.000065a2.0010 LEN: 0005 NST: 0001 SCN: 0x0000.001ca86a)
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.001ca868 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0007 sqn: 0x00000000 flg: 0x0002 siz: 356 fbi: 0
            uba: 0x00c0026c.01d4.12    pxid:  0x0000.000.00000000
CHANGE #2 TYP:0 CLS:36 AFN:3 DBA:0x00c0026c OBJ:4294967295 SCN:0x0000.001ca868 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 356 spc: 6204 flg: 0x0022 seq: 0x01d4 rec: 0x12
            xid:  0x000a.007.000007e9  
ktubu redo: slt: 7 rci: 0 opc: 11.1 objn: 4 objd: 2 tsn: 0    <---object_id=4 and data_object_id=2,TAB$
Undo type:  Regular undo       Undo type:  Last buffer split:  No 
。。。。。
KDO Op code: URP row dependencies Disabled
。。。。。
CHANGE #3 TYP:2 CLS:1 AFN:1 DBA:0x00407b0d OBJ:2 SCN:0x0000.001ca864 SEQ:1 OP:11.5 ENC:0 RBL:0  <---OBJ:2表示data_object_id=2
KTB Redo
op: 0x11  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x000a.007.000007e9    uba: 0x00c0026c.01d4.12
Block cleanout record, scn:  0x0000.001ca86c ver: 0x01 opt: 0x02, entries follow...
  itli: 2  flg: 2  scn: 0x0000.001ca864
KDO Op code: URP row dependencies Disabled


REDO RECORD - Thread:1 RBA: 0x000034.000065a3.01b0 LEN: 0x00d0 VLD: 0x01 <---第四條redo record
SCN: 0x0000.001ca86c SUBSCN:  2 12/19/2016 11:18:33
CHANGE #1 TYP:0 CLS:36 AFN:3 DBA:0x00c0026c OBJ:4294967295 SCN:0x0000.001ca86c SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 68 spc: 5846 flg: 0x0022 seq: 0x01d4 rec: 0x13
            xid:  0x000a.007.000007e9
ktubu redo: slt: 7 rci: 18 opc: 11.1 objn: 21 objd: 2 tsn: 0   <---object_id=21 and data_object_id=2,COL$
Undo type:  Regular undo       Undo type:  Last buffer split:  No
。。。。。
KDO Op code: LKR row dependencies Disabled
。。。。。
CHANGE #2 TYP:0 CLS:1 AFN:1 DBA:0x00407b0d OBJ:2 SCN:0x0000.001ca86c SEQ:1 OP:11.4 ENC:0 RBL:0 <---OBJ:2表示data_object_id=2
KTB Redo
op: 0x02  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c0026c.01d4.13
KDO Op code: LKR row dependencies Disabled
。。。。。


REDO RECORD - Thread:1 RBA: 0x000034.000065a4.0090 LEN: 0x01e8 VLD: 0x01  <---第五條redo record
SCN: 0x0000.001ca86c SUBSCN:  3 12/19/2016 11:18:33
CHANGE #1 TYP:0 CLS:36 AFN:3 DBA:0x00c0026c OBJ:4294967295 SCN:0x0000.001ca86c SEQ:2 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 208 spc: 5776 flg: 0x0022 seq: 0x01d4 rec: 0x14
            xid:  0x000a.007.000007e9
ktubu redo: slt: 7 rci: 19 opc: 11.1 objn: 21 objd: 2 tsn: 0   <---object_id=21 and data_object_id=2,COL$
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c0026c.01d4.13
KDO Op code: URP row dependencies Disabled
。。。。。
CHANGE #2 TYP:0 CLS:1 AFN:1 DBA:0x00407b0d OBJ:2 SCN:0x0000.001ca86c SEQ:2 OP:11.5 ENC:0 RBL:0  <---OBJ:2表示data_object_id=2
KTB Redo
op: 0x02  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c0026c.01d4.14
KDO Op code: URP row dependencies Disabled
。。。。。


REDO RECORD - Thread:1 RBA: 0x000034.000065a5.0088 LEN: 0x01f0 VLD: 0x01    <---第六條redo record
SCN: 0x0000.001ca86c SUBSCN:  4 12/19/2016 11:18:33
CHANGE #1 TYP:0 CLS:36 AFN:3 DBA:0x00c0026c OBJ:4294967295 SCN:0x0000.001ca86c SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 196 spc: 5566 flg: 0x0022 seq: 0x01d4 rec: 0x15
            xid:  0x000a.007.000007e9
ktubu redo: slt: 7 rci: 20 opc: 11.1 objn: 18 objd: 18 tsn: 0   <---object_id=18 and data_object_id=18,OBJ$
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
KDO undo record:
KTB Redo
op: 0x04  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x000a.010.000007e8 uba: 0x00c0026b.01d4.04
                      flg: C---    lkc:  0     scn: 0x0000.001ca864
KDO Op code: URP row dependencies Disabled
。。。。。
CHANGE #2 TYP:2 CLS:1 AFN:1 DBA:0x00405a22 OBJ:18 SCN:0x0000.001ca864 SEQ:1 OP:11.5 ENC:0 RBL:0 <---OBJ:18表示data_object_id=18
KTB Redo
op: 0x11  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x000a.007.000007e9    uba: 0x00c0026c.01d4.15
Block cleanout record, scn:  0x0000.001ca86c ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x0000.001ca864
KDO Op code: URP row dependencies Disabled            


REDO RECORD - Thread:1 RBA: 0x000034.000065a6.0088 LEN: 0x00b0 VLD: 0x01  <---第七條redo record
SCN: 0x0000.001ca86d SUBSCN:  1 12/19/2016 11:18:33
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.001ca86c SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0007 sqn: 0x000007e9 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c0026c.01d4.15 ext: 2 spc: 5368 fbi: 0 
CHANGE #2 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:17.28 ENC:0
STANDBY METADATA CACHE INVALIDATION
KQR INFO:
[f06d0a44, 8, 14369]   <---14369是scott.t0107_1的object_id
KGL INFO:
[db6d4360.141d8603.b334057c.60f21170, 1]   <---select kglnahsv from x$kglob where kglnaobj='T0107_1' and kglnaown='SCOTT';
[db6d4360.141d8603.b334057c.60f21170, 1]
END OF REDO DUMP


執行了alter table scott.t0107_1 modify (username varchar2(31))之後會對TAB$、COL$、OBJ$三個系統表進行操作，這三個表的data_object_id分別為2、2、18，object_id分別為4、21、18，在undo block的redo裡會顯示object_id和data_object_id，在data block的undo裡只會顯示data_object_id，redolog裡並不會顯式的記錄DDL語句


//////////////////
// 測試redolog如何記錄MERGE語句所產生的修改
//////////////////
drop table scott.t0108_1;
drop table scott.t0108_2;


create table scott.t0108_1 (id1 number,col1 varchar2(1)) tablespace test1;
create table scott.t0108_2 (id2 number,col2 varchar2(1)) tablespace test1;


insert into scott.t0108_1 values(1,'A');
insert into scott.t0108_1 values(2,'B');
insert into scott.t0108_1 values(3,'C');
commit;


insert into scott.t0108_2 values(1,'a');
insert into scott.t0108_2 values(3,'c');
insert into scott.t0108_2 values(5,'E');
commit;


select * from scott.t0108_1;
       ID1 C
---------- -
         1 A
         2 B
         3 C


select * from scott.t0108_2;
       ID2 C
---------- -
         1 a
         3 c
         5 E


select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from scott.t0108_1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
                                 271                                    4
                                 271                                    4
                                 271                                    4


col member format a50
set linesize 100
select lf.member,l.status,l.sequence# from v$log l,v$logfile lf where l.group#=lf.group#;
MEMBER                                             STATUS            SEQUENCE#
-------------------------------------------------- ---------------- ----------
/odata/redo/ORA55/onlinelog/o1_mf_1_d4wdjm7w_.log  INACTIVE                 43
/odata/redo/ORA55/onlinelog/o1_mf_2_d4wdjmmx_.log  CURRENT                  44
/odata/redo/ORA55/onlinelog/o1_mf_3_d4wdjn3k_.log  INACTIVE                 42


alter system switch logfile;


select lf.member,l.status,l.sequence# from v$log l,v$logfile lf where l.group#=lf.group#;
MEMBER                                             STATUS            SEQUENCE#
-------------------------------------------------- ---------------- ----------
/odata/redo/ORA55/onlinelog/o1_mf_1_d4wdjm7w_.log  INACTIVE                 43
/odata/redo/ORA55/onlinelog/o1_mf_2_d4wdjmmx_.log  ACTIVE                   44
/odata/redo/ORA55/onlinelog/o1_mf_3_d4wdjn3k_.log  CURRENT                  45


merge into scott.t0108_1 t1 using scott.t0108_2 t2 on (t1.id1=t2.id2) when matched then update set col1=col2 delete where col1='a' when not matched then insert (id1,col1) values(id2,col2);


commit;


SQL> select * from scott.t0108_1;


       ID1 C
---------- -
         2 B
         3 c
         5 E
         
select value from v$diag_info where name='Default Trace File';
VALUE
----------------------------------------------------------------------------------------------------
/oracle/app/oracle/diag/rdbms/ora55/ora55/trace/ora55_ora_33226830.trc


SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from scott.t0108_1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
                                 271                                    4
                                 271                                    4
                                 271                                    4


alter system dump logfile '/odata/redo/ORA55/onlinelog/o1_mf_3_d4wdjn3k_.log' dba min 4 271 dba max 4 271;


***ora55_ora_33226830.trc檔案內容
>>> redo record由此處開始，先是record頭資訊
REDO RECORD - Thread:1 RBA: 0x00002d.00000002.0010 LEN: 0x0518 VLD: 0x0d
SCN: 0x0000.0017f1d4 SUBSCN:  1 12/12/2016 12:11:41
(LWN RBA: 0x00002d.00000002.0010 LEN: 0003 NST: 0001 SCN: 0x0000.0017f1d3)


>>> OP:11.4 lock row操作，具體就是更新block 4/271裡某一行的lb欄位
CHANGE #1 TYP:2 CLS:1 AFN:4 DBA:0x0100010f OBJ:13780 SCN:0x0000.0017f148 SEQ:1 OP:11.4 ENC:0 RBL:0
KTB Redo 
op: 0x11  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x000a.004.000004b6    uba: 0x00c04f83.00ed.39
Block cleanout record, scn:  0x0000.0017f1cd ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x0000.0017f148
KDO Op code: LKR row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100010f  hdba: 0x0100010a
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0 to: 2   <---表示block 4/271第1行(slot: 0)的lb欄位指向0x02號itl，此時dump block 4/271應可以看到0x02號Itl的Lck欄位值為1；
delete t0108_1 where dbms_rowid.rowid_row_number(rowid)=1執行前給4/271裡的第一行，也就是id1=1所在行上鎖的操作


>>> OP:5.2 更新undo header
CHANGE #2 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.0017f18a SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0004 sqn: 0x000004b6 flg: 0x0012 siz: 112 fbi: 0
            uba: 0x00c04f83.00ed.39    pxid:  0x0000.000.00000000


>>> OP:11.3 刪除記錄
CHANGE #3 TYP:0 CLS:1 AFN:4 DBA:0x0100010f OBJ:13780 SCN:0x0000.0017f1d4 SEQ:1 OP:11.3 ENC:0 RBL:0
KTB Redo 
op: 0x02  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c04f83.00ed.3a
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100010f  hdba: 0x0100010a
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0)  <---表裡的第1行
記錄delete t0108_1 where id1=1的操作


>>> OP:11.4 lock row操作，具體就是更新block 4/271裡的lb位
CHANGE #4 TYP:0 CLS:1 AFN:4 DBA:0x0100010f OBJ:13780 SCN:0x0000.0017f1d4 SEQ:2 OP:11.4 ENC:0 RBL:0
KTB Redo 
op: 0x02  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c04f83.00ed.3b
KDO Op code: LKR row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100010f  hdba: 0x0100010a
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 2 to: 2   <---表示block 4/271第3行(slot: 3)的lb欄位指向0x02號itl，此時dump block 4/271應可以看到0x02號Itl的Lck欄位值為；
update t0108_1 set col1='c' where id1=3前給id1=3所在行上鎖的操作


>>> OP:11.5 update row操作
CHANGE #5 TYP:0 CLS:1 AFN:4 DBA:0x0100010f OBJ:13780 SCN:0x0000.0017f1d4 SEQ:3 OP:11.5 ENC:0 RBL:0
KTB Redo 
op: 0x02  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c04f83.00ed.3c
KDO Op code: URP row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x0100010f  hdba: 0x0100010a
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 2 ckix: 0  <---更新第三行
ncol: 2 nnew: 1 size: 0  <---更新的表共有2列，此次更新只涉及其中1列，被更新的欄位前值與後值的長度相等
Vector content: 
col  1: [ 1]  63   <---更新後的值為'c'
記錄update t0108_1 set col1='c' where id1=3操作所涉及的行以及更新後的欄位值


>>> OP:11.11 一次插入大於等於一行的操作
CHANGE #6 TYP:0 CLS:1 AFN:4 DBA:0x0100010f OBJ:13780 SCN:0x0000.0017f1d4 SEQ:4 OP:11.11 ENC:0 RBL:0
KTB Redo 
op: 0x02  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c04f83.00ed.3d
KDO Op code: QMI row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100010f  hdba: 0x0100010a
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 lock: 2 nrow: 1  <---插入一行
slot[0]: 3   <---在第4行插入新值
tl: 8 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 06
col  1: [ 1]  45   <---'E'
記錄insert into t0108_1 values(5,'E')的操作


>>> OP:5.4 commit操作
CHANGE #7 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.0017f1d4 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0004 sqn: 0x000004b6 srt: 0 sta: 9 flg: 0x0 


>>> OP:5.1 儲存修改前映像至undo的操作
CHANGE #8 TYP:0 CLS:36 AFN:3 DBA:0x00c04f83 OBJ:4294967295 SCN:0x0000.0017f188 SEQ:2 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 112 spc: 606 flg: 0x0012 seq: 0x00ed rec: 0x39
            xid:  0x000a.004.000004b6  
ktubl redo: slt: 4 rci: 0 opc: 11.1 [objn: 13780 objd: 13780 tsn: 4]
Undo type:  Regular undo        Begin trans    Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
             0x00000000  prev ctl uba: 0x00c04f83.00ed.37 
prev ctl max cmt scn:  0x0000.00159fbb  prev tx cmt scn:  0x0000.00178877 
txn start scn:  0x0000.0017f152  logon user: 0  prev brb: 12603268  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo 
op: 0x03  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: LKR row dependencies Disabled  <---LKR表示lock row，給行設定鎖欄位的操作
  xtype: XA flags: 0x00000000  bdba: 0x0100010f  hdba: 0x0100010a
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0 to: 0   <---第一行的lb欄位的前映像為0
把第一行的lb欄位置為0的操作，是CHANGE #1的逆操作


>>> OP:5.1 儲存修改前映像至undo的操作
CHANGE #9 TYP:0 CLS:36 AFN:3 DBA:0x00c04f83 OBJ:4294967295 SCN:0x0000.0017f1d4 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 112 spc: 492 flg: 0x0022 seq: 0x00ed rec: 0x3a
            xid:  0x000a.004.000004b6  
ktubu redo: slt: 4 rci: 57 opc: 11.1 objn: 13780 objd: 13780 tsn: 4
Undo type:  Regular undo       Undo type:  Last buffer split:  No 
Tablespace Undo:  No 
             0x00000000
KDO undo record:
KTB Redo 
op: 0x02  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c04f83.00ed.39
KDO Op code: IRP row dependencies Disabled   <---Insert 單行的操作
  xtype: XA flags: 0x00000000  bdba: 0x0100010f  hdba: 0x0100010a
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 8
fb: --H-FL-- lb: 0x2  cc: 2
null: --
col  0: [ 2]  c1 02
col  1: [ 1]  41     <---'A'
對應insert into t0108_1 values(1,'A')操作，是CHANGE #3的逆操作


>>> OP:5.1 儲存修改前映像至undo的操作
CHANGE #10 TYP:0 CLS:36 AFN:3 DBA:0x00c04f83 OBJ:4294967295 SCN:0x0000.0017f1d4 SEQ:2 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 68 spc: 378 flg: 0x0022 seq: 0x00ed rec: 0x3b
            xid:  0x000a.004.000004b6  
ktubu redo: slt: 4 rci: 58 opc: 11.1 objn: 13780 objd: 13780 tsn: 4
Undo type:  Regular undo       Undo type:  Last buffer split:  No 
Tablespace Undo:  No 
             0x00000000
KDO undo record:
KTB Redo 
op: 0x02  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c04f83.00ed.3a
KDO Op code: LKR row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100010f  hdba: 0x0100010a
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 2 to: 0 <---第三行的lb欄位的前映像為0
把第三行的lb欄位置為0的操作，是CHANGE #4的逆操作


>>> OP:5.1 儲存修改前映像至undo的操作
CHANGE #11 TYP:0 CLS:36 AFN:3 DBA:0x00c04f83 OBJ:4294967295 SCN:0x0000.0017f1d4 SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 88 spc: 308 flg: 0x0022 seq: 0x00ed rec: 0x3c
            xid:  0x000a.004.000004b6  
ktubu redo: slt: 4 rci: 59 opc: 11.1 objn: 13780 objd: 13780 tsn: 4
Undo type:  Regular undo       Undo type:  Last buffer split:  No 
Tablespace Undo:  No 
             0x00000000
KDO undo record:
KTB Redo 
op: 0x02  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c04f83.00ed.3b
KDO Op code: URP row dependencies Disabled   <---URP表示update操作
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x0100010f  hdba: 0x0100010a
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 2 ckix: 0  <---update第3行
ncol: 2 nnew: 1 size: 0
Vector content: 
col  1: [ 1]  43  <---'C'
對應的update t0108_1 set col1='C' where id1=3操作，是CHANGE #5的逆操作


>>> OP:5.1 儲存修改前映像至undo的操作
CHANGE #12 TYP:0 CLS:36 AFN:3 DBA:0x00c04f83 OBJ:4294967295 SCN:0x0000.0017f1d4 SEQ:4 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 72 spc: 218 flg: 0x0022 seq: 0x00ed rec: 0x3d
            xid:  0x000a.004.000004b6  
ktubu redo: slt: 4 rci: 60 opc: 11.1 objn: 13780 objd: 13780 tsn: 4
Undo type:  Regular undo       Undo type:  Last buffer split:  No 
Tablespace Undo:  No 
             0x00000000
KDO undo record:
KTB Redo 
op: 0x02  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c04f83.00ed.3c
KDO Op code: QMD row dependencies Disabled   <---Delete多行
  xtype: XA flags: 0x00000000  bdba: 0x0100010f  hdba: 0x0100010a
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 lock: 0 nrow: 1  <---nrow表示此次僅delete一行
slot[0]: 3 <---delete表中的第四行
對應delete scott.t0108_1 where id1=5這一操作，是CHANGE #6的逆操作


>>> 以下部分是block written record
REDO RECORD - Thread:1 RBA: 0x00002d.00000006.01b0 LEN: 0x0134 VLD: 0x02
SCN: 0x0000.0017f1e1 SUBSCN:  1 12/12/2016 12:12:16
(LWN RBA: 0x00002d.00000006.0010 LEN: 0005 NST: 0001 SCN: 0x0000.0017f1e1)
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
 Block Written - afn: 4 rdba: 0x01000108 BFT:(1024,16777480) non-BFT:(4,264)
                   scn: 0x0000.0017f146 seq: 0x02 flg:0x04
 Block Written - afn: 4 rdba: 0x01000109 BFT:(1024,16777481) non-BFT:(4,265)
                   scn: 0x0000.0017f13f seq: 0x02 flg:0x04
 Block Written - afn: 4 rdba: 0x0100010a BFT:(1024,16777482) non-BFT:(4,266)
                   scn: 0x0000.0017f146 seq: 0x03 flg:0x04
 Block Written - afn: 4 rdba: 0x0100010b BFT:(1024,16777483) non-BFT:(4,267)
                   scn: 0x0000.0017f146 seq: 0x02 flg:0x04
 Block Written - afn: 4 rdba: 0x0100010c BFT:(1024,16777484) non-BFT:(4,268)
                   scn: 0x0000.0017f146 seq: 0x02 flg:0x04
 Block Written - afn: 4 rdba: 0x0100010d BFT:(1024,16777485) non-BFT:(4,269)
                   scn: 0x0000.0017f146 seq: 0x02 flg:0x04
 Block Written - afn: 4 rdba: 0x0100010e BFT:(1024,16777486) non-BFT:(4,270)
                   scn: 0x0000.0017f146 seq: 0x02 flg:0x04
 Block Written - afn: 4 rdba: 0x0100010f BFT:(1024,16777487) non-BFT:(4,271)
                   scn: 0x0000.0017f1d4 seq: 0x06 flg:0x06
 Block Written - afn: 4 rdba: 0x01000110 BFT:(1024,16777488) non-BFT:(4,272)
                   scn: 0x0000.0017f150 seq: 0x02 flg:0x04
 Block Written - afn: 4 rdba: 0x01000111 BFT:(1024,16777489) non-BFT:(4,273)
                   scn: 0x0000.0017f149 seq: 0x02 flg:0x04
 Block Written - afn: 4 rdba: 0x01000112 BFT:(1024,16777490) non-BFT:(4,274)
                   scn: 0x0000.0017f150 seq: 0x03 flg:0x04
 Block Written - afn: 4 rdba: 0x01000113 BFT:(1024,16777491) non-BFT:(4,275)
                   scn: 0x0000.0017f150 seq: 0x02 flg:0x04
 Block Written - afn: 4 rdba: 0x01000114 BFT:(1024,16777492) non-BFT:(4,276)
                   scn: 0x0000.0017f150 seq: 0x02 flg:0x04
 Block Written - afn: 4 rdba: 0x01000115 BFT:(1024,16777493) non-BFT:(4,277)
                   scn: 0x0000.0017f150 seq: 0x02 flg:0x04
 Block Written - afn: 4 rdba: 0x01000116 BFT:(1024,16777494) non-BFT:(4,278)
                   scn: 0x0000.0017f150 seq: 0x02 flg:0x04
 Block Written - afn: 4 rdba: 0x01000117 BFT:(1024,16777495) non-BFT:(4,279)
                   scn: 0x0000.0017f152 seq: 0x01 flg:0x06
END OF REDO DUMP
用OP：23.1表示Dummy block written callback的操作，block written record主要是DBWR在完成資料塊從記憶體寫入到磁碟的操作後，通知LGWR記錄下這寫block及其寫入時刻對應的SCN，可用於避免例項恢復情況下對已經發生checkpoint的data block再次運用Redo的重複勞動，比如:
T0時刻：使用者對block A的第1行記錄進行了update操作，這是對應的RBA是：100.2.11
T1時刻：使用者對block B的第1行記錄進行了update操作，這是對應的RBA是：100.3.20
T2時刻：使用者對block C的第1行記錄進行了update操作，這是對應的RBA是：100.3.50
T3時刻：使用者對block A的第2行記錄進行了update操作，這是對應的RBA是：100.4.14
T4時刻：使用者對block D的第1行記錄進行了update操作，這是對應的RBA是：100.5.15


這時候checkpoint queue裡的髒塊按照修改的先後順序有


block A <-- block B <-- block C <--- block D


T5時刻：發生了增量檢查點，DBWR將checkpoint queue中的髒塊A、B寫入磁碟(注意這時block A裡的第1、2行記錄更新後的值都已經存入了磁碟，block A和block B都已經不是髒塊了)，DBWR在寫髒塊時通知LGWR在redo日誌裡同步更新block A、block B的block written record資訊，其中就包括了寫入時的scn，controlfile裡的LRBA值會隨著寫髒塊的動作前移到RBA:100.3.50，若此時例項發生crash，恢復的起點雖然是RBA:100.3.50，但RBA:100.4.14對應的redo改變向量裡記錄的block A的scn值小於等於block written record中的scn，所以RBA:100.4.14不會被重複應用在block A上


Merge操作所生成的redolog內容分析，Merge的功能本質上是由兩表關聯後透過update、insert、delete三種DML操作及它們的組合效果所實現的。本例中merge into scott.t0108_1 t1 using scott.t0108_2 t2 on (t1.id1=t2.id2) when matched then update set col1=col2 delete where col1='a' when not matched then insert (id1,col1) values(id2,col2)可分解為如下四個步驟：
(1)update t0108_1 set col1='a' where id1=1;
(2)update t0108_1 set col1='c' where id1=3;
(3)delete t0108_1 where col1='a';
(4)insert into t0108_1 values(5,'E');
在解析redolog時我們不難看出oracle將(1)和(3)兩個步驟進行了融合，融合的結果是刪除了id1=1這條記錄，所以從redolog裡我們看到的最終順序是(3)->(2)->(4)。


知識擴充套件：大家可能注意到上面的dump結果中每一條redo change vector都有一個SEQ值，且稱其為改變序列號，這個序列號有時是接著上一個值繼續增大，有時又會從1開始重新編號，是連續編號還是重新編號取決於：
1、是否在一個邏輯操作中；2、與上一條redo變化向量的block型別是否相同，data block與undo block就屬於不同型別的block；3、是使用者級資料的修改還是後設資料的修改，諸如修改lb欄位的操作就屬於後設資料的修改。只有這三個條件都滿足，SEQ才會連續進行編號


//////////////////
// commit與不commit的情況下對於insert語句記入Redo的內容有何差異
//////////////////
>>>>> insert後不commit
drop table scott.t0115_2;
create table scott.t0115_2 (id number,col2 varchar2(1)) tablespace test1;
alter system switch logfile;
col member format a50
set linesize 100
select lf.member,l.status,l.sequence# from v$log l,v$logfile lf where l.group#=lf.group#;
MEMBER                                             STATUS            SEQUENCE#
-------------------------------------------------- ---------------- ----------
/odata/redo/ORA55/onlinelog/o1_mf_1_d4wdjm7w_.log  CURRENT                  55
/odata/redo/ORA55/onlinelog/o1_mf_2_d4wdjmmx_.log  ACTIVE                   53
/odata/redo/ORA55/onlinelog/o1_mf_3_d4wdjn3k_.log  ACTIVE                   54


insert into scott.t0115_2 values(1,'A');
select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from scott.t0115_2;
DBMS_ROWID.ROWID_BLOCK_NUMBER( DBMS_ROWID.ROWID_RELATIVE_FNO(
------------------------------ ------------------------------
                           331                              4
                           
select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/oracle/app/oracle/diag/rdbms/ora55/ora55/trace/ora55_ora_33226834.trc


alter system dump logfile '/odata/redo/ORA55/onlinelog/o1_mf_1_d4wdjm7w_.log' dba min 4 331 dba max 4 331;


***ora55_ora_33226834.trc檔案內容
REDO RECORD - Thread:1 RBA: 0x000037.0000000d.00b4 LEN: 0x005c VLD: 0x01
SCN: 0x0000.001cc020 SUBSCN:  2 12/19/2016 15:25:20
(LWN RBA: 0x000037.0000000c.0010 LEN: 0003 NST: 0001 SCN: 0x0000.001cc01f)
CHANGE #1 TYP:1 CLS:1 AFN:4 DBA:0x0100014b OBJ:14373 SCN:0x0000.001cc020 SEQ:1 OP:13.21 ENC:0 RBL:0 <---format datablock
ktspbfredo - Format Pagetable Datablock
Parent(l1) DBA:  0x01000148 typ: 1 objd: 14373 itls: 2 fmt_flag: 0 poff: 0
 cscn: 0x0000.001cc020 inc#:0


REDO RECORD - Thread:1 RBA: 0x000037.0000000f.016c LEN: 0x016c VLD: 0x01
SCN: 0x0000.001cc021 SUBSCN:  1 12/19/2016 15:25:25
(LWN RBA: 0x000037.0000000f.0010 LEN: 0002 NST: 0001 SCN: 0x0000.001cc021)
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.001cc020 SEQ:1 OP:5.2 ENC:0 RBL:0 <---undo header事務表更新
ktudh redo: slt: 0x000e sqn: 0x000007f7 flg: 0x0012 siz: 112 fbi: 0
            uba: 0x00c00251.01d8.04    pxid:  0x0000.000.00000000
CHANGE #2 TYP:0 CLS:36 AFN:3 DBA:0x00c00251 OBJ:4294967295 SCN:0x0000.001cc01f SEQ:1 OP:5.1 ENC:0 RBL:0 <---記錄insert into的逆操作
ktudb redo: siz: 112 spc: 7828 flg: 0x0012 seq: 0x01d8 rec: 0x04
            xid:  0x000a.00e.000007f7
ktubl redo: slt: 14 rci: 0 opc: 11.1 [objn: 14373 objd: 14373 tsn: 4]
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x00c00251.01d8.03
prev ctl max cmt scn:  0x0000.001cbea7  prev tx cmt scn:  0x0000.001cbea8
txn start scn:  0xffff.ffffffff  logon user: 0  prev brb: 12583504  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100014b  hdba: 0x0100014a
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0)
CHANGE #3 TYP:0 CLS:1 AFN:4 DBA:0x0100014b OBJ:14373 SCN:0x0000.001cc020 SEQ:2 OP:11.2 ENC:0 RBL:0  <---記錄insert操作
KTB Redo
op: 0x01  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x000a.00e.000007f7    uba: 0x00c00251.01d8.04
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100014b  hdba: 0x0100014a
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 8
fb: --H-FL-- lb: 0x1  cc: 2
null: --
col  0: [ 2]  c1 02
col  1: [ 1]  41


記錄內容及順序：13.21(format new block)->5.2(更新undo header事務表)->5.1(前映像寫入undo block)->11.2(insert)


如果之後再執行一次不帶commit的insert
SQL> alter system switch logfile;


System altered.


col member format a50
set linesize 100
select lf.member,l.status,l.sequence# from v$log l,v$logfile lf where l.group#=lf.group#;


MEMBER                                             STATUS            SEQUENCE#
-------------------------------------------------- ---------------- ----------
/odata/redo/ORA55/onlinelog/o1_mf_1_d4wdjm7w_.log  INACTIVE                 64
/odata/redo/ORA55/onlinelog/o1_mf_2_d4wdjmmx_.log  INACTIVE                 65
/odata/redo/ORA55/onlinelog/o1_mf_3_d4wdjn3k_.log  CURRENT                  66


SQL>  insert into scott.t0115_2 values(3,'C');


1 row created.


SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from scott.t0115_2;


DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
                               12651                                    4
                               12651                                    4
                               12653                                    4


select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/oracle/app/oracle/diag/rdbms/ora55/ora55/trace/ora55_ora_15925408.trc


SQL> alter system dump logfile '/odata/redo/ORA55/onlinelog/o1_mf_3_d4wdjn3k_.log' dba min 4 12653 dba max 4 12653;


System altered.


生成的redo日誌ora55_ora_15925408.trc內容如下：
REDO RECORD - Thread:1 RBA: 0x000042.00000015.0010 LEN: 0x0198 VLD: 0x0d
SCN: 0x0000.001ce329 SUBSCN:  1 12/19/2016 21:20:56
(LWN RBA: 0x000042.00000015.0010 LEN: 0002 NST: 0001 SCN: 0x0000.001ce329)
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x0100316d OBJ:14378 SCN:0x0000.001ce0ab SEQ:2 OP:11.2 ENC:0 RBL:0  <---insert操作
KTB Redo
op: 0x01  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x000a.00f.00000807    uba: 0x00c00202.01e0.05
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100316d  hdba: 0x0100316a
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 8
fb: --H-FL-- lb: 0x1  cc: 2
null: --
col  0: [ 2]  c1 04
col  1: [ 1]  43
CHANGE #2 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.001ce1eb SEQ:1 OP:5.2 ENC:0 RBL:0  <---更新undo header事務表
ktudh redo: slt: 0x000f sqn: 0x00000807 flg: 0x0012 siz: 112 fbi: 0
            uba: 0x00c00202.01e0.05    pxid:  0x0000.000.00000000
CHANGE #3 TYP:0 CLS:36 AFN:3 DBA:0x00c00202 OBJ:4294967295 SCN:0x0000.001ce1ea SEQ:1 OP:5.1 ENC:0 RBL:0  <---insert的逆向操作
ktudb redo: siz: 112 spc: 7606 flg: 0x0012 seq: 0x01e0 rec: 0x05
            xid:  0x000a.00f.00000807
ktubl redo: slt: 15 rci: 0 opc: 11.1 [objn: 14378 objd: 14378 tsn: 4]
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x00c00202.01e0.03
prev ctl max cmt scn:  0x0000.001ce1a8  prev tx cmt scn:  0x0000.001ce1a9
txn start scn:  0x0000.001ce2bf  logon user: 0  prev brb: 12583425  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100316d  hdba: 0x0100316a
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0)


記錄內容及順序與之前create table後直接insert的會話略有區別：11.2(insert)->5.2(更新undo header事務表)->5.1(前映像寫入undo block)


>>>>> insert後commit
drop table scott.t0115_2;
create table scott.t0115_2 (id number,col2 varchar2(1)) tablespace test1;
alter system switch logfile;


col member format a50
set linesize 100
select lf.member,l.status,l.sequence# from v$log l,v$logfile lf where l.group#=lf.group#;
MEMBER                                             STATUS            SEQUENCE#
-------------------------------------------------- ---------------- ----------
/odata/redo/ORA55/onlinelog/o1_mf_1_d4wdjm7w_.log  INACTIVE                 61
/odata/redo/ORA55/onlinelog/o1_mf_2_d4wdjmmx_.log  ACTIVE                   62
/odata/redo/ORA55/onlinelog/o1_mf_3_d4wdjn3k_.log  CURRENT                  63


insert into scott.t0115_2 values(1,'A');    <---在create table相同的會話裡執行insert
commit;


select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from scott.t0115_2;
DBMS_ROWID.ROWID_BLOCK_NUMBER( DBMS_ROWID.ROWID_RELATIVE_FNO(
------------------------------ ------------------------------
                           358                              4
                                                      
select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/oracle/app/oracle/diag/rdbms/ora55/ora55/trace/ora55_ora_18022448.trc




alter system dump logfile '/odata/redo/ORA55/onlinelog/o1_mf_3_d4wdjn3k_.log' dba min 4 358 dba max 4 358;


***ora55_ora_18022448.trc檔案內容
REDO RECORD - Thread:1 RBA: 0x00003a.0000000d.00f4 LEN: 0x005c VLD: 0x01
SCN: 0x0000.001cc2e1 SUBSCN:  1 12/19/2016 15:52:41
(LWN RBA: 0x00003a.0000000d.0010 LEN: 0003 NST: 0001 SCN: 0x0000.001cc2e1)
CHANGE #1 TYP:1 CLS:1 AFN:4 DBA:0x0100015e OBJ:14375 SCN:0x0000.001cc2e0 SEQ:1 OP:13.21 ENC:0 RBL:0  <---format 
ktspbfredo - Format Pagetable Datablock
Parent(l1) DBA:  0x01000158 typ: 1 objd: 14375 itls: 2 fmt_flag: 0 poff: 0
 cscn: 0x0000.001cc2e1 inc#:0


REDO RECORD - Thread:1 RBA: 0x00003a.0000000e.0094 LEN: 0x016c VLD: 0x01
SCN: 0x0000.001cc2e1 SUBSCN:  2 12/19/2016 15:52:41
CHANGE #1 TYP:0 CLS:33 AFN:3 DBA:0x00c00100 OBJ:4294967295 SCN:0x0000.001cc299 SEQ:1 OP:5.2 ENC:0 RBL:0  <---undo header事務表
ktudh redo: slt: 0x0020 sqn: 0x000000cb flg: 0x0012 siz: 112 fbi: 0
            uba: 0x00c000cb.008d.0d    pxid:  0x0000.000.00000000
CHANGE #2 TYP:0 CLS:34 AFN:3 DBA:0x00c000cb OBJ:4294967295 SCN:0x0000.001cc298 SEQ:2 OP:5.1 ENC:0 RBL:0  <---insert into ...的逆操作
ktudb redo: siz: 112 spc: 4338 flg: 0x0012 seq: 0x008d rec: 0x0d
            xid:  0x0009.020.000000cb
ktubl redo: slt: 32 rci: 0 opc: 11.1 [objn: 14375 objd: 14375 tsn: 4]
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x00c000cb.008d.0b
prev ctl max cmt scn:  0x0000.001cb7da  prev tx cmt scn:  0x0000.001cb7fd
txn start scn:  0xffff.ffffffff  logon user: 0  prev brb: 12583113  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100015e  hdba: 0x0100015a
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0)
CHANGE #3 TYP:0 CLS:1 AFN:4 DBA:0x0100015e OBJ:14375 SCN:0x0000.001cc2e1 SEQ:1 OP:11.2 ENC:0 RBL:0   <---insert into ...操作
KTB Redo
op: 0x01  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x0009.020.000000cb    uba: 0x00c000cb.008d.0d
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100015e  hdba: 0x0100015a
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 8
fb: --H-FL-- lb: 0x1  cc: 2
null: --
col  0: [ 2]  c1 02
col  1: [ 1]  41


我們發現雖然釋出了commit，但dump出來的redo裡仍然沒有commit的操作。redo的記錄順序為：
下面另啟一個session往表中插入一條記錄
 
SQL> select * from scott.t0115_2;


        ID C
---------- -
         1 A


SQL> insert into scott.t0115_2 values(2,'B');


1 row created.


SQL> commit;


Commit complete.


col member format a50
set linesize 100
select lf.member,l.status,l.sequence# from v$log l,v$logfile lf where l.group#=lf.group#;


MEMBER                                             STATUS            SEQUENCE#
-------------------------------------------------- ---------------- ----------
/odata/redo/ORA55/onlinelog/o1_mf_1_d4wdjm7w_.log  INACTIVE                 64
/odata/redo/ORA55/onlinelog/o1_mf_2_d4wdjmmx_.log  CURRENT                  65
/odata/redo/ORA55/onlinelog/o1_mf_3_d4wdjn3k_.log  INACTIVE                 63


SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from scott.t0115_2;


DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
                               12651                                    4
                               12651                                    4


SQL> select value from v$diag_info where name='Default Trace File';


VALUE
----------------------------------------------------------------------------------------------------
/oracle/app/oracle/diag/rdbms/ora55/ora55/trace/ora55_ora_15925404.trc


SQL> alter system dump logfile '/odata/redo/ORA55/onlinelog/o1_mf_2_d4wdjmmx_.log' dba min 4 12651 dba max 4 12651;


System altered.


***ora55_ora_15925404.trc檔案內容：
REDO RECORD - Thread:1 RBA: 0x000041.00000b0b.0010 LEN: 0x01e0 VLD: 0x0d
SCN: 0x0000.001ce21e SUBSCN:  1 12/19/2016 21:08:25
(LWN RBA: 0x000041.00000b0b.0010 LEN: 0001 NST: 0001 SCN: 0x0000.001ce21d)
CHANGE #1 TYP:2 CLS:1 AFN:4 DBA:0x0100316b OBJ:14378 SCN:0x0000.001ce0ad SEQ:1 OP:11.2 ENC:0 RBL:0   <---insert
KTB Redo 
op: 0x01  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x0003.00d.000000a4    uba: 0x00c000a3.0081.16
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100316b  hdba: 0x0100316a
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1) size/delt: 8
fb: --H-FL-- lb: 0x2  cc: 2
null: --
col  0: [ 2]  c1 03
col  1: [ 1]  42
CHANGE #2 TYP:0 CLS:21 AFN:3 DBA:0x00c000a0 OBJ:4294967295 SCN:0x0000.001ce1dd SEQ:1 OP:5.2 ENC:0 RBL:0   <---更新undo header事務表
ktudh redo: slt: 0x000d sqn: 0x000000a4 flg: 0x0012 siz: 112 fbi: 0
            uba: 0x00c000a3.0081.16    pxid:  0x0000.000.00000000
CHANGE #3 TYP:0 CLS:21 AFN:3 DBA:0x00c000a0 OBJ:4294967295 SCN:0x0000.001ce21e SEQ:1 OP:5.4 ENC:0 RBL:0   <---commit
ktucm redo: slt: 0x000d sqn: 0x000000a4 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c000a3.0081.16 ext: 0 spc: 5438 fbi: 0 
CHANGE #4 TYP:0 CLS:22 AFN:3 DBA:0x00c000a3 OBJ:4294967295 SCN:0x0000.001ce1dc SEQ:1 OP:5.1 ENC:0 RBL:0   <---insert的逆向操作
ktudb redo: siz: 112 spc: 5552 flg: 0x0012 seq: 0x0081 rec: 0x16
            xid:  0x0003.00d.000000a4  
ktubl redo: slt: 13 rci: 0 opc: 11.1 [objn: 14378 objd: 14378 tsn: 4]
Undo type:  Regular undo        Begin trans    Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
             0x00000000  prev ctl uba: 0x00c000a3.0081.15 
prev ctl max cmt scn:  0x0000.001cc3ee  prev tx cmt scn:  0x0000.001cc3ef 
txn start scn:  0x0000.00000000  logon user: 0  prev brb: 12583274  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01 
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100316b  hdba: 0x0100316a
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1)
END OF REDO DUMP


階段結論：insert後不commit的場景下，redolog裡不會出現commit；insert如果與create table在一個會話裡那麼即便insert後執行了commit，在redolog裡也有可能找不到commit，這時在redolog裡會多出一個OP:13.21(format pagetable datablock)的操作；如果insert在單獨的會話裡那麼commit的記錄會在redo裡找到。
上述四個場景產生的redo順序分別是：
(1) insert與create table在一個session裡，insert後不commit：13.21(format new block)->5.2(更新undo header事務表)->5.1(前映像寫入undo block)->11.2(insert)
(2) insert在獨立的session裡，insert後不commit：11.2(insert)->5.2(更新undo header事務表)->5.1(前映像寫入undo block)
(3) insert與create table在一個session裡，insert後commit：13.21(format new block)->5.2(更新undo header事務表)->5.1(前映像寫入undo block)->11.2(insert)
(4) insert在獨立的session裡，insert後commit：11.2(insert)->5.2(更新undo header事務表)->5.4(commit)->5.1(前映像寫入undo block)


//////////////////
// commit與不commit的情況下對於update語句記入Redo的內容有何差異
//////////////////
>>>>> update後不commit
SQL> select * from scott.t0115_2;


        ID C
---------- -
         1 A


SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from scott.t0115_2;


DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
                                 350                                    4


alter system switch logfile;


col member format a50
set linesize 100
select lf.member,l.status,l.sequence# from v$log l,v$logfile lf where l.group#=lf.group#;
MEMBER                                             STATUS            SEQUENCE#
-------------------------------------------------- ---------------- ----------
/odata/redo/ORA55/onlinelog/o1_mf_1_d4wdjm7w_.log  ACTIVE                   58
/odata/redo/ORA55/onlinelog/o1_mf_2_d4wdjmmx_.log  CURRENT                  59
/odata/redo/ORA55/onlinelog/o1_mf_3_d4wdjn3k_.log  INACTIVE                 57


update scott.t0115_2 set col2='a' where id=1;


alter system dump logfile '/odata/redo/ORA55/onlinelog/o1_mf_2_d4wdjmmx_.log' dba min 4 350 dba max 4 350;


select value from v$diag_info where name='Default Trace File';


VALUE
----------------------------------------------------------------------------------------------------
/oracle/app/oracle/diag/rdbms/ora55/ora55/trace/ora55_ora_42598518.trc


***ora55_ora_42598518.trc內容
REDO RECORD - Thread:1 RBA: 0x00003b.00000004.0010 LEN: 0x01fc VLD: 0x0d
SCN: 0x0000.001ccab1 SUBSCN:  1 12/19/2016 17:12:34
(LWN RBA: 0x00003b.00000004.0010 LEN: 0002 NST: 0001 SCN: 0x0000.001ccab1)
CHANGE #1 TYP:2 CLS:1 AFN:4 DBA:0x0100015e OBJ:14375 SCN:0x0000.001cc2e2 SEQ:1 OP:11.19 ENC:0 RBL:0  <---update對應的redo
KTB Redo
op: 0x11  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x000a.011.000007fc    uba: 0x00c001b2.01d9.48
Block cleanout record, scn:  0x0000.001cca92 ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x0000.001cc2e2
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 1
ncol: 2 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x0100015e  hdba: 0x0100015a
itli: 2  ispac: 0  maxfr: 4858
vect = 3
col  1: [ 1]  61
CHANGE #2 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.001cca20 SEQ:1 OP:5.2 ENC:0 RBL:0   <---undo header事務槽更新
ktudh redo: slt: 0x0011 sqn: 0x000007fc flg: 0x0012 siz: 164 fbi: 0
            uba: 0x00c001b2.01d9.48    pxid:  0x0000.000.00000000
CHANGE #3 TYP:0 CLS:36 AFN:3 DBA:0x00c001b2 OBJ:4294967295 SCN:0x0000.001cca1f SEQ:1 OP:5.1 ENC:0 RBL:0   <---undo的redo
ktudb redo: siz: 164 spc: 1228 flg: 0x0012 seq: 0x01d9 rec: 0x48
            xid:  0x000a.011.000007fc
ktubl redo: slt: 17 rci: 0 opc: 11.1 [objn: 14375 objd: 14375 tsn: 4]
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x00c001b2.01d9.46
prev ctl max cmt scn:  0x0000.001cc7a3  prev tx cmt scn:  0x0000.001cc7a4
txn start scn:  0x0000.001cca9c  logon user: 0  prev brb: 12583311  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 1
ncol: 2 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x0100015e  hdba: 0x0100015a
itli: 2  ispac: 0  maxfr: 4858
vect = 3
col  1: [ 1]  41
END OF REDO DUMP


>>>>> update後commit
SQL> select * from scott.t0115_2;


        ID C
---------- -
         1 A


SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from scott.t0115_2;


DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
                                 350                                    4


alter system switch logfile;


col member format a50
set linesize 100
select lf.member,l.status,l.sequence# from v$log l,v$logfile lf where l.group#=lf.group#;
MEMBER                                             STATUS            SEQUENCE#
-------------------------------------------------- ---------------- ----------
/odata/redo/ORA55/onlinelog/o1_mf_1_d4wdjm7w_.log  INACTIVE                 61
/odata/redo/ORA55/onlinelog/o1_mf_2_d4wdjmmx_.log  CURRENT                  62
/odata/redo/ORA55/onlinelog/o1_mf_3_d4wdjn3k_.log  INACTIVE                 60


update scott.t0115_2 set col2='b' where id=1;
commit;


alter system dump logfile '/odata/redo/ORA55/onlinelog/o1_mf_2_d4wdjmmx_.log' dba min 4 350 dba max 4 350;


select value from v$diag_info where name='Default Trace File';


VALUE
----------------------------------------------------------------------------------------------------
/oracle/app/oracle/diag/rdbms/ora55/ora55/trace/ora55_ora_8323230.trc


***ora55_ora_8323230.trc內容如下：
REDO RECORD - Thread:1 RBA: 0x00003e.00000002.0010 LEN: 0x025c VLD: 0x0d            
SCN: 0x0000.001cdcbd SUBSCN:  1 12/19/2016 20:19:19
(LWN RBA: 0x00003e.00000002.0010 LEN: 0002 NST: 0001 SCN: 0x0000.001cdcbc)
CHANGE #1 TYP:2 CLS:1 AFN:4 DBA:0x0100015e OBJ:14375 SCN:0x0000.001ccd96 SEQ:2 OP:11.19 ENC:0 RBL:0  <---對應update
KTB Redo 
op: 0x11  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x000a.01f.00000808    uba: 0x00c001c2.01dd.11
Block cleanout record, scn:  0x0000.001cdcbb ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x0000.001ccd96
Array Update of 1 rows: 
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 1
ncol: 2 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x0100015e  hdba: 0x0100015a
itli: 2  ispac: 0  maxfr: 4858
vect = 3
col  1: [ 1]  62
CHANGE #2 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.001cdc0a SEQ:1 OP:5.2 ENC:0 RBL:0  <---undo header事務表更新
ktudh redo: slt: 0x001f sqn: 0x00000808 flg: 0x0012 siz: 188 fbi: 0
            uba: 0x00c001c2.01dd.11    pxid:  0x0000.000.00000000
CHANGE #3 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.001cdcbd SEQ:1 OP:5.4 ENC:0 RBL:0  <---commit
ktucm redo: slt: 0x001f sqn: 0x00000808 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c001c2.01dd.11 ext: 3 spc: 5068 fbi: 0 
CHANGE #4 TYP:0 CLS:36 AFN:3 DBA:0x00c001c2 OBJ:4294967295 SCN:0x0000.001cdc09 SEQ:1 OP:5.1 ENC:0 RBL:0  <---undo的redo
ktudb redo: siz: 188 spc: 5258 flg: 0x0012 seq: 0x01dd rec: 0x11
            xid:  0x000a.01f.00000808  
ktubl redo: slt: 31 rci: 0 opc: 11.1 [objn: 14375 objd: 14375 tsn: 4]
Undo type:  Regular undo        Begin trans    Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
             0x00000000  prev ctl uba: 0x00c001c2.01dd.10 
prev ctl max cmt scn:  0x0000.001cdbcd  prev tx cmt scn:  0x0000.001cdbcf 
txn start scn:  0x0000.00000000  logon user: 0  prev brb: 12583360  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo 
op: 0x04  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x000a.00a.000007f9 uba: 0x00c001b0.01d9.04
                      flg: C---    lkc:  0     scn: 0x0000.001ccbd3
Array Update of 1 rows: 
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 1
ncol: 2 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x0100015e  hdba: 0x0100015a
itli: 2  ispac: 0  maxfr: 4858
vect = 3
col  1: [ 1]  62


階段小結：
update不commit的情況下，redo裡的操作順序是：11.19(update)->5.2(undo header事務表更新)->5.1(前映像寫入undo block);
update且commit的情況下，redo裡的操作順序是：11.19(update)->5.2(undo header事務表更新)->5.4(commit)->5.1(前映像寫入undo block);


//////////////////
// commit與不commit的情況下對於delete語句記入Redo的內容有何差異
//////////////////
>>>>> delete後不commit
SQL> select * from scott.t0115_2;


        ID C
---------- -
         1 A
         2 B
         3 C


SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from scott.t0115_2;


DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
                               12651                                    4
                               12651                                    4
                               12653                                    4


alter system switch logfile;


col member format a50
set linesize 100
select lf.member,l.status,l.sequence# from v$log l,v$logfile lf where l.group#=lf.group#;
MEMBER                                             STATUS            SEQUENCE#
-------------------------------------------------- ---------------- ----------
/odata/redo/ORA55/onlinelog/o1_mf_1_d4wdjm7w_.log  ACTIVE                   73
/odata/redo/ORA55/onlinelog/o1_mf_2_d4wdjmmx_.log  CURRENT                  74
/odata/redo/ORA55/onlinelog/o1_mf_3_d4wdjn3k_.log  INACTIVE                 72


delete scott.t0115_2 where id=3;


alter system dump logfile '/odata/redo/ORA55/onlinelog/o1_mf_2_d4wdjmmx_.log' dba min 4 12653 dba max 4 12653;


select value from v$diag_info where name='Default Trace File';


VALUE
----------------------------------------------------------------------------------------------------
/oracle/app/oracle/diag/rdbms/ora55/ora55/trace/ora55_ora_34013408.trc


***ora55_ora_34013408.trc內容
REDO RECORD - Thread:1 RBA: 0x00004a.00000104.0010 LEN: 0x01c0 VLD: 0x0d
SCN: 0x0000.002277c0 SUBSCN:  1 12/28/2016 18:06:16
(LWN RBA: 0x00004a.00000104.0010 LEN: 0001 NST: 0001 SCN: 0x0000.002277bf)
CHANGE #1 TYP:2 CLS:1 AFN:4 DBA:0x0100316d OBJ:14378 SCN:0x0000.001cfebf SEQ:1 OP:11.3 ENC:0 RBL:0   <---對應delete
KTB Redo
op: 0x11  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x000a.00f.00000bb8    uba: 0x00c00115.03b2.07
Block cleanout record, scn:  0x0000.002277c0 ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x0000.001cfebf
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100316d  hdba: 0x0100316a
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0)    <---block 4/12653的第一行
CHANGE #2 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.002277ad SEQ:1 OP:5.2 ENC:0 RBL:0   <---undo header事務表的更新
ktudh redo: slt: 0x000f sqn: 0x00000bb8 flg: 0x0012 siz: 156 fbi: 0
            uba: 0x00c00115.03b2.07    pxid:  0x0000.000.00000000
CHANGE #3 TYP:0 CLS:36 AFN:3 DBA:0x00c00115 OBJ:4294967295 SCN:0x0000.002277ac SEQ:1 OP:5.1 ENC:0 RBL:0   <---undo的redo
ktudb redo: siz: 156 spc: 7334 flg: 0x0012 seq: 0x03b2 rec: 0x07
            xid:  0x000a.00f.00000bb8
ktubl redo: slt: 15 rci: 0 opc: 11.1 [objn: 14378 objd: 14378 tsn: 4]
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x00c00115.03b2.05
prev ctl max cmt scn:  0x0000.0022776c  prev tx cmt scn:  0x0000.0022776d
txn start scn:  0x0000.00000000  logon user: 0  prev brb: 12583188  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100316d  hdba: 0x0100316a
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 8
fb: --H-FL-- lb: 0x0  cc: 2
null: --
col  0: [ 2]  c1 04
col  1: [ 1]  43
END OF REDO DUMP


>>>>> delete後commit
將上一個場景delete後未提交的事務透過alter system disconnect session強行kill掉，開測下面的場景


SQL> select * from scott.t0115_2;


        ID C
---------- -
         1 A
         2 B
         3 C


SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from scott.t0115_2;


DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
                               12651                                    4
                               12651                                    4
                               12653                                    4




alter system switch logfile;


col member format a50
set linesize 100
select lf.member,l.status,l.sequence# from v$log l,v$logfile lf where l.group#=lf.group#;
MEMBER                                             STATUS            SEQUENCE#
-------------------------------------------------- ---------------- ----------
/odata/redo/ORA55/onlinelog/o1_mf_1_d4wdjm7w_.log  INACTIVE                 73
/odata/redo/ORA55/onlinelog/o1_mf_2_d4wdjmmx_.log  ACTIVE                   74
/odata/redo/ORA55/onlinelog/o1_mf_3_d4wdjn3k_.log  CURRENT                  75


delete scott.t0115_2 where id=3;
commit;


alter system dump logfile '/odata/redo/ORA55/onlinelog/o1_mf_3_d4wdjn3k_.log' dba min 4 12653 dba max 4 12653;


select value from v$diag_info where name='Default Trace File';


VALUE
----------------------------------------------------------------------------------------------------
/oracle/app/oracle/diag/rdbms/ora55/ora55/trace/ora55_ora_20775002.trc


***ora55_ora_20775002.trc內容如下：
REDO RECORD - Thread:1 RBA: 0x00004b.00000004.014c LEN: 0x0040 VLD: 0x01
SCN: 0x0000.00227a73 SUBSCN:  1 12/28/2016 18:21:50
(LWN RBA: 0x00004b.00000004.0010 LEN: 0001 NST: 0001 SCN: 0x0000.00227a73)
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x0100316d OBJ:14378 SCN:0x0000.00227a60 SEQ:1 OP:4.1 ENC:0 RBL:0   <---延遲塊清除操作
Block cleanout record, scn:  0x0000.00227a73 ver: 0x01 opt: 0x01, entries follow...


REDO RECORD - Thread:1 RBA: 0x00004b.00000005.0010 LEN: 0x0070 VLD: 0x06                            <---block written record
SCN: 0x0000.00227a8c SUBSCN:  1 12/28/2016 18:23:02
(LWN RBA: 0x00004b.00000005.0010 LEN: 0001 NST: 0001 SCN: 0x0000.00227a8c)
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
 Block Written - afn: 4 rdba: 0x0100316d BFT:(1024,16789869) non-BFT:(4,12653)
                   scn: 0x0000.00227a73 seq: 0x01 flg:0x04


REDO RECORD - Thread:1 RBA: 0x00004b.00000006.0010 LEN: 0x00f0 VLD: 0x05                            <---Alter system disconnect session引起的回滾操作，重新往表中插入id=3的記錄
SCN: 0x0000.00227aa4 SUBSCN:  1 12/28/2016 18:24:11
(LWN RBA: 0x00004b.00000006.0010 LEN: 0001 NST: 0001 SCN: 0x0000.00227aa4)
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x0100316d OBJ:14378 SCN:0x0000.00227a73 SEQ:1 OP:11.2 ENC:0 RBL:0
KTB Redo
op: 0x03  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: IRP row dependencies Disabled
  xtype: XR flags: 0x00000000  bdba: 0x0100316d  hdba: 0x0100316a
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 8
fb: --H-FL-- lb: 0x0  cc: 2
null: --
col  0: [ 2]  c1 04
col  1: [ 1]  43
CHANGE #2 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.00227827 SEQ:2 OP:5.11 ENC:0 RBL:0  <---5.11操作猜測可能是對應使用undo block進行回滾的操作
ktubu redo: slt: 15 rci: 0 opc: 11.1 objn: 14378 objd: 14378 tsn: 4
Undo type:  Regular undo       Undo type:  User undo done    Begin trans    Last buffer split:  No   <---Undo type標示為User undo done表明undo block的內容被apply到了data block
Tablespace Undo:  No
             0x00000000
BuExt idx: 0 flg2: 0


REDO RECORD - Thread:1 RBA: 0x00004b.00000007.0010 LEN: 0x0198 VLD: 0x0d
SCN: 0x0000.00227aa6 SUBSCN:  1 12/28/2016 18:24:11
(LWN RBA: 0x00004b.00000007.0010 LEN: 0002 NST: 0001 SCN: 0x0000.00227aa6)
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x0100316d OBJ:14378 SCN:0x0000.00227aa4 SEQ:1 OP:11.3 ENC:0 RBL:0  <---delete操作
KTB Redo
op: 0x01  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x000a.00a.00000bbb    uba: 0x00c00116.03b2.02
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100316d  hdba: 0x0100316a
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0)
CHANGE #2 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.00227aa5 SEQ:1 OP:5.2 ENC:0 RBL:0  <---undo header事務表更新
ktudh redo: slt: 0x000a sqn: 0x00000bbb flg: 0x0012 siz: 156 fbi: 0
            uba: 0x00c00116.03b2.02    pxid:  0x0000.000.00000000
CHANGE #3 TYP:0 CLS:36 AFN:3 DBA:0x00c00116 OBJ:4294967295 SCN:0x0000.00227825 SEQ:2 OP:5.1 ENC:0 RBL:0  <---undo的redo
ktudb redo: siz: 156 spc: 8068 flg: 0x0012 seq: 0x03b2 rec: 0x02
            xid:  0x000a.00a.00000bbb
ktubl redo: slt: 10 rci: 0 opc: 11.1 [objn: 14378 objd: 14378 tsn: 4]
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x00c00116.03b2.01
prev ctl max cmt scn:  0x0000.0022776f  prev tx cmt scn:  0x0000.00227773
txn start scn:  0x0000.00227a73  logon user: 0  prev brb: 12583188  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100316d  hdba: 0x0100316a
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 8
fb: --H-FL-- lb: 0x0  cc: 2
null: --
col  0: [ 2]  c1 04
col  1: [ 1]  43
END OF REDO DUMP


階段小結：
從上述兩段dump的結果來看，delete後commit的情況下並沒有發現OP:5.4的操作，與不commit的情況產生的redo記錄並無多大差別，均按照如下順序進行
11.3(delete)->5.2(undo header事務表更新)->5.1(前映像寫入undo block);


再進行一次delete後commit的測試：
SQL> select * from scott.t0115_2;


        ID C
---------- -
         1 A
         2 B
         
SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from scott.t0115_2;


DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
                               12651                                    4
                               12651                                    4


alter system switch logfile;


col member format a50
set linesize 100
select lf.member,l.status,l.sequence# from v$log l,v$logfile lf where l.group#=lf.group#;
MEMBER                                             STATUS            SEQUENCE#
-------------------------------------------------- ---------------- ----------
/odata/redo/ORA55/onlinelog/o1_mf_1_d4wdjm7w_.log  CURRENT                  76
/odata/redo/ORA55/onlinelog/o1_mf_2_d4wdjmmx_.log  INACTIVE                 74
/odata/redo/ORA55/onlinelog/o1_mf_3_d4wdjn3k_.log  INACTIVE                 75


delete scott.t0115_2 where id=2;
commit;


alter system dump logfile '/odata/redo/ORA55/onlinelog/o1_mf_1_d4wdjm7w_.log' dba min 4 12651 dba max 4 12651;


select value from v$diag_info where name='Default Trace File';


VALUE
----------------------------------------------------------------------------------------------------
/oracle/app/oracle/diag/rdbms/ora55/ora55/trace/ora55_ora_14811308.trc


***ora55_ora_14811308.trc內容如下：
REDO RECORD - Thread:1 RBA: 0x00004c.00000002.0010 LEN: 0x0228 VLD: 0x0d
SCN: 0x0000.00227d5e SUBSCN:  1 12/28/2016 18:56:42
(LWN RBA: 0x00004c.00000002.0010 LEN: 0002 NST: 0001 SCN: 0x0000.00227d5c)
CHANGE #1 TYP:2 CLS:1 AFN:4 DBA:0x0100316b OBJ:14378 SCN:0x0000.001ce21e SEQ:2 OP:11.3 ENC:0 RBL:0   <---delete操作
KTB Redo
op: 0x11  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x0007.00f.000000e3    uba: 0x00c00db9.006a.0e
Block cleanout record, scn:  0x0000.00227d5d ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x0000.001ce0ad
  itli: 2  flg: 2  scn: 0x0000.001ce21e
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100316b  hdba: 0x0100316a
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1)
CHANGE #2 TYP:0 CLS:29 AFN:3 DBA:0x00c000e0 OBJ:4294967295 SCN:0x0000.00227b93 SEQ:1 OP:5.2 ENC:0 RBL:0   <---undo header事務表更新
ktudh redo: slt: 0x000f sqn: 0x000000e3 flg: 0x0012 siz: 180 fbi: 0
            uba: 0x00c00db9.006a.0e    pxid:  0x0000.000.00000000
CHANGE #3 TYP:0 CLS:29 AFN:3 DBA:0x00c000e0 OBJ:4294967295 SCN:0x0000.00227d5e SEQ:1 OP:5.4 ENC:0 RBL:0   <---commit
ktucm redo: slt: 0x000f sqn: 0x000000e3 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c00db9.006a.0e ext: 2 spc: 6066 fbi: 0
CHANGE #4 TYP:0 CLS:30 AFN:3 DBA:0x00c00db9 OBJ:4294967295 SCN:0x0000.00227b92 SEQ:2 OP:5.1 ENC:0 RBL:0   <---undo的redo，對於delete操作來講記錄的就是insert操作
ktudb redo: siz: 180 spc: 6248 flg: 0x0012 seq: 0x006a rec: 0x0e
            xid:  0x0007.00f.000000e3
ktubl redo: slt: 15 rci: 0 opc: 11.1 [objn: 14378 objd: 14378 tsn: 4]
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x00c00db9.006a.0c
prev ctl max cmt scn:  0x0000.00225a73  prev tx cmt scn:  0x0000.00225a74
txn start scn:  0x0000.00000000  logon user: 0  prev brb: 12586411  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x04  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x0001.01a.00000097 uba: 0x00c00085.0091.39
                      flg: C---    lkc:  0     scn: 0x0000.001ce0ad
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100316b  hdba: 0x0100316a
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1) size/delt: 8
fb: --H-FL-- lb: 0x0  cc: 2
null: --
col  0: [ 2]  c1 03
col  1: [ 1]  42
END OF REDO DUMP


階段小結：
這次delete後commit的順序恢復到了正常的順序
11.3(delete)->5.2(undo header事務表更新)->5.4(commit)->5.1(前映像寫入undo block);
因此，我們可以看出在delete操作後的commit再某些場景下是不會記錄redo的