分类目录归档：数据库

bbed模拟提交事务二之屏蔽smon回滚事务

发表于 2013 年 3 月 12 日 由 惜分飞

在上一篇修改datablock itl(bbed模拟提交事务一之修改itl)的基础之上，本篇实现修改undo segment header中的相关事务槽信息,从而屏蔽数据库在重启或者进程异常的时候,smon的回滚操作,从而比较完美的实现了手工提交数据库事务
update table and uncommit(session 1)

SQL> select distinct
  2  dbms_rowid.rowid_relative_fno(rowid) rel_fno,
  3  dbms_rowid.rowid_block_number(rowid) block_no
  4  from chf.t_xifenfei;   

   REL_FNO   BLOCK_NO
---------- ----------
         4         28

SQL> select * from chf.t_xifenfei;

 OBJECT_ID OBJECT_NAME
---------- --------------------
        20 xifenfei.com
        44 xifenfei.com
        28 xifenfei.com
        15 xifenfei.com
        29 xifenfei.com
         3 xifenfei.com
        25 xifenfei.com
        39 xifenfei.com
        51 xifenfei.com
        26 xifenfei.com
        17 xifenfei.com
        13 xifenfei.com
         9 xifenfei.com
        41 xifenfei.com
        48 I_CON1
        38 I_OBJ3
         7 I_TS#
        53 I_CDEF4
        19 IND$

19 rows selected.

SQL> update chf.t_xifenfei set object_name='orasos.com' where rownum<10;

9 rows updated.

SQL> select * from chf.t_xifenfei;

 OBJECT_ID OBJECT_NAME
---------- --------------------
        20 orasos.com
        44 orasos.com
        28 orasos.com
        15 orasos.com
        29 orasos.com
         3 orasos.com
        25 orasos.com
        39 orasos.com
        51 orasos.com
        26 xifenfei.com
        17 xifenfei.com
        13 xifenfei.com
         9 xifenfei.com
        41 xifenfei.com
        48 I_CON1
        38 I_OBJ3
         7 I_TS#
        53 I_CDEF4
        19 IND$

19 rows selected.

dump undo header(session 2)

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> SELECT XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC FROM v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC
---------- ---------- ---------- ---------- ---------- ---------- ----------
         1         13        367          2       1126        362         32

SQL>  alter system dump undo header "_SYSSMU1$";

System altered.


index  state cflags  wrap#    uel         scn            dba     parent-xid          nub       stmt_num       cmt
------------------------------------------------------------------------------------------------------------------
   …………
0x0c    9    0x00  0x016e  0x0029  0x0b2c.c02d1f6a  0x00800464  0x0000.000.00000000  0x00000001   0x00000000  1358813163
0x0d   10    0x80  0x016f  0x0002  0x0b2c.c02d7b15  0x00800466  0x0000.000.00000000  0x00000001   0x00000000  0
0x0e    9    0x00  0x016f  0x000f  0x0b2c.c02d2ae2  0x00800466  0x0000.000.00000000  0x00000001   0x00000000  1358820065
   …………

通过结合dump undo header 中的TRN TBL的state为10的为active事务,然后结合scn/dba等信息,
来确定是哪条记录是需要我们修改.然后通过find命令快速定位到0x0d这条记录,然后进行修改

通过结合bbed的dump命令得出16进制数据分析得出如下结论

--index 0x0c
6e01        0000 64048000      6a1f2dc0 2c0b0000      09       00          2900      
0000000000000000            00000000          01000000     ebd7fd50(1358813163注意存储顺序)

--index 0x0d
6f01  wrap# 0000 66048000 dba  157b2dc0 2c0b0000 scn  0a state 80  cflags  0200 uel  
0000000000000000 parent-xid 00000000 stmt_num 01000000 nub 00000000 cmt

--index 0x0e
6f01        0000 66048000      e22a2dc0 2c0b0000      09       00          0f00      
0000000000000000            00000000          01000000     e1f2fd50(1358820065)

bbed modify undo segment header(session 2)

BBED> f /x 0a80
 File: /u01/oracle/oradata/XFF/undotbs01.dbf (0)
 Block: 9                Offsets: 6736 to 6751           Dba:0x00000000
------------------------------------------------------------------------
 0a800200 00000000 00000000 00000000 

 <32 bytes per line>

