sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 产品DBA > Oracle BBED >

【学习笔记】Oracle bbed修改itl值手手动提交事务案例

时间:2016-10-22 22:29   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 分享一篇关于Oracle数据库BBEd工具的使用案例,使用bbed修改itl值手手动提交事务详细步骤。
数据库的版本是11.2.0.4

1,数据库版本

oracleplus.net> select * from v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production

2,创建测试表


oracleplus.net> create table scott.htz tablespace users as select * from dba_objects where rownum<2;

Table created.

oracleplus.net> select rowid from scott.htz;

ROWID
——————
AAASkGAAEAAABkzAAA

oracleplus.net> @rowid_to_info.sql
Enter value for rowid: AAASkGAAEAAABkzAAA
ROWID_TYPE: 1
OBJECT_NUMBER: 76038
RELATIVE_FNO: 4
BLOCK_NUMBER: 6451
ROW_NUMBER: 0

PL/SQL procedure successfully completed.

3,模拟实验


3.1 更新行记录
oracleplus.net> conn scott/oracle
Connected.
oracleplus.net> select owner from htz;

OWNER
——————————
SYS

oracleplus.net> update htz set owner=’HUANGTINGZHONG’;

1 row updated.
3.2 将BUFFER_CACHE中的块刷到数据文件中
这里另外打开一个窗口来执行
oracleplus.net> alter system flush buffer_cache;

System altered.

oracleplus.net> select owner from scott.htz;

OWNER
——————————
SYS
3.3 DUMP数据文件中的块
这里dump块的目的是为了查看值是否发生了变化,以及行的标识符信息
oracleplus.net> select file_name from dba_data_files;

FILE_NAME
——————————————————————————–
/oracle/app/oracle/oradata/orcl1123/users01.dbf
/oracle/app/oracle/oradata/orcl1123/undotbs1.dbf
/oracle/app/oracle/oradata/orcl1123/sysaux01.dbf
/oracle/app/oracle/oradata/orcl1123/system01.dbf
/oracle/app/oracle/oradata/orcl1123/undotbs2.dbf
/oracle/app/oracle/oradata/orcl1123/htz01.dbf
/oracle/app/oracle/oradata/orcl1123/tbs_data.dbf
/oracle/app/oracle/oradata/orcl1123/undotbs3.dbf
oracleplus.net> @dump_block.sql
Enter value for datafile: 4
Enter value for block_id: 6451
old 1: alter system dump datafile &datafile block &block_id
new 1: alter system dump datafile 4 block 6451

System altered.
在trace文件中可以看到如下的信息
Block header dump: 0x01001933
Object id on Block Y
seg/obj: 0x12906 csc: 0x00.24e77c itc: 3 flg: E typ: 1 – DATA
brn: 0 bdba: 0x1001930 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.0024e747
0x02 0x000d.003.000000d7 0x0140026c.0202.04 —- 1 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
bdba: 0x01001933
data_block_dump,data header at 0x2a97c20a7c
===============
tsiz: 0x1f80
hsiz: 0x14
pbl: 0x2a97c20a7c
76543210
flag=——–
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1edb
avsp=0x1f14
tosp=0x1f14
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1edb
block_row_dump:
tab 0, row 0, @0x1edb
tl: 88 fb: –H-FL– lb: 0x2 cc: 14
col 0: [14] 48 55 41 4e 47 54 49 4e 47 5a 48 4f 4e 47
col 1: [ 5] 49 43 4f 4c 24
col 2: *NULL*
col 3: [ 2] c1 15
col 4: [ 2] c1 03
col 5: [ 5] 54 41 42 4c 45
col 6: [ 7] 78 6f 09 11 0a 2f 0e
col 7: [ 7] 78 6f 09 11 0a 3b 01
col 8: [19] 32 30 31 31 2d 30 39 2d 31 37 3a 30 39 3a 34 36 3a 31 33
col 9: [ 5] 56 41 4c 49 44
col 10: [ 1] 4e
col 11: [ 1] 4e
col 12: [ 1] 4e
col 13: [ 2] c1 02

4,bbed修改块的相关的内容


