实验内容：非imu模式下dml语句产生的redo日志内容格式解读
最详细的解读是update的。实验环境准备11g中默认是开启imu特性的，做此实验需要关闭此特性。
alter system set _in_memory_undo=false;
alter system set _in_memory_undo=true; --实验结束后使用此语句改回使用imu特性。
修改参数完成后，重启数据库：
shutdown immediate;
startup;
准备好实验用的表----bys.dept表。
sys@ bys3>select object_id from dba_objects where object_name='dept';
object_id
----------
22327
sys@ bys3>select * from bys.dept;
deptno dname loc
---------- -------------- -------------
10 accounting new york
20 research dallas
40 operations boston
11 chedan bj
22 test sh
bys@ bys3>col file_name for a35
bys@ bys3>select file_id,file_name from dba_data_files;
file_id file_name
---------- -----------------------------------
1 /u01/oradata/bys3/system01.dbf
2 /u01/oradata/bys3/sysaux01.dbf
3 /u01/oradata/bys3/undotbs01.dbf
4 /u01/oradata/bys3/user01.dbf
bys@ bys3>select ts#,name from v$tablespace;
ts# name
---------- ------------------------------
0 system
1 sysaux
2 undotbs1
3 temp
4 users
###################################################
开始实验分三步，分别是插入、更新、删除语句的操作。最详细的解读是update的。实验一：insert 操作的redo日志解读会话1： --sys用户切换日志并查出当前日志名：
sys@ bys3>alter system switch logfile;
system altered.
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 310 no current online /u01/oradata/bys3/redo01.log
2 308 yes inactive online /u01/oradata/bys3/redo02.log
3 309 yes active online /u01/oradata/bys3/redo03.log
普通用户做插入语句：
sys@ bys3>conn bys/bys
select * from dept;
connected.
bys@ bys3>
deptno dname loc
---------- -------------- -------------
10 accounting new york
20 research dallas
40 operations boston
11 chedan bj
22 test sh
bys@ bys3>set time on
20:34:07 bys@ bys3>insert into dept values(33,'imutest','hz');
1 row created.
20:34:12 bys@ bys3>commit;
commit complete.
会话2：dump当前redo日志：
sys@ bys3>alter system dump logfile '/u01/oradata/bys3/redo01.log';
system altered.
sys@ bys3>select value from v$diag_info where name like 'de%' ;
value
----------------------------------------------------------------------------------------------------
/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_29063.trc
######################
查看此trace文件： 方法是：vi /u01/diag/rdbms/bys3/bys3/trace/bys3_ora_29063.trc 然后搜索修改的对象的object_id-- 22327
详解参考下一步的update的redo讲解。
redo record - thread:1 rba: 0x000136.0000000d.0010 len: 0x01a0 vld: 0x05
scn: 0x0000.00702f08 subscn: 1 01/06/2014 20:34:13
(lwn rba: 0x000136.0000000d.0010 len: 0002 nst: 0001 scn: 0x0000.00702f08)
change #1 typ:0 cls:21 afn:3 dba:0x00c000a0 obj:4294967295 scn:0x0000.00702eda seq:1 op:5.2 enc:0 rbl:0
ktudh redo: slt: 0x0003 sqn: 0x00000eaa flg: 0x0012 siz: 112 fbi: 0
uba: 0x00c01ac7.0250.25 pxid: 0x0000.000.00000000
change #2 typ:0 cls:22 afn:3 dba:0x00c01ac7 obj:4294967295 scn:0x0000.00702ed9 seq:3op:5.1 enc:0 rbl:0
ktudb redo: siz: 112 spc: 3494 flg: 0x0012 seq: 0x0250 rec: 0x25
xid: 0x0003.003.00000eaa
ktubl redo: slt: 3 rci: 0 opc: 11.1 [objn: 22327 objd: 22327 tsn: 4] ------- tsn: 4，插入的这个是在表空间号为4--obj:22327--是插入的对象的object_id
undo type: regular undo begin trans last buffer split: no
temp object: no
tablespace undo: no
0x00000000 prev ctl uba: 0x00c01ac7.0250.22
prev ctl max cmt scn: 0x0000.007029ac prev tx cmt scn: 0x0000.007029d6
txn start scn: 0xffff.ffffffff logon user: 32 prev brb: 12589763 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: 0x010000ff hdba: 0x010000fa
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0)
change #3 typ:0 cls:1 afn:4 dba:0x010000ff obj:22327 scn:0x0000.003eec75 seq:1 op:11.2 enc:0 rbl:0 --op:11.2 这个应该是插入的操作的了。
ktb redo -----afn:4，插入的这个是在4号数据文件中--obj:22327--是插入的对象的object_id
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: f xid: 0x0003.003.00000eaa uba: 0x00c01ac7.0250.25
kdo op code: irp row dependencies disabled
xtype: xa flags: 0x00000000 bdba: 0x010000ff hdba: 0x010000fa
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 17
fb: --h-fl-- lb: 0x1 cc: 3
null: ---
col 0: [ 2] c1 22 ---插入语句是：insert into dept values(33,'imutest','hz'); 对应是：select dump(33,16) from dual; --要注意数字在dump时不要加单引号
col 1: [ 7] 69 6d 75 74 65 73 74 --对应select dump('imutest',16) from dual; [7]--中括号里的是字节数，col 1:对应的是第二列，
col 2: [ 2] 68 7a ---对应 select dump('hz',16) from dual; --中括号里的是字节数 col 2: 对应 的是第三列
redo record - thread:1 rba: 0x000136.0000000d.01b0 len: 0x0060 vld: 0x01
scn: 0x0000.00702f09 subscn: 1 01/06/2014 20:34:13 ----op:5.4，提交操作
change #1 typ:0 cls:21 afn:3 dba:0x00c000a0 obj:4294967295 scn:0x0000.00702f08 seq:1 op:5.4 enc:0 rbl:0
ktucm redo: slt: 0x0003 sqn: 0x00000eaa srt: 0 sta: 9 flg: 0x2 ktucf redo:uba: 0x00c01ac7.0250.25 ext: 12 spc: 3380 fbi: 0
############################实验二：update 操作的redo日志解读会话3： --sys用户切换日志：
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 310 yes active online /u01/oradata/bys3/redo01.log
2 311 no current online /u01/oradata/bys3/redo02.log
3 309 yes inactive 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 chedan bj
22 test sh
33 imutest hz
6 rows selected.
bys@ bys3>set time on
20:39:23 bys@ bys3>update dept set dname='database' where deptno=11;
1 row updated.
20:39:43 bys@ bys3>commit;
commit complete.
20:39:46 bys@ bys3>
会话4：dump当前redo日志：
sys@ bys3>alter system dump logfile '/u01/oradata/bys3/redo02.log';
system altered.
sys@ bys3>select value from v$diag_info where name like 'de%' ;
value
----------------------------------------------------------------------------------------------------
/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_29141.trc
##############
查看此trace文件： 方法是：vi /u01/diag/rdbms/bys3/bys3/trace/bys3_ora_29141.trc 然后搜索修改的对象的object_id:22327
update 一行记录产生一个redo record，提交产生一个redo record。非imu下如果一个update更新多行，则会产生多条redo record。
######一个redo record: record头+change vector组成（一个cv就是一个操作）
redo record - thread:1 rba: 0x000137.00000003.0010 len: 0x01ec vld: 0x05
scn: 0x0000.00702fe0 subscn: 1 01/06/2014 20:39:45
(lwn rba: 0x000137.00000003.0010 len: 0001 nst: 0001 scn: 0x0000.00702fdf)
##以上是日志头,thread:1 线程号，rac时会有1，2等
rba: 0x000137.00000003.0010 将16进制转换为十进制分别是日志文件号、日志块号、在块上第n字节
vld: 0x05日志类型；
scn: 0x0000.00702fe0 subscn: 1 01/06/2014 20:39:45
--是此redo条目产生时的scn号，转为十进制现转为时间戳为：06-jan-14 08.39.42， 更新语句完成是在20:39:43 bys@bys3>commit;
(lwn rba: 0x000137.00000003.0010 len: 0001 nst: 0001 scn: 0x0000.00702fdf)
括号中scn: scn: 0x0000.00702fdf 比上一句：scn: 0x0000.00702fe0 subscn: 1 01/06/2014 20:39:45 少了1个scn。
######
change #1 typ:0 cls:17 afn:3 dba:0x00c00080 obj:4294967295 scn:0x0000.00702f9a seq:1 op:5.2 enc:0 rbl:0
ktudh redo: slt: 0x000f sqn: 0x00000e9c flg: 0x0012 siz: 164 fbi: 0
uba: 0x00c01cbf.0250.0f pxid: 0x0000.000.00000000
### #####################事务信息
typ:0 普通块 ，cls:17 class大于16是undo块-递增。afn:3 绝对文件号dba_data_files.file_id--这里是undo文件的文件号
dba:0x00c00080 数据块在内存中地址
obj:4294967295 --十进制，转为16进制是ffffffff
scn:0x0000.00702f9a 转换为16进制可与操作时对比
op:5.2 -> operation code 向undo段头的事务表写事务信息-事务开始
uba: 0x00c01cbf.0250.0f undo块地址
####################
change #2 typ:0 cls:18 afn:3 dba:0x00c01cbf obj:4294967295 scn:0x0000.00702f99 seq:2 op:5.1 enc:0 rbl:0
##op:5.1 --把数据修改前值放到undo --afn:3 --在undo文件里操作，undo文件号是3。。cls:18 --比change #1中大1，顺序增长哈哈
ktudb redo: siz: 164 spc: 6494 flg: 0x0012 seq: 0x0250 rec: 0x0f
xid: 0x0001.00f.00000e9c
ktubl redo: slt: 15 rci: 0 opc: 11.1 [objn: 22327 objd: 22327 tsn: 4] ------- tsn: 4，插入的这个是在表空间号-users的表空间号，--obj:22327--是插入的对象的object_id
undo type: regular undo begin trans last buffer split: no
temp object: no
tablespace undo: no
0x00000000 prev ctl uba: 0x00c01cbf.0250.0d
prev ctl max cmt scn: 0x0000.00702afa prev tx cmt scn: 0x0000.00702b2e
txn start scn: 0xffff.ffffffff logon user: 32 prev brb: 12590267 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.008.00000e9d uba: 0x00c01840.02e4.13
flg: c--- lkc: 0 scn: 0x0000.00702b1f
kdo op code: urp row dependencies disabled
xtype: xa flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 5(0x5) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: -2 ##列字符长度减少2：chedan减去database就是-2---根据多次update并dump的日志来看，这里的size的值应该是：当前change中的值减去另一个
col 1: [ 6] 63 68 65 64 61 6e --修改语句是：update dept set dname='database' where deptno=11; deptno=11时的为chedan-dump为16进制，与col 1: [ 6] 63 68 65 64 61 6e对应，[ 6]
与chedan对应，col 1是第二列--修改的dname列就是第二列。
sys@ bys3>select dump('chedan',16) from dual;
dump('chedan',16)
-------------------------------
typ=96 len=6: 63,68,65,64,61,6e
##############
change #3 typ:2 cls:1 afn:4dba:0x010000fd obj:22327 scn:0x0000.00702d7a seq:1 op:11.5 enc:0 rbl:0
## op:11.19 或者op:11.5都是--update语句，开始修改数据， afn:4 --这次是在users表空间修改数据-文件号就是4
ktb redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: f xid: 0x0001.00f.00000e9c uba: 0x00c01cbf.0250.0f
block cleanout record, scn: 0x0000.00702fe0 ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.00702b1f
itli: 2 flg: 2 scn: 0x0000.00702d7a
kdo op code: urp row dependencies disabled
###此语句也说明是update语句，urp row dependencies disabled --urp=update row piece。有时会是：kdo op code:21 row dependencies disabled
xtype: xa flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 5(0x5) flag: 0x2c lock: 1 ckix: 0
ncol: 3 nnew: 1 size: 2 ##size: 2，列字符长度增加2：database减去chedan---根据多次update并dump的日志来看，这里的size的值应该是：当前change中的值减去另一个
col 1: [ 8] 64 61 74 61 62 61 73 65 ---update dept set dname='database' where deptno=11;，对应select dump('database',16) from dual; col 1: [ 8] ---第二列，8位，
####################################################
redo record - thread:1 rba: 0x000137.00000004.0010 len: 0x008c vld: 0x05
scn: 0x0000.00702fe2 subscn: 1 01/06/2014 20:39:46
(lwn rba: 0x000137.00000004.0010 len: 0001 nst: 0001 scn: 0x0000.00702fe1)
change #1 typ:0 cls:17 afn:3 dba:0x00c00080 obj:4294967295 scn:0x0000.00702fe0 seq:1op:5.4 enc:0 rbl:0
ktucm redo: slt: 0x000f sqn: 0x00000e9c srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c01cbf.0250.0f ext: 4 spc: 6328 fbi: 0
###########
op:5.4 表明是提交操作。
change #1 typ:0 cls:17 afn:3 dba:0x00c00080 obj:4294967295 scn:0x0000.00702fe0 seq:1 op:5.4 enc:0 rbl:0
afn:3 对应的是undo文件，
slt: 0x000f 修改了undo文件的这个事务槽
scn: 0x0000.00702fe2 subscn: 1 01/06/2014 20:39:46 提交操作的时间 ：
20:39:43 bys@ bys3>commit;
commit complete.
20:39:46 bys@ bys3>
########################################实验三：delete 操作的redo日志解读会话5： --sys用户切换日志：
alter system switch logfile;
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 310 yes inactive online /u01/oradata/bys3/redo01.log
2 311 yes active online /u01/oradata/bys3/redo02.log
3 312 no current online /u01/oradata/bys3/redo03.log
切换到普通用户做删除语句：
conn bys/bys
bys@ bys3>select * from dept;
deptno dname loc
---------- -------------- -------------
10 accounting new york
20 research dallas
40 operations boston
11 database bj
22 test sh
33 imutest hz
6 rows selected.
bys@ bys3>set time on
20:43:18 bys@ bys3>delete dept where deptno=33;
1 row deleted.
20:43:25 bys@ bys3>commit;
commit complete.
会话6，dump当前redo日志：
sys@ bys3>alter system dump logfile '/u01/oradata/bys3/redo03.log';
system altered.
sys@ bys3>select value from v$diag_info where name like 'de%' ;
value
----------------------------------------------------------------------------------------------------
/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_29214.trc
##############
查看此trace文件： 方法是：vi /u01/diag/rdbms/bys3/bys3/trace/bys3_ora_29214.trc 然后搜索修改的对象的object_id:22327
redo record - thread:1 rba: 0x000138.0000002b.0010 len: 0x01c8 vld: 0x05
scn: 0x0000.0070306c subscn: 1 01/06/2014 20:43:26
(lwn rba: 0x000138.0000002b.0010 len: 0002 nst: 0001 scn: 0x0000.0070306b)
change #1 typ:0 cls:29 afn:3 dba:0x00c000e0 obj:4294967295 scn:0x0000.0070303f seq:1 op:5.2 enc:0 rbl:0
ktudh redo: slt: 0x0008 sqn: 0x00000e9b flg: 0x0012 siz: 168 fbi: 0
uba: 0x00c0193e.02b8.03 pxid: 0x0000.000.00000000
change #2 typ:0 cls:30 afn:3 dba:0x00c0193e obj:4294967295 scn:0x0000.0070303e seq:1 op:5.1 enc:0 rbl:0
ktudb redo: siz: 168 spc: 7794 flg: 0x0012 seq: 0x02b8 rec: 0x03
xid: 0x0007.008.00000e9b
ktubl redo: slt: 8 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: 0x00c0193e.02b8.02
prev ctl max cmt scn: 0x0000.00702b2d prev tx cmt scn: 0x0000.00702b3e
txn start scn: 0xffff.ffffffff logon user: 32 prev brb: 12589367 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: 0x010000ff hdba: 0x010000fa
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 17
fb: --h-fl-- lb: 0x0 cc: 3
null: ---
col 0: [ 2] c1 22 ---记录删除前的原值，delete dept where deptno=33; 删除的是这一行值： 33 imutest hz
col 1: [ 7] 69 6d 75 74 65 73 74col 2: [ 2] 68 7a
###################
bys@ bys3>select dump(33,16),dump('imutest',16),dump('hz',16) from dual;
dump(33,16) dump('imutest',16) dump('hz',16)
------------------ ---------------------------------- -------------------
typ=2 len=2: c1,22 typ=96 len=7: 69,6d,75,74,65,73,74 typ=96 len=2: 68,7a
#####################
change #3 typ:2 cls:1 afn:4 dba:0x010000ff obj:22327 scn:0x0000.00702f09 seq:1 op:11.3 enc:0 rbl:0
ktb redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: f xid: 0x0007.008.00000e9b uba: 0x00c0193e.02b8.03
block cleanout record, scn: 0x0000.0070306c ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.00702f09
kdo op code: drp row dependencies disabled
xtype: xa flags: 0x00000000 bdba: 0x010000ff hdba: 0x010000fa
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0)
redo record - thread:1 rba: 0x000138.0000002b.01d8 len: 0x0060 vld: 0x01
scn: 0x0000.0070306d subscn: 1 01/06/2014 20:43:26
change #1 typ:0 cls:29 afn:3 dba:0x00c000e0 obj:4294967295 scn:0x0000.0070306c seq:1 op:5.4 enc:0 rbl:0
ktucm redo: slt: 0x0008 sqn: 0x00000e9b srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c0193e.02b8.03 ext: 3 spc: 7624 fbi: 0