BBED> m /x 0900
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/XFF/undotbs01.dbf (0)
 Block: 9                Offsets: 6736 to 6751           Dba:0x00000000
------------------------------------------------------------------------
 09000200 00000000 00000000 00000000  

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 9:
current = 0xecdd, required = 0xecdd

bbed modify data block itl(session 2)

struct ktbbhitl[0], 24 bytes             @44      
      struct ktbitxid, 8 bytes              @44      
         ub2 kxidusn                        @44       0x0001
         ub2 kxidslt                        @46       0x000d
         ub4 kxidsqn                        @48       0x0000016f
      struct ktbituba, 8 bytes              @52      
         ub4 kubadba                        @52       0x00800466
         ub2 kubaseq                        @56       0x016a
         ub1 kubarec                        @58       0x20
      ub2 ktbitflg                          @60       0x0009 (NONE)
      union _ktbitun, 2 bytes               @62      
         b2 _ktbitfsc                       @62       18
         ub2 _ktbitwrp                      @62       0x0012
      ub4 ktbitbas                          @64       0x00000000

BBED> m /x 0080 offset 60
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 28               Offsets:   60 to  571           Dba:0x00000000
------------------------------------------------------------------------
 00801200 00000000 09002a00 36020000  

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 28:
current = 0xccf1, required = 0xccf1

restart db and select table(session 3)

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  306184192 bytes
Fixed Size                  1267164 bytes
Variable Size             109054500 bytes
Database Buffers          188743680 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
SQL> col object_name for a20
SQL> set pages 100
SQL> select * from chf.t_xifenfei;

 OBJECT_ID OBJECT_NAME
---------- --------------------
        20 orasos.com
        44 orasos.com
        28 orasos.com
        15 orasos.com
        29 orasos.com
         3 orasos.com
        25 orasos.com
        39 orasos.com
        51 orasos.com
        26 xifenfei.com
        17 xifenfei.com
        13 xifenfei.com
         9 xifenfei.com
        41 xifenfei.com
        48 I_CON1
        38 I_OBJ3
         7 I_TS#
        53 I_CDEF4
        19 IND$

19 rows selected.

到此证明,通过修改undo segment header中的state和cflags实现数据库启动不回滚未提交事务；通过修改datablock itl实现数据库在访问未提交数据块时候不访问undo。从而整体上较完美的实现了手工提交一个事务(数据库提交一个事务涉及的方方面面较为复杂，这里只是通过修改最核心的两部分来大致模拟提交事务)

发表在 Oracle | 标签为 , | 2 条评论

bbed模拟提交事务一之修改itl

发表于 2013 年 3 月 11 日 由 惜分飞

我们都知道，根据oracle 事务的一致性,当我们在session 1中进行dml操作,如果未提交在其他会话中是无法看到修改后的值(只能看到修改前的值).这里通过bbed模拟部分提交事务从而实现在其他会话中查看到另外会话未提交事务(本质已经部分模拟提交,还有undo segment header中信息未清理,下篇补充)
create table(session 1)

SQL> create table chf.t_xifenfei
  2  as
  3  select object_id,object_name from dba_objects where rownum<20;

Table created.

SQL> select rowid,
  2  dbms_rowid.rowid_relative_fno(rowid) rel_fno,
  3  dbms_rowid.rowid_block_number(rowid) block_no
  4  from chf.t_xifenfei;

ROWID                 REL_FNO   BLOCK_NO
------------------ ---------- ----------
AAANL3AAEAAAAAcAAA          4         28
AAANL3AAEAAAAAcAAB          4         28
AAANL3AAEAAAAAcAAC          4         28
AAANL3AAEAAAAAcAAD          4         28
AAANL3AAEAAAAAcAAE          4         28
AAANL3AAEAAAAAcAAF          4         28
AAANL3AAEAAAAAcAAG          4         28
AAANL3AAEAAAAAcAAH          4         28
AAANL3AAEAAAAAcAAI          4         28
AAANL3AAEAAAAAcAAJ          4         28
AAANL3AAEAAAAAcAAK          4         28
AAANL3AAEAAAAAcAAL          4         28
AAANL3AAEAAAAAcAAM          4         28
AAANL3AAEAAAAAcAAN          4         28
AAANL3AAEAAAAAcAAO          4         28
AAANL3AAEAAAAAcAAP          4         28
AAANL3AAEAAAAAcAAQ          4         28
AAANL3AAEAAAAAcAAR          4         28
AAANL3AAEAAAAAcAAS          4         28

