ITPub博客

首页 > 应用开发 > IT综合 > undo的初步研究

undo的初步研究

原创 IT综合 作者:jolly10 时间:2008-11-10 14:08:11 0 删除 编辑
看深入浅出时做的一些实验,算是了解一个大概了吧。[@more@]

Session 1:

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> update emp set sal=4000 where empno=7788 and sal=3000;

1 row updated.

Session 2:


看到session 1的事务位于6号回滚段(XIDUSN),3号事务槽(XIDSLOT)

SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;

XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC
---------- ---------- ---------- ---------- ---------- ----------
6 3 591 1122 2 33


从v$rollstat可获得事务的信息,XACTS表示活动事务的数量,同样看到该事务位于6号回滚段

SQL> select usn,writes,rssize,xacts,hwmsize,shrinks,wraps from v$rollstat;

USN WRITES RSSIZE XACTS HWMSIZE SHRINKS WRAPS
---------- ---------- ---------- ---------- ---------- ---------- ----------
0 24800 385024 0 385024 0 0
1 433078 516096 0 516096 0 7
2 247450 319488 0 319488 0 4
3 383922 516096 0 516096 0 7
4 817382 2088960 0 2088960 0 16
5 506990 712704 0 712704 0 9
6 608826 843776 1 843776 0 12
7 509312 712704 0 712704 0 9
8 336446 450560 0 450560 0 6
9 3672318 4186112 0 4186112 0 18
10 328002 450560 0 450560 0 5

11 rows selected.


获得回滚段的名称并转储段头信息
SQL> select * from v$rollname where usn=6;

USN NAME
---------- ------------------------------
6 _SYSSMU6$

SQL> alter system dump undo header '_SYSSMU6$';

System altered.

