1.IMU模式与非IMU模式下REDO的区别
1.1.IMU模式
REDO RECORD - Thread:1 RBA: 0x000004.0000000b.0010 LEN: 0x0374 VLD: 0x0d //RBA: 0x000004.0000000b.0010 日志文件号4+块号64+偏移量10
SCN: 0x0000.04fd17af SUBSCN: 1 11/06/2016 04:32:47
CHANGE #1 TYP:2 CLS:1 AFN:4 DBA:0x01000a77 OBJ:91779 SCN:0x0000.04fd177c 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: 0x000a.00d.00007207 uba: 0x00c1198a.0242.30
Block cleanout record, scn: 0x0000.04fd17a9 ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.04fd177c
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000a77 hdba: 0x01000a72
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 5] 41 41 41 41 41
CHANGE #2 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.04fd1783 SEQ:2 OP:5.2 ENC:0 RBL:0 //CLS:35,大于16,对UNDO段的操作,OP:5.2 事务对UNDO段的操作,存放修改前值
ktudh redo: slt: 0x000d sqn: 0x00007207 flg: 0x0012 siz: 168 fbi: 0
uba: 0x00c1198a.0242.30 pxid: 0x0000.000.00000000
CHANGE #3 TYP:0 CLS:1 AFN:4 DBA:0x01000a77 OBJ:91779 SCN:0x0000.04fd17af 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: 0x00c1198a.0242.31
Array Update of 1 rows:
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 2 ckix: 0
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000a77 hdba: 0x01000a72
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 5] 42 42 42 42 42
CHANGE #4 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.04fd17af SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x000d sqn: 0x00007207 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c1198a.0242.31 ext: 2 spc: 1276 fbi: 0
CHANGE #5 TYP:0 CLS:36 AFN:3 DBA:0x00c1198a OBJ:4294967295 SCN:0x0000.04fd1783 SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 168 spc: 1572 flg: 0x0012 seq: 0x0242 rec: 0x30
xid: 0x000a.00d.00007207
ktubl redo: slt: 13 rci: 0 opc: 11.1 [objn: 91779 objd: 91779 tsn: 4]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c1198a.0242.2d
prev ctl max cmt scn: 0x0000.04fd1109 prev tx cmt scn: 0x0000.04fd1133
txn start scn: 0x0000.04fd178c logon user: 84 prev brb: 12654980 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: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000a77 hdba: 0x01000a72
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 5] 61 61 61 61 61
CHANGE #6 TYP:0 CLS:36 AFN:3 DBA:0x00c1198a OBJ:4294967295 SCN:0x0000.04fd17af SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 124 spc: 1402 flg: 0x0022 seq: 0x0242 rec: 0x31
xid: 0x000a.00d.00007207
ktubu redo: slt: 13 rci: 48 opc: 11.1 objn: 91779 objd: 91779 tsn: 4
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: 0x00c1198a.0242.30
Array Update of 1 rows:
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000a77 hdba: 0x01000a72
itli: 2 ispac: 0 maxfr: 4858
vect = 3
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000a77 hdba: 0x01000a72
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 5] 61 61 61 61 61
CHANGE #6 TYP:0 CLS:36 AFN:3 DBA:0x00c1198a OBJ:4294967295 SCN:0x0000.04fd17af SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 124 spc: 1402 flg: 0x0022 seq: 0x0242 rec: 0x31
xid: 0x000a.00d.00007207
ktubu redo: slt: 13 rci: 48 opc: 11.1 objn: 91779 objd: 91779 tsn: 4
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: 0x00c1198a.0242.30
Array Update of 1 rows:
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000a77 hdba: 0x01000a72
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 5] 62 62 62 62 62
1.2 非IMU模式
REDO RECORD - Thread:1 RBA: 0x000006.00000002.0010 LEN: 0x0204 VLD: 0x05
SCN: 0x0000.04fd31be SUBSCN: 1 11/06/2016 05:06:27
CHANGE #1 TYP:0 CLS:23 AFN:3 DBA:0x00c000b0 OBJ:4294967295 SCN:0x0000.04fd3151 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0014 sqn: 0x0000708a flg: 0x0012 siz: 168 fbi: 0
uba: 0x00c1a662.02f9.27 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:24 AFN:3 DBA:0x00c1a662 OBJ:4294967295 SCN:0x0000.04fd3150 SEQ:9 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 168 spc: 2698 flg: 0x0012 seq: 0x02f9 rec: 0x27
xid: 0x0004.014.0000708a
ktubl redo: slt: 20 rci: 0 opc: 11.1 [objn: 91780 objd: 91780 tsn: 4]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c1a662.02f9.1e
prev ctl max cmt scn: 0x0000.04fd205c prev tx cmt scn: 0x0000.04fd29f4
txn start scn: 0xffff.ffffffff logon user: 84 prev brb: 12691023 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: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000a7f hdba: 0x01000a7a
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 5] 61 61 61 61 61
CHANGE #3 TYP:2 CLS:1 AFN:4 DBA:0x01000a7f OBJ:91780 SCN:0x0000.04fd1dca 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: 0x0004.014.0000708a uba: 0x00c1a662.02f9.27
Block cleanout record, scn: 0x0000.04fd31be ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.04fd1dca
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000a7f hdba: 0x01000a7a
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 5] 41 41 41 41 41
REDO RECORD - Thread:1 RBA: 0x000006.0000000c.0010 LEN: 0x016c VLD: 0x05
SCN: 0x0000.04fd31c9 SUBSCN: 1 11/06/2016 05:06:42
CHANGE #1 TYP:0 CLS:24 AFN:3 DBA:0x00c1a662 OBJ:4294967295 SCN:0x0000.04fd31be SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 124 spc: 2528 flg: 0x0022 seq: 0x02f9 rec: 0x28
xid: 0x0004.014.0000708a
ktubu redo: slt: 20 rci: 39 opc: 11.1 objn: 91780 objd: 91780 tsn: 4
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: 0x00c1a662.02f9.27
Array Update of 1 rows:
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000a7f hdba: 0x01000a7a
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 5] 62 62 62 62 62
CHANGE #2 TYP:0 CLS:1 AFN:4 DBA:0x01000a7f OBJ:91780 SCN:0x0000.04fd31be SEQ:1 OP:11.19 ENC:0 RBL:0 //OP:11.19 更新数据块
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x00c1a662.02f9.28
Array Update of 1 rows:
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 2 ckix: 0
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000a7f hdba: 0x01000a7a
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 5] 42 42 42 42 42
REDO RECORD - Thread:1 RBA: 0x000006.0000000c.017c LEN: 0x0060 VLD: 0x01
SCN: 0x0000.04fd31ca SUBSCN: 1 11/06/2016 05:06:42
CHANGE #1 TYP:0 CLS:23 AFN:3 DBA:0x00c000b0 OBJ:4294967295 SCN:0x0000.04fd31be SEQ:1 OP:5.4 ENC:0 RBL:0 //提交事务
ktucm redo: slt: 0x0014 sqn: 0x0000708a srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c1a662.02f9.28 ext: 31 spc: 2402 fbi: 0
END OF REDO DUMP
----- Redo read statistics for thread 1 -----
Read rate (SYNC): 5Kb in 0.01s => 0.54 Mb/sec
Total redo bytes: 5Kb Longest record: 0Kb, moves: 0/14 moved: 0Mb (0%)
Longest LWN: 1Kb, reads: 5
Last redo scn: 0x0000.04fd31ca (83702218)
Change vector header moves = 0/27 (0%)
----------------------------------------------
总结:
IMU模式下:日志一次性写入,一个事务对应一条日志记录,先写入事务更改前的所有旧值,后写入更新后的新值
非IMU模式下:日志多次写,一个事务中有N次DML操作,则有N条日志记录,每条日志对应一次DML操作,存放该DML操作的旧值和更改后值。
解决问题:减少REDO日志写入次数;快速构造CR块
二、通过实验重现ORA-01555快照过旧的错误,如何避免错误?
重现ORA-01555错误:
SQL> create undo tablespace undo1 datafile '/u01/app/oracle/oradata/edus/undo01.dbf' size 1m;
Tablespace created.
SQL> alter system set undo_retention=1;
System altered.
SQL> var x refcursor
SQL> begin
2 open :x for select * from lvtu;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> begin
2 for i in 1 .. 20000 loop
3 update lvtu set id=id+1;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> print :x
ERROR:
ORA-01555: snapshot too old
如何避免错误:将undo_retention参数设置大点,但会导致UNDO表空间过度扩展,但治标不治本,
从根本就应该从优化SQL语句和数据库对象设计入手,充分利用索引,减少SQL执行时间
3.UNDO的一致性读
SQL> select * from lvtu;
ID NAME
---------- ------------------------------------------------------------
1 DDDDD
2 BBBBB
SQL> var x refcursor;
SQL> exec open:x for select * from lvtu where id=1;
PL/SQL procedure successfully completed.
Commit complete.
SQL> update lvtu set name='AAAAA' where id=1;
1 row updated.
SQL> print x
ID NAME
---------- ------------------------------------------------------------
1 DDDDD
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from lvtu where id=1;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 2687
另一会话:
SQL> select addr,xid,xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn from v$transaction;
ADDR XID XIDUSN XIDSLOT XIDSQN UBAFIL
---------------- ---------------- ---------- ---------- ---------- ----------
UBABLK UBASQN
---------- ----------
00000000951B0FF0 0500120027730000 5 18 29479 3
91676 781
SQL> alter system checkpoint;
System altered.
SQL> alter system dump datafile 4 block 2687;
System altered.
SQL> select * from v$diag_info where name like '%Default%';
INST_ID
----------
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1
Default Trace File
/u01/oracle/diag/rdbms/edus/edus/trace/edus_ora_14202.trc
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.012.00007327 0x00c1661c.030d.1c ---- 1 fsc 0x0000.00000000 //未有明确提交标记,XID,通过事务表发现未提交,可以根据UBA(3号文件91676块第28条记录),往前SCN
0x02 0x0009.00c.00007259 0x00c08040.028f.11 C--- 0 scn 0x0000.04fd42a5
bdba: 0x01000a7f
data_block_dump,data header at 0x2ab3be5aea64
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x2ab3be5aea64
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f80
avsp=0x1f6a
tosp=0x1f6a
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f8c
0x14:pri[1] offs=0x1f80
block_row_dump:
tab 0, row 0, @0x1f8c
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 5] 41 41 41 41 41 //修改未提交的数据
tab 0, row 1, @0x1f80
tl: 12 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [ 5] 42 42 42 42 42
第二次DUMP
SQL> alter system dump datafile 3 block 91676;
System altered.
SQL> select * from v$diag_info where name like '%Default%';
INST_ID
----------
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1
Default Trace File
/u01/oracle/diag/rdbms/edus/edus/trace/edus_ora_14423.trc
*-----------------------------
* Rec #0x1c slt: 0x12 objn: 91780(0x00016684) objd: 91780 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x00c1661c.030d.19 ctl max scn: 0x0000.04fd3c64 prv tx scn: 0x0000.04fd3c6d
txn start scn: scn: 0x0000.04fd42b8 logon user: 84
prev brb: 12674585 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0009.000.00007259 uba: 0x00c08040.028f.10
flg: C--- lkc: 0 scn: 0x0000.04fd4272 //该UNDO记录关联的事务槽信息,FLG:已经提交,对比SCN,发会该SCN与SELECT语发出的SCN最相近
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000a7f hdba: 0x01000a7a
itli: 1 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 5] 44 44 44 44 44 //更新为AAAAA前的旧值,DDDDDD,当前事务的相关旧值