在上一篇的基礎上，本篇實現修改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