19 rows selected.

SQL>  select * from chf.t_xifenfei;

 OBJECT_ID OBJECT_NAME
---------- --------------------
        20 ICOL$
        44 I_USER1
        28 CON$
        15 UNDO$
        29 C_COBJ#
         3 I_OBJ#
        25 PROXY_ROLE_DATA$
        39 I_IND1
        51 I_CDEF2
        26 I_PROXY_ROLE_DATA$_1
        17 FILE$
        13 UET$
         9 I_FILE#_BLOCK#
        41 I_FILE1
        48 I_CON1
        38 I_OBJ3
         7 I_TS#
        53 I_CDEF4
        19 IND$

19 rows selected.

dump block(session 2)

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 4 block 28;

System altered.

 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0b2c.c02d1987
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

update record(session 1)

SQL> update chf.t_xifenfei set object_name ='www.xifenfei.com' where rownum<15;

14 rows updated.

SQL> col object_name for a20
SQL> select * from chf.t_xifenfei;

 OBJECT_ID OBJECT_NAME
---------- --------------------
        20 www.xifenfei.com
        44 www.xifenfei.com
        28 www.xifenfei.com
        15 www.xifenfei.com
        29 www.xifenfei.com
         3 www.xifenfei.com
        25 www.xifenfei.com
        39 www.xifenfei.com
        51 www.xifenfei.com
        26 www.xifenfei.com
        17 www.xifenfei.com
        13 www.xifenfei.com
         9 www.xifenfei.com
        41 www.xifenfei.com
        48 I_CON1
        38 I_OBJ3
         7 I_TS#
        53 I_CDEF4
        19 IND$

19 rows selected.

dump block(session 2)

SQL> alter system checkpoint;

System altered.

--注意flush buffer_cache(不然后面bbed修改会被不能通过select显示,而且会被覆盖)
SQL> alter system flush buffer_cache;

System altered.

SQL>  alter system flush shared_pool;

System altered.

SQL> alter system dump datafile 4 block 28;

System altered.

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0b2c.c02d1987
0x02   0x000a.00a.0000017e  0x0081ffc7.01a2.22  ----   14  fsc 0x0000.00000000  <--注意Lck 14
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

bbed commit Transaction(session 3)

BBED> p ktbbh
struct ktbbh, 96 bytes                      @20      
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24      
      ub4 ktbbhsg1                          @24       0x0000d2f7
      ub4 ktbbhod1                          @24       0x0000d2f7
   struct ktbbhcsc, 8 bytes                 @28      
      ub4 kscnbas                           @28       0xc02d1aec
      ub2 kscnwrp                           @32       0x0b2c
   b2 ktbbhict                              @36       3
   ub1 ktbbhflg                             @38       0x32 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x01000019
   struct ktbbhitl[0], 24 bytes             @44      
      struct ktbitxid, 8 bytes              @44      
         ub2 kxidusn                        @44       0xffff
         ub2 kxidslt                        @46       0x0000
         ub4 kxidsqn                        @48       0x00000000
      struct ktbituba, 8 bytes              @52      
         ub4 kubadba                        @52       0x00000000
         ub2 kubaseq                        @56       0x0000
         ub1 kubarec                        @58       0x00
      ub2 ktbitflg                          @60       0x8000 (KTBFCOM)
      union _ktbitun, 2 bytes               @62      
         b2 _ktbitfsc                       @62       2860
         ub2 _ktbitwrp                      @62       0x0b2c
      ub4 ktbitbas                          @64       0xc02d1987
   struct ktbbhitl[1], 24 bytes             @68      
      struct ktbitxid, 8 bytes              @68      
         ub2 kxidusn                        @68       0x000a
         ub2 kxidslt                        @70       0x000a
         ub4 kxidsqn                        @72       0x0000017e
      struct ktbituba, 8 bytes              @76      
         ub4 kubadba                        @76       0x0081ffc7
         ub2 kubaseq                        @80       0x01a2
         ub1 kubarec                        @82       0x22
      ub2 ktbitflg                          @84       0x000e (NONE) <--修改要改为8000
      union _ktbitun, 2 bytes               @86      
         b2 _ktbitfsc                       @86       0
         ub2 _ktbitwrp                      @86       0x0000
      ub4 ktbitbas                          @88       0x00000000
   struct ktbbhitl[2], 24 bytes             @92      
      struct ktbitxid, 8 bytes              @92      
         ub2 kxidusn                        @92       0x0000
         ub2 kxidslt                        @94       0x0000
         ub4 kxidsqn                        @96       0x00000000
      struct ktbituba, 8 bytes              @100     
         ub4 kubadba                        @100      0x00000000
         ub2 kubaseq                        @104      0x0000
         ub1 kubarec                        @106      0x00
      ub2 ktbitflg                          @108      0x0000 (NONE)
      union _ktbitun, 2 bytes               @110     
         b2 _ktbitfsc                       @110      0
         ub2 _ktbitwrp                      @110      0x0000
      ub4 ktbitbas                          @112      0x00000000

