使用BBED理解和修改Oracle数据块
1.生成bbed list file文件:
SQL> select file#||' '||name||' '||bytes from v$datafile;
$ vim dbfile.txt
1 /u01/app/oradata/sydb/system01.dbf 754974720
2 /u01/app/oradata/sydb/sysaux01.dbf 587202560
3 /u01/app/oradata/sydb/undotbs01.dbf 429916160
4 /u01/app/oradata/sydb/users01.dbf 13107200
5 /disk2/oradata/sydb/tbs01.dbf 1816133632
6 /disk2/oradata/sydb/tbs02.dbf 119537664
7 /disk2/oradata/sydb/tbs03.dbf 20971520
8 /tmp/tbs_tmp.dbf 10485760
2.生成bbed参数文件:
$ vim bbed.par
mode=edit
listfile= dbfile.txt
blocksize=8192
3.创建测试表和数据:
create table tt1 (id number,name varchar2(30)) tablespace tbs03;
insert into tt1 values(1,'AAAA');
insert into tt1 values(2,'BBBB');
commit;
4.查询表数据行所在的数据块号:
select
dbms_rowid.rowid_block_number(rowid) blocknum,
dbms_rowid.ROWID_RELATIVE_FNO(rowid) fileNo,
id,name
from tt1;
BLOCKNUM FILENO ID NAME
---------- ---------- ---------- ------------------------------
135 7 1 AAAA
135 7 2 BBBB
5.使用bbed:
$ bbed parfile=bbed.par
Password: ==>blockedit is default password of bbed
BBED: Release 2.0.0.0.0 - Limited Production on Tue Mar 31 20:39:29 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set filename '/disk2/oradata/sydb/tbs03.dbf' block 135
FILENAME /disk2/oradata/sydb/tbs03.dbf
BLOCK# 135
BBED> map
File: /disk2/oradata/sydb/tbs03.dbf (0)
Block: 135 Dba:0x00000000
------------------------------------------------------------
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[2] @118 ==>行目录层
ub1 freespace[8044] @122 ==>空闲空间
ub1 rowdata[22] @8166 ==>实际行数据
ub4 tailchk @8188 ==>校验码
使用print打印数据块结构信息PRINT[/x|d|u|o|c] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
BBED> print kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06 ==>块类型(参考下图:Block Type)
ub1 frmt_kcbh @1 0xa2 ==>块格式
ub1 spare1_kcbh @2 0x00 ==>保留空间
ub1 spare2_kcbh @3 0x00 ==>保留空间
ub4 rdba_kcbh @4 0x01c00087 ==>块地址
ub4 bas_kcbh @8 0x002fa91a ==>base SCN
ub2 wrp_kcbh @12 0x0000 ==>wrap SCN
ub1 seq_kcbh @14 0x01 ==>SCN 序列
ub1 flg_kcbh @15 0x06 (KCBHFDLC, KCBHFCKV) ==> 块属性(0x01 新块,0x02我的理解是延迟修改的块,0x04检查保存的块,0x08 Temporary block)
ub2 chkval_kcbh @16 0xe534 ==>校验码,它的值取决于参数(db_block_checksum)的设置值
ub2 spare3_kcbh @18 0x0000 ==>保留空间
BlockTypeNumber | BlockType |
01 | Undo segment header |
02 | Undo data block |
03 | Save undo header |
04 | Save undo data block |
05 | Data segment header |
06 | Trans data,KTB manager data block (with ITL) |
07 | Temp table data block (no ITL) |
08 | Sort key |
09 | Sort run |
10 | Segment free list block |
11 | Data file header |
(图:Block Type)
打印具体结构值:
BBED> print type_kcbh
ub1 type_kcbh @0 0x06
BBED> print frmt_kcbh
ub1 frmt_kcbh @1 0xa2
事务层:
BBED> print ktbbh
struct ktbbh, 72 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA) ==>块类型,01 Data,02 Index
union ktbbhsid, 4 bytes @24 ==>对象号(DBA_OBJECTS.OBJECT_ID)/段号
ub4 ktbbhsg1 @24 0x0001a2c4 ==>对象段号
ub4 ktbbhod1 @24 0x0001a2c4 ==>对象号
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0x002fbaab ==>SCN Base
ub2 kscnwrp @32 0x0000 ==>SCN Wrap
sb2 ktbbhict @36 2 ==>事务槽号(ITL number)
ub1 ktbbhflg @38 0x32 (NONE) ==>on the free list
ub1 ktbbhfsl @39 0x00 ==>ITL TX free list slot
ub4 ktbbhfnx @40 0x01c00080 ==>下一个空闲块的地址 (next free block dba)
struct ktbbhitl[0], 24 bytes @44 ==>事务槽1
struct ktbitxid, 8 bytes @44 ==>xid V$TRANSACTION.XID
ub2 kxidusn @44 0x0005 ==>usn V$TRANSACTION.XIDUSN
ub2 kxidslt @46 0x001f ==>slot V$TRANSACTION.XIDSLOT
ub4 kxidsqn @48 0x000009cb ==>sqn V$TRANSACTION.UBASQN
struct ktbituba, 8 bytes @52 ==>uba
ub4 kubadba @52 0x00c002d1 ==>dba
ub2 kubaseq @56 0x0375 ==>seq
ub1 kubarec @58 0x11 ==>V$TRANSACTION.UBAREC
ub2 ktbitflg @60 0xa000 (KTBFUPB, KTBFCOM)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x002fb364
struct ktbbhitl[1], 24 bytes @68 ==>事务槽2
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0000
ub2 kxidslt @70 0x0000
ub4 kxidsqn @72 0x00000000
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00000000
ub2 kubaseq @80 0x0000
ub1 kubarec @82 0x00
ub2 ktbitflg @84 0x0000 (NONE)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x00000000
数据层:
BBED> print kdbh
struct kdbh, 14 bytes @100
ub1 kdbhflag @100 0x00 (NONE) ==>标记(N=ptcfree hit(clusters),F=do not put on free list,K=flushable cluster keys)
sb1 kdbhntab @101 1 ==>表个数
sb2 kdbhnrow @102 2 ==>块包含的行数
sb2 kdbhfrre @104 -1 ==>是否在空闲列表 -1不在空闲列表
sb2 kdbhfsbo @106 22 ==>空闲空间的开始补偿
sb2 kdbhfseo @108 8066 ==>空闲空间的结束补偿
sb2 kdbhavsp @110 8044 ==>块的平均空间
sb2 kdbhtosp @112 8044 ==>块可以使用的总空间
校验码:
BBED> print tailchk
ub4 tailchk @8188 0xbaab0601 --尾部校验码
tailchk=(低位2个字节)bas_kcbh(0x002fbaab)+(1位字节)type_kcbh(0x06)+(1位字节)seq_kcbh(0x01) ==>值在块头记录)
查找:
数据表存储的值:
SQL>column name format a8
SQL>column dump format a40
SQL>select name,dump(name,16)dump from tt1;
NAME DUMP
-------- ----------------------------------------
AAAA Typ=1 Len=4: 41,41,41,41
BBBB Typ=1 Len=4: 42,42,42,42
FIND[/x|d|u|o|c] numeric/character string [ TOP | CURR ]
Find Switch | DataType |
x | Haxadecimal |
d | Decimal |
u | Unsigned decimal |
o | Octal |
c | Character |
/c 表示通过字符查找:
BBED> find /c AAAA
File: /disk2/oradata/sydb/tbs03.dbf (0)
Block: 135 Offsets: 8184 to 8191 Dba:0x00000000
------------------------------------------------------------------------
41414141 01061aa9
datafile为7,block 135,offset 为8184就是存储AAAA的位置
BBED> dump /v dba 7,135 offset 8184
File: /disk2/oradata/sydb/tbs03.dbf (7)
Block: 135 Offsets: 8184 to 8191 Dba:0x01c00087
-------------------------------------------------------
41414141 0106abba l AAAA..
/x 表示使用十六进制查找:
BBED> find /x 41414141
File: /disk2/oradata/sydb/tbs03.dbf (0)
Block: 135 Offsets: 8184 to 8191 Dba:0x00000000
------------------------------------------------------------------------
41414141 01061aa9
修改值(mode 必须为edit):
MODIFY[/x|d|u|o|c] numeric/character string
通过已知的文件号7,block 135,offset把AAAA的值修改为ABCE
BBED> modify /c ABCE file 7 block 135 offset 8184
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File: /disk2/oradata/sydb/tbs03.dbf (7)
Block: 135 Offsets: 8184 to 8191 Dba:0x01c00087
------------------------------------------------------------------------
41424345 0106abba
提交修改:
SUM [ DBA | FILE | FILENAME | BLOCK ] [ APPLY ]
BBED> sum apply
Check value for File 7, Block 135:
current = 0xbe37, required = 0xbe37
验证数据块:
VERIFY [ DBA | FILE | FILENAME | BLOCK ]
BBED> verify
DBVERIFY - Verification starting
FILE = /disk2/oradata/sydb/tbs03.dbf
BLOCK = 135
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
验证数据的修改,注:应该在数据库关闭时使用bbed修改数据块,因为当数据库运行时,数据块可能被修改覆盖,这里只是做为测试,并没有其它任何dml或其它操作,当bbed修改数据块并物理保存后,在内存中的数据块并没有刷新,所以此时查询到的数据依然是旧数据:
SQL> column dump format a60
SQL> select name,dump(name,16)dump from tt1;
NAME DUMP
------------------------------ ------------------------------------------------------------
AAAA Typ=1 Len=4: 41,41,41,41
BBBB Typ=1 Len=4: 42,42,42,42
清理buffer_cache和share_pool 触发物理读,数据正确:
SQL>alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.93
SQL>alter system flush shared_pool;
System altered.
SQL> select name,dump(name,16)dump from tt1;
NAME DUMP
------------------------------ ------------------------------------------------------------
ABCE Typ=1 Len=4: 41,42,43,45
BBBB Typ=1 Len=4: 42,42,42,42
撤销操作:
REVERT [ DBA | FILE | FILENAME | BLOCK ]
撤销当前会话所有操作:
BBED> revert
All changes made in this session will be rolled back. Proceed? (Y/N) y
Reverted file '/disk2/oradata/sydb/tbs03.dbf', block 135
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
选择文件和数据块撤销:
BBED> revert dba 7,135
撤销当前会话上一个操作:
UNDO
当前值:
SQL> select name,dump(name,16)dump from tt1;
NAME DUMP
------------------------------ ----------------------------------------
DFCA Typ=1 Len=4: 44,46,43,41
BBBB Typ=1 Len=4: 42,42,42,42
BBED> modify /c ibmc dba 7,135 offset 8184
File: /disk2/oradata/sydb/tbs03.dbf (7)
Block: 135 Offsets: 8184 to 8191 Dba:0x01c00087
------------------------------------------------------------------------
69626d63 0106abba
BBED> sum apply
Check value for File 7, Block 135:
current = 0xa93a, required = 0xa93a
确认值:
SQL> alter system flush shared_pool;
SQL> alter system flush buffer_cache;
SQL> select name,dump(name,16)dump from tt1;
NAME DUMP
------------------------------ ----------------------------------------
ibmc Typ=1 Len=4: 69,62,6d,63
BBBB Typ=1 Len=4: 42,42,42,42
UNDO操作:
BBED> undo
BBED> modify /x 44464341 filename '/disk2/oradata/sydb/tbs03.dbf' block 135. offset 8184.
File: /disk2/oradata/sydb/tbs03.dbf (0)
Block: 135 Offsets: 8184 to 8191 Dba:0x00000000
------------------------------------------------------------------------
44464341 0106abba
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 135:
current = 0xaf39, required = 0xaf39
--The end