使用BBED帮助理解Oracle数据块结构

使用BBED帮助理解Oracle数据块结构

BBED是Oracle提供的块编辑器，借助BBED，可以帮助我们更好的理解Oracle的Block的结构。当然，反过来说，也只有更加理解块的结构，才能更好的利用BBED完成某些特殊情况下的灾难恢复。

Oracle Data Block的结构简图如下，其中从Data header到Row Data部分合称Data Layer:

 

 

---------------------
- Cache Layer       -   20bytes
---------------------
- Transaction Layer -  48bytes
---------------------
- Data Header       -  14bytes
---------------------
- Table Directory   -  4bytes
---------------------
- Row Directory     -  2bytes
---------------------
- Free Space        -
---------------------
- Row Data          -
---------------------
- Tailchk           -  4bytes
---------------------

 

 

--===================以上结构 是重点--====================

 

通过bbed的map命令，可以看到数据块内部的一些数据结构名：

BBED> map
 File: /u01/oracle/oradata/dbmon/system.dbf (1)
 Block: 31729                                 Dba:0x00407bf1
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 72 bytes                     @20
 struct kdbh, 14 bytes                      @92
 struct kdbt[1], 4 bytes                    @106
 sb2 kdbr[336]                              @110
 ub1 freespace[821]                         @782
 ub1 rowdata[6585]                          @1603
 ub4 tailchk                                @8188

Cache Layer：Block的第一部分，长度为20字节，内部数据结构名为kcbh，包括
type_kcbh:块类型(table/index,rollback segment,temporary segment等)
frmt_kcbh:块格式(v6,v7,v8)
rdba_kcbh:块地址DBA
bas_kcbh/wrp_kcbh:SCN
seq_kcbh:块的序列号
flg_kcbh:块的标志

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        0x00407bf1
   ub4 bas_kcbh                             @8        0xd6449de8
   ub2 wrp_kcbh                             @12       0x0595
   ub1 seq_kcbh                             @14       0x03
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x9130
   ub2 spare3_kcbh                          @18       0x0000

--=========================================== 

Transaction Layer：内部结构名kcbbh。分成两部分，第一部分为固定长度，长度为24字节，包含事务相关的一些基本信息。第二部分为可变长度，包含itl，长度根据itl条目的个数变化，每个itl长度为24字节，内部结构名ktbbhitl。

BBED> p ktbbh
struct ktbbh, 72 bytes                      @20
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24
      ub4 ktbbhsg1                          @24       0x000050fc
      ub4 ktbbhod1                          @24       0x000050fc
   struct ktbbhcsc, 8 bytes                 @28
      ub4 kscnbas                           @28       0xd6449de7
      ub2 kscnwrp                           @32       0x0595
   b2 ktbbhict                              @36       2
   ub1 ktbbhflg                             @38       0x02 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x00000000
   struct ktbbhitl[0], 24 bytes             @44
      struct ktbitxid, 8 bytes              @44
         ub2 kxidusn                        @44       0x0008
         ub2 kxidslt                        @46       0x000c
         ub4 kxidsqn                        @48       0x0000e991
      struct ktbituba, 8 bytes              @52
         ub4 kubadba                        @52       0x0080222e
         ub2 kubaseq                        @56       0x03a4
         ub1 kubarec                        @58       0x22
      ub2 ktbitflg                          @60       0x8000 (KTBFCOM)
      union _ktbitun, 2 bytes               @62
         b2 _ktbitfsc                       @62       1429
         ub2 _ktbitwrp                      @62       0x0595
      ub4 ktbitbas                          @64       0xd6449de6
   struct ktbbhitl[1], 24 bytes             @68
      struct ktbitxid, 8 bytes              @68
         ub2 kxidusn                        @68       0x0008
         ub2 kxidslt                        @70       0x0015
         ub4 kxidsqn                        @72       0x0000e992
      struct ktbituba, 8 bytes              @76
         ub4 kubadba                        @76       0x0080222e
         ub2 kubaseq                        @80       0x03a4
         ub1 kubarec                        @82       0x23
      ub2 ktbitflg                          @84       0x0001 (NONE)
      union _ktbitun, 2 bytes               @86
         b2 _ktbitfsc                       @86       0
         ub2 _ktbitwrp                      @86       0x0000
      ub4 ktbitbas                          @88       0x00000000

Data Layer：包括Data Header，Table Directory，Row Directory，Free Space和Row Data。其中
Data Header：长度14字节，内部数据结构名kdbh

BBED> p kdbh
struct kdbh, 14 bytes                       @92
   ub1 kdbhflag                             @92       0x00 (NONE)
   b1 kdbhntab                              @93       1
   b2 kdbhnrow                              @94       336
   sb2 kdbhfrre                             @96      -1
   sb2 kdbhfsbo                             @98       690
   sb2 kdbhfseo                             @100      1511
   b2 kdbhavsp                              @102      821
   b2 kdbhtosp                              @104      821

Table Directory: 一般table只有一个条目，cluster则有一个或多个条目。每个条目长4字节，内部数据结构名kdbt。

