Oracle bbed好文章

 

Oracle bbed 五个 实用示例

 Oracle BBED 工具 说明

       http://blog.csdn.net/tianlesoftware/article/details/5006580

在这篇主要看一下BBED 工具的几个使用示例。

       Althoughbbed can modify data in the data files of an open Oracle database, it isadvisable to shut down the database before making any changes. This avoids thecheckpoint process overwriting the changes made with bbed from the Oracle blockcache. It also avoids Oracle reading the block before the modifications arecomplete and declaring the block corrupt.

       虽然bbed 可以在db open 状态来进行修改，但是建议在做任何修改操作之前先shutdown db。 这样避免checkpoint 进程重写bbed 对block 的修改。 也避免oracle 在bbed 修改完成之前读block 或者申明block 为corrupt。

       Important:Using bbed to modify the contents of an Oracle data block renders the dataun-supported by Oracle. These examples should be used for educational purposesonly. If they are used on real production databases they should only be used asa last resort and once the immediate problem has been resolved, all retrievabledata should be exported and a new database created.

       bbed工具不受Oracle 的技术支持。

       Althoughbbed can be used to open a database that would otherwise be beyond salvaging,the DBA must bear in mind that the internal tables such as OBJ$, UET$ and FET$may no longer match the contents of the data blocks. The behavior of thedatabase will therefore be unpredictable and ORA-600 errors are likely.

一. 示例： 修改Data 内容

 1.1 连接bbed

[oracle@db2 ~]$ bbed parfile=/u01/bbed.par

Password:

BBED: Release 2.0.0.0.0 - LimitedProduction on Fri Aug 12 18:26:46 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

************* !!! For Oracle Internal Useonly !!! ***************

BBED>

1.2 查看要修改的内容

SYS@dave2(db2)> select * from dvd;

JOB

--------------------------------------------------------------------------------

Dave is DBA!

Dave like Oracle!

注意： bbed 的修改仅仅是对原有位置内容的一个替换。

对应block 的信息如下：

SYS@dave2(db2)> select

  2   rowid,

 3 dbms_rowid.rowid_relative_fno(rowid)rel_fno,

 4 dbms_rowid.rowid_block_number(rowid)blockno,

 5 dbms_rowid.rowid_row_number(rowid) rowno

 6  from dvd;

ROWID                 REL_FNO    BLOCKNO     ROWNO

------------------ ---------- --------------------

AAAN9VAABAAAcKiAAA          1    115362          0

AAAN9VAABAAAcKiAAB          1    115362          1

SYS@dave2(db2)>

1.3 查找关键字Dave，确定其在block中的偏移量offset。

BBED> set dba 1,115362 offset 0

       DBA             0x0041c2a2(4309666 1,115362)

       OFFSET          0

BBED> find /c Dave

 File:/u01/app/oracle/oradata/dave2/system01.dbf (1)

 Block: 115362           Offsets: 8176 to 8191           Dba:0x0041c2a2

------------------------------------------------------------------------

 44617665 20697320 44424121 020616b3

 <32 bytes="" per="" line="">

dump 查看具体内容：

BBED> dump /v dba 1,115362 offset 8176count 128

 File: /u01/app/oracle/oradata/dave2/system01.dbf(1)

 Block: 115362 Offsets: 8176 to 8191 Dba:0x0041c2a2

-------------------------------------------------------

 44617665 20697320 44424121 020616b3 l Dave isDBA!...?

 <16 bytes="" per="" line="">

注意这里面的Offsets:8176 to 8191， 它指的是这一行的一个地址。其中

D 的offset 是8176

a 的offset 是8177

v 的offset 是8178

e 的offset 是8179

空格也算offset。

1.4 修改block，将Dave 换成DMM

BBED> modify /c 'DMM ' dba 1,115362offset 8176

 File:/u01/app/oracle/oradata/dave2/system01.dbf (1)

 Block: 115362           Offsets: 8176 to 8191           Dba:0x0041c2a2

------------------------------------------------------------------------

 444d4d20 20697320 44424121 020616b3

 <32 bytes="" per="" line="">

--注意这里DMM我用单引号括起来，并且最后还有一个空格，这样就是4个bytes，不用单引号括起来，无法表示空格，验证一下

