背景
在之前几篇文章http://blog.itpub.net/9240380/viewspace-1816245/，简短测试了如何修改表中数据数据为字符的列内容，本文开始测试如何修改数据数据类型为数字的列。
结论
1,用bbed修改数据块，不能仅修改列内容部分2,还要修改kcbh结构体中的chkval_kcbh，即用于校验数据块的，不过要修改时
3,kcbh结构体中的chkval_kcbh如何修改呢，先要sum dba 文件号，块号
会提示一个需要的chkval_kcbh值
然后用这个值去修改chkval_kcbh值，不过一定要用反序修改
接着再verify,确认数据块没有报坏块错误
最后再sum dba 文件号，块号
4，tailchk构成就是bas_kcbh的低2位，即81d0,接着是type_kcbh,即06
最后是seq_kcbh，即02,也就是这个值接接在一起
tailchk=Lower order two bytes of SCN Base(bas_kcbh) + Block Type(type_kcbh) + SCN Seq(seq_kcbh)
0x81d00602=0x024381d0 + 0x06 + 0x02
5,当你用bbed调整了数据块中的chkval_kcbh的值及表记录列值，tailchk会自动进行对应调整，不用手工调整
6,通过对比测试发现tailchk以及chkval_kcbh的真正含义
碰到问题，一定要多换思路
7,set offset 偏移量，然后可以直接修改内容modify /x 修改后的新值
8,有时用BBED修改要多刷新几次缓冲池，修改的结果才能体现出来
测试
SQL> create table t_number(a int);
Table created.
SQL> insert into t_number values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select dump(1,16) from dual;
DUMP(1,16)
----------------------------------
Typ=2 Len=2: c1,2
SQL> alter system flush buffer_cache;
System altered.
SQL> select DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),dbms_rowid.rowid_row_number(rowid) from t_number;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)
------------------------------------ ------------------------------------ ----------------------------------
4 99027 0
BBED> set file 4
FILE# 4
BBED> set block 99027
BLOCK# 99027
BBED> find /x c102
File: /oracle/oradata/guowang/users01.dbf (4)
Block: 99027 Offsets: 8186 to 8191 Dba:0x010182d3
------------------------------------------------------------------------
c1020106 e6ce
<32 bytes per line>
数据8的16进制为c109
SQL> select dump(8,16) from dual;
DUMP(8,16)
----------------------------------
Typ=2 Len=2: c1,9
BBED> dump /v dba 4,99027 offset 8186
File: /oracle/oradata/guowang/users01.dbf (4)
Block: 99027 Offsets: 8186 to 8191 Dba:0x010182d3
-------------------------------------------------------
c1020106 e6ce l ......
<16 bytes per line>
修改数字1为8
BBED> modify /x c109 dba 4,99027 offset 8186
File: /oracle/oradata/guowang/users01.dbf (4)
Block: 99027 Offsets: 8186 to 8191 Dba:0x010182d3
------------------------------------------------------------------------
c1090106 e6ce
<32 bytes per line>
验证数据块，出现坏块
BBED> verify
Message 500 not found; No message file for product=RDBMS, facility=BBED
Message 501 not found; No message file for product=RDBMS, facility=BBED
Message 520 not found; No message file for product=RDBMS, facility=BBED
Corrupt block relative dba: 0x010182d3 (file 0, block 99027)
Bad check value found during verification
Data in bad block:
type: 6 format: 2 rdba: 0x010182d3
last change scn: 0x0000.023ecee6 seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xcee60601
check value in block header: 0x5704
computed block checksum: 0xb00
回退操作
BBED> revert
All changes made in this session will be rolled back. Proceed? (Y/N) y
Reverted file '/oracle/oradata/guowang/users01.dbf', block 99027
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
继续修改,还原来原来的列值，这下成功了，可见不能修改为上述的值
BBED> modify /x c102 dba 4,99027 offset 8186
File: /oracle/oradata/guowang/users01.dbf (4)
Block: 99027 Offsets: 8186 to 8191 Dba:0x010182d3
------------------------------------------------------------------------
c1020106 e6ce
<32 bytes per line>
BBED> verify
我们换一个新列值，再次进行修改,还是不成功，报坏块错误
SQL> select dump(2,16) from dual;
DUMP(2,16)
----------------------------------
Typ=2 Len=2: c1,3
BBED> modify /x c103 dba 4,99027 offset 8186
File: /oracle/oradata/guowang/users01.dbf (4)
Block: 99027 Offsets: 8186 to 8191 Dba:0x010182d3
------------------------------------------------------------------------
c1030106 e6ce
<32 bytes per line>
BBED> verify
Message 500 not found; No message file for product=RDBMS, facility=BBED
Message 501 not found; No message file for product=RDBMS, facility=BBED
Message 520 not found; No message file for product=RDBMS, facility=BBED
Corrupt block relative dba: 0x010182d3 (file 0, block 99027)
Bad check value found during verification
Data in bad block:
type: 6 format: 2 rdba: 0x010182d3
last change scn: 0x0000.023ecee6 seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xcee60601
check value in block header: 0x5704
computed block checksum: 0x100
我们换种思路，采用update更新列值为8，然后用BBED查看这个列值如何存储的
SQL> update t_number set a=8;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system flush buffer_cache;
System altered.
SQL> select DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),dbms_rowid.rowid_row_number(rowid) from t_number;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)
------------------------------------ ------------------------------------ ----------------------------------
4 99027 0
确认是变成了c109,即8的16进制，但注意注意啊，原来用dump查看的e6ce变成了a612
BBED> dump /v dba 4,99027 offset 8186
File: /oracle/oradata/guowang/users01.dbf (4)
Block: 99027 Offsets: 8186 to 8191 Dba:0x010182d3
-------------------------------------------------------
c1090206 a612 l ......
<16 bytes per line>
所以现在要搞清楚，这个a612这块信息是存储什么，起什么作用，就可以解决这个坏块问题了？
BBED>
我们继续把列值变成为9,主要查看DUMP /V第2部分是否变化，想找这个值变化的规律
SQL> select 1,dump(9,16) from dual;
1 DUMP(9,16)
---------- ----------------------------------
1 Typ=2 Len=2: c1,a
SQL> update t_number set a=9;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system flush buffer_cache;
System altered.
BBED> dump /v dba 4,99027 offset 8186
File: /oracle/oradata/guowang/users01.dbf (4)
Block: 99027 Offsets: 8186 to 8191 Dba:0x010182d3
-------------------------------------------------------
c10a0206 3c95 l ....<.
<16 bytes per line>
还是找不到DUMP /V第2部分内容到底是什么含义，直接DUMP数据文件，看看会不会找到线索
SQL> alter session set tracefile_identifier='block_dump';
Session altered.
SQL> alter system dump datafile 4 block 99027;
System altered.
感觉和SCN及TAIL有关系
[oracle@seconary trace]$ more guowang_ora_9150_block_dump.trc|grep -i --color '3c'
scn: 0x0000.0242953c seq: 0x02 flg: 0x06 tail: 0x953c0602
2B16FD587A00 0000A206 010182D3 0242953C 06020000 [........<.B.....]
2B16FD587A40 0242953C 000400F9 00000715 01C00AC1 [<.B.............]
2B16FD5899F0 00000000 012C0000 0AC10201 953C0602 [......,.......<.]
0x01 0x0125.01d.00000550 0x01c00db7.0133.3a --U- 1 fsc 0x0000.0242953c
感觉像是tail的值，准确来说是前2个高位，且是反序存储
BBED> p tailchk
ub4 tailchk @8188 0x953c0602
我们再UPDATE换一个新值，确认不是不tail的值
SQL> select * from t_number;
A
----------
9
SQL> update t_number set a=5;
1 row updated.
SQL> commit;
Commit complete.
SQL> select dump(5,16) from dual;
DUMP(5,16)
----------------------------------
Typ=2 Len=2: c1,6
SQL> alter system flush buffer_cache;
System altered.
这下发现tailchk没有变化哟
BBED> p tailchk
ub4 tailchk @8188 0x953c0602
但是dump发生变化了，真晕
BBED> dump /v dba 4,99027 offset 8186
File: /oracle/oradata/guowang/users01.dbf (4)
Block: 99027 Offsets: 8186 to 8191 Dba:0x010182d3
-------------------------------------------------------
c1060206 d081 l ......
<16 bytes per line>
多刷新几次缓冲池，就正常了，确认了dump /v第2部分就是tailchk的值，不是以反序显示的
SQL> alter system flush buffer_cache;
System altered.
BBED> p tailchk
ub4 tailchk @8188 0x81d00602
这下我们就来研究下tailchk含义及作用？
从如下文章
http://www.xifenfei.com/2011/08/通过bbed查看数据库结构.html
可知tailchk的含义及构成
tailchk=Lower order two bytes of SCN Base(bas_kcbh) + Block Type(type_kcbh) + SCN Seq(seq_kcbh)
分别查看bas_kcbh及type_kcbh和seq_kcbh的值
bas_kcbh及type_kcbh存储在数据块头结构体kcbh中
BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06 --type_kcbh为0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x010182d3
ub4 bas_kcbh @8 0x024381d0 --bas_kcbh为0x024381d0
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x02 --seq_kcbh为0x02
ub1 flg_kcbh @15 0x06 (KCBHFDLC, KCBHFCKV)
ub2 chkval_kcbh @16 0x5d11
ub2 spare3_kcbh @18 0x0000
可否理解为tailchk构成就是bas_kcbh的低2位，即81d0,接着是type_kcbh,即06
最后是seq_kcbh，即02,也就是这个值接接在一起
tailchk=Lower order two bytes of SCN Base(bas_kcbh) + Block Type(type_kcbh) + SCN Seq(seq_kcbh)
0x81d00602=0x024381d0 + 0x06 + 0x02
也就是说这里面变化只有bas_kcbh及seq_kcbh（因为数据块类型基本很少变化）
我猜测只要我手动每次把tailchk根据这个算法，进行调整，就可以用BBED修改列值了
我们先要学习下如何手工修改tailchk的值
BBED> map
File: /oracle/oradata/guowang/users01.dbf (4)
Block: 99027 Dba:0x010182d3
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @100
struct kdbt[1], 4 bytes @114
sb2 kdbr[1] @118
ub1 freespace[8062] @120
ub1 rowdata[6] @8182
ub4 tailchk @8188
BBED> set offset 8188
OFFSET 8188
BBED> dump /v
File: /oracle/oradata/guowang/users01.dbf (4)
Block: 99027 Offsets: 8188 to 8191 Dba:0x010182d3
-------------------------------------------------------
0206d081 l ....
<16 bytes per line>
BBED>
可见把tailchk可以修改为指定的值
BBED> modify /x 0303d111
File: /oracle/oradata/guowang/users01.dbf (4)
Block: 99027 Offsets: 8188 to 8191 Dba:0x010182d3
------------------------------------------------------------------------
0303d111
<32 bytes per line>
一验证有坏块出现了
BBED> verify
Message 500 not found; No message file for product=RDBMS, facility=BBED
Message 501 not found; No message file for product=RDBMS, facility=BBED
Message 520 not found; No message file for product=RDBMS, facility=BBED
Corrupt block relative dba: 0x010182d3 (file 0, block 99027)
Fractured block found during verification
Data in bad block:
type: 6 format: 2 rdba: 0x010182d3
last change scn: 0x0000.024381d0 seq: 0x2 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x11d10303
check value in block header: 0x5d11
回退，再验证又恢复正常了
BBED> revert
All changes made in this session will be rolled back. Proceed? (Y/N) y
Reverted file '/oracle/oradata/guowang/users01.dbf', block 99027
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
BBED> verify
我们现在手工用BBED来修改列的值
SQL> select * from t_number;
A
----------
1
查看数据块的存储信息
BBED> dump /v dba 4,99027 offset 8186
File: /oracle/oradata/guowang/users01.dbf (4)
Block: 99027 Offsets: 8186 to 8191 Dba:0x010182d3
-------------------------------------------------------
c1020106 e6ce l ......
<16 bytes per line>
开始修改列值，由1变成8
SQL> select dump(8,16) from dual;
DUMP(8,16)
----------------------------------
Typ=2 Len=2: c1,9
BBED> modify /x c109 dba 4,99027 offset 8186
File: /oracle/oradata/guowang/users01.dbf (4)
Block: 99027 Offsets: 8186 to 8191 Dba:0x010182d3
------------------------------------------------------------------------
c1090106 e6ce
<32 bytes per line>
出现坏块
BBED> verify
Message 500 not found; No message file for product=RDBMS, facility=BBED
Message 501 not found; No message file for product=RDBMS, facility=BBED
Message 520 not found; No message file for product=RDBMS, facility=BBED
Corrupt block relative dba: 0x010182d3 (file 0, block 99027)
Bad check value found during verification
Data in bad block:
type: 6 format: 2 rdba: 0x010182d3
last change scn: 0x0000.023ecee6 seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xcee60601
check value in block header: 0x5704
修改tailchk
BBED> p tailchk
ub4 tailchk @8188 0xcee60601
BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x010182d3
ub4 bas_kcbh @8 0x023ecee6
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x06 (KCBHFDLC, KCBHFCKV)
ub2 chkval_kcbh @16 0x5704
ub2 spare3_kcbh @18 0x0000
可见也要调整kcbh数据块头结构体的chkval_kcbh的值
BBED> sum dba 4,99027
Check value for File 4, Block 99027:
current = 0x5704, required = 0x5c04
修改chkval_kcbh的值
BBED> set offset 16
OFFSET 16
BBED> p
kcbh.chkval_kcbh
----------------
ub2 chkval_kcbh @16 0x5704
BBED> modify /x 5c04
File: /oracle/oradata/guowang/users01.dbf (4)
Block: 99027 Offsets: 16 to 21 Dba:0x010182d3
------------------------------------------------------------------------
5c040000 0100
<32 bytes per line>
可见修改chkval_kcbh的值，搞反了
BBED> sum dba 4,99027
Check value for File 4, Block 99027:
current = 0x045c, required = 0x5c04
BBED> modify /x 045c
File: /oracle/oradata/guowang/users01.dbf (4)
Block: 99027 Offsets: 16 to 21 Dba:0x010182d3
------------------------------------------------------------------------
045c0000 0100
<32 bytes per line>
这下成功了
BBED> sum dba 4,99027
Check value for File 4, Block 99027:
current = 0x5c04, required = 0x5c04
这下tailchk也发生变化了
BBED> p tailchk
ub4 tailchk @8188 0xcee60601
验证也成功了
BBED> verify
真正修改数据块
BBED> sum dba 4,99027 apply
Check value for File 4, Block 99027:
current = 0x5c04, required = 0x5c04
终于修改成功了，哈哈哈
SQL> select * from t_number;
A
----------
1
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from t_number;
A
----------
8
个人简介：
8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。
服务过的客户：
中国电信
中国移动
中国联通
中国电通
国家电网
四川达州商业银行
湖南老百姓大药房
山西省公安厅
中国邮政
北京302医院
河北廊坊新奥集团公司
项目经验:
中国电信3G项目AAA系统数据库部署及优化
中国联通CRM数据库性能优化
中国移动10086电商平台数据库部署及优化
湖南老百姓大药房ERR数据库sql优化项目
四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化
四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化
北京高铁信号监控系统RAC数据库部署及优化
河南宇通客车数据库性能优化
中国电信电商平台核心采购模块表模型设计及优化
中国邮政储蓄系统数据库性能优化及sql优化
北京302医院数据库迁移实施
河北廊坊新奥data guard部署及优化
山西公安厅身份证审计数据库系统故障评估
联系方式：
手机：18201115468
qq : 305076427
qq微博: wisdomone1
新浪微博：wisdomone9
qq群：275813900
itpub博客名称：wisdomone1
http://blog.itpub.net/9240380/
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1816247/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-1816247/
7190
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
