oracle的undo的工作过程

1.从DML更新事务开始

来看这个更新语句:

SQL> conn scott/scott
Connected.
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            10         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

      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>

SQL> select sal from emp where empno=7788;

       SAL
----------
        10

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

1 row updated.

SQL> select sal from emp where empno=7788;

       SAL
----------
      4000

先不提交这个事务,在另外窗口新开session,使用sys用户查询相关信息,进行进一步的分析

2.获得事务信息
从事务表中可以获得关于这个事务的信息,该事务位于9号回滚段(XIDUSN),在9号回滚段
上,该事务位于第29号事务槽(XIDSLOT):

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

    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBAREC
---------- ---------- ---------- ---------- ---------- ----------
         9         29        385       1350          2         22

从v$rollstat视图中也可获得事务信息,xacts字段代表的是活动事务的数量,同样看到该事务
位于9号回滚段

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

       USN     WRITES     RSSIZE      XACTS    HWMSIZE    SHRINKS      WRAPS
---------- ---------- ---------- ---------- ---------- ---------- ----------
         0       7620     385024          0     385024          0          0
         1      21390   29351936          0   29351936          0          0
         2      22108    3268608          0    3268608          0          0
         3      29954     450560          0     450560          0          0
         4      23700     843776          0     843776          0          0
         5      23334     450560          0     450560          0          0
         6      21082     450560          0     450560          0          0
         7      23146    2285568          0    2285568          0          0
         8      28742     843776          0     843776          0          1
         9      22648    2088960          1    2088960          0          0
        10      24326    2220032          0    2220032          0          0

11 rows selected.

获得回滚段名称并转储段头信息
查询v$rollname视图获得回滚段名称,并转储回滚段头信息:

SQL> select * from v$rollname a where a.usn=9;

       USN NAME
---------- ------------------------------
         9 _SYSSMU9$

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

System altered.

生成的跟踪文件如下:

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from sys.v$mystat m,
  6  sys.v$session s,sys.v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from sys.v$thread  t,sys.v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from sys.v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_2611.trc

4.获得跟踪文件信息
注意这就是前边多闪提到过的回滚段头信息,其中包括事务表信息,从以下的跟踪文件中,
可以清晰地看到这些内容:

*** 2013-01-07 08:19:09.209

