Oracle redo IMU 与非IMU区别

原创张鹏 2023-07-17

150

1349.Oracle redo IMU 与非IMU区别
oracle分享技术2021-02-06
188
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 例程已经启动。

创建测试表

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 的区别

墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle redo IMU 与非IMU区别

MEMBER

MEMBER

评论