A-A+
BBED的常见使用命令及修改字符
参考文档：disassembling the oracle data block,writter by graham thornton 2005
注意：一般建议在数据库关闭的情况下使用bbed。防止正常的checkpoint和bbed的sum apply冲突导致数据块损坏。
操作系统信息
[oracle@rhl6 lib]$ uname -a Linux rhl6.0x64 2.6.32-71.el6.x86_64 #1 SMP Wed Sep 1 01:33:01 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
数据库版本
SQL> set line 400
SQL> select * from gv$version;
INST_ID BANNER
---------- --------------------------------------------------------------------------------
1 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
1 PL/SQL Release 11.2.0.3.0 - Production
1 CORE 11.2.0.3.0 Production
1 TNS for Linux: Version 11.2.0.3.0 - Production
1 NLSRTL Version 11.2.0.3.0 - Production
测试数据
SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
Session altered.
SQL> select * from yallonking;
ID NAME MY_DATE
---------- -------------------- -------------------
1 yallonking 2013/07/19 09:36:25
2 oraking 2013/07/19 09:45:44
创建函数get_rowid
注：该函数作用从rowid得到块对象的一些信息。
SQL> create or replace function get_rowid(l_rowid in varchar2) return varchar2 is 2 ls_my_rowid varchar2(200); 3 rowid_type number; 4 object_number number; 5 relative_fno number; 6 block_number number; 7 row_number number; 8 begin 9 dbms_rowid.rowid_info(l_rowid, 10 rowid_type, 11 object_number, 12 relative_fno, 13 block_number, 14 row_number); 15 ls_my_rowid := 'Row_id type is :' || to_char(rowid_type) || chr(10) || 16 'Object# is :' || to_char(object_number) || chr(10) || 17 'Relative_fno is :' || to_char(relative_fno) || chr(10) || 18 'Block number is :' || to_char(block_number) || chr(10) || 19 'Row number is :' || to_char(row_number); 20 return ls_my_rowid; 21 end; 22 / Function created.
（一）修改字符串
此处将id=1的yallonking为yallonora，注意：字符长度不一致
首先需要获取待修改列的块信息
SQL> select rowid,t.* from yallonking t where t.id=1;
ROWID ID NAME MY_DATE
------------------ ---------- -------------------- -------------------
AAARciAAFAAAACHAAA 1 yallonking 2013/07/19 09:36:25
SQL> select get_rowid('AAARciAAFAAAACHAAA') from dual;
GET_ROWID('AAARCIAAFAAAACHAAA')
--------------------------------------------------------------------------------
Row_id type is :1
Object# is :71458
Relative_fno is :5
Block number is :135
Row number is :0
配置bbed
SQL> set line 400 SQL> col FILE_NAME for a50 SQL> select a.OWNER,a.TABLE_NAME,b.file_name from dba_tables a,dba_data_files b 2 where a.TABLESPACE_NAME=b.TABLESPACE_NAME 3 and a.TABLE_NAME='YALLONKING'; OWNER TABLE_NAME FILE_NAME ------------------------------ ------------------------------ -------------------------------------------------- YALLONKING YALLONKING /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf
以下为批量这是bbed待修改的数据文件
SQL> select file#||' '||name||' '||bytes from v$datafile where name like '%yallonking%'; FILE#||''||NAME||''||BYTES ---------------------------------------------------------------------- 5 /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf 10485760 [oracle@rhl6 ~]$ pwd /home/oracle [oracle@rhl6 ~]$ cat bbedfile.lst 5 /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf 10485760
以下为设置bbed的参数文件
[oracle@rhl6 ~]$ cat bbed.par blocksize=8192 listfile=/home/oracle/bbedfile.lst mode=edit
查看BBED帮助
BBED> help all
SET DBA [ dba | file#, block# ]
SET FILENAME 'filename'
SET FILE file#
SET BLOCK [+/-]block#
SET OFFSET [ [+/-]byte offset | symbol | *symbol ]
SET BLOCKSIZE bytes
SET LIST[FILE] 'filename'
SET WIDTH character_count
SET COUNT bytes_to_display
SET IBASE [ HEX | OCT | DEC ]
SET OBASE [ HEX | OCT | DEC ]
SET MODE [ BROWSE | EDIT ]
SET SPOOL [ Y | N ]
SHOW [ <SET parameter> | ALL ]
INFO
MAP[/v] [ DBA | FILENAME | FILE | BLOCK ]
DUMP[/v] [ DBA | FILENAME | FILE | BLOCK | OFFSET | COUNT ]
PRINT[/x|d|u|o|c] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
EXAMINE[/Nuf] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
</Nuf>:
N - a number which specifies a repeat count.
u - a letter which specifies a unit size:
b - b1, ub1 (byte)
h - b2, ub2 (half-word)
w - b4, ub4(word)
r - Oracle table/index row
f - a letter which specifies a display format:
x - hexadecimal
d - decimal
u - unsigned decimal
o - octal
c - character (native)
n - Oracle number
t - Oracle date
i - Oracle rowid
FIND[/x|d|u|o|c] numeric/character string [ TOP | CURR ]
COPY [ DBA | FILE | FILENAME | BLOCK ] TO [ DBA | FILE | FILENAME | BLOCK ]
MODIFY[/x|d|u|o|c] numeric/character string
[ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
ASSIGN[/x|d|u|o] <target spec>=<source spec>
<target spec> : [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
<source spec> : [ value | <target spec options> ]
SUM [ DBA | FILE | FILENAME | BLOCK ] [ APPLY ]
PUSH [ DBA | FILE | FILENAME | BLOCK | OFFSET ]
POP [ALL]
REVERT [ DBA | FILE | FILENAME | BLOCK ]
UNDO
HELP [ <bbed command> | ALL ]
VERIFY [ DBA | FILE | FILENAME | BLOCK ]
CORRUPT [ DBA | FILE | FILENAME | BLOCK ]
定位记录的块位置
BBED> set filename '/u01/app/oracle/oradata/ora11gr2/yallonking01.dbf'
FILENAME /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf
注：该命令是定位指定有效的oracle数据文件
BBED> set dba 5,135 BBED-00312: no LISTFILE specified
注：设定数据块位置，第一个参数文件文件号，第二参数为块位置
BBED> set listfile '/home/oracle/bbedfile.lst'
LISTFILE /home/oracle/bbedfile.lst
注：设定一个文件列表的参数文件
BBED> set dba 5,135
DBA 0x01400087 (20971655 5,135)
BBED> set blocksize 8192
BLOCKSIZE 8192
注：指定数据库标准块大小
BBED> set MODE edit
MODE Edit
注：设定BBED模式为修改
BBED> verify dba 5,135 DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/ora11gr2/yallonking01.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> show all
FILE# 5
BLOCK# 135
OFFSET 8170
DBA 0x01400087 (20971655 5,135)
FILENAME /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf
BIFILE bifile.bbd
LISTFILE /home/oracle/bbedfile.lst
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
注：查看当前BBED的设置
BBED> find /c yallonking File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5) Block: 135 Offsets: 8170 to 8191 Dba:0x01400087 ------------------------------------------------------------------------ 79616c6c 6f6e6b69 6e670778 7107130a 251a0206 f099 <32 bytes per line>
注：定位字符位置（/c 指字符）
BBED> dump /v dba 5,135 offset 8170 count 10 File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5) Block: 135 Offsets: 8170 to 8179 Dba:0x01400087 ------------------------------------------------------- 79616c6c 6f6e6b69 6e67 l yallonking <16 bytes per line>
注：找到待修改的字符（king改为ora）的具体偏移量，此处偏移量为8176
再次确认如下
BBED> dump /v dba 5,135 offset 8176 File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5) Block: 135 Offsets: 8176 to 8185 Dba:0x01400087 ------------------------------------------------------- 6b696e67 07787107 130a l king.xq... <16 bytes per line>
修改指定字符
BBED> modify /c ora dba 5,135 offset 8176 File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5) Block: 135 Offsets: 8176 to 8185 Dba:0x01400087 ------------------------------------------------------------------------ 6f726167 07787107 130a <32 bytes per line>
注：该命令修改指定块指定偏移量上的字符
再次确认修改后的
BBED> dump /v dba 5,135 offset 8176 File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5) Block: 135 Offsets: 8176 to 8185 Dba:0x01400087 ------------------------------------------------------- 6f726167 07787107 130a l orag.xq... <16 bytes per line>
回滚上一操作
BBED> undo BBED> modify /x 6B696E filename '/u01/app/oracle/oradata/ora11gr2/yallonking01.dbf' block 135. offset 8176. File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5) Block: 135 Offsets: 8176 to 8185 Dba:0x01400087 ------------------------------------------------------------------------ 6b696e67 07787107 130a <32 bytes per line>
再次确认回滚后的数据
BBED> dump /v dba 5,135 offset 8176 File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5) Block: 135 Offsets: 8176 to 8185 Dba:0x01400087 ------------------------------------------------------- 6b696e67 07787107 130a l king.xq... <16 bytes per line>
再次修改指定字符
BBED> modify /c ora. dba 5,135 offset 8176 File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5) Block: 135 Offsets: 8176 to 8185 Dba:0x01400087 ------------------------------------------------------------------------ 6f72612e 07787107 130a <32 bytes per line>
再次确认修改后结果
BBED> dump /v dba 5,135 offset 8176 File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5) Block: 135 Offsets: 8176 to 8185 Dba:0x01400087 ------------------------------------------------------- 6f72612e 07787107 130a l ora..xq... <16 bytes per line>
验证修改后的块
BBED> verify dba 5,135 DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf BLOCK = 135 Block 135 is corrupt Corrupt block relative dba: 0x01400087 (file 0, block 135) Bad check value found during verification Data in bad block: type: 6 format: 2 rdba: 0x01400087 last change scn: 0x0000.000b99f0 seq: 0x2 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x99f00602 check value in block header: 0xc0ab computed block checksum: 0x520b DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 0 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 1 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED
注：此处已经破坏了块，所有校验后标记为Corrupt
重新计算校验和并提交变化
BBED> sum dba 5,135 apply Check value for File 5, Block 135: current = 0x92a0, required = 0x92a0
再次查看块校验结果
BBED> verify dba 5,135 DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/ora11gr2/yallonking01.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
在sql中确认该表记录
SQL> alter system flush BUFFER_CACHE;
System altered.
SQL> select t.*,length(name) from yallonking.yallonking t;
ID NAME MY_DATE LENGTH(NAME)
---------- -------------------- ------------------- ------------
1 yallonora. 2013/07/19 09:36:25 10
2 oraking 2013/07/19 09:45:44 7
注：此处发现后边的小数点需要去掉
（二）BBED删除指定字符（间接）
此处删除上例中的小数点（由于修改字符数需要一致，所以只能用空格占位间接的进行修改）
BBED> show all
FILE# 5
BLOCK# 135
OFFSET 8170
DBA 0x01400087 (20971655 5,135)
FILENAME /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf
BIFILE bifile.bbd
LISTFILE /home/oracle/bbedfile.lst
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
BBED> dump /v dba 5,135
File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5)
Block: 135 Offsets: 8170 to 8191 Dba:0x01400087
-------------------------------------------------------
79616c6c 6f6e6f72 612e0778 7107130a l yallonora..xq...
251a0206 f099 l %...e.
<16 bytes per line>
BBED> dump /v dba 5,135
File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5)
Block: 135 Offsets: 8170 to 8191 Dba:0x01400087
-------------------------------------------------------
79616c6c 6f6e6f72 612e0778 7107130a l yallonora..xq...
251a0206 f099 l %...e.
<16 bytes per line>
BBED> modify /c 'yallonora ' dba 5,135 offset 8170
File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5)
Block: 135 Offsets: 8170 to 8191 Dba:0x01400087
------------------------------------------------------------------------
79616c6c 6f6e6f72 61200778 7107130a 251a0206 f099
<32 bytes per line>
注：此处的字符后边多加了一个空格来占位
BBED> dump /v dba 5,135
File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5)
Block: 135 Offsets: 8170 to 8191 Dba:0x01400087
-------------------------------------------------------
79616c6c 6f6e6f72 61200778 7107130a l yallonora .xq...
251a0206 f099 l %...e.
<16 bytes per line>
BBED> sum dba 5,135 apply
Check value for File 5, Block 135:
current = 0x9ca0, required = 0x9ca0
SQL> alter system flush BUFFER_CACHE;
System altered.
SQL> select t.*,length(name) from yallonking.yallonking t;
ID NAME MY_DATE LENGTH(NAME)
---------- -------------------- ------------------- ------------
1 yallonora 2013/07/19 09:36:25 10
2 oraking 2013/07/19 09:45:44 7
注：虽然修改了字符，但是字符长度还是没有变。只是通过修改间接的删除了字符。
