【Oracle】使用bbed提交事务（二）

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

实验过程如下

Session 1中

JP@ORCL>select last_name from bbed_test;

 

LAST_NAME

-------------------------

OConnell

Grant

Whalen

Hartstein

Fay

Mavris

Baer

Higgins

Gietz

King

 

10 rows selected.

 

JP@ORCL>update bbed_test set last_name='BADLY9';

 

10 rows updated.

事务未提交

 

Session 2中：

SYS@ORCL>select rowid, dbms_rowid.rowid_relative_fno(rowid)rel_fno,

dbms_rowid.rowid_block_number(rowid)blockno,

dbms_rowid.rowid_row_number(rowid) rowno

from   2    3    4  

  5  JP.BBED_TEST;

 

ROWID                 REL_FNO    BLOCKNO      ROWNO

------------------ ---------- ---------- ----------

AAAM8CAAEAAAAG8AAA          4        444          0

AAAM8CAAEAAAAG8AAB          4        444          1

AAAM8CAAEAAAAG8AAC          4        444          2

AAAM8CAAEAAAAG8AAD          4        444          3

AAAM8CAAEAAAAG8AAE          4        444          4

AAAM8CAAEAAAAG8AAF          4        444          5

AAAM8CAAEAAAAG8AAG          4        444          6

AAAM8CAAEAAAAG8AAH          4        444          7

AAAM8CAAEAAAAG8AAI          4        444          8

AAAM8CAAEAAAAG8AAJ          4        444          9

 

10 rows selected.

 

SYS@ORCL>SELECT XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC FROM v$transaction;

 

    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC

---------- ---------- ---------- ---------- ---------- ---------- ----------

         7         38        369          2        786        302         16

 

SYS@ORCL>select segment_name,header_file,header_block 

  2  from dba_segments where segment_name=(

  3  select name from v$rollname where usn=(select xidusn from v$transaction));

 

SEGMENT_NAME  HEADER_FILE  HEADER_BLOCK

 

------------------  ----------- ------------

 

_SYSSMU7$      2          105

         

 

SYS@ORCL>alter system dump undo header "_SYSSMU7$";

 

System altered.

 

SYS@ORCL>oradebug setmypid

Statement processed.

SYS@ORCL>oradebug tracefile_name

/u01/app/oracle/admin/ORCL/udump/orcl_ora_8512.trc

查看/u01/app/oracle/admin/ORCL/udump/orcl_ora_8512.trc文件，在其中可以看到

   0x25    9    0x00  0x0171  0x001d  0x0000.000ee92e  0x00000000  0x0000.000.00000000  0x00000000   0x00000000  1402237152

   0x26   10    0x80  0x0171  0x0001  0x0000.000f3981  0x00800312  0x0000.000.00000000  0x00000001   0x00000000  0

   0x27    9    0x00  0x0171  0x0022  0x0000.000f38cc  0x00800311  0x0000.000.00000000  0x00000001   0x00000000  1402238227

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

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

--index 0x26

7101 wrap#  0000 12038000 dba 81390f00 00000000  scn  0a s tate  80  cflags  0100  uel 00000000 00000000 parent-xid 00000000 stmt_num 01000000 nub 00000000 cmt

 

BBED> f /x 0a80

 File: /u01/app/oracle/oradata/ORCL/undotbs01.dbf (2)

 Block: 105              Offsets: 7736 to 8191           Dba:0x00800069

------------------------------------------------------------------------

 0a800100 00000000 00000000 00000000 01000000 00000000 71010000 11038000 

 

 <32 bytes per line>

 

 

BBED>  m /x 0900

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y

 File: /u01/app/oracle/oradata/ORCL/undotbs01.dbf (2)

 Block: 105              Offsets: 7736 to 8191           Dba:0x00800069

------------------------------------------------------------------------

 09000100 00000000 00000000 00000000 01000000 00000000 71010000 11038000 

 

 <32 bytes per line>

 

 

BBED> sum apply

Check value for File 2, Block 105:

current = 0x8e65, required = 0x8e65

 

接下来修改itl

   struct ktbbhitl[1], 24 bytes             @68      

      struct ktbitxid, 8 bytes              @68      

         ub2 kxidusn                        @68       0x0007

         ub2 kxidslt                        @70       0x0026

         ub4 kxidsqn                        @72       0x00000171

      struct ktbituba, 8 bytes              @76      

         ub4 kubadba                        @76       0x00800312

         ub2 kubaseq                        @80       0x012e

         ub1 kubarec                        @82       0x10

      ub2 ktbitflg                          @84       0x000a (NONE)

      union _ktbitun, 2 bytes               @86      

         b2 _ktbitfsc                       @86       0

         ub2 _ktbitwrp                      @86       0x0000

      ub4 ktbitbas                          @88       0x00000000

 

BBED> m /x 0080 offset 84

 File: /u01/app/oracle/oradata/ORCL/users01.dbf (4)

 Block: 444              Offsets:   84 to  595           Dba:0x010001bc

------------------------------------------------------------------------

 00800000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 

 <32 bytes per line>

 

BBED> sum apply

Check value for File 4, Block 444:

current = 0x17c1, required = 0x17c1

 

 

Session3重启数据库查询数据：

SYS@ORCL>shutdown abort

ORACLE instance shut down.

SYS@ORCL>startup

ORACLE instance started.

 

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              75499088 bytes

Database Buffers          205520896 bytes

Redo Buffers                2973696 bytes

Database mounted.

Database opened.

SYS@ORCL>select last_name from jp.bbed_test;

 

LAST_NAME

-------------------------

BADLY9

BADLY9

BADLY9

BADLY9

BADLY9

BADLY9

BADLY9

BADLY9

BADLY9

BADLY9

 

10 rows selected.

 

实验过程参考飞总的文章：http://www.orasos.com/4194.html