在一个事务里有多条更新如何从Undo查找前镜像
针对这个问题，我们做了如下实验。
我的实验环境：
• OS : Oracle Enterprise Linux 5.5 64Bit
• DB Type : Oracle Restart
• DB Version : 11.2.0.3
两个用到的脚本：
查看锁信息[oracle@maa3 ~]$ cat showlock.sql
select SID,
TYPE,
ID1,
ID2,
LMODE,
REQUEST,
BLOCK
from vlock
where TYPE in ('TM','TX')
order by 1,2/
查看事务信息[oracle@maa3 ~] cat showtra.sql
select XIDUSN,
XIDSLOT,
XIDSQN,
UBAFIL,
UBABLK,
UBASQN,
UBAREC,
STATUS
from vSESSION
WHERE SID=&SID
)/
==== 实验开始 ====
luocs@MAA> select sid from v$mystat where rownum=1;
SID
---------- 58
luocs@MAA> create table l1(id number, 2 name varchar2(20) 3 );
luocs@MAA> insert into l1 values(1,‘LUOCS’);luocs@MAA> insert into l1 values(2,‘ORACLE’);luocs@MAA> commit;luocs@MAA> create table l2 as select * from l1;luocs@MAA> update l1 set name=‘OO’ where id=1;luocs@MAA> update l2 set name=‘LL’ where id=1;luocs@MAA> update l1 set name=‘XX’ where id=2;
sys@MAA> @showlock
SID TYPE ID1 ID2 LMODE REQUEST BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- 58 TM 24633 0 3 0 0 58 TM 24634 0 3 0 0 58 TX 589827 738 6 0 0
sys@MAA> @showtraEnter value for sid: 58old 13: WHERE SID=&SID
new 13: WHERE SID=58
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS
---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------------------- 9 3 738 3 3169 209 36 ACTIVE
查找当前使用的回退段sys@MAA> select name from v
DUMP出该回退段头信息sys@MAA> alter system dump undo header '_SYSSMU9_578104089diag_info where name = ‘Default Trace File’;
VALUE
-------------------------------------------------------------------------------------------------------------------/u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_14815.trc
TRC内容，部分内容略掉 index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt ------------------------------------------------------------------------------------------------ 0x00 9 0x00 0x02df 0x000b 0x0000.003f4409 0x00c00c65 0x0000.000.00000000 0x00000001 0x00000000 1354068343 0x01 9 0x00 0x02e2 0x0000 0x0000.003f43ff 0x00c00c65 0x0000.000.00000000 0x00000001 0x00000000 1354068343 0x02 9 0x00 0x02e1 0xffff 0x0000.003f4de1 0x00c00c61 0x0000.000.00000000 0x00000001 0x00000000 1354074347 0x03 10 0x80 0x02e2 0x0002 0x0000.003f4e6f 0x00c00c61 0x0000.000.00000000 0x00000001 0x00000000 0 0x04 9 0x00 0x02e2 0x000c 0x0000.003f4592 0x00c00c65 0x0000.000.00000000 0x00000001 0x00000000 1354069244-- 索引为0x03的行，state为10,cflags为0x80，表示此行是活动事务。-- wrap#为0x02e2，等于vtransaction里的XIDSQL值，也就是回滚槽被重用了738次。-- uel为0x0002，表示事务当前区，和vrollstat查到的CUREXT(当前区编号)一致。-- scn为0x0000.003f4e6f，转换为十进制是4148847，和vtransaction的START_SCNB()值相符。-- dba为0x00c00c61，事务所占数据文件编号、块编号sys@MAA> select to_number('02e2','xxxxx') from dual;
TO_NUMBER('02E2','XXXXX')------------------------- 738
sys@MAA> select EXTENTS, WRAPS, EXTENDS, CUREXT, CURBLK from vrollstat where usn=9;
EXTENTS WRAPS EXTENDS CUREXT CURBLK
---------- ---------- ---------- ---------- ---------- 3 41 14 2 101
sys@MAA> select to_number(‘3f4e6f’,‘xxxxxxx’) from dual;
TO_NUMBER(‘3F4E6F’,‘XXXXXXX’)----------------------------- 4148847
sys@MAA> select START_SCNB from vSESSION WHERE SID=58);
START_SCNB
---------- 4148847
sys@MAA> select to_number(‘c00c61’,‘xxxxxxx’) from dual;
TO_NUMBER(‘C00C61’,‘XXXXXXX’)----------------------------- 12586081
根据dba地址得到undo block：sys@MAA> select dbms_utility.data_block_address_file(12586081) FILE#, 2 dbms_utility.data_block_address_block(12586081) BLOCK# 3 from dual;
FILE# BLOCK#---------- ---------- 3 3169
和vtransaction的UBAFIL(回滚段所在的数据文件编号)、UBABLK(回滚段块号)值相符sys@MAA> select UBAFIL, UBABLK from vtransaction where ADDR = (SELECT TADDR FROM VSESSION WHERE SID=58);
UBAFIL UBABLK
---------- ---------- 3 3169
再DUMP下Undo块sys@MAA> alter system dump datafile 3 block 3169;
System altered.
sys@MAA> select value from vdiag_info where name = ‘Default Trace File’;
VALUE
----------------------------------------------------------------------------------------------------/u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_15261.trc
TRC内容，部分内容略UNDO BLK:xid: 0x0009.003.000002e2 seq: 0xd1 cnt: 0x24 irb: 0x24 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset---------------------------------------------------------------------------0x01 0x1f90 0x02 0x1f28 0x03 0x1ee4 0x04 0x1e80 0x05 0x1e000x06 0x1c58 0x07 0x1c04 0x08 0x1b44 0x09 0x1b00 0x0a 0x1a9c0x0b 0x19d8 0x0c 0x197c 0x0d 0x121c 0x0e 0x11d8 0x0f 0x11740x10 0x110c 0x11 0x10a8 0x12 0x0f20 0x13 0x0edc 0x14 0x0e780x15 0x0e28 0x16 0x0d6c 0x17 0x0cbc 0x18 0x0c10 0x19 0x0b640x1a 0x0ab4 0x1b 0x0a08 0x1c 0x093c 0x1d 0x0890 0x1e 0x07e40x1f 0x0738 0x20 0x0688 0x21 0x05e4 0x22 0x0558 0x23 0x05000x24 0x04a0-- irb : 0x24,回滚链尾端记录号为0x24
根据irb : 0x24，查找Rec #0x24*-----------------------------* Rec #0x24 slt: 0x03 objn: 24633(0x00006039) objd: 24633 tblspc: 6(0x00000006)* Layer: 11 (Row) opc: 1 rci 0x23Undo type: Regular undo Last buffer split: NoTemp Object: NoTablespace Undo: Nordba: 0x00000000*-----------------------------KDO undo record:KTB Redoop: 0x02 ver: 0x01compat bit: 4 (post-11) padding: 1op: C uba: 0x00c00c61.00d1.22KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x020000de hdba: 0x020000daitli: 2 ispac: 0 maxfr: 4858tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 0ncol: 2 nnew: 1 size: 4col 1: [ 6] 4f 52 41 43 4c 45-- rci 0x23，且rdba: 0x00000000，表示回滚记录都在此块儿上面，如果rdba非0，那就代表回滚链中前一个块所在的位置，我们可以根据它的值再dump相关block查看。-- col 1: [ 6] 4f 52 41 43 4c 45，就是我做最后一次update的前镜像我的更新语句是：update l1 set name=‘XX’ where id=2;而在更新之前name值为ORACLE，十六进制为sys@MAA> select dump(‘ORACLE’,16) from dual;
DUMP(‘ORACLE’,16)--------------------------------------------------------------Typ=96 Len=6: 4f,52,41,43,4c,45-- 可见和col值一致。
Trc往上翻，会找到Rec #0x22和Rec #0x23：----------------------------- Rec #0x22 slt: 0x03 objn: 24633(0x00006039) objd: 24633 tblspc: 6(0x00000006)* Layer: 11 (Row) opc: 1 rci 0x00Undo type: Regular undo Begin trans Last buffer split: NoTemp Object: NoTablespace Undo: Nordba: 0x00000000Ext idx: 0flg2: 0*-----------------------------uba: 0x00c00c61.00d1.21 ctl max scn: 0x0000.003f40f3 prv tx scn: 0x0000.003f4243txn start scn: scn: 0x0000.00000000 logon user: 59 prev brb: 12586070 prev bcl: 0KDO undo record:KTB Redoop: 0x03 ver: 0x01compat bit: 4 (post-11) padding: 1op: Z
KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x020000de hdba: 0x020000daitli: 2 ispac: 0 maxfr: 4858tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0ncol: 2 nnew: 1 size: 3col 1: [ 5] 4c 55 4f 43 53
----------------------------- Rec #0x23 slt: 0x03 objn: 24634(0x0000603a) objd: 24634 tblspc: 6(0x00000006)* Layer: 11 (Row) opc: 1 rci 0x22Undo type: Regular undo Last buffer split: NoTemp Object: NoTablespace Undo: Nordba: 0x00000000*-----------------------------KDO undo record:KTB Redoop: 0x03 ver: 0x01compat bit: 4 (post-11) padding: 1op: Z
KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x020000e3 hdba: 0x020000e2itli: 2 ispac: 0 maxfr: 4858tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0ncol: 2 nnew: 1 size: 3col 1: [ 5] 4c 55 4f 43 53-- 分别就是第一次和第二次update的前镜像，值也一致。
sys@MAA> select dump(‘LUOCS’,16) from dual;
DUMP(‘LUOCS’,16)--------------------------------------------------------Typ=96 Len=5: 4c,55,4f,43,53
另外，我们也可以如下方式dump undo block
sys@MAA> @showtraEnter value for sid: 58old 13: WHERE SID=&SID
new 13: WHERE SID=58
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS
---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------------------- 9 3 738 3 3169 209 36 ACTIVE
sys@MAA> alter system dump undo block '_SYSSMU9_578104089diag_info where name = ‘Default Trace File’;
VALUE
----------------------------------------------------------------------------------------------------/u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_15140.trc
TRC内容如下，部分内容略掉UNDO BLK: Extent: 2 Block: 97 dba (file#, block#): 3,0x00000c61xid: 0x0009.003.000002e2 seq: 0xd1 cnt: 0x24 irb: 0x24 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset---------------------------------------------------------------------------0x01 0x1f90 0x02 0x1f28 0x03 0x1ee4 0x04 0x1e80 0x05 0x1e00
0x06 0x1c58 0x07 0x1c04 0x08 0x1b44 0x09 0x1b00 0x0a 0x1a9c
0x0b 0x19d8 0x0c 0x197c 0x0d 0x121c 0x0e 0x11d8 0x0f 0x1174
0x10 0x110c 0x11 0x10a8 0x12 0x0f20 0x13 0x0edc 0x14 0x0e78
0x15 0x0e28 0x16 0x0d6c 0x17 0x0cbc 0x18 0x0c10 0x19 0x0b64
0x1a 0x0ab4 0x1b 0x0a08 0x1c 0x093c 0x1d 0x0890 0x1e 0x07e4
0x1f 0x0738 0x20 0x0688 0x21 0x05e4 0x22 0x0558 0x23 0x0500
0x24 0x04a0
----------------------------- Rec #0x24 slt: 0x03 objn: 24633(0x00006039) objd: 24633 tblspc: 6(0x00000006)* Layer: 11 (Row) opc: 1 rci 0x23
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000*-----------------------------KDO undo record:KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1op: C uba: 0x00c00c61.00d1.22KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x020000de hdba: 0x020000daitli: 2 ispac: 0 maxfr: 4858tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 0ncol: 2 nnew: 1 size: 4col 1: [ 6] 4f 52 41 43 4c 45
----------------------------- Rec #0x23 slt: 0x03 objn: 24634(0x0000603a) objd: 24634 tblspc: 6(0x00000006)* Layer: 11 (Row) opc: 1 rci 0x22
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000*-----------------------------KDO undo record:KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1op: Z
KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x020000e3 hdba: 0x020000e2itli: 2 ispac: 0 maxfr: 4858tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0ncol: 2 nnew: 1 size: 3col 1: [ 5] 4c 55 4f 43 53
----------------------------- Rec #0x22 slt: 0x03 objn: 24633(0x00006039) objd: 24633 tblspc: 6(0x00000006)* 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: 0flg2: 0*-----------------------------uba: 0x00c00c61.00d1.21 ctl max scn: 0x0000.003f40f3 prv tx scn: 0x0000.003f4243txn start scn: scn: 0x0000.00000000 logon user: 59 prev brb: 12586070 prev bcl: 0KDO undo record:KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1op: Z
KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x020000de hdba: 0x020000daitli: 2 ispac: 0 maxfr: 4858tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0ncol: 2 nnew: 1 size: 3col 1: [ 5] 4c 55 4f 43 53
其实，我们只要知道Undo里会保存我们所做DML操作的前镜像(非永久)即可，至于如何去查找它们并应用交给Oracle就行了，就当它是一个黑盒子。
当然，对底层感兴趣的朋友可以深入研究。