BBED> dump /v dba 1,115362 offset 8176count 128

 File:/u01/app/oracle/oradata/dave2/system01.dbf (1)

 Block: 115362 Offsets: 8176 to 8191 Dba:0x0041c2a2

-------------------------------------------------------

 444d4d20 20697320 44424121 020616b3 l DMM  is DBA!...?

 <16 bytes="" per="" line="">

1.5 应用变更

BBED> sum dba 1,115362

Check value for File 1, Block 115362:

current = 0xdef7, required = 0x8cc0

此时 current checksum 是0xdef7，requiredchecksum 是0x8cc0

BBED> sum dba 1,115362 apply

Check value for File 1, Block 115362:

current = 0x8cc0, required = 0x8cc0

加上apply参数，使checksum一致。即之前的修改生效。

SYS@dave2(db2)> alter system flushbuffer_cache;

System altered.

SYS@dave2(db2)> select * from dvd;

JOB

--------------------------------------------------------------------------------

DMM  is DBA!

Dave like Oracle!

二. 示例：恢复delete 的rows

       Whenrows are deleted in Oracle the data is not actually removed. The row is simplymarked as deleted and the free space counters and pointers adjustedaccordingly. The status of a row is stored in the Row Header which occupies thefirst few bytes of each row.

       当row 被delete 的时候，实际上data 并没有被remove，只是将该row 标记为delete，然后其对应的空间被统计为free space。 row 的status 存在每个row的row header里。

       TheRow Header consists of the Row Flag, Lock Byte (ITL entry) and Column Count.The first of these - the Row Flag - is a single byte that holds a bitmask thatshows the status of the row. The bitmask is decoded as follows:

       RowHeader 包含Row Flag，Lock Byte（ITL）和column Count。其中Row Flag占用1个byte，并且以bitmask 来保存。bitmask 的解释如下：

Cluster Key	Cluster Table Member	Head of row piece	Deleted	First data piece	Last data piece	1st Column continues from previous piece	Last column continues in next piece
128	64	32	16	8	4	2	1

我们dump 一个block，看一个row Flag，来帮助理解这个bitmask。

SYS@dave2(db2)> alter system dump datafile1 block 115362;

System altered.

SYS@dave2(db2)> oradebug setmypid

Statement processed.

SYS@dave2(db2)> oradebug tracefile_name

/u01/app/oracle/admin/dave2/udump/dave2_ora_9396.trc

trace file有关row的信息如下：

block_row_dump:

tab 0, row 0, @0x1f90

tl: 16 fb: --H-FL--lb: 0x1  cc: 1

col 0: [12]  44 4d 4d 20 20 69 73 2044 42 41 21

tab 0, row 1, @0x1f7b

tl: 21 fb: --H-FL-- lb: 0x2  cc: 1

col 0: [17]  64 6d 6d 65 20 6c 69 6b65 20 4f 72 61 63 6c 65 21

end_of_block_dump

我们的表dvd里只有2行记录，所以这里显示的row 为2.

注意这里的fb: --H-FL--。 其有8个选项，每个值分别与bitmask 对应。

       Therefore,columns that fit within a single block, are not chained, migrated or part of aclustered table and are not deleted will have the following attributes:

       （1）Head of Row Piece

       （2）First Data Piece

       （3）Last Data Piece

       如果一个row 没有被删除，那么它就具有上面的3个属性，即Flag 表示为：--H-FL--. 这里的字母分别代表属性的首字母。其对应的值：32 + 8 + 4 =44 or 0x2c.

       如果一个row 被delete了，那么row flag 就会更新，bitmask 里的deleted 被设置为16. 此时row flag 为： 32 + 16 + 8 + 4 = 60 or 0x3c.

验证一下：

SYS@dave2(db2)> delete from dvd whererownum=1;

1 row deleted.

SYS@dave2(db2)> commit;

Commit complete.

查看dump 的标记：

block_row_dump:

tab 0, row 0, @0x1f90

tl: 2 fb: --HDFL--lb: 0x1

tab 0, row 1, @0x1f7b

tl: 21 fb: --H-FL-- lb: 0x0  cc: 1

col 0: [17]  64 6d 6d 65 20 6c 69 6b65 20 4f 72 61 63 6c 65 21

end_of_block_dump

这里的row 1flag 变成了--HDFL--。

 

