本篇主要是方便大家更加加深了解oracle底层数据块的结构,并且能够轻松处理一些坏块问题。
以及BBED常用的一些命令。
首先我们创建一张测试表,并插入两条数据
TC@ENMOEDU>create table tc (id number,name varchar2(20));
Table created.
TC@ENMOEDU>insert into tc values(1,'tc');
1 row created.
TC@ENMOEDU>insert into tc values(2,'bj');
1 row created.
第二步,我们根据dbms_rowid这个包找到,这张表数据所在的数据文件号和block号,
TC@ENMOEDU>select dbms_rowid.rowid_relative_fno(rowid) f_no,dbms_rowid.rowid_block_number(rowid) block_no from tc;
F_NO BLOCK_NO
---------- ----------
4 1097
4 1097
下面我们查一下该数据库的数据文件,可以看到4号数据文件,为user表空间
TC@ENMOEDU>col name for a50
TC@ENMOEDU>l
1* select file#,name,bytes from v$datafile
TC@ENMOEDU>/
FILE# NAME BYTES
---------- -------------------------------------------------- ----------
1 /ORA11G/app/oracle/oradata/ENMOEDU/system01.dbf 536870912
2 /ORA11G/app/oracle/oradata/ENMOEDU/sysaux01.dbf 536870912
3 /ORA11G/app/oracle/oradata/ENMOEDU/undotbs01.dbf 314572800
4 /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf 1073741824
5 /ORA11G/app/oracle/oradata/ENMOEDU/tc01.dbf 536870912
6 /ORA11G/app/oracle/oradata/ENMOEDU/enmo01.dbf 10485760
6 rows selected.
第三步,编译BBED,简单介绍一下。由于我的oracle是11g的版本,所以需要从oracle10g的$ORACLE_HOME/rdbms/lib中,
拷贝ssbbded.o, sbbdpt.o,以及$ORACLE_HOME/rdbms/msg中,拷贝bbedus.msb,到我的数据库相应的路径。
然后在lib目录下编译
[oracle@issapp01 ~]$ cd $ORACLE_HOME/rdbms/lib
[oracle@issapp01 ~]$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
编译完成后在lib目录下,会生成bbed文件
[oracle@issapp01 lib]$ ls |grep bbed
bbed
bbedus.msb
copy bbed到oracle的bin目录下,并添加环境变量
[oracle@issapp01 ~]$ vim .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin:/ORA11G/app/oracle/product/11.2.0/dbhome_1/bin
export PATH
export ORACLE_BASE=/ORA11G/app/oracle
export ORACLE_SID=ENMOEDU
export ORACLE_HOME=/ORA11G/app/oracle/product/11.2.0/dbhome_1
编辑2个bbed需要的文件(文件名可以自定义),bbed.txt和par.bbd
[oracle@issapp01 ~]$ vim bbed.txt
1 /ORA11G/app/oracle/oradata/ENMOEDU/system01.dbf
2 /ORA11G/app/oracle/oradata/ENMOEDU/sysaux01.dbf
3 /ORA11G/app/oracle/oradata/ENMOEDU/undotbs01.dbf
4 /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf
5 /ORA11G/app/oracle/oradata/ENMOEDU/tc01.dbf
6 /ORA11G/app/oracle/oradata/ENMOEDU/enmo01.dbf
[oracle@issapp01 ~]$ vim par.bbd
blocksize=8192 /*数据库文件都是8k,有16k数据文件,则指定16k
listfile=bbed.txt
mode=edit /*设置编辑模式
第四步,使用BBED登陆,密码blockedit。并开始构造数据
[oracle@issapp01 ~]$ bbed parfile=par.bbd
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Wed Mar 30 02:31:57 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED>
BBED>
-------------设置当前数据块为,4号数据文件的1097块-------------------------------
BBED> set dba 4,1097
DBA 0x01000449 (16778313 4,1097)
------------查看1097块的详细结构,具体每个结构的意思,在另外一篇博客介绍,这里我们只简单描述。我们构造数据只需要知道,具体数据的偏移量从@8152开始即可
BBED> map /v
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Dba:0x01000449
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0 --
ub1 type_kcbh @0 --块类型
ub1 frmt_kcbh @1 --块格式
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4 --块地址
ub4 bas_kcbh @8 --scn的base部分
ub2 wrp_kcbh @12 --scn的wrad部分
ub1 seq_kcbh @14 --相同scn下的seq#
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 72 bytes @20 --块头结构
ub1 ktbbhtyp @20 --块类型
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28 --最后一次cleanout的scn
b2 ktbbhict @36 --itl槽的数量,需要先和ox00ff按位与操作,取低位数据
ub1 ktbbhflg @38 --标志
ub1 ktbbhfsl @39 --空闲空间lock fsl
ub4 ktbbhfnx @40 --freelist链指针
struct ktbbhitl[2], 48 bytes @44
struct kdbh, 14 bytes @92
ub1 kdbhflag @92 --块标记
sb1 kdbhntab @93 --块中表数量
sb2 kdbhnrow @94 --具体行数
sb2 kdbhfrre @96 --第一个空闲位置
sb2 kdbhfsbo @98 --块中空闲地址的起始偏移量
sb2 kdbhfseo @100 --块中空闲地址的末尾偏移量
sb2 kdbhavsp @102 --平均可利用空间
sb2 kdbhtosp @104 --总的可利用空间
struct kdbt[1], 4 bytes @106
sb2 kdbtoffs @106
sb2 kdbtnrow @108 --记录数量
sb2 kdbr[2] @110 --行数据字典
ub1 freespace[8038] @114
ub1 rowdata[36] @8152 --具体数据位置
ub4 tailchk @8188
-----------打印数据行-----------------------------------------------------
BBED> p rowdata
ub1 rowdata[0] @8152 0x2c
ub1 rowdata[1] @8153 0x01
ub1 rowdata[2] @8154 0x02
ub1 rowdata[3] @8155 0x02
ub1 rowdata[4] @8156 0xc1
ub1 rowdata[5] @8157 0x03
ub1 rowdata[6] @8158 0x02
ub1 rowdata[7] @8159 0x62
ub1 rowdata[8] @8160 0x6a
ub1 rowdata[9] @8161 0x2c
ub1 rowdata[10] @8162 0x01
ub1 rowdata[11] @8163 0x02
ub1 rowdata[12] @8164 0x02
ub1 rowdata[13] @8165 0xc1
ub1 rowdata[14] @8166 0x02
ub1 rowdata[15] @8167 0x02
ub1 rowdata[16] @8168 0x74
ub1 rowdata[17] @8169 0x63
ub1 rowdata[18] @8170 0x3c
ub1 rowdata[19] @8171 0x01
ub1 rowdata[20] @8172 0x02
ub1 rowdata[21] @8173 0x02
ub1 rowdata[22] @8174 0xc1
ub1 rowdata[23] @8175 0x03
ub1 rowdata[24] @8176 0x02
ub1 rowdata[25] @8177 0x62
ub1 rowdata[26] @8178 0x6a
ub1 rowdata[27] @8179 0x3c
ub1 rowdata[28] @8180 0x01
ub1 rowdata[29] @8181 0x02
ub1 rowdata[30] @8182 0x02
ub1 rowdata[31] @8183 0xc1
ub1 rowdata[32] @8184 0x02
ub1 rowdata[33] @8185 0x02
ub1 rowdata[34] @8186 0x74
ub1 rowdata[35] @8187 0x63
-------------输出一下第二行(块存储数据,从高位向低位)----------
BBED> x /rnc offset 8152
rowdata[0] @8152
----------
flag@8152: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8153: 0x01
cols@8154: 2
col 0[2] @8155: 2
col 1[2] @8158: bj
----------------------------------------------------------------
知识小结:看到这一堆,大家可能觉得会有点蒙,下面我简单解释一下,数据行的结构。在oracle数据块里,一个完整的数据行公式如下:
数据行=flag( fb 1bytes )+lock (lb 1bytes )+cols(列数量)+ col0 length (第一列长度) + col0 bytes(第一列列值) + col1 length(第二列长度) + col1 bytes(第二列列值)。。
以我们的数据为例,对应关系如下:
数据行= flag + lock + 列数量 + 第一列长度 + 第一列列值 + 第二列长度 + 第二列列值
第二行= 2c + 01 + 02 + 02 + c103 + 02 + 626a
第一行= 2c + 01 + 02 + 02 + c102 + 02 + 7463
看到这里大家可能还会有所怀疑我们的对应关系,是不是对的,下面我们根据列值来验证一下我们的公式是否正确:
验证第一行:列值为1,tc
------------------------------------------------------------------------------------------------------
TC@ENMOEDU>select dump(1,16) from dual;
DUMP(1,16)
-----------------
Typ=2 Len=2: c1,2 ---这里我们看到1对应的16进制编码为c102
TC@ENMOEDU>select dump('tc',16) from dual;
DUMP('TC',16)
-------------------
Typ=96 Len=2: 74,63 ---这里我们看到tc对应的16进制编码为7463
发现和我们的公式完全对应,那我们验证一下第二行:列值为2,bj
------------------------------------------------------------------------------------------------------
TC@ENMOEDU>select dump(2,16) from dual;
DUMP(2,16)
-----------------
Typ=2 Len=2: c1,3
TC@ENMOEDU>select dump('bj',16) from dual;
DUMP('BJ',16)
-------------------
Typ=96 Len=2: 62,6a
两行数据,验证和我们预测完全一致,
----------------------------------------------------------------------------------------------------
下面我们来构造第三行数据,值为(3,enmo)
TC@ENMOEDU>select dump(3,16) from dual;
DUMP(3,16)
-----------------
Typ=2 Len=2: c1,4
TC@ENMOEDU>select dump('enmo',16) from dual;
DUMP('ENMO',16)
-------------------------
Typ=96 Len=4: 65,6e,6d,6f
带入我们公式后结果如下
数据行= flag + lock + 列数量 + 第一列长度 + 第一列列值 + 第二列长度 + 第二列列值
第三行= 2c + 01 + 02 + 02 + c104 + 04 + 656e6d6f
第二行= 2c + 01 + 02 + 02 + c103 + 02 + 626a
第一行= 2c + 01 + 02 + 02 + c102 + 02 + 7463
-------------s数据构造---------------------------------------------------------------------------------
BBED> set offset 8150
OFFSET 8150
BBED> m /x 6d6f
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Offsets: 8150 to 8191 Dba:0x01000449
------------------------------------------------------------------------
6d6f2c01 0202c103 02626a2c 010202c1 02027463 3c010202 c1030262 6a3c0102
02c10202 74630106 44d9
<32 bytes="" per="" line="">
BBED> set offset -2
OFFSET 8148
BBED> m /x 656e
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Offsets: 8148 to 8191 Dba:0x01000449
------------------------------------------------------------------------
656e6d6f 2c010202 c1030262 6a2c0102 02c10202 74633c01 0202c103 02626a3c
010202c1 02027463 010644d9
<32 bytes="" per="" line="">
BBED> dump /v
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Offsets: 8148 to 8191 Dba:0x01000449
-------------------------------------------------------
656e6d6f 2c010202 c1030262 6a2c0102 l enmo,...?.bj,..
02c10202 74633c01 0202c103 02626a3c l .?.tc<...?.bj<
010202c1 02027463 010644d9 l ...?.tc..D
<16 bytes="" per="" line="">
BBED> set offset -2
OFFSET 8146
BBED> m /x 0404
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Offsets: 8146 to 8191 Dba:0x01000449
------------------------------------------------------------------------
0404656e 6d6f2c01 0202c103 02626a2c 010202c1 02027463 3c010202 c1030262
6a3c0102 02c10202 74630106 44d9
<32 bytes="" per="" line="">
BBED> set offset -2
OFFSET 8144
BBED> m /x 02c1
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Offsets: 8144 to 8191 Dba:0x01000449
------------------------------------------------------------------------
02c10404 656e6d6f 2c010202 c1030262 6a2c0102 02c10202 74633c01 0202c103
02626a3c 010202c1 02027463 010644d9
<32 bytes="" per="" line="">
BBED> set offset -2
OFFSET 8142
BBED> m /x 0102
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Offsets: 8142 to 8191 Dba:0x01000449
------------------------------------------------------------------------
010202c1 0404656e 6d6f2c01 0202c103 02626a2c 010202c1 02027463 3c010202
c1030262 6a3c0102 02c10202 74630106 44d9
<32 bytes="" per="" line="">
BBED> dump /v
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Offsets: 8142 to 8191 Dba:0x01000449
-------------------------------------------------------
010202c1 0404656e 6d6f2c01 0202c103 l ...?.enmo,...?
02626a2c 010202c1 02027463 3c010202 l .bj,...?.tc<...
c1030262 6a3c0102 02c10202 74630106 l ?.bj<...?.tc..
44d9 l D
<16 bytes="" per="" line="">
BBED> set offset 8142
OFFSET 8142
BBED> dump
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Offsets: 8142 to 8191 Dba:0x01000449
------------------------------------------------------------------------
010202c1 0402656e 6d6f2c01 0202c103 02626a2c 010202c1 02027463 3c010202
c1030262 6a3c0102 02c10202 74630106 44d9
<32 bytes="" per="" line="">
BBED> set offset -2
OFFSET 8140
BBED> dump
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Offsets: 8140 to 8191 Dba:0x01000449
------------------------------------------------------------------------
74030102 02c10402 656e6d6f 2c010202 c1030262 6a2c0102 02c10202 74633c01
0202c103 02626a3c 010202c1 02027463 010644d9
<32 bytes="" per="" line="">
BBED> m /x 742c
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Offsets: 8140 to 8191 Dba:0x01000449
------------------------------------------------------------------------
742c0102 02c10402 656e6d6f 2c010202 c1030262 6a2c0102 02c10202 74633c01
0202c103 02626a3c 010202c1 02027463 010644d9
<32 bytes="" per="" line="">
BBED> dump /v
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Offsets: 8140 to 8191 Dba:0x01000449
-------------------------------------------------------
742c0102 02c10404 656e6d6f 2c010202 l t,...?.enmo,...
c1030262 6a2c0102 02c10202 74633c01 l ?.bj,...?.tc<.
0202c103 02626a3c 010202c1 02027463 l ..?.bj<...?.tc
010644d9 l ..D
<16 bytes="" per="" line="">
-------------查看一下,我们数据是否构造成功--------------------------
BBED> x /rnc offset 8141
freespace[8027] @8141
---------------
flag@8141: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8142: 0x01
cols@8143: 2
col 0[2] @8144: 3
col 1[4] @8147: enmo
从这里可看到,我们的数据已经在数据块构造完成
---------------------------------------------------------------------------------
我们来查看一下表里是否有了这一条数据。
TC@ENMOEDU>select * from tc;
ID NAME
---------- --------------------
1 tc
2 bj
小结知识:虽然数据块已经有了我们需要的数据,但是oracle现在还不能识别,因为还需要把
这条数据的信息写入到行数据字典里(row directory),行数据字典的结构为kdbr,下面我们查看一下
BBED> p kdbr
sb2 kdbr[0] @110 8069
sb2 kdbr[1] @112 8060
row directory,每条记录占用2个字节,存储实际数据偏移量的相对位置,
对于不同段空间管理类型的表空间,计算公式不同,分别如下
自动段空间管理表空间(ASSM) 公式如下:
实际偏移量地址 = kdbr[n] + 76 + (itls-1) *24
手工段空间管理表空间(MSSM) 公式如下:
实际偏移量地址= kdbr[n] + 68 + (itls-1) *24
同样,理论讲完,我们来验证公式是否正确
-------------------查看itls值-----------------------------------------------------------------------------------------------------------
BBED> map /v
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Dba:0x01000449
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 72 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
sb2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[2], 48 bytes @44 ------从这里看出itls值为2------------------------------
---------------查看表空间段空间管理类型------------------------------------------------------------------------------------
TC@ENMOEDU>select TABLESPACE_NAME,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;
TABLESPACE_NAME EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ ---------- --------- ------
SYSTEM LOCAL SYSTEM MANUAL
SYSAUX LOCAL SYSTEM AUTO
UNDOTBS1 LOCAL SYSTEM MANUAL
TEMP LOCAL UNIFORM MANUAL
USERS LOCAL SYSTEM MANUAL
TC LOCAL SYSTEM AUTO
ENMO LOCAL UNIFORM AUTO
7 rows selected.
这里我们看出users表空间是手工段空间管理类型,那么我们根据公式来计算一下,两行数据的真实偏移量
BBED> p kdbr
sb2 kdbr[0] @110 8069
sb2 kdbr[1] @112 8060
第一行数据真实偏移量=kdbr[0]+68+(2-1)*24=8069+68+24=8161
第二行数据真实偏移量=kdbr[1]+68+(2-1)*24=8060+68+24=8152
上面是根据公式计算的,
下面我们再输出一下数据行位置,验证
BBED> p rowdata
ub1 rowdata[0] @8152 0x2c ************第二行***********************
ub1 rowdata[1] @8153 0x01
ub1 rowdata[2] @8154 0x02
ub1 rowdata[3] @8155 0x02
ub1 rowdata[4] @8156 0xc1
ub1 rowdata[5] @8157 0x03
ub1 rowdata[6] @8158 0x02
ub1 rowdata[7] @8159 0x62
ub1 rowdata[8] @8160 0x6a
ub1 rowdata[9] @8161 0x2c *************第一行************************
ub1 rowdata[10] @8162 0x01
ub1 rowdata[11] @8163 0x02
ub1 rowdata[12] @8164 0x02
ub1 rowdata[13] @8165 0xc1
经过验证,可以看出我们的计算结果和真实位置是一样的。
那么下面我们就根据公式来构造row directory的数据,根据公式结果如下
第三行的真实偏移量=kdbr[2]+68+(2-1)*24=8141 注意(8141为我们构造的第三行起始位置的偏移量,不记得的话,再看一眼数据构造)
从而得出kdbr[2]=8049
kdbr[2]=8049, 10进制转换16进制为1f71
kdbr[1]=8060, 10进制转换16进制为1f7c
kdbr[0]=8069, 10进制转换16进制为1f85
----------------------------------------------------------------------------------------------------------------
BBED> p kdbr
sb2 kdbr[0] @110 8069
sb2 kdbr[1] @112 8060
得出开始偏移量为110
BBED> set offset 110
OFFSET 110
查看实际值
BBED> dump
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Offsets: 110 to 621 Dba:0x01000449
------------------------------------------------------------------------
851f7c1f 79000600 06005f0c 0000d200 c0009d07 0d000080 000055d7 79000300
2000040d 0000920f c000dc08 07000080 000056d7 79000700 1300270c 00001f03
c0004e08 1e000120 0100aad7 79000500 2100300c 00000e02 c000f007 4b000080
000028d7 79000a00 1100480c 00003805 c0004208 42000080 000029d7 79000200
1700480c 00002404 c0003308 0e000080 00002ad7 79000300 1a000a0d 0000920f
c000dc08 05000080 00002cd7 79000001 1700ffff 4000eb03 67146814 00001700
eb037b1d 5f1cf51a a31a511a cf19ce0f dc186817 1d17f70e 83163916 f315a515
5015fb14 a00e6214 c0068e09 4d070000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
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> set offset +4
OFFSET 114
BBED> m /x 711f ------将8049写入
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Offsets: 114 to 625 Dba:0x01000449
------------------------------------------------------------------------
711f0600 06005f0c 0000d200 c0009d07 0d000080 000055d7 79000300 2000040d
0000920f c000dc08 07000080 000056d7 79000700 1300270c 00001f03 c0004e08
1e000120 0100aad7 79000500 2100300c 00000e02 c000f007 4b000080 000028d7
79000a00 1100480c 00003805 c0004208 42000080 000029d7 79000200 1700480c
00002404 c0003308 0e000080 00002ad7 79000300 1a000a0d 0000920f c000dc08
05000080 00002cd7 79000001 1700ffff 4000eb03 67146814 00001700 eb037b1d
5f1cf51a a31a511a cf19ce0f dc186817 1d17f70e 83163916 f315a515 5015fb14
a00e6214 c0068e09 4d070000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
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> set offset 108 -----找到row directory的行记录数位置
OFFSET 108
BBED> dump
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Offsets: 108 to 619 Dba:0x01000449
------------------------------------------------------------------------
0200851f 7c1f711f 06000600 5f0c0000 d200c000 9d070d00 00800000 55d77900
03002000 040d0000 920fc000 dc080700 00800000 56d77900 07001300 270c0000
1f03c000 4e081e00 01200100 aad77900 05002100 300c0000 0e02c000 f0074b00
00800000 28d77900 0a001100 480c0000 3805c000 42084200 00800000 29d77900
02001700 480c0000 2404c000 33080e00 00800000 2ad77900 03001a00 0a0d0000
920fc000 dc080500 00800000 2cd77900 00011700 ffff4000 eb036714 68140000
1700eb03 7b1d5f1c f51aa31a 511acf19 ce0fdc18 68171d17 f70e8316 3916f315
a5155015 fb14a00e 6214c006 8e094d07 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
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 0300 -------将2条记录修改为3条记录-
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Offsets: 108 to 619 Dba:0x01000449
------------------------------------------------------------------------
0300851f 7c1f711f 06000600 5f0c0000 d200c000 9d070d00 00800000 55d77900
03002000 040d0000 920fc000 dc080700 00800000 56d77900 07001300 270c0000
1f03c000 4e081e00 01200100 aad77900 05002100 300c0000 0e02c000 f0074b00
00800000 28d77900 0a001100 480c0000 3805c000 42084200 00800000 29d77900
02001700 480c0000 2404c000 33080e00 00800000 2ad77900 03001a00 0a0d0000
920fc000 dc080500 00800000 2cd77900 00011700 ffff4000 eb036714 68140000
1700eb03 7b1d5f1c f51aa31a 511acf19 ce0fdc18 68171d17 f70e8316 3916f315
a5155015 fb14a00e 6214c006 8e094d07 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
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 kdbh
struct kdbh, 14 bytes @92
ub1 kdbhflag @92 0x00 (NONE)
sb1 kdbhntab @93 1
sb2 kdbhnrow @94 2 -------将2条记录修改为3条记录------------------------
sb2 kdbhfrre @96 -1
sb2 kdbhfsbo @98 22
sb2 kdbhfseo @100 8060
sb2 kdbhavsp @102 8056
sb2 kdbhtosp @104 8056
------检查row directory----------------------------------------------------------------------------------------------
BBED> p kdbr
sb2 kdbr[0] @110 8069
sb2 kdbr[1] @112 8060
sb2 kdbr[2] @114 8049
-------------------------------------------------------------------------------------------------------------------
BBED> p kdbh
struct kdbh, 14 bytes @92
ub1 kdbhflag @92 0x00 (NONE)
sb1 kdbhntab @93 1
sb2 kdbhnrow @94 3
sb2 kdbhfrre @96 -1
sb2 kdbhfsbo @98 24
sb2 kdbhfseo @100 8049
sb2 kdbhavsp @102 8025
sb2 kdbhtosp @104 8025
BBED> set offset 98
OFFSET 98
BBED> dump
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Offsets: 98 to 609 Dba:0x01000449
------------------------------------------------------------------------
16007c1f 781f781f 00000300 851f7c1f 711f0600 06005f0c 0000d200 c0009d07
0d000080 000055d7 79000300 2000040d 0000920f c000dc08 07000080 000056d7
79000700 1300270c 00001f03 c0004e08 1e000120 0100aad7 79000500 2100300c
00000e02 c000f007 4b000080 000028d7 79000a00 1100480c 00003805 c0004208
42000080 000029d7 79000200 1700480c 00002404 c0003308 0e000080 00002ad7
79000300 1a000a0d 0000920f c000dc08 05000080 00002cd7 79000001 1700ffff
4000eb03 67146814 00001700 eb037b1d 5f1cf51a a31a511a cf19ce0f dc186817
1d17f70e 83163916 f315a515 5015fb14 a00e6214 c0068e09 4d070000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
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 1800
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Offsets: 98 to 609 Dba:0x01000449
------------------------------------------------------------------------
18007c1f 781f781f 00000300 851f7c1f 711f0600 06005f0c 0000d200 c0009d07
0d000080 000055d7 79000300 2000040d 0000920f c000dc08 07000080 000056d7
79000700 1300270c 00001f03 c0004e08 1e000120 0100aad7 79000500 2100300c
00000e02 c000f007 4b000080 000028d7 79000a00 1100480c 00003805 c0004208
42000080 000029d7 79000200 1700480c 00002404 c0003308 0e000080 00002ad7
79000300 1a000a0d 0000920f c000dc08 05000080 00002cd7 79000001 1700ffff
4000eb03 67146814 00001700 eb037b1d 5f1cf51a a31a511a cf19ce0f dc186817
1d17f70e 83163916 f315a515 5015fb14 a00e6214 c0068e09 4d070000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
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="">
-------------------------------------------------------------------------------
小结:由于我们新加了一条数据,所以相应的下面四个值都要发生变化
sb2 kdbhfsbo @98 --块中空闲地址的起始偏移量
sb2 kdbhfseo @100 --块中空闲地址的末尾偏移量
sb2 kdbhavsp @102 --平均可利用空间
sb2 kdbhtosp @104 --总的可利用空间
BBED> p kdbh
struct kdbh, 14 bytes @92
ub1 kdbhflag @92 0x00 (NONE)
sb1 kdbhntab @93 1
sb2 kdbhnrow @94 3
sb2 kdbhfrre @96 -1
sb2 kdbhfsbo @98 22
sb2 kdbhfseo @100 8060
sb2 kdbhavsp @102 8056
sb2 kdbhtosp @104 8056
新加数据后,起始位置值+2(不然,verify会报错),kdbhfseo为8049,平均可利用空间和总可以用空间一致位8049-24=8025
即调整为后,值如下:
sb2 kdbhfsbo @98 24
sb2 kdbhfseo @100 8049
sb2 kdbhavsp @102 8025
sb2 kdbhtosp @104 8025
----------------------------------------------------------------------------------------------------------------
下面开始调整
BBED> set offset 98
OFFSET 98
BBED> dump
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Offsets: 98 to 609 Dba:0x01000449
------------------------------------------------------------------------
1600711f 5b1f5b1f 00000300 851f7c1f 711f0600 06005f0c 0000d200 c0009d07
0d000080 000055d7 79000300 2000040d 0000920f c000dc08 07000080 000056d7
79000700 1300270c 00001f03 c0004e08 1e000120 0100aad7 79000500 2100300c
00000e02 c000f007 4b000080 000028d7 79000a00 1100480c 00003805 c0004208
42000080 000029d7 79000200 1700480c 00002404 c0003308 0e000080 00002ad7
79000300 1a000a0d 0000920f c000dc08 05000080 00002cd7 79000001 1700ffff
4000eb03 67146814 00001700 eb037b1d 5f1cf51a a31a511a cf19ce0f dc186817
1d17f70e 83163916 f315a515 5015fb14 a00e6214 c0068e09 4d070000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
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 1800
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Offsets: 98 to 609 Dba:0x01000449
------------------------------------------------------------------------
1800711f 5b1f5b1f 00000300 851f7c1f 711f0600 06005f0c 0000d200 c0009d07
0d000080 000055d7 79000300 2000040d 0000920f c000dc08 07000080 000056d7
79000700 1300270c 00001f03 c0004e08 1e000120 0100aad7 79000500 2100300c
00000e02 c000f007 4b000080 000028d7 79000a00 1100480c 00003805 c0004208
42000080 000029d7 79000200 1700480c 00002404 c0003308 0e000080 00002ad7
79000300 1a000a0d 0000920f c000dc08 05000080 00002cd7 79000001 1700ffff
4000eb03 67146814 00001700 eb037b1d 5f1cf51a a31a511a cf19ce0f dc186817
1d17f70e 83163916 f315a515 5015fb14 a00e6214 c0068e09 4d070000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
BBED> set offset 100
OFFSET 100
BBED> m /x 711f
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Offsets: 100 to 611 Dba:0x01000449
------------------------------------------------------------------------
711f781f 781f0000 0300851f 7c1f711f 06000600 5f0c0000 d200c000 9d070d00
00800000 55d77900 03002000 040d0000 920fc000 dc080700 00800000 56d77900
07001300 270c0000 1f03c000 4e081e00 01200100 aad77900 05002100 300c0000
0e02c000 f0074b00 00800000 28d77900 0a001100 480c0000 3805c000 42084200
00800000 29d77900 02001700 480c0000 2404c000 33080e00 00800000 2ad77900
03001a00 0a0d0000 920fc000 dc080500 00800000 2cd77900 00011700 ffff4000
eb036714 68140000 1700eb03 7b1d5f1c f51aa31a 511acf19 ce0fdc18 68171d17
f70e8316 3916f315 a5155015 fb14a00e 6214c006 8e094d07 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
BBED> set offset +2
OFFSET 102
BBED> m /x 591f
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Offsets: 102 to 613 Dba:0x01000449
------------------------------------------------------------------------
591f781f 00000300 851f7c1f 711f0600 06005f0c 0000d200 c0009d07 0d000080
000055d7 79000300 2000040d 0000920f c000dc08 07000080 000056d7 79000700
1300270c 00001f03 c0004e08 1e000120 0100aad7 79000500 2100300c 00000e02
c000f007 4b000080 000028d7 79000a00 1100480c 00003805 c0004208 42000080
000029d7 79000200 1700480c 00002404 c0003308 0e000080 00002ad7 79000300
1a000a0d 0000920f c000dc08 05000080 00002cd7 79000001 1700ffff 4000eb03
67146814 00001700 eb037b1d 5f1cf51a a31a511a cf19ce0f dc186817 1d17f70e
83163916 f315a515 5015fb14 a00e6214 c0068e09 4d070000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
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> set offset +2
OFFSET 104
BBED> m /x 591f
File: /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf (4)
Block: 1097 Offsets: 104 to 615 Dba:0x01000449
------------------------------------------------------------------------
591f0000 0300851f 7c1f711f 06000600 5f0c0000 d200c000 9d070d00 00800000
55d77900 03002000 040d0000 920fc000 dc080700 00800000 56d77900 07001300
270c0000 1f03c000 4e081e00 01200100 aad77900 05002100 300c0000 0e02c000
f0074b00 00800000 28d77900 0a001100 480c0000 3805c000 42084200 00800000
29d77900 02001700 480c0000 2404c000 33080e00 00800000 2ad77900 03001a00
0a0d0000 920fc000 dc080500 00800000 2cd77900 00011700 ffff4000 eb036714
68140000 1700eb03 7b1d5f1c f51aa31a 511acf19 ce0fdc18 68171d17 f70e8316
3916f315 a5155015 fb14a00e 6214c006 8e094d07 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
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 1097:
current = 0x23e6, required = 0x23e6
BBED> verify -------验证有没有逻辑错误
DBVERIFY - Verification starting
FILE = /ORA11G/app/oracle/oradata/ENMOEDU/users01.dbf
BLOCK = 1097
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
essage 531 not found; product=RDBMS; facility=BBED
--------------------------------------------------------------------------------------
没有问题,我们检查数据库表时候,已经有了新数据
SYS@ENMOEDU>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ENMOEDU>startup
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2220200 bytes
Variable Size 3305115480 bytes
Database Buffers 956301312 bytes
Redo Buffers 12144640 bytes
Database mounted.
Database opened.
SYS@ENMOEDU>conn tc/tc
Connected.
TC@ENMOEDU>select * from tc;
ID NAME
---------- --------------------
1 tc
2 bj
3 enmo
至此我们构造的新数据,已经完成。欢迎大家交流,和提出宝贵意见。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26366838/viewspace-2072427/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26366838/viewspace-2072427/