BBED> m /x 0080 offset 84
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 28               Offsets:   84 to  115           Dba:0x00000000
------------------------------------------------------------------------
 00800000 00000000 00000000 00000000  

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 28:
current = 0x03dc, required = 0x03dc


BBED> p ktbbh
struct ktbbh, 96 bytes                      @20      
  …………
   struct ktbbhitl[1], 24 bytes             @68      
      struct ktbitxid, 8 bytes              @68      
         ub2 kxidusn                        @68       0x000a
         ub2 kxidslt                        @70       0x000a
         ub4 kxidsqn                        @72       0x0000017e
      struct ktbituba, 8 bytes              @76      
         ub4 kubadba                        @76       0x0081ffc7
         ub2 kubaseq                        @80       0x01a2
         ub1 kubarec                        @82       0x22
      ub2 ktbitflg                          @84       0x8000 (KTBFCOM) <--修改值
      union _ktbitun, 2 bytes               @86      
         b2 _ktbitfsc                       @86       0
         ub2 _ktbitwrp                      @86       0x0000
      ub4 ktbitbas                          @88       0x00000000
  …………

dump block(session 2)

SQL> alter system dump datafile 4 block 28;

System altered.

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0b2c.c02d1987
0x02   0x000a.00a.0000017e  0x0081ffc7.01a2.22  C---    0  scn 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

select data(session 4)

SQL> select object_id,object_name from chf.t_xifenfei;

 OBJECT_ID OBJECT_NAME
---------- --------------------
        20 www.xifenfei.com
        44 www.xifenfei.com
        28 www.xifenfei.com
        15 www.xifenfei.com
        29 www.xifenfei.com
         3 www.xifenfei.com
        25 www.xifenfei.com
        39 www.xifenfei.com
        51 www.xifenfei.com
        26 www.xifenfei.com
        17 www.xifenfei.com
        13 www.xifenfei.com
         9 www.xifenfei.com
        41 www.xifenfei.com
        48 I_CON1
        38 I_OBJ3
         7 I_TS#
        53 I_CDEF4
        19 IND$

19 rows selected.

这里可以看到,已经模拟出来在其他session中可以访问数据库为commit的记录(在该block级别已经模拟了commit)

发表在 Oracle | 标签为 , | 2 条评论

undo异常事务回滚规则分析

发表于 2013 年 3 月 10 日 由 惜分飞

undo事务具体是如何回滚,这里提供了大概的异常undo事务回滚的一个过程(更加准确的说，这个过程是在以下几种情况中发生的过程：1.数据库非正常关闭后启动,2.事务未提交会话终止)，数据库先扫描所有回滚段，然后发现有事务未提交回滚段,然后根据这个回滚段定位到undo block,然后定位到data block，当一个undo block回滚完成之后，利用undo的链表规则完成下一个undo block的回滚操作，依次类此，从而实现数据库的回滚操作；回滚的过程是先回滚后操作的块(先进后出原则)
创建测试表

SQL> create table chf.t_xifenfei(a varchar2(4000));

Table created.

SQL> insert into chf.t_xifenfei values (lpad('www.xifenfei.com',4000,'a'));

1 row created.

SQL> insert into chf.t_xifenfei values (lpad('www.xifenfei.com',4000,'b'));

1 row created.

SQL> commit;

Commit complete.

SQL> update chf.t_xifenfei set a=lpad('www.xifenfei.com',4000,'F');