BBED> p kdbt
struct kdbt[0], 4 bytes                     @106
   b2 kdbtoffs                              @106      0
   b2 kdbtnrow                              @108      336

Row Directory：数目由块中数据的行数决定，每个条目长2字节，内部数据结构名kdbr

BBED> p kdbr
sb2 kdbr[0]                                 @110      7998
sb2 kdbr[1]                                 @112      8017
...
sb2 kdbr[335]                               @780      1511

Free Space：表示数据块中可用空间，内部数据结构名freespace

Row Data：表示实际的数据，内部数据结构名rowdata

Tailchk:保存在块结尾用于校验的数据，长度4个字节，内部结构名tailchk。

BBED>p tailchk
ub4 tailchk                                 @8188     0x9de80603

 

注意到tailchk=bas_kcbh低2字节(9de8)+type_kcbh(06)+seq_kcbh(03)

 

--=========================================

 

[使用]
修改数据块内容的内容的一般流程：
先找一张表中的一个rowid，如scott 的dept 表
SQL> run
1* select rowid,d.* from scott.dept d
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAMfKAAEAAAAAQAAA 10 ACCOUNTING NEW YORK
AAAMfKAAEAAAAAQAAB 20 RESEARCH DALLAS
AAAMfKAAEAAAAAQAAC 30 SALES CHICAGO
AAAMfKAAEAAAAAQAAD 40 OPERATIONS BOSTON
通过rowid 得到文件号和块号
SQL> select DBMS_ROWID.ROWID_RELATIVE_FNO('AAAMfKAAEAAAAAQAAA') file_id
from dual;
FILE_ID
----------
4
SQL> select DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAMfKAAEAAAAAQAAA')
block_id from dual;
BLOCK_ID
----------
16
选择dept 表上的16号block
BBED> set dba 4,16
DBA 0x01000010 (16777232 4,16)
使用map 查看block 结构
BBED> map
File: /u01/app/oracle/oradata/orcl/users01.dbf (4)
Block: 16 Dba:0x01000010
------------------------------------------------------------
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[4] @118
ub1 freespace[7970] @126
ub1 rowdata[92] @8096
ub4 tailchk @8188
设置偏移量
BBED> set offset 8096
OFFSET 8096
查看block 内容
BBED> d /v
File: /u01/app/oracle/oradata/orcl/users01.dbf (4)
Block: 16 Offsets: 8096 to 8191 Dba:0x01000010
-------------------------------------------------------
2c010302 c1290a4f 50455241 54494f4e l ,...?.OPERATION
5306424f 53544f4e 2c010302 c11f0553 l S.BOSTON,...?.S
414c4553 07434849 4341474f 2c010302 l ALES.CHICAGO,...
c1150852 45534541 52434806 44414c4c l ?.RESEARCH.DALL
41532c01 0302c10b 0a414343 4f554e54 l AS,...?.ACCOUNT
494e4708 4e455720 594f524b 0606dbbf l ING.NEW YORK..劭
<16 bytes per line>
查找NEW YORK，并将其修改为ZHEJIANG
BBED> f /c NEW TOP
File: /u01/app/oracle/oradata/orcl/users01.dbf (4)
Block: 16 Offsets: 8180 to 8191 Dba:0x01000010
------------------------------------------------------------------------
4e455720 594f524b 0606dbbf
<32 bytes per line>
BBED> d /v
File: /u01/app/oracle/oradata/orcl/users01.dbf (4)
Block: 16 Offsets: 8180 to 8191 Dba:0x01000010
-------------------------------------------------------
4e455720 594f524b 0606dbbf l NEW YORK..劭
<16 bytes per line>
BBED> m /c ZHEJIANG dba 4,16 offset 8180
File: /u01/app/oracle/oradata/orcl/users01.dbf (4)
Block: 16 Offsets: 8180 to 8191 Dba:0x01000010
------------------------------------------------------------------------
5a48454a 49414e47 0606dbbf
<32 bytes per line>
BBED> d /v
File: /u01/app/oracle/oradata/orcl/users01.dbf (4)
Block: 16 Offsets: 8180 to 8191 Dba:0x01000010
-------------------------------------------------------
5a48454a 49414e47 0606dbbf l ZHEJIANG..劭
<16 bytes per line>
在修改数据块之后，重新计算并更新checksum
BBED> sum dba 4,16
Check value for File 4, Block 16:
current = 0xf309, required = 0x9603
BBED> sum dba 4,16 apply
Check value for File 4, Block 16:
current = 0x9603, required = 0x9603
此时数据块已被修改，查询业务表验证
SQL> run
1* ALTER SYSTEM FLUSH BUFFER_CACHE
System altered.
SQL> select rowid,d.* from scott.dept d;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAMfKAAEAAAAAQAAA 10 ACCOUNTING ZHEJIANG
AAAMfKAAEAAAAAQAAB 20 RESEARCH DALLAS
AAAMfKAAEAAAAAQAAC 30 SALES CHICAGO
AAAMfKAAEAAAAAQAAD 40 OPERATIONS BOSTON

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/607244/viewspace-751087/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/607244/viewspace-751087/