********************************************************************************
Undo Segment:  _SYSSMU9$ (9)
********************************************************************************
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 17     #blocks: 255
                  last map  0x00000000  #maps: 0      offset: 4080
      Highwater::  0x00800546  ext#: 10     blk#: 61     ext size: 128
  #blocks in seg. hdr's freelists: 0
  #blocks below: 0
  mapblk  0x00000000  offset: 10
                   Unlocked
     Map Header:: next  0x00000000  #extents: 17   obj#: 0      flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x0080008a  length: 7
   0x008000f1  length: 8
   0x008001d1  length: 8
   0x008001d9  length: 8
   0x008001e1  length: 8
   0x008001e9  length: 8
   0x008001f1  length: 8
   0x008001f9  length: 8
   0x00800201  length: 8
   0x00800289  length: 8
   0x00800509  length: 128
   0x00800011  length: 8
   0x00800041  length: 8
   0x00800061  length: 8
   0x008000d9  length: 8
   0x008000e9  length: 8
   0x008000a9  length: 8

 Retention Table
  -----------------------------------------------------------
 Extent Number:0  Commit Time: 1388711270
 Extent Number:1  Commit Time: 1388711270
 Extent Number:2  Commit Time: 1388711270
 Extent Number:3  Commit Time: 1388711270
 Extent Number:4  Commit Time: 1388711270
 Extent Number:5  Commit Time: 1388711270
 Extent Number:6  Commit Time: 1388711270
 Extent Number:7  Commit Time: 1388711270
 Extent Number:8  Commit Time: 1388711270
 Extent Number:9  Commit Time: 1388712870
 Extent Number:10  Commit Time: 0
 Extent Number:11  Commit Time: 1388710789
 Extent Number:12  Commit Time: 1388710854
 Extent Number:13  Commit Time: 1388710854
 Extent Number:14  Commit Time: 1388710854
 Extent Number:15  Commit Time: 1388711270
 Extent Number:16  Commit Time: 1388711270

  TRN CTL:: seq: 0x0129 chd: 0x001e ctl: 0x0019 inc: 0x00000000 nfb: 0x0000
            mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x00800546.0129.16 scn: 0x0000.000e4b58
Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00000000.0129.15 ext: 0xa  spc: 0x1440
    uba: 0x00000000.0129.05 ext: 0xa  spc: 0x1e08
    uba: 0x00000000.0129.42 ext: 0xa  spc: 0x73e
    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  0x0181  0x0004  0x0000.000e5e2d  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357510314
   0x01    9    0x00  0x0181  0x0003  0x0000.000e570f  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357505284
   0x02    9    0x00  0x0181  0x0009  0x0000.000e5c13  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357508885
   0x03    9    0x00  0x0181  0x0007  0x0000.000e58ac  0x00800545  0x0000.000.00000000  0x00000002   0x00000000   1357506485
   0x04    9    0x00  0x0181  0x0006  0x0000.000e5f7d  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357511286
   0x05    9    0x00  0x0181  0x0002  0x0000.000e5c07  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357508885
   0x06    9    0x00  0x0181  0x000a  0x0000.000e5f89  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357511286
   0x07    9    0x00  0x0181  0x0008  0x0000.000e58b6  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357506485
   0x08    9    0x00  0x0181  0x002e  0x0000.000e5a56  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357507685
   0x09    9    0x00  0x0181  0x000b  0x0000.000e5c1e  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357508886
   0x0a    9    0x00  0x0181  0x0011  0x0000.000e5f93  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357511286
   0x0b    9    0x00  0x0181  0x002a  0x0000.000e5dba  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357510086
   0x0c    9    0x00  0x0181  0x0000  0x0000.000e5dd4  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357510086
   0x0d    9    0x00  0x0181  0x0010  0x0000.000e5fa8  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357511286
   0x0e    9    0x00  0x0181  0x0016  0x0000.000e62f8  0x00800546  0x0000.000.00000000  0x00000001   0x00000000   1357513687
   0x0f    9    0x00  0x0181  0x0014  0x0000.000e6662  0x00800546  0x0000.000.00000000  0x00000001   0x00000000   1357516088
   0x10    9    0x00  0x0181  0x0012  0x0000.000e5fb2  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357511286
   0x11    9    0x00  0x0181  0x000d  0x0000.000e5f9d  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357511286
   0x12    9    0x00  0x0181  0x0017  0x0000.000e6150  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357512487
   0x13    9    0x00  0x0181  0x002d  0x0000.000e64a1  0x00800546  0x0000.000.00000000  0x00000001   0x00000000   1357514887
   0x14    9    0x00  0x0180  0x001b  0x0000.000e67fe  0x00800546  0x0000.000.00000000  0x00000001   0x00000000   1357517287
   0x15    9    0x00  0x0181  0x000f  0x0000.000e6658  0x00800546  0x0000.000.00000000  0x00000001   0x00000000   1357516088
   0x16    9    0x00  0x0181  0x0013  0x0000.000e6301  0x00800546  0x0000.000.00000000  0x00000001   0x00000000   1357513687
   0x17    9    0x00  0x0181  0x000e  0x0000.000e615b  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357512487
   0x18    9    0x00  0x0180  0x0029  0x0000.000e53b5  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357502883
   0x19    9    0x00  0x0181  0xffff  0x0000.000e6813  0x00800546  0x0000.000.00000000  0x00000001   0x00000000   1357517288
   0x1a    9    0x00  0x0181  0x0015  0x0000.000e64ba  0x00800546  0x0000.000.00000000  0x00000001   0x00000000   1357514888
   0x1b    9    0x00  0x0181  0x0019  0x0000.000e6809  0x00800546  0x0000.000.00000000  0x00000001   0x00000000   1357517288
   0x1c    9    0x00  0x0180  0x001f  0x0000.000e4cff  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357498082
   0x1d   10    0x80  0x0181  0x000a  0x0000.000e6858  0x00800546  0x0000.000.00000000  0x00000001   0x00000000  0
   0x1e    9    0x00  0x0180  0x001c  0x0000.000e4cf4  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357498082
   0x1f    9    0x00  0x0180  0x0024  0x0000.000e4e9c  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357499280
   0x20    9    0x00  0x0180  0x002f  0x0000.000e5059  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357500481
   0x21    9    0x00  0x0180  0x0005  0x0000.000e5a6a  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357507685
   0x22    9    0x00  0x0180  0x0023  0x0000.000e5044  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357500481
   0x23    9    0x00  0x0180  0x0020  0x0000.000e504f  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357500481
   0x24    9    0x00  0x0180  0x0022  0x0000.000e4ea6  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357499282
   0x25    9    0x00  0x0180  0x0026  0x0000.000e5568  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357504084
   0x26    9    0x00  0x0180  0x0001  0x0000.000e5705  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357505284
   0x27    9    0x00  0x0180  0x0028  0x0000.000e5201  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357501683
   0x28    9    0x00  0x0180  0x002c  0x0000.000e520c  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357501683
   0x29    9    0x00  0x0180  0x002b  0x0000.000e5553  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357504083
   0x2a    9    0x00  0x0180  0x000c  0x0000.000e5dc6  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357510086
   0x2b    9    0x00  0x0180  0x0025  0x0000.000e555e  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357504084
   0x2c    9    0x00  0x0180  0x0018  0x0000.000e53a9  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357502883
   0x2d    9    0x00  0x0180  0x001a  0x0000.000e64ac  0x00800546  0x0000.000.00000000  0x00000001   0x00000000   1357514888
   0x2e    9    0x00  0x0180  0x0021  0x0000.000e5a60  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357507685
   0x2f    9    0x00  0x0180  0x0027  0x0000.000e5064  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357500483

