Oracle特殊恢复原理与实战_08 Redo Architecture and Configuration
IMU的好处
IMU(In Memory Undo)顾名思义就是在内存中的undo，现在每次更改data block，Oracle不用去更改这个undo block（也不会生成相应的redo了），而是把undo信息缓存到IMU里去了，只有最后commit或者flush IMU时，这些undo 信息才会批量更新到undo block,并生成redo。可以避免Undo信息以前在Buffer Cache中的读写操作，从而可以进一步的减少Redo生成，同时可以大大减少以前的UNDO SEGMENT的操作。IMU中数据通过暂存、整理与收缩之后也可以写出到回滚段，这样的写出提供了有序、批量写的性能提升。
IMU主要作用
- 减少CR块–>在构造CR block时，不用像以前那样从
undo block中获取undo record了，而是用共享池私有IMU区域里的信息来构造cr block，减少了BUFFER CACEH中CBC LATCH竞争。 - 减少REDO日志条目数–>不再是每条DML语句一个
redo records,而是每个事务一个redo records--REDO RECORD的产生会传到LOG BUFFER，会申请LATCH。 - 减少LATCH–>首先因为减少
REDO RECORD数目；其次用一个IMU latch代替redo allocation latch和redo copy latch这两个,也减少了LATCH争用。
在哪些场景下不会使用IMU特性
- 在RAC环境中不支持IMU
- 开启FLASHBACK DATABASE时会开启打开辅助日志，此时不能用IMU
- 事务过大–据说每个IMU Buffer的Private redo strand area大小大概是64KB（64位的Oracle版本是128KB），大事务不能用。比如一个事务，先有一条UPDATE，此时将REDO私有区域使用完了，此事务的其它DML语句，将自动使用非IMU模式
- 共享池太小时，ORACLE会自动不使用IMU
- 无法获取IMU LATCH时，将自动使用非IMU模式
IMU下的redo产生过程
# 查看隐含参数_in_memory_undo的状态，默认开启IMU开启TRUE
SQL> @show_para
Enter value for p: in_memory_undo
old 12: AND upper(i.ksppinm) LIKE upper('%&p%')
new 12: AND upper(i.ksppinm) LIKE upper('%in_memory_undo%')
P_NAME P_DESCRIPTION P_VALUE ISDEFAULT ISMODIFIED ISADJ
------------------------------------------------ -------------------------------------------------------------- ---------- --------- ---------- -----
_in_memory_undo Make in memory undo for top level transactions TRUE TRUE FALSE FALSE
# 切换日志组，使CURRENT日志组为新的
SQL> alter system switch logfile;
SQL> col member for a50
SQL> select l.GROUP#,l.STATUS,f.MEMBER from v$log l,v$logfile f
where l.GROUP#=f.GROUP#;
GROUP# STATUS MEMBER
---------- ---------------- --------------------------------------------------
1 INACTIVE /u01/app/oracle/oradata/orcl/redo01.log
2 INACTIVE /u01/app/oracle/oradata/orcl/redo02.log
3 CURRENT /u01/app/oracle/oradata/orcl/redo03.log
# 执行测试语句
create tablespace lyj_ts datafile '/u01/app/oracle/oradata/orcl/lyj_01.dbf' size 100m autoextend on next 50m maxsize unlimited;
create user lyj identified by lyj default tablespace lyj_ts;
grant dba to lyj;
conn lyj/lyj
drop table t1 purge;
create table t1 (id int,name varchar2(10));
insert into t1 values(1,'AAAAA');
insert into t1 values(2,'BBBBB');
commit;
update t1 set name='aaaaa' where name='AAAAA';
update t1 set name='bbbbb' where name='BBBBB';
commit;
select object_id from user_objects where object_name='T1';
OBJECT_ID
----------
15682
# 导出当前redo log
alter system dump logfile '/u01/app/oracle/oradata/orcl/redo03.log';
SQL> select * from v$diag_info where name='Default Trace File';
INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------------------------------------
1 Default Trace File
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26888.trc
|
查看分析dump的trace日志，一共4条REDO RECORD
第一个REDO RECORD, REDO RECORD头+CHANGE VECTOR组成（一个CV就是一个操作），由三个CV组成
REDO RECORD - Thread:1 RBA: 0x00003f.00000039.0010 LEN: 0x0170 VLD: 0x01
SCN: 0x0000.000c6433 SUBSCN: 3 04/19/2018 10:59:35
# --------------------------------------------------------->REDO RECORD头
RBA: 0x00003f.00000039.0010 ==（由三部分组成：序列号，块号，偏移量或着叫第几个字节）
LEN: 0x01e8：一条日志的长度
VLD: 0x01 ：日志类型
# CHANGE VECTOR 1
CHANGE #1 TYP:0 CLS:17 AFN:3 DBA:0x00c00080 OBJ:4294967295 SCN:0x0000.000c6425 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0003 sqn: 0x000001db flg: 0x0012 siz: 112 fbi: 0
uba: 0x00c0032d.0103.17 pxid: 0x0000.000.00000000
# -------------------------------------------------------->UNDO段头事务表
OP:5.2==>OPRATION CODE 向UNDO段的段头的事务表写事务信息，开始事务
TYP:0（普通块）
CLS:17 （CLASS）超过16表示undo
AFN:3 (绝对文件号)
DBA:0x00c00080 数据块的地址
OBJ:4294967295 FFFFFFFF 表示undo
SCN:0x0000.000c6425 产生事务的时间
# CHANGE VECTOR 2
CHANGE #2 TYP:0 CLS:18 AFN:3 DBA:0x00c0032d OBJ:4294967295 SCN:0x0000.000c6424 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 112 spc: 3810 flg: 0x0012 seq: 0x0103 rec: 0x17
xid: 0x0001.003.000001db
ktubl redo: slt: 3 rci: 0 opc: 11.1 [objn: 15682 objd: 15682 tsn: 5]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c0032d.0103.15
prev ctl max cmt scn: 0x0000.000c56d6 prev tx cmt scn: 0x0000.000c56d7
txn start scn: 0xffff.ffffffff logon user: 34 prev brb: 12583727 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: 0x01400087 hdba: 0x01400082
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0)
# ―――――――――――――――――――――――――――――――――――――――――――――――――――――――>undo数据块头
OP:5.1 undo segment header
# CHANGE VECTOR 3
CHANGE #3 TYP:0 CLS:1 AFN:5 DBA:0x01400087 OBJ:15682 SCN:0x0000.000c6433 SEQ:2 OP:11.2 ENC:0 RBL:0
KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0001.003.000001db uba: 0x00c0032d.0103.17
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01400087 hdba: 0x01400082
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 12
fb: --H-FL-- lb: 0x1 cc: 2
null: --
col 0: [ 2] c1 02
col 1: [ 5] 41 41 41 41 41
# ----------------------------------------------------------->插入AAAAA
OP:11.2:Insert Row Piece
|
第二个REDO RECORD
REDO RECORD - Thread:1 RBA: 0x00003f.00000039.0180 LEN: 0x0100 VLD: 0x01
SCN: 0x0000.000c6433 SUBSCN: 4 04/19/2018 10:59:35
# --------------------------------------------------------->REDO RECORD头
CHANGE #1 TYP:0 CLS:18 AFN:3 DBA:0x00c0032d OBJ:4294967295 SCN:0x0000.000c6433 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 68 spc: 3696 flg: 0x0022 seq: 0x0103 rec: 0x18
xid: 0x0001.003.000001db
ktubu redo: slt: 3 rci: 23 opc: 11.1 objn: 15682 objd: 15682 tsn: 5
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x00c0032d.0103.17
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01400087 hdba: 0x01400082
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1)
# ―――――――――――――――――――――――――――――――――――――――――――――――――――――――>undo数据块头
CHANGE #2 TYP:0 CLS:1 AFN:5 DBA:0x01400087 OBJ:15682 SCN:0x0000.000c6433 SEQ:3 OP:11.2 ENC:0 RBL:0
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x00c0032d.0103.18
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01400087 hdba: 0x01400082
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1) size/delt: 12
fb: --H-FL-- lb: 0x1 cc: 2
null: --
col 0: [ 2] c1 03
col 1: [ 5] 42 42 42 42 42
# ----------------------------------------------------------->插入BBBBB
|
第三个REDO RECORD
REDO RECORD - Thread:1 RBA: 0x00003f.0000003a.0090 LEN: 0x0060 VLD: 0x01
SCN: 0x0000.000c6434 SUBSCN: 1 04/19/2018 10:59:35
CHANGE #1 TYP:0 CLS:17 AFN:3 DBA:0x00c00080 OBJ:4294967295 SCN:0x0000.000c6433 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0003 sqn: 0x000001db srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c0032d.0103.18 ext: 2 spc: 3626 fbi: 0
# ----------------------------------------------------------->提交
OP:5.4 提交作为单独的一条RECORD
|
第四个REDO RECORD
REDO RECORD - Thread:1 RBA: 0x00003f.0000003b.0010 LEN: 0x0374 VLD: 0x0d
SCN: 0x0000.000c6436 SUBSCN: 1 04/19/2018 10:59:35
(LWN RBA: 0x00003f.0000003b.0010 LEN: 0002 NST: 0001 SCN: 0x0000.000c6435)
# --------------------------------------------------------->REDO RECORD头
CHANGE #1 TYP:2 CLS:1 AFN:5 DBA:0x01400087 OBJ:15682 SCN:0x0000.000c6434 SEQ:1 OP:11.19 ENC:0 RBL:0
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0008.011.000001d7 uba: 0x00c00bd1.00dd.22
Block cleanout record, scn: 0x0000.000c6435 ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.000c6434
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 216
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01400087 hdba: 0x01400082
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 5] 61 61 61 61 61
# -------------------------------------------------------->数据修改成aaaaa
OP:11.19 数据修改操作
CHANGE #2 TYP:0 CLS:31 AFN:3 DBA:0x00c000f0 OBJ:4294967295 SCN:0x0000.000c6404 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0011 sqn: 0x000001d7 flg: 0x0012 siz: 168 fbi: 0
uba: 0x00c00bd1.00dd.22 pxid: 0x0000.000.00000000
# -------------------------------------------------------->UNDO段头事务表
OP:5.2 事务开始
CHANGE #3 TYP:0 CLS:1 AFN:5 DBA:0x01400087 OBJ:15682 SCN:0x0000.000c6436 SEQ:1 OP:11.19 ENC:0 RBL:0
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x00c00bd1.00dd.23
Array Update of 1 rows:
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 2 ckix: 216
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01400087 hdba: 0x01400082
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 5] 62 62 62 62 62
# -------------------------------------------------------->数据修改成bbbbb
CHANGE #4 TYP:0 CLS:31 AFN:3 DBA:0x00c000f0 OBJ:4294967295 SCN:0x0000.000c6436 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0011 sqn: 0x000001d7 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c00bd1.00dd.23 ext: 2 spc: 3340 fbi: 0
# -------------------------------------------------------->提交
CHANGE #5 TYP:0 CLS:32 AFN:3 DBA:0x00c00bd1 OBJ:4294967295 SCN:0x0000.000c6403 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 168 spc: 3636 flg: 0x0012 seq: 0x00dd rec: 0x22
xid: 0x0008.011.000001d7
ktubl redo: slt: 17 rci: 0 opc: 11.1 [objn: 15682 objd: 15682 tsn: 5]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c00bd1.00dd.21
prev ctl max cmt scn: 0x0000.000c52b6 prev tx cmt scn: 0x0000.000c52c7
txn start scn: 0x0000.000c6434 logon user: 34 prev brb: 12585939 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
Array Update of 1 rows:
tabn: |