现在我们用bbed 将删除的row 1 内容找回来。

BBED> set dba1,115362 offset 0

       DBA             0x0041c2a2(4309666 1,115362)

       OFFSET          0

BBED> find /c DMM

 File: /u01/app/oracle/oradata/dave2/system01.dbf(1)

 Block: 115362           Offsets: 8176 to 8191           Dba:0x0041c2a2

------------------------------------------------------------------------

 444d4d20 20697320 44424121 020616b3

 <32 bytes="" per="" line="">

BBED> d /v dba 1,115362 offset 8176count 128

 File:/u01/app/oracle/oradata/dave2/system01.dbf (1)

 Block: 115362 Offsets: 8176 to 8191 Dba:0x0041c2a2

-------------------------------------------------------

 444d4d20 20697320 44424121 020616b3 l DMM  is DBA!...?

 <16 bytes="" per="" line="">

注意：我们还是可以通过dump查看我们delete 掉的row记录。但是在sql里用select 已经看出到了。 这个也证明，delete 并未真正的删除data。

我们的row 的内容保存在offset 8176的位置，我们将offset 往前移动一段，在dump，来确定row header的内容。

 

这个移位有一定的规律。 我们看一下：

 

BBED> d /v dba 1,115362 offset 8176count 128

 File: /u01/app/oracle/oradata/dave2/system01.dbf(1)

 Block: 115362 Offsets: 8176 to 8191 Dba:0x0041c2a2

-------------------------------------------------------

 444d4d20 20697320 44424121 020616b3 l DMM  is DBA!...?

我们的一条记录是从444d4d20开始的，此时的offsets8176开始的。我们offset 减小一位，在dump：

BBED> d /v dba 1,115362 offset 8175

 File:/u01/app/oracle/oradata/dave2/system01.dbf (1)

 Block: 115362 Offsets: 8175 to 8191 Dba:0x0041c2a2

-------------------------------------------------------

 0c444d4d 20206973 20444241 210206d7 l.DMM  is DBA!..×

 73                                  l s

 <16 bytes="" per="" line="">

此时dump 的内容多了2个字符，而一个完整的是8个字符，所以要想完整的显示，一次要减少4个offsets。

BBED> d /v dba 1,115362 offset 8172

 File:/u01/app/oracle/oradata/dave2/system01.dbf (1)

 Block: 115362 Offsets: 8172 to 8191 Dba:0x0041c2a2

-------------------------------------------------------

 3c01010c 444d4d20 20697320 44424121 l <...DMM  is DBA!

 0206d773                            l ..×s

 <16 bytes="" per="" line="">

       这里已经出现了我们3c（deleted）标志，但是注意这里的位置的根据我们的查找的字符串来分的，实际在block里的分割方式不一样按照我们的offset 来进行。 我们可以通过row directory 来进行一个确认。

我们print row directory 确认一下：

BBED> p kdbr

sb2 kdbr[0]                                 @110      8080

sb2 kdbr[1]                                 @112      8059

BBED> p *kdbr[0]

rowdata[21]

-----------

ub1 rowdata[21]                             @8172     0x3c

BBED> p *kdbr[1]

rowdata[0]

----------

ub1 rowdata[0]                              @8151     0x2c

       通过row directory，我们可以确认对应row记录的row header保存在offset 8172的位置，值为3c。 我们find 字符串的目的就是为了和rowdirectory 中的offset 进行比较。 他们相近时，就可以确定。

       现在我们将@8172位置的3c 变成2c。 即从deleted 变成正常。

BBED> modify /x 2c offset 8172

 File:/u01/app/oracle/oradata/dave2/system01.dbf (1)

 Block: 115362           Offsets: 8172 to 8191           Dba:0x0041c2a2

------------------------------------------------------------------------

 2c01010c 444d4d20 20697320 44424121 0206d773

 <32 bytes="" per="" line="">

BBED> sum apply

Check value for File 1, Block 115362:

current = 0x2cb4, required = 0x2cb4

--flush buffer cache，然后查询

SYS@dave2(db2)> alter system flushbuffer_cache;

System altered.

SYS@dave2(db2)> select * from dvd;

JOB

--------------------------------------------------------------------------------

DMM is DBA!

Dave like Oracle!

之前delete 的数据已经恢复出来。