回顾前面的事务信息,该事务正好占用的是第29号事务槽(0x1d),状态(state)为10代表是活动的事务

SQL> select to_number('1d','xx') from dual  ;

TO_NUMBER('1D','XX')
--------------------
                  29

5.转储前镜像信息
再来看DBA(Data Block Address),这个DBA指向的就是包含这个事务的前镜像的数据块地址
0x00800546

DBA代表数据块的存储地址,通过转换DBA计算出文件号和数据块号

SQL> SELECT DBMS_UTILITY.data_block_address_file (
  2            TO_NUMBER (LTRIM ('0x00800546', '0x'), 'xxxxxxxx'))
  3            AS file_no,
  4         DBMS_UTILITY.data_block_address_block (
  5            TO_NUMBER (LTRIM ('0x00800546', '0x'), 'xxxxxxxx'))
  6            AS block_no
  7    FROM DUAL;

   FILE_NO   BLOCK_NO
---------- ----------
         2       1350

经过转换后,该前镜像信息位于file 2, block 1350,这和从事务表中查询得的数据完全一致:

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

    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBAREC
---------- ---------- ---------- ---------- ---------- ----------
         9         29        385       1350          2         22

为了说明一些其他内容,继续先前scott用户的事务,再更新2条记录:

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

1 row updated.

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

1 row updated.

将回滚段中的这个block转储出来:

SQL> alter system dump datafile 2 block 1350;

System altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from sys.v$mystat m,
  6  sys.v$session s,sys.v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from sys.v$thread  t,sys.v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from sys.v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4778.trc

这是跟踪文件开始部分的信息:

*** 2013-01-07 08:46:13.536
*** SERVICE NAME:(SYS$USERS) 2013-01-07 08:46:13.536
*** SESSION ID:(142.51) 2013-01-07 08:46:13.536
Start dump data blocks tsn: 1 file#: 2 minblk 1350 maxblk 1350
buffer tsn: 1 rdba: 0x00800546 (2/1350)
scn: 0x0000.000e6ba3 seq: 0x01 flg: 0x00 tail: 0x6ba30201
frmt: 0x02 chkval: 0x0000 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1