2 rows updated.

SQL> select
  2  dbms_rowid.rowid_relative_fno(rowid) rel_fno,
  3  dbms_rowid.rowid_block_number(rowid) block_no
  4  from chf.t_xifenfei;

   REL_FNO   BLOCK_NO
---------- ----------
         9        421
         9        422

确保表中有两条记录,存储在两个block中

查询测试表相关信息

SQL> select object_id,data_object_id from dba_objects where owner='CHF' AND OBJECT_NAME='T_XIFENFEI';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     53917          53917

SQL> SELECT TO_CHAR(53917,'XXXXX') FROM DUAL;

TO_CHA
------
  D29D

SQL> SELECT XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC FROM v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC
---------- ---------- ---------- ---------- ---------- ---------- ----------
         2         28        513          2       3627        253          1

SQL> select to_char(28,'xxx') from dual;

TO_C
----
  1c

SQL> select to_char(513,'xxx') from dual;

TO_C
----
 201

------------------------
--xid=0002.01c.00000201
------------------------

SQL> variable dba varchar2(30)
SQL> exec :dba := dbms_utility.make_data_block_address(2,3627);

PL/SQL procedure successfully completed.

SQL> print dba

DBA
--------------------------------
8392235

SQL> select to_char(253,'xxx') from dual;

TO_C
----
  fd
---------------------
--uda=800e2a.00fd.01
---------------------

通过这些查询可以得知:
1)chf.t_xifenfei的object_id/data_object_id的值为53917/D29D
2)xid=0002.01c.00000201(Xid=usn.slot.wrap)
3)uda=800e2a.00fd.01(Uba=undo block dba.undo record number.undo block sequence number)

dump rollback header

SQL> select * from v$rollname where usn=2;

       USN NAME
---------- ------------------------------
         2 _SYSSMU2$

SQL>  alter system dump undo header "_SYSSMU2$";

System altered.

 index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  ------------------------------------------------------------------------------------------------
0x00    9    0x00  0x0201  0x0012  0x0b2c.c02c9b85  0x00800e27  0x0000.000.00000000  0x00000001   0x00000000  1358780575
   ………………
0x1b    9    0x00  0x0201  0x0028  0x0b2c.c02c9bf4  0x00800e27  0x0000.000.00000000  0x00000001   0x00000000  1358780575
0x1c   10    0x80  0x0201  0x0002  0x0b2c.c02ca2a8  0x00800e2b  0x0000.000.00000000  0x00000002   0x00000000  0
0x1d    9    0x00  0x0200  0x0004  0x0b2c.c02c9a8b  0x00800e26  0x0000.000.00000000  0x00000001   0x00000000  1358780444
   ………………
0x2f    9    0x00  0x0201  0x001a  0x0b2c.c02ca1da  0x00800e29  0x0000.000.00000000  0x00000001   0x00000000  1358784176

------------------------------------------------------------------------------
==> Transaction Table 
==>  state      0 = IDLE 
                1 = Collecting       2 = Prepared            3 = Committed 
                4 = Forced Abort     5 = Forced Commit       6 = Forced Mixed 
                7 = try again later 
                9 = No TX (Committed)    10= 'a' = Active local TX 
==>  cflags     1 = TX has started storing collecting information 
 
                2 = TX has forced the collecting information 
                4 = Prepared TX needs distributed recovery 
                10= Rollback failed on this TX - mark SMON for recover 
                20= TX has rolled back its updates 
==>  wrap#      is incremented TX slot reuse. 
==>  uel        ? 
==>  scn        SCN for the TX prepare / commit 
==>  dba        is DBA of HEAD of the REDO - Ie: The MOST RECENT CHANG 
------------------------------------------------------------------------------

--通过上面的提示可以知道index为0x1c为未提交事务

SQL> select to_number('800e2b','xxxxxxx') from dual;

TO_NUMBER('800E2B','XXXXXXX')
-----------------------------
                      8392235

SQL> select dbms_utility.data_block_address_block(8392235) "block",
  2  dbms_utility.data_block_address_file(8392235)  "file" from dual;

     block       file
---------- ----------
      3627          2
--v$transaction表中查询出来的UBAFIL与UBABLK一致

dump undo block

SQL> alter system dump datafile 2 block 3627;

System altered.