其实bbed只需要改2个地址,一个地方是改itl中的flg,更改为提交,另一个地方是清除行中的itl的值
BBED> p ktbbh
struct ktbbh, 96 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 0x00012906
ub4 ktbbhod1 @24 0x00012906
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0x0024e77c
ub2 kscnwrp @32 0x0000
sb2 ktbbhict @36 3
ub1 ktbbhflg @38 0x32 (NONE)
ub1 ktbbhfsl @39 0x00
ub4 ktbbhfnx @40 0x01001930
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 0x0024e747
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x000d
ub2 kxidslt @70 0x0003
ub4 kxidsqn @72 0x000000d7
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x0140026c
ub2 kubaseq @80 0x0202
ub1 kubarec @82 0x04
ub2 ktbitflg @84 0x0001 (NONE)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x00000000

BBED> set mode edit
MODE Edit

BBED> set count 16
COUNT 16
这里有一点需要注意的是,测试平台是X86的,如果是小机需要注意了,有一点不一样的地方,不需要高低位转换
BBED> modify /x 0080 offset 84
Warning: contents of previous BIFILE will be lost. Proceed (Y/N) y
File: /oracle/app/oracle/oradata/orcl1123/users01.dbf (0)
Block: 6451 Offsets: 84 to 99 Dba:0x00000000
————————————————————————
00800000 00000000 00000000 00000000

<32 bytes per line>

BBED> p ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn Oracle oracleplus.net @68 0x000d
ub2 kxidslt @70 0x0003
ub4 kxidsqn @72 0x000000d7
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x0140026c
ub2 kubaseq @80 0x0202
ub1 kubarec @82 0x04
ub2 ktbitflg @84 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x00000000

这里可以看到,下面报了行为的itl值有问题

BBED> verify
DBVERIFY – Verification starting
FILE = /oracle/app/oracle/oradata/orcl1123/users01.dbf
BLOCK = 6451

Block Checking: DBA = 16783667, Block Type = KTB-managed data block
data header at 0x2a98b8727c
kdbchk: row locked by non-existent transaction
table=0 slot=0
lockid=2 ktbbhitc=3
Block 6451 failed with check code 6101

DBVERIFY – Verification complete

Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED

BBED> p *kdbr[0]
rowdata[0]
———-
ub1 rowdata[0] @8023 0x2c

BBED> set offset 8023
OFFSET 8023

BBED> dump
File: /oracle/app/oracle/oradata/orcl1123/users01.dbf (0)
Block: 6451 Offsets: 8023 to 8038 Dba:0x00000000
————————————————————————
2c020e0e 4855414e 4754494e 475a484f

<32 bytes per line>

BBED> modify /x 2c00
File: /oracle/app/oracle/oradata/orcl1123/users01.dbf (0)
Block: 6451 Offsets: 8023 to 8038 Dba:0x00000000
————————————————————————
2c000e0e 4855414e 4754494e 475a484f

<32 bytes per line>

BBED> sum apply
Check value for File 0, Block 6451:
current = 0xbebc, required = 0xbebc
下面再也没有报错了
BBED> verify
DBVERIFY – Verification starting
FILE = /oracle/app/oracle/oradata/orcl1123/users01.dbf
BLOCK = 6451

DBVERIFY – Verification complete

Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED

6,查看bbed更改后的数据


另外开一个窗口来查看表中的数据
oracleplus.net> select owner from scott.htz;

OWNER
——————————
HUANGTINGZHONG
这里可以看到数据已经被更新了。
不过需要注意的是在update窗口,执行rollback语句,还是可以回退的
如果在原窗口执行rollback命令的时候,仍然会回滚记录的。

oracleplus.net> rollback;

Rollback complete.

oracleplus.net> select owner from scott.htz;

OWNER
——————————
SYS

本文固定链接: http://www.htz.pw/2014/05/26/bbed%e4%bf%ae%e6%94%b9itl%e5%80%bc%ef%bc%8c%e6%89%8b%e5%8a%a8%e6%8f%90%e4%ba%a4%e4%ba%8b%e5%8a%a1.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle bbed修改itl值手手动提交事务案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1032.html

Oracle研究中心

关键词:

Oracle bbed工具使用案例

Oracle bbed修改itl值的方法

Oracle bbed修改完成后手动提交事务的案例