********************************************************************************
UNDO BLK:
xid: 0x0009.01d.00000181  seq: 0x129 cnt: 0x18  irb: 0x18  icl: 0x0   flg: 0x0000

注意,这部分信息中有一个参数irb:0x18,irb指的是回滚段中记录的最近的没有提交变更的开始
之处,如果开始回滚,这是起始的搜索点.

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

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f3c     0x02 0x1ed0     0x03 0x1e54     0x04 0x1de8     0x05 0x1d3c
0x06 0x1c90     0x07 0x1c24     0x08 0x1b78     0x09 0x1acc     0x0a 0x1a20
0x0b 0x1974     0x0c 0x1908     0x0d 0x189c     0x0e 0x17f0     0x0f 0x1784
0x10 0x1718     0x11 0x166c     0x12 0x1600     0x13 0x1594     0x14 0x14e8
0x15 0x147c     0x16 0x13e4     0x17 0x1388     0x18 0x132c

那么我们可以找到Rec 0x18的信息:

*-----------------------------
* Rec #0x18  slt: 0x1d  objn: 51148(0x0000c7cc)  objd: 51148  tblspc: 4(0x00000004)
*       Layer:  11 (Row)   opc: 1   rci 0x17
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: 0x00800546.0129.17
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000020  hdba: 0x0100001b
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 5(0x5) flag: 0x2c lock: 0 ckix: 191
ncol: 8 nnew: 1 size: 1
col  5: [ 3]  c2 1d 33

从上面的信息中可以看到objn: 51148(0x0000c7cc) objd: 51148 tblspc: 4(0x00000004)
惹objn: 51148(0x0000c7cc) objd: 51148 是指object_id
tblspc: 4(0x00000004)是指表空间号4

SQL> select a.owner,a.object_name,a.object_type from dba_objects a where a.object_id=51148;

OWNER                          OBJECT_NAME       OBJECT_TYPE
------------------------------ ----------------- -------------------
SCOTT                          EMP               TABLE
SQL> select  * from v$tablespace;

       TS# NAME                           INCLUDED_IN_DATABASE_BACKUP BIGFILE FLASHBACK_ON ENCRYPT_IN_BACKUP
---------- ------------------------------ --------------------------- ------- ------------ -----------------
         0 SYSTEM                         YES                         NO      YES
         1 UNDOTBS1                       YES                         NO      YES
         2 SYSAUX                         YES                         NO      YES
         4 USERS                          YES                         NO      YES
         6 EXAMPLE                        YES                         NO      YES
         3 TEMP                           NO                          NO      YES

6 rows selected

SQL> select username,default_tablespace from dba_users where username='SCOTT';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
SCOTT                          USERS

通过上面的查询可以知道0x18的前镜像记录是记录的对scott用户的emp表修改的记录的前镜像信息
col 5: [ 3] c2 1d 33
c2 id 33转换为十进制是2850

SQL> select utl_raw.cast_to_number('c21d33') from dual;

UTL_RAW.CAST_TO_NUMBER('C21D33
------------------------------
                          2850

这就是下面的更新语句的前镜像信息,oracle就是这样通过回滚段保留前镜像信息的

SQL> update emp set sal=4000 where empno=7698;
SQL> select empno,sal from scott.emp where empno in(7788,7782,7698);

     EMPNO        SAL
---------- ----------
      7698       2850
      7782       2450
      7788         10

在这条undo记录上,还记录一个数据rci,该参数代表的就是undo chain(同一个事务中的多次修改,
根据undo chain链接关联)的下一个偏移量,此处为0x17,找到0x17这条undo记录:

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

这里记录的c2 19 33转换为十进制就是2450,是第二条更新记录的前镜像信息

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

SQL> select utl_raw.cast_to_number('c21933') from dual;

UTL_RAW.CAST_TO_NUMBER('C21933')
------------------------------
                          2450

这条undo记录中的rci指向下一条记录是0x16,找到0x16:

*-----------------------------
* Rec #0x16  slt: 0x1d  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: 0x00800546.0129.14 ctl max scn: 0x0000.000e4b4d prv tx scn: 0x0000.000e4b58
txn start scn: scn: 0x0000.000e684e logon user: 54
 prev brb: 8389956 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04  ver: 0x01
op: L  itl: xid:  0x0009.00e.0000017e uba: 0x0080053d.0129.2f
                      flg: C---    lkc:  0     scn: 0x0000.000d444e
KDO Op code: URP row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x01000020  hdba: 0x0100001b
itli: 1  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]  c1 0b

