在一个事务里有多条更新如何从Undo查找前镜像
来源：互联网 发布：知乎内容推送到kindle 编辑：程序博客网 时间：2024/06/06 00:59
在一个事务里有多条更新如何从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 v$lock where TYPE in ('TM','TX') order by 1,2/查看事务信息[oracle@maa3 ~]$ cat showtra.sql select XIDUSN, XIDSLOT, XIDSQN, UBAFIL, UBABLK, UBASQN, UBAREC, STATUS from v$transactionwhere ADDR = ( SELECT TADDR FROM V$SESSION WHERE SID=&SID )/
==== 实验开始 ====
luocs@MAA> select sid from v$mystat where rownum=1; SID---------- 58luocs@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 0sys@MAA> @showtraEnter value for sid: 58old 13: WHERE SID=&SIDnew 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$rollname where usn=9;NAME------------------------------------------------------------_SYSSMU9_578104089$DUMP出该回退段头信息sys@MAA> alter system dump undo header '_SYSSMU9_578104089$';System altered.sys@MAA> select value from v$diag_info where name = 'Default Trace File';VALUE-------------------------------------------------------------------------------------------------------------------/u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_14815.trcTRC内容，部分内容略掉 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，等于v$transaction里的XIDSQL值，也就是回滚槽被重用了738次。-- uel为0x0002，表示事务当前区，和v$rollstat查到的CUREXT(当前区编号)一致。-- scn为0x0000.003f4e6f，转换为十进制是4148847，和v$transaction的START_SCNB()值相符。-- dba为0x00c00c61，事务所占数据文件编号、块编号sys@MAA> select to_number('02e2','xxxxx') from dual;TO_NUMBER('02E2','XXXXX')------------------------- 738sys@MAA> select EXTENTS, WRAPS, EXTENDS, CUREXT, CURBLK from v$rollstat where usn=9; EXTENTS WRAPS EXTENDS CUREXT CURBLK---------- ---------- ---------- ---------- ---------- 3 41 14 2 101sys@MAA> select to_number('3f4e6f','xxxxxxx') from dual;TO_NUMBER('3F4E6F','XXXXXXX')----------------------------- 4148847sys@MAA> select START_SCNB from v$transaction where ADDR = (SELECT TADDR FROM V$SESSION WHERE SID=58);START_SCNB---------- 4148847sys@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和v$transaction的UBAFIL(回滚段所在的数据文件编号)、UBABLK(回滚段块号)值相符sys@MAA> select UBAFIL, UBABLK from v$transaction where ADDR = (SELECT TADDR FROM V$SESSION 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 v$diag_info where name = 'Default Trace File';VALUE----------------------------------------------------------------------------------------------------/u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_15261.trcTRC内容，部分内容略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: ZKDO 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: ZKDO 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=&SIDnew 13: WHERE SID=58 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------------------- 9 3 738 3 3169 209 36 ACTIVEsys@MAA> alter system dump undo block '_SYSSMU9_578104089$' xid 9 3 738;System altered.sys@MAA> select value from v$diag_info where name = 'Default Trace File';VALUE----------------------------------------------------------------------------------------------------/u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_15140.trcTRC内容如下，部分内容略掉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: ZKDO 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: ZKDO 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就行了，就当它是一个黑盒子。
当然，对底层感兴趣的朋友可以深入研究。
Permalink
- 在一个事务里有多条更新如何从Undo查找前镜像
- mysql 从库如何跳过一个事务
- Oracle UPDATE、DELETE、INSERT与Undo(UPDATE、DELETE如何从Undo里找到前镜像)
- 在oracle下，UPDATE、DELETE操作如何从Undo里找到其修改值得前镜像(模拟实验)
- 如何在 Linux 中查找一个文件
- 如何在 Linux 中查找一个文件
- 如何在 Linux 中查找一个文件
- 如何在 Linux 中查找一个文件
- 如何在Linux中查找一个文件
- 如何在Linux中查找一个文件
- mysql undo redo log在事务中起的作用
- 如何开启一个事务
- 如何在Linux下查找一个类型的定义
- eclipse中如何在当前工程中查找一个字符串
- eclipse中如何在当前工程中查找一个字符串
- eclipse中如何在当前工程中查找一个字符串
- eclipse中如何在当前工程中查找一个字符串
- RO39 – 在一个事务中实现多个ClientDataSets 更新
- python学习笔记
- 华为笔试面试题大全
- java中list、set和map 的区别
- ORACLE EBS 计划功能 理论整理
- 实现CCLayer只显示一个矩形可见区域
- 在一个事务里有多条更新如何从Undo查找前镜像
- Redis的master/slave复制(主从复制)
- adb 常用命令
- toString()以及__toString()
- Ubuntu 12.04 改造指南
- 改写IKAnalyzer分词器
- 11个在线编码大赛
- hadoop部署错误集锦
- php发送get、post请求的几种方法