三. 示例：Recoveringdeleted/damaged Data

       可是使用BBED 的copy 命令来从旧的data file中copy block，从而恢复已经删除或者删除的记录。

先将我们的表dvd 移动到我们的单独的datafile里：

SYS@dave2(db2)> selectfile_name,tablespace_name from dba_data_files where file_id=6;

FILE_NAME                                     TABLESPACE_NAME

---------------------------------------------------------------------------

/u01/app/oracle/oradata/dave2/dave01.dbf      DAVE2

SYS@dave2(db2)> alter table dvd movetablespace DAVE2;

Table altered.

SYS@dave2(db2)> select table_name,tablespace_namefrom dba_tables where table_name='DVD';

TABLE_NAME                     TABLESPACE_NAME

------------------------------------------------------------

DVD                            DAVE2

SYS@dave2(db2)> select * from dvd;

JOB

--------------------------------------------------------------------------------

DMM is DBA!

dmme like Oracle!

--shutdown db，将dave01.dbfcopy 一份做恢复用

SYS@dave2(db2)> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@db2 ~]$ cd  /u01/app/oracle/oradata/dave2/

[oracle@db2 dave2]$ ls

control01.ctl  dave01.dbf      redo01.log  sysaux01.dbf undotbs01.dbf

control02.ctl  example01.dbf   redo02.log system01.dbf  undotbs02.dbf

control03.ctl  huaining01.dbf  redo03.log temp01.dbf    users01.dbf

[oracle@db2 dave2]$ cp dave01.dbfdave01.dbf.bak

[oracle@db2 dave2]$ ls

control01.ctl  dave01.dbf      huaining01.dbf  redo03.log   temp01.dbf     users01.dbf

control02.ctl  dave01.dbf.bak  redo01.log      sysaux01.dbf  undotbs01.dbf

control03.ctl  example01.dbf   redo02.log      system01.dbf  undotbs02.dbf

将copy 的bak datafile 添加到bbed 的parfile里面

[oracle@db2 u01]$ cat filelist.txt

1/u01/app/oracle/oradata/dave2/system01.dbf 1761607680

2/u01/app/oracle/oradata/dave2/undotbs01.dbf 927989760

3/u01/app/oracle/oradata/dave2/sysaux01.dbf 398458880

4 /u01/app/oracle/oradata/dave2/users01.dbf5242880

5/u01/app/oracle/oradata/dave2/example01.dbf 104857600

6 /u01/app/oracle/oradata/dave2/dave01.dbf10485760

7 /u01/app/oracle/oradata/dave2/undotbs02.dbf1048576

8/u01/app/oracle/oradata/dave2/huaining01.dbf 52428800

9/u01/app/oracle/oradata/dave2/dave01.dbf.bak 10485760

最后一个9 是我们添加的。

 

--启动db

SYS@dave2(db2)> startup

ORACLE instance started.

Total System Global Area  239075328 bytes

Fixed Size                  1218724 bytes

Variable Size              71305052 bytes

Database Buffers          163577856 bytes

Redo Buffers                2973696 bytes

Database mounted.

Database opened.

--查看表block的信息：

select

rowid,

dbms_rowid.rowid_relative_fno(rowid)rel_fno,

dbms_rowid.rowid_block_number(rowid)blockno,

dbms_rowid.rowid_row_number(rowid) rowno

from dvd;

ROWID                 REL_FNO    BLOCKNO     ROWNO

------------------ ---------- --------------------

AAAN9hAAGAAAAAcAAA          6         28          0

AAAN9hAAGAAAAAcAAB          6         28          1

每个block里都可能有多个row，如果表很大，那么就有返回很多条结果。 在这种情况下，这种SQL 语句就显得不够明显。

SYS@dave2(db2)>  select owner, segment_name, header_file,header_block, blocks  fromdba_segments  where owner = 'SYS' andsegment_name = 'DVD';

OWNER SEGMENT_NAME  HEADER_FILEHEADER_BLOCK   BLOCKS

---------- ------------- ----------------------- ----------

SYS       DVD                     6           27          8

       从这个查询结果，我们可以看到，对象保存在datafile 6里，从27 的block 开始存储，占用8个blocks。

       这里要注意的一点是：dba_segments 视图里的block 是从0开始的统计的，而bbed 里是从1. 所以我们在bbed中指定block时，需要加1.