这里的c10b转换十进制是10,正是第一条更新的记录:

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

SQL> select utl_raw.cast_to_number('c10b') from dual;

UTL_RAW.CAST_TO_NUMBER('C10B')
------------------------------
                            10

这是这个事务中最后一条更新数据,所以其实undo chain的指针rci为0x00,表示这是最后
一条记录.也可以从x$bh中找到这些数据块:

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

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

7 rows selected

注意class为4的是段头,class为1,块号为28到32的为数据块,如果此时在其他进程中查询
scott.emp表,oracle需要构造一致性读,通过前镜像把变化前的数据展现给用户:

SQL> select * from scott.emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19       10.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10

14 rows selected

再来查询哪些数据是脏数据

SQL> select b.segment_name,a.file#,a.dbarfil,a.dbablk,a.class,a.state,
  2  decode(bitand(flag,1),0,'N','Y') dirty
  3  from x$bh a,dba_extents b
  4  where b.relative_fno=a.dbarfil
  5  and b.block_id< =a.dbablk and b.block_id+b.blocks>a.dbablk
  6  and b.owner='SCOTT' and b.segment_name='EMP' order by a.dbablk asc;

SEGMENT_NAME                   FILE#    DBARFIL     DBABLK      CLASS      STATE DIRTY
------------------------- ---------- ---------- ---------- ---------- ---------- -----
EMP                                4          4         27          4          1 N
EMP                                4          4         28          1          1 N
EMP                                4          4         29          1          1 N
EMP                                4          4         30          1          1 N
EMP                                4          4         31          1          1 N
EMP                                4          4         32          1          3 N
EMP                                4          4         32          1          3 N
EMP                                4          4         32          1          1 Y
EMP                                4          4         32          1          3 N

9 rows selected

注意此时,buffer cache中多出了两个数据块,也就是32存在4份,其中state为3的就是一致性读
构造的前镜像.dirty为Y的记录就是进行更改后没有提交的记录

6.转储数据块信息
在前镜像信息中,oracle还记录了前镜像对应的数据块的地址.可以从bdba记录中获得这部分信息
以上面数据为例bdba: 0x01000020记录了更改的数据块块地址.

SQL> SELECT DBMS_UTILITY.data_block_address_file (
  2            TO_NUMBER (LTRIM ('0x01000020', '0x'), 'xxxxxxxx'))
  3            AS file_no,
  4         DBMS_UTILITY.data_block_address_block (
  5            TO_NUMBER (LTRIM ('0x01000020', '0x'), 'xxxxxxxx'))
  6            AS block_no
  7    FROM DUAL;

   FILE_NO   BLOCK_NO
---------- ----------
         4         32
0x01000020记录的正是file 4,block 32

现在将数据表中的block转储出来,看看其中的记录是什么样的信息:

SQL> alter system dump datafile 4 block 32;

System altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from sys.v$mystat m,
  6  sys.v$session s,sys.v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from sys.v$thread  t,sys.v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from sys.v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4859.trc

检查跟踪文件信息:

** 2013-01-07 09:27:18.032
*** SERVICE NAME:(SYS$USERS) 2013-01-07 09:27:18.030
*** SESSION ID:(142.53) 2013-01-07 09:27:18.029
Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32
buffer tsn: 4 rdba: 0x01000020 (4/32)
scn: 0x0000.000e6ebb seq: 0x01 flg: 0x04 tail: 0x6ebb0601
frmt: 0x02 chkval: 0xf364 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1