SQL> SELECT d.VALUE
2 || '/'
3 || LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
4 5 6 || '.trc' trace_file_name
7 FROM (SELECT p.spid
8 FROM v$mystat m, v$session s, v$process p
9 WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p ,
10 (SELECT t.INSTANCE
11 FROM v$thread t, v$parameter v
12 WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
13 14 (SELECT VALUE
15 FROM v$parameter
16 WHERE NAME = 'user_dump_dest') d
17 /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/admin/orcl/udump/orcl_ora_4837.trc


********************************************************************************
Undo Segment: _SYSSMU6$ (6)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 13 #blocks: 103
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x00800463 ext#: 12 blk#: 2 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 12
Unlocked
Map Header:: next 0x00000000 #extents: 13 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0080005a length: 7
0x00800061 length: 8
0x008000a9 length: 8
0x008000b1 length: 8
0x00800131 length: 8
0x00800149 length: 8
0x00800291 length: 8
0x008002e9 length: 8
0x00800361 length: 8
0x00800419 length: 8
0x00800421 length: 8
0x00800459 length: 8
0x00800461 length: 8

Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1225112446
Extent Number:1 Commit Time: 1225112446
Extent Number:2 Commit Time: 1225112446
Extent Number:3 Commit Time: 1225116009
Extent Number:4 Commit Time: 1225116021
Extent Number:5 Commit Time: 1225116031
Extent Number:6 Commit Time: 1225116039
Extent Number:7 Commit Time: 1225116054
Extent Number:8 Commit Time: 1225116054
Extent Number:9 Commit Time: 1225116061
Extent Number:10 Commit Time: 1225116061
Extent Number:11 Commit Time: 1225116061
Extent Number:12 Commit Time: 1225116061

TRN CTL:: seq: 0x02cd chd: 0x0000 ctl: 0x0001 inc: 0x00000000 nfb: 0x0001
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00800462.02cd.21 scn: 0x0000.00146c6b
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.02cd.20 ext: 0xc spc: 0xd8a
uba: 0x00800463.02cd.08 ext: 0xc spc: 0x1832
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
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 0x024e 0x0005 0x0000.00146c6c 0x008002eb 0x0000.000.00000000 0x00000002 0x00000000 1225116042
0x01 9 0x00 0x024f 0xffff 0x0000.0014707f 0x00800462 0x0000.000.00000000 0x00000001 0x00000000 1225116608
0x02 9 0x00 0x024f 0x0001 0x0000.0014703d 0x00800462 0x0000.000.00000000 0x00000001 0x00000000 1225116488
0x03 10 0x80 0x024f 0x000c 0x0000.00147075 0x00800462 0x0000.000.00000000 0x00000001 0x00000000 0
0x04 9 0x00 0x024e 0x0007 0x0000.00146c8e 0x008002ed 0x0000.000.00000000 0x00000003 0x00000000 1225116043
0x05 9 0x00 0x024e 0x0006 0x0000.00146c8c 0x008002eb 0x0000.000.00000000 0x00000001 0x00000000 1225116043
...

状态为10代表是活动的事务,该事务正好占用的是第3号事务槽(0x03),DBA(data block address)是0x00800462代表数据块的存储地址,转换为二进制就是0000 0000 1000 0000 0000 0100 0110 0010,一共是32位,前10位代表文件号,后22位代表block,转成10进制后文件号为2,block_id为1122.正好对应于v$transaction的UBAFIL和UBABLK栏位.


为了说明其它内容,在session 1再更新2条记录.

SESSION 1:

SQL> update emp set sal=4000 where empno=7782 and sal=2450;

1 row updated.

SQL> update emp set sal=4000 where empno=7698 and sal=2850;

1 row updated.


SESSION 2:

dump出文件号为2,block_id为1122回滚段信息

SQL> alter system dump datafile 2 block 1122;


Start dump data blocks tsn: 1 file#: 2 minblk 1122 maxblk 1122
buffer tsn: 1 rdba: 0x00800462 (2/1122)
scn: 0x0000.001475f4 seq: 0x01 flg: 0x00 tail: 0x75f40201
frmt: 0x02 chkval: 0x0000 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CEB6800 to 0x0CEB8800
CEB6800 0000A202 00800462 001475F4 00010000 [....b....u......]
CEB6810 00000000 00030006 0000024F 232302CD [........O.....##]
CEB6820 1FE80000 1F181F70 1E401EC4 1D6C1DD8 [....p.....@...l.]
...

下面有一个参数irb: 0x23,irb指的是回滚段中记录的最近的未提交变更开始之处,如果开始回滚,就是从这个点开始搜索.
********************************************************************************
UNDO BLK:
xid: 0x0006.003.0000024f seq: 0x2cd cnt: 0x23 irb: 0x23 icl: 0x0 flg: 0x0000

接下来是回滚信息的偏移量,最后一个偏移地址正是0x23

Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f70 0x02 0x1f18 0x03 0x1ec4 0x04 0x1e40 0x05 0x1dd8
0x06 0x1d6c 0x07 0x1be0 0x08 0x1b44 0x09 0x1ad4 0x0a 0x1a74
0x0b 0x19f0 0x0c 0x1988 0x0d 0x191c 0x0e 0x178c 0x0f 0x16f0
0x10 0x1680 0x11 0x1620 0x12 0x154c 0x13 0x14e8 0x14 0x1404
0x15 0x139c 0x16 0x1344 0x17 0x12ac 0x18 0x1228 0x19 0x119c
0x1a 0x1118 0x1b 0x109c 0x1c 0x1018 0x1d 0x0f80 0x1e 0x0e9c
0x1f 0x0e34 0x20 0x0ddc 0x21 0x0d5c 0x22 0x0d00 0x23 0x0ca4

下面有0x23的信息,c2 1d 33转换成十进制为2850.

研究了一下转换方法:
c2 1d 33转换十进制数为194,29,51.
所存储的数值计算方法为:∑每个数字位*100^(指数-N),其中N是有效位数的顺序位,第一个有效位的N=0

指数位的换算:
正数:指数=第一个字节-193
负数:指数=62-第一个字节

有效位的存储
正数:加1存储(为了避免null)
负数:被101减,如果总长度小于21个字节,最后加一个102(是为了排序的需要)

可以看出194为指数,指数位为194-193=1

(29-1)*100^(194-193-0)=2800,(51-1)*100^(194-193-1)=50,2800+50=2850.

好,2850就是更新记录的前镜像,oracle就是这样通过回滚段保留前镜像信息的.

*-----------------------------
* Rec #0x23 slt: 0x03 objn: 51148(0x0000c7cc) objd: 51148 tblspc: 4(0x00000004)
* 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: 0x02 ver: 0x01
op: C uba: 0x00800462.02cd.22
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000020 hdba: 0x0100001b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 5(0x5) flag: 0x2c lock: 0 ckix: 0
ncol: 8 nnew: 1 size: 1
col 5: [ 3] c2 1d 33


objd:51148表示object_id,就是SCOTT.EMP
SQL> select owner,object_name From dba_objects where object_id='51148';

OWNER OBJECT_NAME
------- -------------------
SCOTT EMP

在这条UNDO记录上,还记录了一个数据rci:0x22,该参数就是Undo Chain(同一个事务中的多次修改,根据Chain链接关联)的下一个偏移量,此处是0x22,再看看0x22的记录:

c2 19 33转换后为2450,正是第二条更新的记录
SQL> update emp set sal=4000 where empno=7782 and sal=2450;

同样rci为下一个链接,为0x21

*-----------------------------
* Rec #0x22 slt: 0x03 objn: 51148(0x0000c7cc) objd: 51148 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x21
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
op: C uba: 0x00800462.02cd.21
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000020 hdba: 0x0100001b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 6(0x6) flag: 0x2c lock: 0 ckix: 0
ncol: 8 nnew: 1 size: 1
col 5: [ 3] c2 19 33

0x21的值为c2 1f,转换后为3000,正是第一条修改的记录
SQL> update emp set sal=4000 where empno=7788 and sal=3000;

rci为0x00表示这是最后一条记录了.


*-----------------------------
* Rec #0x21 slt: 0x03 objn: 51148(0x0000c7cc) objd: 51148 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: 0x00000000
*-----------------------------
uba: 0x00800462.02cd.1e ctl max scn: 0x0000.00146c6a prv tx scn: 0x0000.00146c6b
txn start scn: scn: 0x0000.00146fff logon user: 54
prev brb: 8389354 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000020 hdba: 0x0100001b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 0 ckix: 0
ncol: 8 nnew: 1 size: 0
Vector content:
col 5: [ 2] c2 1f

可以从x$bh中找到这些数据块

SQL> select b.segment_name,a.file#,a.dbarfil,a.dbablk,a.class,a.state
from x$bh a,dba_extents b
where b.relative_fno=a.dbarfil
and b.block_id<=a.dbablk and b.block_id+b.blocks>a.dbablk
and b.owner='SCOTT' and b.segment_name='EMP' 2 3 4 5 ;

SEGMENT_NA FILE# DBARFIL DBABLK CLASS STATE
---------- ---------- ---------- ---------- ---------- ----------
EMP 4 4 31 1 1
EMP 4 4 28 1 1
EMP 4 4 30 1 1
EMP 4 4 27 4 1
EMP 4 4 32 1 1
EMP 4 4 29 1 1


CLASS为4是段头,CLASS为1是数据块.如果此时其它进程查询scott.emp表,oracle需要构造一致性读,通过前镜像把变化前的数据展现给用户:

Session 2:
SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

再来查询,发现多出了几个state为3的记录,这些就是一致性读构造的前镜像.

SQL> select b.segment_name,a.file#,a.dbarfil,a.dbablk,a.class,a.state
from x$bh a,dba_extents b
where b.relative_fno=a.dbarfil
and b.block_id<=a.dbablk and b.block_id+b.blocks>a.dbablk
and b.owner='SCOTT' and b.segment_name='EMP' 2 3 4 5 ;

SEGMENT_NA FILE# DBARFIL DBABLK CLASS STATE
---------- ---------- ---------- ---------- ---------- ----------
EMP 4 4 31 1 1
EMP 4 4 28 1 1
EMP 4 4 30 1 1
EMP 4 4 27 4 1
EMP 4 4 32 1 3
EMP 4 4 32 1 1
EMP 4 4 32 1 3
EMP 4 4 32 1 3
EMP 4 4 29 1 1

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/271283/viewspace-1013155/,如需转载,请注明出处,否则将追究法律责任。

上一篇: buffer cache的疑问
请登录后发表评论 登录
全部评论

注册时间:2008-02-20

  • 博文量
    263
  • 访问量
    769016