redo 在 10G 以后增加了新功能就是 in memory undo
我们看一下 10G 前的 redo 与 in memory undo 的区别
1.非IMU 环境测试
SQL> set linesize 132
SQL> column name format a30
SQL> column value format a25
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE FROM SYS.x$ksppi x, SYS.x$ksppcv y WHERE x.indx = y.indx AND x.ksppinm = '_in_memory_undo';
NAME VALUE
------------------------------ -------------------------
_in_memory_undo TRUE
有看到 _in_memory_undo 是开启的
我们现在 修改参数
SQL> alter system set "_in_memory_undo" = false scope=spfile;
系统已更改。
SQL> startup force;
ORACLE 例程已经启动。
Total System Global Area 1.2827E+10 bytes
Fixed Size 2240344 bytes
Variable Size 1644167336 bytes
Database Buffers 1.1174E+10 bytes
Redo Buffers 7335936 bytes
数据库装载完毕。
数据库已经打开。
建立测试表
SQL> create table system.wh as select * from dba_objects;
表已创建。
SQL> alter system switch logfile;
系统已更改。
做一次更新操作
SQL> update system.wh set object_name='wh' where object_id=2;
已更新 1 行。
SQL> commit;
提交完成。
定位redo 文件
SQL> select group#, SEQUENCE#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 3289 INACTIVE
2 3290 INACTIVE
3 3291 INACTIVE
4 3292 INACTIVE
5 3293 ACTIVE
6 3294 CURRENT
已选择6行。
SQL> select member from v$logfile where group#=6;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo06.log
将 redo 信息dump 出来
分析 trc 文件内容
由于文件里过多 REDO RECORD .我们根据 object_name='wh' wh 这个字符来定位
Typ=96 Len=2: 77,68
REDO RECORD - Thread:1 RBA: 0x000cde.00000007.0010 LEN: 0x01a0 VLD: 0x05
SCN: 0x0dcd.742ebacb SUBSCN: 1 03/13/2017 11:04:50
(LWN RBA: 0x000cde.00000007.0010 LEN: 0002 NST: 0001 SCN: 0x0dcd.742ebaca) <= 描述REDO RECORD的基本信息,就是REDO RECORDER头
CHANGE #1 TYP:0 CLS:19 AFN:3(3号文件) DBA:0x00c00090(144数据块) OBJ:4294967295 SCN:0x0dcd.742ebac4 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x000b sqn: 0x000252e3 flg: 0x0012 siz: 140 fbi: 0
uba: 0x00c009bb.1597.1e pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:20 AFN:3(3号文件) DBA:0x00c009bb(2491数据块) OBJ:4294967295 SCN:0x0dcd.742ebac3 SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 140 spc: 2360 flg: 0x0012 seq: 0x1597 rec: 0x1e
xid: 0x0002.00b.000252e3
ktubl redo: slt: 11 rci: 0 opc: 11.1 [objn: 150827 objd: 150827 tsn: 0]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c009bb.1597.1b
prev ctl max cmt scn: 0x0dcd.740b6c6a prev tx cmt scn: 0x0dcd.740b6c6c
txn start scn: 0xffff.ffffffff logon user: 0 prev brb: 12585401 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: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00417461 hdba: 0x00417460
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 48(0x30) flag: 0x2c lock: 0 ckix: 0
ncol: 14 nnew: 1 size: 4
col 1: [ 6] 43 5f 4f 42 4a 23(C_OBJ#) <= 更改前的object_name
CHANGE #3 TYP:0 CLS:1 AFN:1 DBA:0x00417461 OBJ:150827 SCN:0x0dcd.742eba7b SEQ:2 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0002.00b.000252e3 uba: 0x00c009bb.1597.1e
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00417461 hdba: 0x00417460
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 48(0x30) flag: 0x2c lock: 2 ckix: 0
ncol: 14 nnew: 1 size: -4
col 1: [ 2] 77 68 (wh) <= 更改后的object_name
2.IMU 进行测试
SQL> alter system set "_in_memory_undo" = true scope=spfile;
系统已更改。
SQL> startup force;
ORACLE 例程已经启动。
Total System Global Area 1.2827E+10 bytes
Fixed Size 2240344 bytes
Variable Size 1644167336 bytes
Database Buffers 1.1174E+10 bytes
Redo Buffers 7335936 bytes
数据库装载完毕。
数据库已经打开。
创建测试表
SQL> drop table system.wh;
表已删除。
SQL> create table system.wh as select * from dba_objects;
表已创建。
SQL> alter system switch logfile;
系统已更改。
做一次commit 操作
SQL> update system.wh set object_name='wh' where object_id=2;
已更新 1 行。
SQL> commit;
提交完成。
定位 redo 文件
SQL> select group#, SEQUENCE#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 3295 ACTIVE
2 3296 CURRENT
3 3291 INACTIVE
4 3292 INACTIVE
5 3293 INACTIVE
6 3294 INACTIVE
已选择6行。
SQL>
SQL> select member from v$logfile where group#=2;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo02.log
我们获取trc文件内容
REDO RECORD - Thread:1 RBA: 0x000ce0.00000003.0010 LEN: 0x01e8 VLD: 0x0d
SCN: 0x0dcd.7463dfd0 SUBSCN: 1 03/13/2017 11:37:21
(LWN RBA: 0x000ce0.00000003.0010 LEN: 0001 NST: 0001 SCN: 0x0dcd.7463dfcf) <= 描述REDO RECORD的基本信息,就是REDO RECORDER头
CHANGE #1 TYP:0 CLS:1 AFN:1 DBA:0x00417461 OBJ:150832 SCN:0x0dcd.7463df8c SEQ:1 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0004.01d.00026ba9 uba: 0x00c028b7.1be9.20
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00417461 hdba: 0x00417460
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 48(0x30) flag: 0x2c lock: 2 ckix: 0
ncol: 14 nnew: 1 size: -4
col 1: [ 2] 77 68 (wh) <= 更改后的object_name
CHANGE #2 TYP:0 CLS:23 AFN:3 DBA:0x00c000b0 OBJ:4294967295 SCN:0x0dcd.7463df74 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x001d sqn: 0x00026ba9 flg: 0x0012 siz: 140 fbi: 0
uba: 0x00c028b7.1be9.20 pxid: 0x0000.000.00000000
CHANGE #3 TYP:0 CLS:23 AFN:3 DBA:0x00c000b0 OBJ:4294967295 SCN:0x0dcd.7463dfd0 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x001d sqn: 0x00026ba9 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c028b7.1be9.20 ext: 110 spc: 3890 fbi: 0
CHANGE #4 TYP:0 CLS:24 AFN:3 DBA:0x00c028b7 OBJ:4294967295 SCN:0x0dcd.7463df73 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 140 spc: 4032 flg: 0x0012 seq: 0x1be9 rec: 0x20
xid: 0x0004.01d.00026ba9
ktubl redo: slt: 29 rci: 0 opc: 11.1 [objn: 150832 objd: 150832 tsn: 0]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c028b6.1be9.18
prev ctl max cmt scn: 0x0dcd.74265910 prev tx cmt scn: 0x0dcd.74265927
txn start scn: 0x0dcd.7463dfbe logon user: 0 prev brb: 12593331 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: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00417461 hdba: 0x00417460
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 48(0x30) flag: 0x2c lock: 0 ckix: 0
ncol: 14 nnew: 1 size: 4
col 1: [ 6] 43 5f 4f 42 4a 23 (C_OBJ#) <= 更新前的数值
IMU 与 非IMU 区别在于 多条commit 内容合并,这个测试只是 一条记录commit ,所以看不出区别.
大家可以亲自动手一下,测试一下 IMU 与 非IMU redo 的区别