Block header dump:  0x01000020
 Object id on Block? Y
 seg/obj: 0xc7cc  csc: 0x00.e6ebb  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000019 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01d.00000181  0x00800546.0129.18  ----    3  fsc 0x0002.00000000
0x02   0x0002.010.00000158  0x00800598.013f.26  C---    0  scn 0x0000.000d4495

其中scn表示提交commit scn,fsc表示快速提交scn
这里存在ITL事务槽信息,ITL事务槽指Interested Transaction List(ITL),事务必须
获得一个ITL事务槽才能进行数据修改,ITL内容主要包括:
Xid———Transaction ID;
Uba———Undo Block Address
Lck———Lock Status
注意:Xid=Undo.segment.number+transaction.table.slot.number+wrap

在上面的输出,看到itl1(0x01)上存在活动事务,将xid=0x0009.01d.00000181分解一下
该事务指向的回滚段号是9

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

    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBAREC
---------- ---------- ---------- ---------- ---------- ----------
         9         29        385       1350          2         22

事务表的事务槽slot号为0x1d(转换为十进制是29)

SQL> select to_number('1d','xx') from dual;

TO_NUMBER('1D','XX')
--------------------
                  29
wrap#为0181正是dump回滚段看到的那个事务
TRN TBL::

index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
------------------------------------------------------------------------------------------------
......
0x1d   10    0x80  0x0181  0x000a  0x0000.000e6858  0x00800546  0x0000.000.00000000  0x00000001   0x00000000  0
.....

可以看到,在数据块上同样存在指向回滚段的事务信息.
uba代表的是undo block address,指向具体的回滚段.可以看到ITL上uba=0x00800546.0129.18
将这个UBA进行分解:
0x00800546正是前镜像的数据块地址
seq:0129是顺序号
18是undo记录的开始地址(irb信息)
uba的内容和undo中的信息完全相符:

********************************************************************************
UNDO BLK:
xid: 0x0009.01d.00000181  seq: 0x129 cnt: 0x18  irb: 0x18  icl: 0x0   flg: 0x0000

继续向下可以找到这3条被修改的记录,锁定位信息LB指向0x01号ITL事务槽:

tab 0, row 5, @0x1d11
tl: 40 fb: --H-FL-- lb: 0x1  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: 0x1  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: 0x1  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

SQL> select utl_raw.cast_to_number('c229') from dual;

UTL_RAW.CAST_TO_NUMBER('C229')
------------------------------
                          4000

这个事务的过程如下:

TRN TBL::

index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
------------------------------------------------------------------------------------------------
......
0x1d   10    0x80  0x0181  0x000a  0x0000.000e6858  0x00800546  0x0000.000.00000000  0x00000001   0x00000000  0
.....                                                                       |
                                                                            |
                                                                         TX table slot
Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01d.00000181  0x00800546.0129.18  ----    3  fsc 0x0002.00000000
0x02   0x0002.010.00000158  0x00800598.013f.26  C---    0  scn 0x0000.000d4495
ITL
   data row1
    data row2  回滚表空间的数据块-------------------------------Undo block
     data row3 (UNDO BLK:                                          |
                xid: 0x0009.01d.00000181                           |
                seq: 0x129 cnt: 0x18  irb: 0x18                    |
                icl: 0x0   flg: 0x0000)                         

锁定位0x01 Rec #0x18 slt: 0x1d objn: 51148(0x0000c7cc) objd: 51148 …

数据段 回滚段

(1)当一个事务开始时,需要在回滚段事务表上分配一个事务槽.
(2)在数据块头部获得一个ITL事务槽,该事务槽指向回滚段头的事务槽
(3)在修改数据之前,需要记录前镜像信息,这个信息以undo record的形式存储在回滚段中,
回滚段头事务槽指向该记录.
(4)锁定修改的行,修改行锁定位(lb-lock byte)指向ITL事务槽
(5)数据修改可以进行
这是一个事务的基本过程

