网上已有不少介绍与分析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的
本文的测试案例旨在回答以下几个问题：
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的
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/53956/viewspace-2132891/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/53956/viewspace-2132891/
11万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
