回滚段笔记

最新推荐文章于 2024-10-17 10:02:06 发布

weixin_33856370

最新推荐文章于 2024-10-17 10:02:06 发布

阅读量86

点赞数

文章标签：数据库

原文链接：http://blog.51cto.com/hunt1574/1226504

版权

更新一条记录：

conn scott/admin
update emp set sal=5000 where empno=7788;

事务信息：

select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;
--xidusn回滚段编号
select usn,writes,rssize,xacts,hwmsize,shrinks,wraps from v$rollstat where xacts=1;
select * from v$rollname where usn=&xidusn;

dump回滚段：

alter system dump undo header "_SYSSMU1$";
通过转储文件可以看到以下内容：
********************************************************************************
Undo Segment:  _SYSSMU1$ (1)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0      spare2: 0      #extents: 3      #blocks: 143
last map  0x00000000  #maps: 0      offset: 4080
Highwater::  0x00800697  ext#: 2      blk#: 14     ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk  0x00000000  offset: 2
Unlocked
Map Header:: next  0x00000000  #extents: 3    obj#: 0      flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0080000a  length: 7
0x008000f9  length: 8
0x00800689  length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0  Commit Time: 1371517425
Extent Number:1  Commit Time: 1371517432
Extent Number:2  Commit Time: 1371513608
TRN CTL:: seq: 0x4411 chd: 0x0006 ctl: 0x000f inc: 0x00000000 nfb: 0x0001
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00800697.4411.01 scn: 0x0000.05d235b9
Version: 0x01
FREE BLOCK POOL::
uba: 0x00800697.4411.01 ext: 0x2  spc: 0x1f88
uba: 0x00000000.4411.04 ext: 0x2  spc: 0x1e12
uba: 0x00000000.440e.03 ext: 0x3  spc: 0xb94
uba: 0x00000000.4244.01 ext: 0x2  spc: 0x1f88
uba: 0x00000000.2864.01 ext: 0x2  spc: 0x1f88
TRN TBL::
index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
------------------------------------------------------------------------------------------------
0x00    9    0x00  0x727d  0x001f  0x0000.05d236f8  0x00800694  0x0000.000.00000000  0x00000001   0x00000000  1371518173
......
0x26   10    0x80  0x727c  0x0002  0x0000.05d23ca1  0x00800696  0x0000.000.00000000  0x00000001   0x00000000  0
0x27    9    0x00  0x7280  0x0002  0x0000.05d237c7  0x00800694  0x0000.000.00000000  0x00000001   0x00000000  1371518320
0x28    9    0x00  0x727c  0x000f  0x0000.05d23ccf  0x00800696  0x0000.000.00000000  0x00000001   0x00000000  1371518881
0x29    9    0x00  0x727b  0x000b  0x0000.05d23859  0x00800695  0x0000.000.00000000  0x00000001   0x00000000  1371518397
0x2a    9    0x00  0x727a  0x0008  0x0000.05d239a2  0x00800695  0x0000.000.00000000  0x00000001   0x00000000  1371518560
0x2b    9    0x00  0x727d  0x000c  0x0000.05d23777  0x00800694  0x0000.000.00000000  0x00000001   0x00000000  1371518254
0x2c    9    0x00  0x727d  0x0023  0x0000.05d236cb  0x00800693  0x0000.000.00000000  0x00000001   0x00000000  1371518156
0x2d    9    0x00  0x727b  0x0018  0x0000.05d23728  0x00800694  0x0000.000.00000000  0x00000001   0x00000000  1371518202
state为10的代表活动事务。
select to_number('26', 'xxxxxxx') from dual;
TO_NUMBER('26','XXXXXX')
------------------------
38
该值与v$transaction中xidslot的值相同。

dump前镜像：

DBA(data block address)前镜像地址为：0x00800696 转换为2进制为：0000 0000 1000 0000 0000 0000 0110 1001 0110前10位代表文件号为2，后22位代表block号为1686，该前镜像信息位于file 2 block 1686,事务表查询结果为：

SELECT  b.USERNAME,a.xidusn,a.xidslot,a.xidsqn,a.ubablk,a.ubafil,a.ubarec FROM v$transaction a,v$session b where a.SES_ADDR=b.SADDR;
USERNAME    XIDUSN  XIDSLOT XIDSQN  UBABLK  UBAFIL  UBAREC
1   SCOTT       1   38  29308   1686    2   14

在前一session继续更新：

update emp set sal=5000 where empno=7782;
update emp set sal=5000 where empno=7698;

dump当前block:

alter system dump datafile 2 block 1686;
********************************************************************************
UNDO BLK:
xid: 0x0006.028.0000729f  seq: 0x3eac cnt: 0x2d  irb: 0x2d  icl: 0x0   flg: 0x0000
Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f7c     0x02 0x1f38     0x03 0x1ef4     0x04 0x1eb0     0x05 0x1e6c
0x06 0x1d9c     0x07 0x1d18     0x08 0x1c88     0x09 0x1c04     0x0a 0x1b80
0x0b 0x1afc     0x0c 0x1a80     0x0d 0x19b0     0x0e 0x192c     0x0f 0x189c
0x10 0x1818     0x11 0x1794     0x12 0x1710     0x13 0x1694     0x14 0x1610
0x15 0x1594     0x16 0x152c     0x17 0x14c0     0x18 0x143c     0x19 0x13c0
0x1a 0x1370     0x1b 0x1304     0x1c 0x1280     0x1d 0x1204     0x1e 0x11b4
0x1f 0x1148     0x20 0x1084     0x21 0x1000     0x22 0x0f70     0x23 0x0eec
0x24 0x0e68     0x25 0x0de4     0x26 0x0d68     0x27 0x0ce4     0x28 0x0c68
0x29 0x0c18     0x2a 0x0bac     0x2b 0x0b14     0x2c 0x0abc     0x2d 0x0a64
irb: 指向最近未提交事务的的起始点。
根据irb找到以下记录。最后更新的事务的undo chain指针0x00,
*-----------------------------
* Rec #0x2b  slt: 0x28  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: 0x00802b2a.3eac.27 ctl max scn: 0x0000.05d75754 prv tx scn: 0x0000.05d7576a
txn start scn: scn: 0x0000.00000000 logon user: 54
prev brb: 8399655 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04  ver: 0x01
op: L  itl: xid:  0x0008.027.00007072 uba: 0x008000ec.4883.02
flg: C---    lkc:  0     scn: 0x0000.0591e02e
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: 191
ncol: 8 nnew: 1 size: 0
Vector content:
col  5: [ 2]  c2 33
*-----------------------------
* Rec #0x2c  slt: 0x28  objn: 51148(0x0000c7cc)  objd: 51148  tblspc: 4(0x00000004)
*       Layer:  11 (Row)   opc: 1   rci 0x2b
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: 0x00802b2a.3eac.2b
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: 6(0x6) flag: 0x2c lock: 0 ckix: 191
ncol: 8 nnew: 1 size: 0
Vector content:
col  5: [ 2]  c2 33
*-----------------------------
* Rec #0x2d  slt: 0x28  objn: 51148(0x0000c7cc)  objd: 51148  tblspc: 4(0x00000004)
*       Layer:  11 (Row)   opc: 1   rci 0x2c
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: 0x00802b2a.3eac.2c
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: 5(0x5) flag: 0x2c lock: 0 ckix: 191
ncol: 8 nnew: 1 size: 0
Vector content:
col  5: [ 2]  c2 33
SQL> select utl_raw.cast_to_number('c233') from dual;
UTL_RAW.CAST_TO_NUMBER('C233')
------------------------------
5000

从x$bh获得数据块的使用情况:

SELECT b.segment_name, a.file#, a.dbarfil, a.dbablk, a.class, a.state,decode(bitand(flag,1),0,'N','Y') dirty
FROM x$bh a, dba_extents b
where a.dbarfil = b.relative_fno
and b.block_id <= a.dbablk
and b.block_id + b.blocks > a.dbablk
and b.owner = 'SCOTT'
and b.segment_name = 'EMP';
前镜像信息中，前镜像对应的数据块地址也被记录了下来，可以从bdba记录中获得。bdba: 0x01000020转换为2进制
0000 0001 0000 0000 0000 0000 0000 0010 0000对应的数据为file 4 block 32

dump file#及dbablk:

Dump block:
alter system dump datafile 4 block 32;
trace文件部分内容：
Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0001.026.0000727c  0x00800696.4411.10  C---    0  scn 0x0000.05d44076
0x02   0x0006.028.0000729f  0x00802b2a.3eac.2d  ----    3  fsc 0x0000.00000000
xid  transaction ID[Undo.Segment.Number+Transaction.Table.Slot.Number+Wrap];
Uba  Undo Block Address
Lck  Lock Status
0x0006.028.0000729f 分解事务指向6号回滚段，solt号为0x28(10进制对应40)，wrap#为729f,下是6号回滚段的dump信息：
index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
------------------------------------------------------------------------------------------------
0x28   10    0x80  0x729f  0x0002  0x0000.00000000  0x00802b2a  0x0000.000.00000000  0x00000001   0x00000000  0
Uba(Undo Block Address):0x00802b2a.3eac.2d 中 0x00802b2a指前镜像地址，seq,3eac是顺序号，2d是UNDO记录的开始地址(irb信息）
uba的内容与Undo中的信息完全符合：
UNDO BLK:
xid: 0x0006.028.0000729f  seq: 0x3eac cnt: 0x2d  irb: 0x2d  icl: 0x0   flg: 0x0000
3条记录被修改，锁定位信息LB指向0x02号Itl:
tab 0, row 5, @0x1d11
tl: 40 fb: --H-FL-- lb: 0x2  cc: 8
col  0: [ 3]  c2 4d 63
col  1: [ 5]  42 4c 41 4b 45
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 05 01 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 1f
tab 0, row 6, @0x1d39
tl: 40 fb: --H-FL-- lb: 0x2  cc: 8
col  0: [ 3]  c2 4e 53
col  1: [ 5]  43 4c 41 52 4b
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 06 09 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 0b
tab 0, row 7, @0x1e4c
tl: 40 fb: --H-FL-- lb: 0x2  cc: 8
col  0: [ 3]  c2 4e 59
col  1: [ 5]  53 43 4f 54 54
col  2: [ 7]  41 4e 41 4c 59 53 54
col  3: [ 3]  c2 4c 43
col  4: [ 7]  77 bb 04 13 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 15

摘自eygle《深入浅出oracle》

转载于:https://blog.51cto.com/hunt1574/1226504