7.块清除(Block Cleanouts)
当用户发出提交(commit)之后,oracle怎样来处理的.oracle是需要写出redo来保证故障时数据可以被
恢复,我们知道oracle并不需要在提交时就写出变更的数据块.那么在提交时,oracle会对数据块进行什么操作?

在事务需要修改数据时,必须分配ITL事务槽,必须锁定行,必须分配回滚段事务槽和回滚表空间来记录要修改
的数据的前镜像.当事务提交时,oracle需要将回滚段上的事务表信息标记为非活动,以便空间可以被重用
那么还有ITL事务信息和锁定信息需要清除,以记录提交.

由于oracle在数据块上存储了ITL和锁定等事务信息,所以oracle必须在事务提交之后清除这些事务数据,
这就是块清除.块清除主要清除的数据有行级锁,ITL信息(包括提交标志,scn等).

如果提交时修改过的数据块仍然在buffer cache中,那么oracle可以清除ITL信息,这种清除叫做快速块清除
(fast block cleanout),快速块清除还有一个限制,当修改的块数量超过buffer cache的10%,则对超出的部
分不再进行快速块清除.

如果提交事务时,修改过的数据块已经被写回到数据文件上(或大量修改超出10%的部分),再次读出该数据块
进行修改,显然成本过于高昂,对于这种情况,oracle选择延迟块清除(delayed block cleanout),等到下一次
访问该block时再来清除ITL锁定信息,这就是延迟块清除.oracle通过延迟块清除来提高数据库的性能,加快
提交操作.快速提交是最普遍的情况.来看一下延迟块清除的处理.
继续进行测试:

SQL> update emp set sal=4000 where empno=7788;
1 row updated.

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

1 row updated.

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

1 row updated.

更新完成之后,强制刷新buffer cache,将buffer cache中的数据都写出到数据文件:

SQL> alter session set events='immediate trace name flush_cache';

Session altered

此时再提交事务;

SQL> commit;

Commit complete.

由于此时更新过的数据已经写出到数据文件,oracle将执行延迟块清除,将此时的数据块和回滚段转储出来:

