最详细的解读是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
---------- ---------- --- ---------------- ------- ------------------------------
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
0