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

联系:手机(+86 13429648788) QQ(107644445)QQ咨询惜分飞

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

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在上一篇修改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 分类目录,贴了 , 标签。将固定链接加入收藏夹。

bbed模拟提交事务二之屏蔽smon回滚事务》有 2 条评论

  1. 惜分飞 说:

    通过bbed 定位到行,然后r 的方式查看,或者借助第三方工具定位到行号

    通过bbed 定位到行,然后r 的方式查看,或者借助第三方工具定位到行号

      [引用]  [回复]

  2. 路人甲 说:

    如何精确获得段头的Block地址,如undoo1.dbf的_SYSSMU13_3953822434$的块号

      [引用]  [回复]

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>