[oracle@jingyong ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 7 10:18:56 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter system dump datafile 4 block 32;

System altered.

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

System altered.

SQL> alter system dump datafile 2 block 1350;

System altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from sys.v$mystat m,
  6  sys.v$session s,sys.v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from sys.v$thread  t,sys.v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from sys.v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_5023.trc

查看跟踪文件信息,看数据块上的信息,ITL事务信息仍然存在:

*** 2013-01-07 10:19:33.032
*** SERVICE NAME:(SYS$USERS) 2013-01-07 10:19:33.031
*** SESSION ID:(140.421) 2013-01-07 10:19:33.031
Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32
buffer tsn: 4 rdba: 0x01000020 (4/32)
scn: 0x0000.000e6ebb seq: 0x01 flg: 0x04 tail: 0x6ebb0601
frmt: 0x02 chkval: 0xf364 type: 0x06=trans data

Block header dump:  0x01000020
 Object id on Block? Y
 seg/obj: 0xc7cc  csc: 0x00.e6ebb  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000019 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01d.00000181  0x00800546.0129.18  ----    3  fsc 0x0002.00000000
0x02   0x0002.010.00000158  0x00800598.013f.26  C---    0  scn 0x0000.000d4495
xid=0x0009.01d.00000181的事务lck=3

数据块的锁定信息仍然存在:

tab 0, row 5, @0x1d11
tl: 40 fb: --H-FL-- lb: 0x1  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: 0x1  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: 0x1  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

再来看回滚段的信息:

*** 2013-01-07 10:20:01.417

********************************************************************************
Undo Segment:  _SYSSMU9$ (9)
********************************************************************************

Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00800546.0129.18 ext: 0xa  spc: 0x12ea
    uba: 0x00000000.0129.05 ext: 0xa  spc: 0x1e08
    uba: 0x00000000.0129.42 ext: 0xa  spc: 0x73e
    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
------------------------------------------------------------------------------------------------
0x1d    9    0x00  0x0181  0xffff  0x0000.000e743c  0x00800546  0x0000.000.00000000  0x00000001   0x00000000   1357525082

事务提交,事务表已经释放。如果此时查询scott.emp表,数据库将产生延迟块清除:

SQL> set autotrace on
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          4000         30

      7782 CLARK      MANAGER         7839 09-JUN-81          4000         10

      7788 SCOTT      ANALYST         7566 19-APR-87          4000         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

      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.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1413  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

注意,在此查询是产生了物理读取和redo,这个redo就是因为延迟块清除导致的,再次查询则不会
产生redo了:

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          4000         30

      7782 CLARK      MANAGER         7839 09-JUN-81          4000         10

      7788 SCOTT      ANALYST         7566 19-APR-87          4000         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

      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.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1413  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

再次转储一下该数据块和回滚段:

SQL> alter system dump datafile 4 block 32;

System altered.

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

System altered.

SQL> alter system dump datafile 2 block 1350;

System altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from sys.v$mystat m,
  6  sys.v$session s,sys.v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from sys.v$thread  t,sys.v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from sys.v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_5043.trc

查看跟踪文件,看到此时ITL事务信息已经清除,但是注意,这里的xid和uba信息仍然存在:

Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32
buffer tsn: 4 rdba: 0x01000020 (4/32)
scn: 0x0000.000e7560 seq: 0x01 flg: 0x00 tail: 0x75600601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

Block header dump:  0x01000020
 Object id on Block? Y
 seg/obj: 0xc7cc  csc: 0x00.e7560  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000019 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01d.00000181  0x00800546.0129.18  C---    0  scn 0x0000.000e743c
0x02   0x0002.010.00000158  0x00800598.013f.26  C---    0  scn 0x0000.000d4495
其中scn表示提交commit scn,fsc表示快速提交scn
数据块块的锁定位也已经清除了:
tab 0, row 5, @0x1d11
tl: 40 fb: --H-FL-- lb: 0x0  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: 0x0  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: 0x0  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

8.提交之后的undo信息
当提交事务之后,回滚段事务表标记该事务为非活动,继续再来看一下回滚段数据块的信息,
看到这里的irb指向了0x28,此前的事务已经不可回滚

********************************************************************************
UNDO BLK:
xid: 0x0009.02f.00000181  seq: 0x129 cnt: 0x28  irb: 0x28  icl: 0x0   flg: 0x0000

看一下偏移量列表也已经新增了一条信息0x28 0x0a4c

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f3c     0x02 0x1ed0     0x03 0x1e54     0x04 0x1de8     0x05 0x1d3c
0x06 0x1c90     0x07 0x1c24     0x08 0x1b78     0x09 0x1acc     0x0a 0x1a20
0x0b 0x1974     0x0c 0x1908     0x0d 0x189c     0x0e 0x17f0     0x0f 0x1784
0x10 0x1718     0x11 0x166c     0x12 0x1600     0x13 0x1594     0x14 0x14e8
0x15 0x147c     0x16 0x13e4     0x17 0x1388     0x18 0x132c     0x19 0x1280
0x1a 0x11d4     0x1b 0x1128     0x1c 0x10ac     0x1d 0x1000     0x1e 0x0f54
0x1f 0x0ee8     0x20 0x0e7c     0x21 0x0e10     0x22 0x0da4     0x23 0x0cf8
0x24 0x0c4c     0x25 0x0ba0     0x26 0x0b24     0x27 0x0ab8     0x28 0x0a4c

再看前镜像0x18 0x132c的信息,仍然存在:

*-----------------------------
* Rec #0x18  slt: 0x1d  objn: 51148(0x0000c7cc)  objd: 51148  tblspc: 4(0x00000004)
*       Layer:  11 (Row)   opc: 1   rci 0x17
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: 0x00800546.0129.17
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000020  hdba: 0x0100001b
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 5(0x5) flag: 0x2c lock: 0 ckix: 191
ncol: 8 nnew: 1 size: 1
col  5: [ 3]  c2 1d 33

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.