A-A+

BBED的常见使用命令及修改字符

2013年07月19日 Backup&Recovery 暂无评论 阅读 1,263 次

参考文档: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

注:虽然修改了字符,但是字符长度还是没有变。只是通过修改间接的删除了字符。

标签:

给我留言

Copyright © YallonKing 保留所有权利.   Theme  Ality

用户登录

分享到: