Oracle undo 镜像数据探究
我们都知道，undo是为了保证一致性读的，也就是说你在对更改的数据但是没有提交，那么其他session是无法查看到你更改的内容的，如果对方 进行读取数据，依然是之前的数据，undo就是保存了数据的前镜像。看完我写的这篇文章之后，你就知道undo是怎么保存前镜像的以及它的内部机 制是什么。现在开始挖掘undo是怎么保存前镜像的。
推荐阅读：
1）首先更新一条数据，但是不进行commit如下：
SQL> col name for a50
SQL> select obj#,name from t where rownum<5;
OBJ# NAME
---------- --------------------------------------------------
46 I_USER1
28 Rhys1
15 RHYS20
29 RHYS3
SQL> update t set name='rhys1' where obj#=46;
1 row updated.
SQL> update t set name='rhys2' where obj#=28;
1 row updated.
SQL> update t set name='rhys3' where obj#=15;
1 row updated.
SQL> update t set name='rhys4' where obj#=29;
1 row updated.
SQL> select xidusn,xidslot,ubafil,ubablk from v$transaction;
XIDUSN XIDSLOT UBAFIL UBABLK
---------- ---------- ---------- ----------
12 7 8 1493
SQL> select * from v$rollname where usn=12;
USN NAME
---------- ------------------------------
12 _SYSSMU12_584745277$
SQL> select usn,status,curext from v$rollstat;
USN STATUS CUREXT
---------- --------------- ----------
0 ONLINE 5
11 ONLINE 4
12 ONLINE 3
13 ONLINE 2
14 ONLINE 3
15 ONLINE 2
16 ONLINE 2
17 ONLINE 14
18 ONLINE 4
19 ONLINE 3
20 ONLINE 2
11 rows selected.
SQL> select usn,status,curext,xacts from v$rollstat where xacts>0;
USN STATUS CUREXT XACTS
---------- --------------- ---------- ----------
12 ONLINE 3 1
SQL> alter system dump undo header '_SYSSMU12_584745277$';
System altered.
SQL> alter system dump datafile 8 block 1493;
System altered.
SQL> col name for a80
SQL> col inst_id for 9999
SQL> col value for a80
SQL> select * from v$diag_info;
INST_ID NAME VALUE
------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 Diag Enabled TRUE
1 ADR Base /opt/app/oracle
1 ADR Home /opt/app/oracle/diag/rdbms/rhys/RHYS
1 Diag Trace /opt/app/oracle/diag/rdbms/rhys/RHYS/trace
1 Diag Alert /opt/app/oracle/diag/rdbms/rhys/RHYS/alert
1 Diag Incident /opt/app/oracle/diag/rdbms/rhys/RHYS/incident
1 Diag Cdump /opt/app/oracle/diag/rdbms/rhys/RHYS/cdump
1 Health Monitor /opt/app/oracle/diag/rdbms/rhys/RHYS/hm
1 Default Trace File /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_5284.trc
1 Active Problem Count 1
1 Active Incident Count 1
11 rows selected.
2）从以上步骤中，我们可以获得的信息如下：
该事务使用的undo段为12，其区为3
undo段的名字为“_SYSSMU12_584745277$”
该事务在undo的第7个槽位，数据文件号为8，使用的数据块为1493；
3）我们开始挖掘undo内部机制，首先转储undo header；（摘录信息如下）
********************************************************************************
Undo Segment: _SYSSMU12_584745277$ (12)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 4 #blocks: 271
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x020005d5 ext#: 3 blk#: 85 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 3
Unlocked
Map Header:: next 0x00000000 #extents: 4 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x02000091 length: 7
0x02000168 length: 8
0x02000280 length: 128
0x02000580 length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1376748046
Extent Number:1 Commit Time: 1376748092
Extent Number:2 Commit Time: 1376804143
Extent Number:3 Commit Time: 1376804143
TRN CTL:: seq: 0x033a chd: 0x0020 ctl: 0x0001 inc: 0x00000000 nfb: 0x0000
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x020005d5.033a.24 scn: 0x0000.009b9276
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.033a.23 ext: 0x3 spc: 0xc78
uba: 0x00000000.033a.27 ext: 0x3 spc: 0xb20
uba: 0x00000000.033a.08 ext: 0x3 spc: 0x1c66
uba: 0x00000000.033a.45 ext: 0x3 spc: 0x200
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x0059 0x0016 0x0000.009b9292 0x020005d3 0x0000.000.00000000 0x00000001 0x00000000 1376806203
0x01 9 0x00 0x005a 0xffff 0x0000.009b94e5 0x020005d5 0x0000.000.00000000 0x00000001 0x00000000 1376806887
0x02 9 0x00 0x005a 0x000a 0x0000.009b9461 0x020005d5 0x0000.000.00000000 0x00000001 0x00000000 1376806707
0x03 9 0x00 0x0059 0x0017 0x0000.009b930e 0x020005d3 0x0000.000.00000000 0x00000001 0x00000000 1376806203
0x04 9 0x00 0x0059 0x0005 0x0000.009b92cf 0x020005d3 0x0000.000.00000000 0x00000001 0x00000000 1376806203
0x05 9 0x00 0x0059 0x0008 0x0000.009b92d8 0x020005d3 0x0000.000.00000000 0x00000001 0x00000000 1376806203
0x06 9 0x00 0x0059 0x0009 0x0000.009b92aa 0x020005d3 0x0000.000.00000000 0x00000001 0x00000000 1376806203
0x07 10 0x80 0x005a 0x0003 0x0000.00000000 0x020005d5 0x0000.000.00000000 0x00000001 0x00000000 0
0x08 9 0x00 0x0059 0x001b 0x0000.009b92e1 0x020005d3 0x0000.000.00000000 0x00000001 0x00000000 1376806203
0x09 9 0x00 0x0059 0x000c 0x0000.009b92b6 0x020005d3 0x0000.000.00000000 0x00000001 0x00000000 1376806203
0x0a 9 0x00 0x0059 0x001f 0x0000.009b9479 0x020005d5 0x0000.000.00000000 0x00000001 0x00000000 1376806707
0x0b 9 0x00 0x0059 0x001a 0x0000.009b92fc 0x020005d3 0x0000.000.00000000 0x00000001 0x00000000 1376806203
0x0c 9 0x00 0x0059 0x0004 0x0000.009b92c3 0x020005d3 0x0000.000.00000000 0x00000001 0x00000000 1376806203
可以看到在事务表中0x07正是第七个slot，把dba转为2进制，在转为10进制，可以打出数据文件号为8，数据块为1493，这正是数据的前镜像。