UNDO BLK:  
xid: 0x0002.01c.00000201  seq: 0xfd  cnt: 0x1   irb: 0x1   icl: 0x0   flg: 0x0000
 
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x0fdc     
 
*-----------------------------
* Rec #0x1  slt: 0x1c  objn: 53917(0x0000d29d)  objd: 53917  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x00  <--- 表明指向下一个block 
Undo type:  Regular undo   Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
rdba: 0x00800e2a   <--- 为下一个undo block rdba
*-----------------------------
KDO undo record:
KTB Redo 
op: 0x03  ver: 0x01  
op: Z
Array Update of 1 rows: 
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 71
ncol: 1 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x024001a6  hdba: 0x024001a3
itli: 2  ispac: 0  maxfr: 4858

--bdba表示undo对应的data block,这里对应的是datafile 9 block 422
--hdba表示chf.t_xifenfei的extent 0的 first block(first mapbit)

SQL> select to_number('800e2a','xxxxxxx') from dual;

TO_NUMBER('800E2A','XXXXXXX')
-----------------------------
                      8392234

SQL> select dbms_utility.data_block_address_block(8392234) "block",
  2  dbms_utility.data_block_address_file(8392234)  "file" from dual;

     block       file
---------- ----------
      3626          2

SQL> alter system dump datafile 2 block 3626;

System altered.

UNDO BLK:  
xid: 0x0002.01c.00000201  seq: 0xfd  cnt: 0x1   irb: 0x1   icl: 0x0   flg: 0x0000
 
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x0fac     
 
*-----------------------------
* Rec #0x1  slt: 0x1c  objn: 53917(0x0000d29d)  objd: 53917  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x00   <--- 表明指向下一个block 
Undo type:  Regular undo    Begin trans    Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
rdba: 0x00000000  <--- 下一个block为 0x00000000,表示事务到此为止
*-----------------------------
uba: 0x00800e29.00fd.24 ctl max scn: 0x0b2c.c02c99cc prv tx scn: 0x0b2c.c02c9a27
txn start scn: scn: 0x0b2c.c02ca0b3 logon user: 0
 prev brb: 8392230 prev bcl: 0
KDO undo record:
KTB Redo 
op: 0x03  ver: 0x01  
op: Z
Array Update of 1 rows: 
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 71
ncol: 1 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x024001a5  hdba: 0x024001a3
itli: 2  ispac: 0  maxfr: 4858

--bdba表示undo对应的data block,这里对应的是datafile 9 block 421

XID: this is the transaction ID, which matches the value made up previously from the query on V$TRANSACTION.

CNT: this is the number of undo records in this block.

IRB: this is the index if the first record to be considered in case of a rollback.

OBJN: this is the object number being altered by the undo record. This matches the value previously queried from DBA_OBJECTS.

RCI: this is the next undo record in the chain to be examined. When rolling back transactions, these undo records are applied one by one on the data blocks to undo the changes.

1. undo链表的意思为:从IRB表示undo 回滚的开始，RCI表示未IRB对应的下一条记录，依次类推。当RCI为0,而且rdba不为0的时候，表示undo 记录关联到下一个block；当RCI为0，而且rdba也为0表示undo结束
2. undo事务回滚是倒序的,这里可以看到事务先读取block 422，再读取block 421

dump data block

SQL> alter system dump datafile 9 block 421;

System altered.

Block header dump:  0x024001a5
 Object id on Block? Y
 seg/obj: 0xd29d  csc: 0xb2c.c02ca2bc  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x24001a1 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01d.00000232  0x0083dbe0.02be.22  C---    0  scn 0x0b2c.c02ca2a8
0x02   0x0002.01c.00000201  0x00800e2a.00fd.01  ----    1  fsc 0x0000.00000000

SQL> alter system dump datafile 9 block 422;

System altered.

Block header dump:  0x024001a6
 Object id on Block? Y
 seg/obj: 0xd29d  csc: 0xb2c.c02ca2bd  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x24001a1 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01d.00000232  0x0083dbe0.02be.23  C---    0  scn 0x0b2c.c02ca2a8
0x02   0x0002.01c.00000201  0x00800e2b.00fd.01  ----    1  fsc 0x0000.00000000

这里可以看到Xid,Uba和上面计算出来的一致，看出来事务未提交标记和上述一致.

发表在 Oracle | 一条评论