[oracle@db2 ~]$ bbed parfile=/u01/bbed.par

Password:

BBED: Release 2.0.0.0.0 - LimitedProduction on Sat Aug 13 01:11:29 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

************* !!! For Oracle Internal Useonly !!! ***************

BBED> set dba 6,27 offset 0

       DBA             0x0180001b(25165851 6,27)

       OFFSET          0

BBED> p ktbbh

BBED-00400: invalid blocktype (35)

--如果指定block27，会报错。 加1后就正常了。

BBED> set dba 6,28 offset 0

       DBA             0x0180001c(25165852 6,28)

       OFFSET          0

BBED> p ktbbh

struct ktbbh, 96 bytes                      @20     

  ub1 ktbbhtyp                            @20       0x01 (KDDBTDATA)

  union ktbbhsid, 4 bytes                 @24     

     ub4 ktbbhsg1                         @24       0x0000df61

     ub4 ktbbhod1                         @24       0x0000df61

  struct ktbbhcsc, 8 bytes                @28     

     ub4 kscnbas                          @28       0x8007a9f4

     ub2 kscnwrp                          @32       0x0000

   b2ktbbhict                             @36       3

  ub1 ktbbhflg                            @38       0x32 (NONE)

  ub1 ktbbhfsl                            @39       0x00

  ub4 ktbbhfnx                            @40       0x01800019

  struct ktbbhitl[0], 24 bytes            @44     

     struct ktbitxid, 8 bytes             @44     

        ub2 kxidusn                        @44       0x0003

        ub2 kxidslt                       @46       0x0010

        ub4 kxidsqn                       @48       0x00000a3b

     struct ktbituba, 8 bytes             @52     

        ub4 kubadba                       @52       0x00000000

        ub2 kubaseq                       @56       0x0000

        ub1 kubarec                       @58       0x00

     ub2 ktbitflg                         @60       0x8000 (KTBFCOM)

     union _ktbitun, 2 bytes              @62     

        b2 _ktbitfsc                      @62       0

        ub2 _ktbitwrp                     @62       0x0000

     ub4 ktbitbas                         @64       0x8007a9dd

  struct ktbbhitl[1], 24 bytes            @68     

     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     

        b2 _ktbitfsc                      @86       0

        ub2 _ktbitwrp                     @86       0x0000

     ub4 ktbitbas                         @88       0x00000000

  struct ktbbhitl[2], 24 bytes            @92     

     struct ktbitxid, 8 bytes             @92     

        ub2 kxidusn                       @92       0x0000

        ub2 kxidslt                       @94       0x0000

        ub4 kxidsqn                        @96       0x00000000

     struct ktbituba, 8 bytes             @100    

        ub4 kubadba                       @100      0x00000000

        ub2 kubaseq                       @104      0x0000

        ub1 kubarec                        @106      0x00

     ub2 ktbitflg                         @108      0x0000 (NONE)

     union _ktbitun, 2 bytes              @110    

        b2 _ktbitfsc                      @110      0

        ub2 _ktbitwrp                     @110      0x0000

     ub4 ktbitbas                         @112      0x00000000

删除表dvd里的所有数据

SYS@dave2(db2)> delete from dvd;

2 rows deleted.

SYS@dave2(db2)> commit;

Commit complete.

使用bbed copy 从旧的datafile里恢复出来

BBED> set width 65

       WIDTH           65

BBED> info

 File# Name                                        Size(blks)

 ----- ----                                        ----------

    1 /u01/app/oracle/oradata/dave2/system01.dbf       215040

    2 /u01/app/oracle/oradata/dave2/undotbs01.dbf      113280

    3 /u01/app/oracle/oradata/dave2/sysaux01.dbf        48640

    4 /u01/app/oracle/oradata/dave2/users01.dbf           640

    5 /u01/app/oracle/oradata/dave2/example01.dbf       12800

    6 /u01/app/oracle/oradata/dave2/dave01.dbf           1280

    7 /u01/app/oracle/oradata/dave2/undotbs02.dbf         128

    8 /u01/app/oracle/oradata/dave2/huaining01.db        6400

    9 /u01/app/oracle/oradata/dave2/dave01.dbf.ba        1280

从9 copy到6，8个block 全部要copy

BBED> copy dba 9,28 to dba 6,28