ITPub博客

首页 > 数据库 > Oracle > commit操作是否一定会被记录到redo等问题的研究

commit操作是否一定会被记录到redo等问题的研究

原创 Oracle 作者:oliseh 时间:2017-01-24 19:22:12 0 删除 编辑
网上已有不少介绍与分析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的

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/53956/viewspace-2132891/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1638298