总结：imu模式下dml语句所产生的redo record式，是先有操作的 change rector，再有向向undo段头的事务表写事务信息的change rector，再提交操作的change rector后，才进行把数据修改前放到undo的change rector。 注意：实验中insert和delete是先后做的，updat
总结：imu模式下dml语句所产生的redo record格式，是先有操作的 change rector，再有向向undo段头的事务表写事务信息的change rector，再提交操作的change rector后，才进行把数据修改前值放到undo的change rector。
注意：实验中insert和delete是先后做的，update操作是其它时间做的，update实验时的表数据和另两步不一样。
dml操作的change rector产生顺序汇总如下： --dml实验及dump的redo日志见下面具体实验步骤。insert --涉及有索引的字段
change #1 op:11.2 --插入操作
change #2 op:5.2 --operation code 向undo段头的事务表写事务信息-事务开始
change #3 op:10.2 --10.2 是插入索引叶子块
change #4 op:5.4 ----提交
change #5 op:5.1 --把表内数据修改前值放到undo--objn: 22327，插入的表的对象id。
change #6 op:5.1 --把索引数据修改前值放到undo--objn: 22818，索引对象id。
一条insert语句为什么写了两次op:5.1操作，是因为存在索引。
#################
update：--这个操作没涉及索引的字段
change#1 op:11.19 --或者op:11.5都是--update语句，开始修改数据，
change#2 op:5.2 --operation code 向undo段头的事务表写事务信息-事务开始
change#3 op:11.19 --或者op:11.5都是--update语句，开始修改数据，
change #4 op:5.4 --提交
change #5 op:5.1 --把表内数据修改前值放到undo
change #6 op:5.1 --把表内数据修改前值放到undo
################
delete: --涉及有索引的字段
change #1 op:11.3 --delete语句的操作
change #2 op:5.2 --operation code 向undo段头的事务表写事务信息-事务开始
change #3 op:10.4 --删除索引叶子块
change #4 op:5.4 --提交
change #5 op:5.1 --把表内数据修改前值放到undo
change #6 op:5.1 --把索引数据修改前值放到undo
一条delete语句为什么写了两次op:5.1操作，是因为存在索引。
以上insert及delete时涉及的对索引的操作，如表上无索引，将涉及索引的change #条目去除，就是正常的change 产生顺序。
具体实验详情如下：--确保环境已经改为使用imu。alter system set _in_memory_undo=true;
insert操作：sys@ bys3>alter system switch logfile;
system altered.
sys@ bys3>col member for a30
sys@ bys3>select a.group#,a.sequence#,a.archived,a.status,b.type,b.member from v$log a,v$logfile b where a.group#=b.group#;
group# sequence# arc status type member
---------- ---------- --- ---------------- ------- ------------------------------
1 322 yes inactive online /u01/oradata/bys3/redo01.log
2 323 yes active online /u01/oradata/bys3/redo02.log
3 324 no current online /u01/oradata/bys3/redo03.log
sys@ bys3>conn bys/bys
connected.
bys@ bys3>select * from dept;
deptno dname loc
---------- -------------- -------------
10 accounting new york
20 research dallas
40 operations boston
11 database bj
22 dataoracle sh
bys@ bys3>select a.index_owner,a.index_name,b.object_id,a.table_owner,a.table_name,a.column_name from all_ind_columns a,dba_objects b where a.index_owner='bys' and a.index_name=b.object_name;
index_owne index_name object_id table_owne table_name column_nam
---------- ---------- ---------- ---------- ------------------------------ ----------
bys indtext 22818 bys dept deptno
bys@ bys3>set time on
19:35:01 bys@ bys3>insert into dept values(66,'imutest2','zhengzhou');
1 row created.
19:35:33 bys@ bys3>commit;
commit complete.
19:35:40 bys@ bys3>
另一会话：
bys@ bys3>alter system dump logfile '/u01/oradata/bys3/redo03.log';
system altered.
bys@ bys3>select value from v$diag_info where name like 'de%' ;
value
----------------------------------------------------------------------------------------------------
/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_28763.trc
bys@ bys3>select chr(to_number(substr(replace('c1 43',' '),rownum*2-1,2),'xxxxxxxx')),chr(to_number(substr(replace('69 6d 75 74 65 73 74 32',' '),rownum*2-1,2),'xxxxxxxx')),chr(to_number(substr(replace('7a 68 65 6e 67 7a 68 6f 75',' '),rownum*2-1,2),'xxxxxxxx')) from v$bh where rownumchr( chr( chr(
---- ---- ----
? i z
c m h
u e
t n
e g
s z
t h
2 o
#####################################
redo record - thread:1 rba: 0x000144.0000000e.0010 len: 0x02e4 vld: 0x0d
scn: 0x0000.00729c6b subscn: 1 01/08/2014 19:35:40
(lwn rba: 0x000144.0000000e.0010 len: 0002 nst: 0001 scn: 0x0000.00729c6a)
#######一个redo record: record头+change vector组成（一个cv就是一个操作）
以上是日志头,thread:1 线程号，rac时会有1，2等
ba: 0x000144.0000000e.0010 将16进制转换为十进制分别是日志文件号、日志块号、在块上第n字节
vld: 0x0d日志类型--imu模式时是这个；非imu时是：vld: 0x05
scn: 0x0000.00729c6b subscn: 1 01/08/2014 19:35:40
bys@ bys3>select scn_to_timestamp(to_number('729c6b','xxxxxxxx')) from dual;
scn_to_timestamp(to_number('729c6b','xxxxxxxx'))
---------------------------------------------------------------------------
08-jan-14 07.35.38.000000000 pm
--是此redo条目产生时的scn号，转为十进制现转为时间戳为：19:35:33， 插入语句完成是在19:35:33 bys@ bys3>commit;
(lwn rba: 0x000144.0000000e.0010 len: 0002 nst: 0001 scn: 0x0000.00729c6a)
括号中scn: 0x0000.00729c6a 比上一行：scn: 0x0000.00729c6b 少了1个scn。
################
change #1 typ:2 cls:1 afn:4 dba:0x010000fd obj:22327 scn:0x0000.00719188 seq:3op:11.2 enc:0 rbl:0
##afn:4，操作是在4号文件做的-dba_data_files.file_id；obj:22327--操作的对象的object_id。op:11.2--插入操作
ktb redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: f xid: 0x0001.00f.00000f13 uba: 0x00c017b7.0262.08
kdo op code: irp row dependencies disabled --这个是irp --insert row piece
xtype: xa flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2) size/delt: 25
fb: --h-fl-- lb: 0x1 cc: 3
null: ---
col 0: [ 2] c1 43 --col 0: [ 2]，第一列，2个字符
col 1: [ 8] 69 6d 75 74 65 73 74 32 --第2列，8个字符
col 2: [ 9] 7a 68 65 6e 67 7a 68 6f 75
#####可以将插入的值转为16进制，可以与这里的值对应上。 insert into dept values(66,'imutest2','zhengzhou');
bys@ bys3>select dump('66',16),dump('imutest2',16),dump('zhengzhou',16) from dual;
dump('66',16) dump('imutest2',16) dump('zhengzhou',16)
------------------- ------------------------------------- ----------------------------------------
typ=96 len=2: 36,36 typ=96 len=8: 69,6d,75,74,65,73,74,32 typ=96 len=9: 7a,68,65,6e,67,7a,68,6f,75
change #2 typ:0 cls:17 afn:3 dba:0x00c00080 obj:4294967295 scn:0x0000.00729c37 seq:2op:5.2 enc:0 rbl:0
ktudh redo: slt: 0x000f sqn: 0x00000f13 flg: 0x0012 siz: 136 fbi: 0 ---op:5.2，向undo段头的事务表写事务信息-事务开始
uba: 0x00c017b7.0262.08 pxid: 0x0000.000.00000000
change #3 typ:0 cls:1 afn:4 dba:0x01003d53 obj:22818 scn:0x0000.00729c68 seq:1 op:10.2 enc:0 rbl:0
index redo (kdxlin): insert leaf row --也说明是向索引插入，obj:22818就是索引的对象id，op:10.2-插入索引叶子块
ktb redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: f xid: 0x0001.00f.00000f13 uba: 0x00c017b7.0262.09
redo: single / -- / --
itl: 2, sno: 5, row size 14
insert key: (10): 02 c1 43 06 01 00 00 fd 00 02 --向索引叶子插入的key值
change #4 typ:0 cls:17 afn:3 dba:0x00c00080 obj:4294967295 scn:0x0000.00729c6b seq:1op:5.4 enc:0 rbl:0
ktucm redo: slt: 0x000f sqn: 0x00000f13 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c017b7.0262.09 ext: 3 spc: 7012 fbi: 0
###op:5.4 --在这个change #4中对此事务做了提交操作
change #5 typ:0 cls:18 afn:3 dba:0x00c017b7 obj:4294967295 scn:0x0000.00729c37 seq:3 op:5.1 enc:0 rbl:0
ktudb redo: siz: 136 spc: 7252 flg: 0x0012 seq: 0x0262 rec: 0x08 ----op:5.1-数据修改前值放到undo
xid: 0x0001.00f.00000f13
ktubl redo: slt: 15 rci: 0 opc: 11.1 [objn: 22327 objd: 22327 tsn: 4] --是对表内数据的
undo type: regular undo begin trans last buffer split: no
temp object: no
tablespace undo: no
0x00000000 prev ctl uba: 0x00c017b7.0262.05
prev ctl max cmt scn: 0x0000.00729783 prev tx cmt scn: 0x0000.0072978f
txn start scn: 0x0000.00729c68 logon user: 32 prev brb: 12588976 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: 0x0005.005.00000ed4 uba: 0x00c029f3.02e9.0e
flg: c--- lkc: 0 scn: 0x0000.007164a1
kdo op code: drp row dependencies disabled
xtype: xa flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2)
change #6 typ:0 cls:18 afn:3 dba:0x00c017b7 obj:4294967295 scn:0x0000.00729c6b seq:1 op:5.1 enc:0 rbl:0
ktudb redo: siz: 100 spc: 7114 flg: 0x0022 seq: 0x0262 rec: 0x09
xid: 0x0001.00f.00000f13
ktubu redo: slt: 15 rci: 8 opc: 10.22 objn: 22818 objd: 22818 tsn: 4 ---objn: 22818是索引的object_id
undo type: regular undo undo type: last buffer split: no
tablespace undo: no
0x00000000
index undo for leaf key operations
ktb redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: l itl: xid: 0x0005.005.00000ed4 uba: 0x00c029f3.02e9.0f
flg: c--- lkc: 0 scn: 0x0000.007164a1
dump kdilk : itl=2, kdxlkflg=0x1 sdc=1 indexid=0x1003d52 block=0x01003d53
(kdxlpu): purge leaf row ----这里的purge leaf row也证明了change #6这个是对索引的操作
key :(10): 02 c1 43 06 01 00 00 fd 00 02
end of redo dump
#################################################################
update操作：明天补。。
###################################################################
delete操作：sys@ bys3>alter system switch logfile;
system altered.
sys@ bys3>select a.group#,a.sequence#,a.archived,a.status,b.type,b.member from v$log a,v$logfile b where a.group#=b.group#;
group# sequence# arc status type member
---------- ---------- --- ---------------- ------- ------------------------------
1 325 no current online /u01/oradata/bys3/redo01.log
2 323 yes inactive online /u01/oradata/bys3/redo02.log
3 324 yes active online /u01/oradata/bys3/redo03.log
sys@ bys3>conn bys/bys
connected.
bys@ bys3>select * from dept;
deptno dname loc
---------- -------------- -------------
10 accounting new york
20 research dallas
66 imutest2 zhengzhou
40 operations boston
11 database bj
22 dataoracle sh
bys@ bys3>set time on
20:32:58 bys@ bys3>delete dept where deptno=66;
1 row deleted.
20:33:02 bys@ bys3>commit;
commit complete.
20:33:06 bys@ bys3>
另一会话dump redo logfile:
bys@ bys3>alter system dump logfile '/u01/oradata/bys3/redo01.log';
system altered.
bys@ bys3>select value from v$diag_info where name like 'de%' ;
value
----------------------------------------------------------------------------------------------------
/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_29403.trc
#######################
redo record - thread:1 rba: 0x000145.00000003.0010 len: 0x0308 vld: 0x0d
scn: 0x0000.0072a6f2 subscn: 1 01/08/2014 20:33:06
(lwn rba: 0x000145.00000003.0010 len: 0002 nst: 0001 scn: 0x0000.0072a6f1)
change #1 typ:2 cls:1 afn:4 dba:0x010000fd obj:22327 scn:0x0000.00729c6b seq:2 op:11.3 enc:0 rbl:0
ktb redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: f xid: 0x000a.002.00000f0c uba: 0x00c0175c.026f.01
block cleanout record, scn: 0x0000.0072a6ee ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.00729c6b
itli: 2 flg: 2 scn: 0x0000.00719188
kdo op code: drp row dependencies disabled --drp drop row piece
xtype: xa flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2)
change #2 typ:0 cls:35 afn:3 dba:0x00c00110 obj:4294967295 scn:0x0000.0072a6b9 seq:1 op:5.2 enc:0 rbl:0
ktudh redo: slt: 0x0002 sqn: 0x00000f0c flg: 0x000a siz: 200 fbi: 0
uba: 0x00c0175c.026f.01 pxid: 0x0000.000.00000000
change #3 typ:0 cls:1 afn:4 dba:0x01003d53 obj:22818 scn:0x0000.0072a6ef seq:1op:10.4 enc:0 rbl:0
index redo (kdxlde): delete leaf row ---删除索引叶
ktb redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: f xid: 0x000a.002.00000f0c uba: 0x00c0175c.026f.02
redo: single / -- / --
itl: 2, sno: 5, row size 14
change #4 typ:0 cls:35 afn:3 dba:0x00c00110 obj:4294967295 scn:0x0000.0072a6f2 seq:1op:5.4 enc:0 rbl:0
ktucm redo: slt: 0x0002 sqn: 0x00000f0c srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c0175c.026f.02 ext: 4 spc: 7846 fbi: 0
change #5 typ:1 cls:36 afn:3 dba:0x00c0175c obj:4294967295 scn:0x0000.0072a6ee seq:1op:5.1enc:0 rbl:0
ktudb redo: siz: 200 spc: 0 flg: 0x000a seq: 0x026f rec: 0x01
xid: 0x000a.002.00000f0c
ktubl redo: slt: 2 rci: 0 opc: 11.1 [objn: 22327 objd: 22327 tsn: 4]
undo type: regular undo begin trans last buffer split: no
temp object: no
tablespace undo: no
0x00000000 prev ctl uba: 0x00c0175b.026f.07
prev ctl max cmt scn: 0x0000.0072a2c6 prev tx cmt scn: 0x0000.0072a2d5
txn start scn: 0x0000.0072a6ef logon user: 32 prev brb: 12588886 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: 0x0005.002.00000edc uba: 0x00c041cd.02ea.02
flg: c--- lkc: 0 scn: 0x0000.00719188
kdo op code: irp row dependencies disabled
xtype: xa flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2) size/delt: 25
fb: --h-fl-- lb: 0x0 cc: 3
null: ---
col 0: [ 2] c1 43 --这三行是删除前的值，参见第一步insert里的dump计算
col 1: [ 8] 69 6d 75 74 65 73 74 32
col 2: [ 9] 7a 68 65 6e 67 7a 68 6f 75
#####可以将第一步插入的值转为16进制，可以与这里的值对应上。 -- 66 imutest2 zhengzhou
bys@ bys3>select dump('66',16),dump('imutest2',16),dump('zhengzhou',16) from dual;
dump('66',16) dump('imutest2',16) dump('zhengzhou',16)
------------------- ------------------------------------- ----------------------------------------
typ=96 len=2: 36,36 typ=96 len=8: 69,6d,75,74,65,73,74,32 typ=96 len=9: 7a,68,65,6e,67,7a,68,6f,75
change #6 typ:0 cls:36 afn:3 dba:0x00c0175c obj:4294967295 scn:0x0000.0072a6f2 seq:1op:5.1 enc:0 rbl:0
ktudb redo: siz: 100 spc: 7948 flg: 0x0022 seq: 0x026f rec: 0x02
xid: 0x000a.002.00000f0c
ktubu redo: slt: 2 rci: 1 opc: 10.22 objn: 22818 objd: 22818 tsn: 4
undo type: regular undo undo type: last buffer split: no
tablespace undo: no
0x00000000
index undo for leaf key operations ---索引叶子值的undo
ktb redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: l itl: xid: 0x0001.00f.00000f13 uba: 0x00c017b7.0262.09
flg: c--- lkc: 0 scn: 0x0000.00729c6b
dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x1003d52 block=0x01003d53
(kdxlre): restore leaf row (clear leaf delete flags) --这个change #6往undo里写恢复索引叶子的，
key :(10): 02 c1 43 06 01 00 00 fd 00 02