这个实验,我们演示怎么使用bbed工具来修改一个事务的状态
模拟环境:
Session A:创建一个表,更新其中一行,但是不提交
SQL> create table goolen as select object_id id,object_name name from user_objects where rownum <=3;
Table created.
SQL> col name for a35
SQL> set lines 120
SQL> select * from goolen;
ID NAME
---------- -----------------------------------
74572 BONUS
74568 DEPT
74570 EMP
SQL> update goolen set id=1 where name='BONUS';
1 row updated.
SQL> select * from goolen;
ID NAME
---------- -----------------------------------
1 BONUS
74568 DEPT
74570 EMP
Session B:
SQL> select
2 dbms_rowid.rowid_relative_fno(rowid) file_number,
3 dbms_rowid.rowid_block_number(rowid) block_number,
4 id,name
5 from goolen;
FILE_NUMBER BLOCK_NUMBER ID NAME
----------- ------------ ---------- -----------------------------------
4 171 74572 BONUS
4 171 74568 DEPT
4 171 74570 EMP
--session A 没有提交,session B查到的数据是修改前的数据
Session C:dump这个块的,查看信息:
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system dump datafile 4 block 171;
System altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/opt/app/oracle/diag/rdbms/goolen/goolen/trace/goolen_ora_30658.trc
--------------------
Block header dump: 0x010000ab
Object id on Block? Y
seg/obj: 0x12400 csc: 0x00.1b10ee itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x10000a8 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.001b0fc6
0x02 0x0001.00a.00000387 0x00c00b9d.0123.11 ---- 1 fsc 0x0002.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x010000ab
--面显示我们Lck了1条记录,并且使用的2号ITL事务槽,锁住了几行数据,对应有几个行锁
--Itl: ITL事务槽号的流水编号
--Flag:C是提交,---是未提交
--下面我们使用bbed来修改这个事务的状态,使其变为提交的状态
[oracle@localhost bbed]$ bbed parfile=bbed.par
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Fri Oct 25 10:17:18 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set dba 4,171
DBA 0x010000ab (16777387 4,171)
BBED> p ktbbh
struct ktbbh, 96 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 0x00012400
ub4 ktbbhod1 @24 0x00012400
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0x001b10ee
ub2 kscnwrp @32 0x0000
sb2 ktbbhict @36 3
ub1 ktbbhflg @38 0x32 (NONE)
ub1 ktbbhfsl @39 0x00
ub4 ktbbhfnx @40 0x010000a8
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
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x001b0fc6
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0001
ub2 kxidslt @70 0x000a
ub4 kxidsqn @72 0x00000387
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c00b9d
ub2 kubaseq @80 0x0123
ub1 kubarec @82 0x11
ub2 ktbitflg @84 0x0001 (NONE)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 2
ub2 _ktbitwrp @86 0x0002
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
sb2 _ktbitfsc @110 0
ub2 _ktbitwrp @110 0x0000
ub4 ktbitbas @112 0x00000000
BBED> m /x 0080 dba 4,171 offset 84
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
Block: 171 Offsets: 84 to 595 Dba:0x010000ab
------------------------------------------------------------------------
00800200 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00010300 ffff1800 051f411f 431f0000 0300051f 1f1f2c1f
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 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 0000 dba 4,171 offset 86
File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
Block: 171 Offsets: 86 to 597 Dba:0x010000ab
------------------------------------------------------------------------
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000001 0300ffff 1800051f 411f431f 00000300 051f1f1f 2c1f0000
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 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 171:
current = 0x8083, required = 0x8083
Seesion B:
--查看表数据,虽然session A没有执行提交,但是从session B已经能查看修改后的数据
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from goolen;
ID NAME
---------- -----------------------------------
1 BONUS
74568 DEPT
模拟环境:
Session A:创建一个表,更新其中一行,但是不提交
SQL> create table goolen as select object_id id,object_name name from user_objects where rownum <=3;
Table created.
SQL> col name for a35
SQL> set lines 120
SQL> select * from goolen;
ID NAME
---------- -----------------------------------
74572 BONUS
74568 DEPT
74570 EMP
SQL> update goolen set id=1 where name='BONUS';
1 row updated.
SQL> select * from goolen;
ID NAME
---------- -----------------------------------
1 BONUS
74568 DEPT
74570 EMP
Session B:
SQL> select
2 dbms_rowid.rowid_relative_fno(rowid) file_number,
3 dbms_rowid.rowid_block_number(rowid) block_number,
4 id,name
5 from goolen;
FILE_NUMBER BLOCK_NUMBER ID NAME
----------- ------------ ---------- -----------------------------------
4 171 74572 BONUS
4 171 74568 DEPT
4 171 74570 EMP
--session A 没有提交,session B查到的数据是修改前的数据
Session C:dump这个块的,查看信息:
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system dump datafile 4 block 171;
System altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/opt/app/oracle/diag/rdbms/goolen/goolen/trace/goolen_ora_30658.trc
--------------------
Block header dump: 0x010000ab
Object id on Block? Y
seg/obj: 0x12400 csc: 0x00.1b10ee itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x10000a8 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.001b0fc6
0x02 0x0001.00a.00000387 0x00c00b9d.0123.11 ---- 1 fsc 0x0002.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x010000ab
--面显示我们Lck了1条记录,并且使用的2号ITL事务槽,锁住了几行数据,对应有几个行锁
--Itl: ITL事务槽号的流水编号
--Flag:C是提交,---是未提交
--下面我们使用bbed来修改这个事务的状态,使其变为提交的状态
[oracle@localhost bbed]$ bbed parfile=bbed.par
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Fri Oct 25 10:17:18 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set dba 4,171
DBA 0x010000ab (16777387 4,171)
BBED> p ktbbh
struct ktbbh, 96 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 0x00012400
ub4 ktbbhod1 @24 0x00012400
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0x001b10ee
ub2 kscnwrp @32 0x0000
sb2 ktbbhict @36 3
ub1 ktbbhflg @38 0x32 (NONE)
ub1 ktbbhfsl @39 0x00
ub4 ktbbhfnx @40 0x010000a8
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
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x001b0fc6
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0001
ub2 kxidslt @70 0x000a
ub4 kxidsqn @72 0x00000387
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c00b9d
ub2 kubaseq @80 0x0123
ub1 kubarec @82 0x11
ub2 ktbitflg @84 0x0001 (NONE)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 2
ub2 _ktbitwrp @86 0x0002
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
sb2 _ktbitfsc @110 0
ub2 _ktbitwrp @110 0x0000
ub4 ktbitbas @112 0x00000000
BBED> m /x 0080 dba 4,171 offset 84
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
Block: 171 Offsets: 84 to 595 Dba:0x010000ab
------------------------------------------------------------------------
00800200 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00010300 ffff1800 051f411f 431f0000 0300051f 1f1f2c1f
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 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 0000 dba 4,171 offset 86
File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
Block: 171 Offsets: 86 to 597 Dba:0x010000ab
------------------------------------------------------------------------
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000001 0300ffff 1800051f 411f431f 00000300 051f1f1f 2c1f0000
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 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 171:
current = 0x8083, required = 0x8083
Seesion B:
--查看表数据,虽然session A没有执行提交,但是从session B已经能查看修改后的数据
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from goolen;
ID NAME
---------- -----------------------------------
1 BONUS
74568 DEPT
74570 EMP