【Oracle】使用bbed手动提交事务
有时候数据库挂掉，起库会出现ORA-00704错误，而导致ORA-00704错误的根本原因是訪问OBJ$的时候。ORACLE须要回滚段中的数据，而訪问回滚段的时候须要的undo数据已经被覆盖，此时我们能够通过bbed工具手工提交事务。从而解决问题。
使用bbed提交事务測试步骤例如以下：
JP@ORCL>create table JP_BBED_TEST as select * from hr.employees where rownum<=10;
Table created.
JP@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 jp_bbed_test; 2 3 4
ROWID REL_FNO BLOCKNO ROWNO
------------------ ---------- ---------- ----------
AAAM7wAAEAAAAGcAAA 4 412 0
AAAM7wAAEAAAAGcAAB 4 412 1
AAAM7wAAEAAAAGcAAC 4 412 2
AAAM7wAAEAAAAGcAAD 4 412 3
AAAM7wAAEAAAAGcAAE 4 412 4
AAAM7wAAEAAAAGcAAF 4 412 5
AAAM7wAAEAAAAGcAAG 4 412 6
AAAM7wAAEAAAAGcAAH 4 412 7
AAAM7wAAEAAAAGcAAI 4 412 8
AAAM7wAAEAAAAGcAAJ 4 412 9
10 rows selected.
JP@ORCL>select last_name from jp_bbed_test;
LAST_NAME
-------------------------
OConnell
Grant
Whalen
Hartstein
Fay
Mavris
Baer
Higgins
Gietz
King
10 rows selected.
JP@ORCL>update jp_bbed_test set LAST_NAME='BADLY9';
10 rows updated.
此时事务没有提交，其它的session是无法查看此时的改动的。
另开一个窗体
[oracle@jp bbed]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jun 6 06:56:52 2014
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
SYS@ORCL>alter system checkpoint;
System altered.
SYS@ORCL>alter system dump datafile 4 block 412;
System altered.
SYS@ORCL>oradebug setmypid
Statement processed.
SYS@ORCL>oradebug tracefile_name
/u01/app/oracle/admin/ORCL/udump/orcl_ora_17715.trc
查看/u01/app/oracle/admin/ORCL/udump/orcl_ora_17715.trc文件
我们能够看到下面内容：
Block header dump: 0x0100019c
Object id on Block?
Y
seg/obj: 0xcef0 csc: 0x00.c3cf8 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000199 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.000c3cf8
0x02 0x0006.02b.000001a1 0x008000d0.00f6.2a ---- 10 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
这里能够看到我们LCK了10条表中记录
这时候我们使用bbed将事务提交：
首先使用session2 flush buffer_pool。假设不清空buffer pool。再次读取该block时将不会进行物理读，无法看到改动后的结果并且会覆盖我们的改动：
SYS@ORCL>alter system flush buffer_cache;
System altered.
使用bbed工具进行改动：
BBED> set dba 4,412
DBA 0x0100019c (16777628 4,412)
BBED> map
File: /u01/app/oracle/oradata/ORCL/users01.dbf (4)
Block: 412 Dba:0x0100019c
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 96 bytes @20
struct kdbh, 14 bytes @124
struct kdbt[1], 4 bytes @138
sb2 kdbr[10] @142
ub1 freespace[6794] @162
ub1 rowdata[1232] @6956
ub4 tailchk @8188
BBED> p ktbbh
struct ktbbh, 96 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 0x0000cef0
ub4 ktbbhod1 @24 0x0000cef0
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0x000c3cf8
ub2 kscnwrp @32 0x0000
b2 ktbbhict @36 3
ub1 ktbbhflg @38 0x32 (NONE)
ub1 ktbbhfsl @39 0x00
ub4 ktbbhfnx @40 0x01000199
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 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x000c3cf8
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0006
ub2 kxidslt @70 0x002b
ub4 kxidsqn @72 0x000001a1
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x008000d0
ub2 kubaseq @80 0x00f6
ub1 kubarec @82 0x2a
ub2 ktbitflg @84 0x000a (NONE)
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
关于事务的状态例如以下：
TRANSACTION_COMMITED = 0×08;
TRANSACTION_UPBOUND = 0×02;
TRANSACTION_ACTIVE = 0×01;
BBED> m /x 0080 offset 84
File: /u01/app/oracle/oradata/ORCL/users01.dbf (4)
Block: 412 Offsets: 84 to 595 Dba:0x0100019c
------------------------------------------------------------------------
00800000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00010a00 ffff2600 b01aac1c ac1c0000 0a008e1c 481cac1e
021cc41b e51d811b 391bf01a b01a0000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> p ktbbh
struct ktbbh, 96 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 0x0000cef0
ub4 ktbbhod1 @24 0x0000cef0
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0x000c3cf8
ub2 kscnwrp @32 0x0000
b2 ktbbhict @36 3
ub1 ktbbhflg @38 0x32 (NONE)
ub1 ktbbhfsl @39 0x00
ub4 ktbbhfnx @40 0x01000199
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 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x000c3cf8
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0006
ub2 kxidslt @70 0x002b
ub4 kxidsqn @72 0x000001a1
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x008000d0
ub2 kubaseq @80 0x00f6
ub1 kubarec @82 0x2a
ub2 ktbitflg @84 0x8000 (KTBFCOM)
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> sum apply
Check value for File 4, Block 412:
current = 0x14cc, required = 0x14cc
BBED> exit
这时候重开一个session查看该表信息：
SYS@ORCL>select last_name from jp.jp_bbed_test;
LAST_NAME
-------------------------
BADLY9
BADLY9
BADLY9
BADLY9
BADLY9
BADLY9
BADLY9
BADLY9
BADLY9
BADLY9
10 rows selected.
尽管session1的事务没有提交但此时其它session已经能够查看该改动结果。
有时改动完ktbitflg之后其它session仍然不能查看改动结果，此时使用bbed将ub2 _ktbitwrp改动为0就可以查看到改动后的结果。
须要注意的是假设此时session 1仍然能够将该事务进行回滚。
Session 1：
JP@ORCL>rollback;
Rollback complete.
Session 3：
JP@ORCL>select last_name from jp_bbed_test;
LAST_NAME
-------------------------
OConnell
Grant
Whalen
Hartstein
Fay
Mavris
Baer
Higgins
Gietz
King
10 rows selected.
