在之前的blog 裡介紹了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

************* !!! 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

************* !!! 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

File: /u01/app/oracle/oradata/dave2/dave01.dbf(6)

Block: 28 Offsets: 0 to 511 Dba:0x0180001c

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

06a20000 1c008001 f4a90780 00000104

f5b40000 01000000 61df0000 f4a90780

....

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

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

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

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

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

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

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

BBED> copy dba 9,35 to dba6,35

--直接select 沒有變化，發db 重啟了一下，還原的資料就出現了

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

no rows selected

SYS@dave2(db2)> startup force

ORACLE instance started.

Total System Global Area 239075328 bytes

Fixed Size 1218724 bytes

Variable Size 62916444 bytes

Database Buffers 171966464 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

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

JOB

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

DMM is DBA!

dmme like Oracle!

四. 示例：File Header Reset

在做db 做不完全恢復的時候，可能會遇到如下錯誤：

ORA-01113:file 6 needs media recovery

ORA-01110:data file 6: '+DATA/rac/datafile/dave01.dbf'

當我們recover 時候，歸檔檔案有丟失，就會報以上的錯誤。在這種情況下，可以設定初始化引數：_allow_resetlogs_corruption＝true，這樣在Oracle 啟動時，不再檢測datafil的一致性，但是如果有檔案損壞，檔案要進行恢復等等，還會有不能open的報錯提示。

還有一種方法就是透過BBED 命令，修改file header reset，讓datafile 保持一致。但是這種僅僅是手工的設定，雖然可以把DB強行拉起來，還是會可能導致其他的問題。

如果不能進行修復，就只能將對應的datafile 進行offline。

為了演示用BBED 修改file header reset。我們先模擬一下這種情況。模擬的方式很多，不完全恢復可以報這個錯誤，offlinedatafile然後online 也會提示這個錯誤。只要datafile scn 資訊不一致，就達到了我們的目的。

操作之前最好對DB 進行一個RMAN 備份，指令碼參考：

Nocatalog 下的RMAN 增量備份 shell指令碼

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

我們這裡採用offlinedatafile 的方式來實現。

alterdatabase datafile offline drop 與 alter tablespace drop datafile 區別

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

SYS@dave2(db2)> select file#,status,name from v$datafile;

FILE# STATUS NAME

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

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

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

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

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

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

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

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

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

8 rows selected.

將datafile 6 offline，在online：

SYS@dave2(db2)> alter database datafile6 offline;

Database altered.

SYS@dave2(db2)> select file#,status,namefrom v$datafile;

FILE# STATUS NAME

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

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

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

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

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

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

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

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

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

8 rows selected.

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

select * from dvd

*

ERROR at line 1:

ORA-00376: file 6 cannot be read at thistime

ORA-01110: data file 6:'/u01/app/oracle/oradata/dave2/dave01.dbf'

SYS@dave2(db2)> create table anqing asselect * from all_objects;

Table created.

SYS@dave2(db2)> update anqing setobject_id=100;

49947 rows updated.

SYS@dave2(db2)> commit;

Commit complete.

SYS@dave2(db2)> alter database datafile6 online;

alter database datafile 6 online

*

ERROR at line 1:

ORA-01113: file 6 needs media recovery

ORA-01110: data file 6:'/u01/app/oracle/oradata/dave2/dave01.dbf'

--報錯了，目的達到了，現在就靠BBED了。

檢視控制檔案裡的SCN:

SYS@dave2(db2)> selectfile#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#

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

1 2148027679

2 2148027679

3 2148027679

4 2148027679

5 2148027679

6 2148020191

7 2148027679

8 2148027679

檢視需要恢復datafile 的SCN:

SYS@dave2(db2)> selectfile#,online_status,change# from v$recover_file;

FILE# ONLINE_ CHANGE#

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

6 OFFLINE 2148020191

The file header is stored in the first block of the data file.We can use bbed to examine the block and show the block map. The header blockscontain a single data structure - kcvfh.

datafile 的file header 儲存在第一個block裡。

Oracleconsiders four attributes of this data structure when determining if a datafile is sync with the other data files of the database:

（1）kscnbas (at offset 484) - SCN of last change to the datafile.

（2）kcvcptim (at offset 492) -Time of the last change to the datafile.

（3）kcvfhcpc (at offset 140) - Checkpoint count.

（4）kcvfhccc (at offset 148) - Unknown, but is always 1 less than thecheckpoint point count.

Oracle有4個屬性來判斷datafile 是否和其他的datafile 一致，如果都一致，可以正常操作，如果不一致，那麼會報ORA-01113錯誤。

Thefirst two attributes are stored in the kcvfhckp sub-structure. The second twoare attributes in their own right.

Wecan use the print command to display them all for the file that requiresrecovery:

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.dbf 6400

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

BBED> set dba 6,1

DBA 0x01800001(25165825 6,1)

--指定datafile6，第一個block

BBED> p kcvfhckp

struct kcvfhckp, 36 bytes @484

struct kcvcpscn, 8 bytes @484

ub4 kscnbas @484 0x80082fdf

ub2 kscnwrp @488 0x0000

ub4 kcvcptim @492 0x2d3dff47

BBED> p kcvfhcpc

ub4 kcvfhcpc @140 0x000000e8

BBED> p kcvfhccc

ub4 kcvfhccc @148 0x000000e7

從上面可以看到datafile 6的SCN 是0x80082fdf，轉換一下：

SYS@dave2(db2)> selectto_number('80082fdf','xxxxxxxxxxx') from dual;

TO_NUMBER('80082FDF','XXXXXXXXXXX')

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

2148020191

這個和我們之前看到的一致。

change time 是0x2d3dff47. 我們dump 一下偏移量484.

BBED> d /v dba 6,1 offset 484 count 64

File: /u01/app/oracle/oradata/dave2/dave01.dbf(6)

Block: 1 Offsets: 484 to 547 Dba:0x01800001

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

df2f0880 0000abbf 47ff3d2d0100bd0c l ?/....??G.=-..?.

da000000 fe310000 10000000 02000000 l?...?1..........

00000000 00000000 00000000 00000000 l................

<16 bytes per line>

這裡面儲存的格式和我們看到的相反。這個在bbed 理論那片裡也提到了這點：the numbers are stored in little endian format (the low-order byte of thenumber is stored in memory at the lowest address) as this example database isrunning on Linux on an Intel platform.

現在我們要做的，就是使用BBED 命令，修改datafile 6的4個屬性，讓其和其他的datafile 一致。

現在看一下systemdatafile 的4個屬性值，然後修改到datafile 6上。

BBED> set dba 1,1

DBA 0x00400001(4194305 1,1)

BBED> p kcvfhckp

struct kcvfhckp, 36 bytes @484

struct kcvcpscn, 8 bytes @484

ub4 kscnbas @484 0x80084d1f

ub2 kscnwrp @488 0x0000

ub4 kcvcptim @492 0x2d3e0d81

ub2 kcvcpthr @496 0x0001

BBED> p kcvfhcpc

ub4 kcvfhcpc @140 0x00000125

BBED> p kcvfhccc

ub4 kcvfhccc @148 0x00000124

SYS@dave2(db2)> select to_number('80084d1f','xxxxxxxxxxx') from dual;

TO_NUMBER('80083775','XXXXXXXXXXX')

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

2148027679

修改datafile 6的4個對應屬性，注意一個一個問題，我們看到的值，在intel 的little endian是低位先儲存，即順序與我們看到的是相反的。

ub4 kscnbas @484 0x80084d1f --&gt 1f4d0880

ub4 kcvcptim @492 0x2d3e0d81 --&gt810d3e2d

ub4 kcvfhcpc @140 0x00000125 --&gt25010000

ub4 kcvfhccc @148 0x00000124 --&gt24010000

這個可以透過dump 對應的offset 進行確認

BBED> d /v dba 1,1 offset 484

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

Block: 1 Offsets: 484 to 547 Dba:0x00400001

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

1f4d0880 00000000 810d3e2d 01000000 l.M........>-....

de000000 02000000 1000abbf 02000000 l?.........??....

00000000 00000000 00000000 00000000 l................

<16 bytes per line>

BBED> d /v dba 1,1 offset 492

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

Block: 1 Offsets: 492 to 555 Dba:0x00400001

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

810d3e2d 01000000de000000 02000000 l ..>-....?.......

1000abbf 02000000 00000000 00000000 l..??............

00000000 00000000 00000000 00000000 l................

<16 bytes per line>

BBED> d /v dba 1,1 offset 140

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

Block: 1 Offsets: 140 to 203 Dba:0x00400001

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

25010000 ccde3d2d24010000 00000000 l %...??=-$.......

00000000 00000000 00000000 00000000 l................

<16 bytes per line>

BBED> d /v dba 1,1 offset 148

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

Block: 1 Offsets: 148 to 211 Dba:0x00400001

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

24010000 00000000 00000000 00000000 l $...............

00000000 00000000 00000000 00000000 l................

<16 bytes per line>

修改datafile 6：

BBED> modify /x 1f4d0880 dba 6,1 offset484

File: /u01/app/oracle/oradata/dave2/dave01.dbf(6)

Block: 1 Offsets: 484 to 547 Dba:0x01800001

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

1f4d0880 0000abbf 47ff3d2d 0100bd0c

da000000 fe310000 10000000 02000000

00000000 00000000 00000000 00000000

<16 bytes per line>

BBED> modify /x 810d3e2ddba 6,1 offset 492

BBED-00209: invalid number (810d3e2d)

--偏移量492 的位置修改失敗.但是這個可以改成比該值小的值。

--注意，這裡經網友提醒，把DB shutdown 之後再次測試了一下。在shutdown 的情況下，4個引數都可以修改。

BBED> modify /x 25010000 dba 6,1 offset140

File: /u01/app/oracle/oradata/dave2/dave01.dbf(6)

Block: 1 Offsets: 140 to 203 Dba:0x01800001

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

25010000 ccde3d2d e7000000 00000000

00000000 00000000 00000000 00000000

<16 bytes per line>

BBED> modify /x 24010000 dba 6,1 offset148

File: /u01/app/oracle/oradata/dave2/dave01.dbf(6)

Block: 1 Offsets: 148 to 211 Dba:0x01800001

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

24010000 00000000 00000000 00000000

00000000 00000000 00000000 00000000

0000000000000000 00000000 00000000

00000000 00000000 00000000 00000000

<16 bytes per line>

BBED> sum dba6,1 apply

Check value for File 6, Block 1:

current = 0x3422, required = 0x3422

應用變跟之後，嘗試onlinedatafile 6 還是失敗。

SYS@dave2(db2)> select file#,checkpoint_change#from v$datafile;

FILE# CHECKPOINT_CHANGE#

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

1 2148027679

2 2148027679

3 2148027679

4 2148027679

5 2148027679

6 2148020191

7 2148027679

8 2148027679

8 rows selected.

SYS@dave2(db2)> selectfile#,online_status,change# from v$recover_file;

FILE# ONLINE_ CHANGE#

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

6 OFFLINE 2148027679

這裡要注意v$datafile裡的結果。雖然我們修改了datafile header裡的幾個值，但是v$datafile裡的scn並沒有改變，因為這裡的scn是從控制檔案裡讀取的。而BBED 不能修改控制檔案，所以，對於offline datafile 的方法，只使用bbed 就行不通，還需要做一些其他的操作。

如果是startup 階段遇到這個問題，那麼就完全可以使用bbed 搞定這個問題。

既然我們測試遇到了這個問題，就繼續研究一下了。dbsnake 有篇blog專門講了這個問題：

一個透過BBED強制恢復offline狀態的datafile的例子

oracle在對某個datafile做offline的時候實際上是相當於offline immediate，此時不會改datafile header中的內容，而只是修改control檔案，等到再想online的時候一定要做recovery，從而讓控制檔案和datafile裡的scn 一致。DSI 403e中的描述：

Offline normal (tablespace)：

1、Checkpoints data blocks oftablespace

2、Updates file headers and controlfile

Offline immediate (tablespace or data file)：

1、Only update control file

2、Data files require recovery

在這種情況下，恢復的大致步驟如下：

1、先透過比對system01.dbf的datafile header的內容來修改datafile的datafile header。

2、重建控制檔案

3、用帶*._allow_resetlogs_corruption=TRUE的pfile啟庫到mount狀態

4、用open resetlogs強制開啟上述資料庫

5、最後shutdown immediate再startup

修改datafile header 我們已經做過了，我們重新一下控制檔案。這裡就不詳細介紹重建控制檔案的過程，關於控制檔案的重建，參考我的blog：

Oracle 控制檔案

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

重建完控制檔案之後：

SYS@dave2(db2)> select open_mode fromv$database;

OPEN_MODE

----------

MOUNTED

SYS@dave2(db2)> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS orNORESETLOGS option for database open

SYS@dave2(db2)> alter database openresetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1:'/u01/app/oracle/oradata/dave2/system01.dbf'

修改初始化引數，新增*._allow_resetlogs_corruption=TRUE

SYS@dave2(db2)> create pfile fromspfile;

File created.

SYS@dave2(db2)> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SYS@dave2(db2)> startup mountpfile=/?/dbs/initdave2.ora

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.

SYS@dave2(db2)> alter database openresetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated.Disconnection forced

--open resetlogs 失敗

alert log 資訊如下：

Sat Aug 13 09:18:16 2011

Errors in file /u01/app/oracle/admin/dave2/udump/dave2_ora_13809.trc:

ORA-00704: bootstrap process failure

ORA-00600: internal error code, arguments:[4000], [1], [], [], [], [], [], []

Sat Aug 13 09:18:16 2011

Error 704 happened during db open, shuttingdown database

USER: terminating instance due to error 704

Instance terminated by USER, pid = 13809

ORA-1092 signalled during: alter databaseopen resetlogs...

ORA-600[4000] 是undo 出現了問題。

重新修改pfile 引數，採用system segment，新增引數：

undo_management='MANUAL'

rollback_segments='SYSTEM'

具體處理方法參考：

Current online Redo 和 Undo 損壞的處理方法

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

SQL> startup mountpfile=/?/dbs/initdave2.ora

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.

SQL > alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01139: RESETLOGS option only validafter an incomplete database recovery

SQL > alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1:'/u01/app/oracle/oradata/dave2/system01.dbf'

SQL > recover database ;

Media recovery complete.

SQL > alter database open;

Database altered.

SQL > select open_mode from v$database;

OPEN_MODE

----------

READ WRITE

SQL > select file#,status,name fromv$datafile;

FILE# STATUS

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

NAME

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

1 SYSTEM

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

2 ONLINE

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

3 ONLINE

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

FILE# STATUS

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

NAME

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

4 ONLINE

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

5 ONLINE

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

6 ONLINE

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

7 ONLINE

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

8 ONLINE

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

8 rows selected.

SQL > select * from dvd;

JOB

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

DMM is DBA!

dmme like Oracle!

這裡總算是開啟了。不過並不是完全恢復。先用bbed 修改datafiler header，然後重建控制檔案，最後open resetlogs。如果遇到ora-600 的錯誤，注意下undo。

小結：

這個實驗的本意是想測試一下startup時報錯用bbed 修改datafile header來處理問題的，結果用例子的時候，選擇的了offline datafile，由此引發了一個棘手的問題。不過最後我們的datafile 還是順利online了。

這裡也僅僅是個演示，對於offline datafile 的情況，online 時使用歸檔recover一下就ok了，不用這麼複雜。

五. 示例：Uncorrupting a Block

Thefollowing example shows how bbed can be used to reset the corrupt-block marker.Although Oracle now supports an official PL/SQL package to repair corruptblocks, the following is still a useful demonstration of the power of bbed.

The following Oracle error shows corruptdata being encountered:

SQL> select * from scott.presidents;

select * from scott.presidents

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted(file # 7, block # 16)

ORA-01110: data file 7:'/home/oracle/OraHome1/oradata/gctdev2/users01.dbf'

When Oracle determines that a data block is corrupt, it marksthe block as corrupt by setting the block sequence number to 0xff. Thiscan be seen as the seq_kcbh attribute of the kcbh structure:

--當Oracle 認為一個block 是corrupt時，會將該block的sequence number 標記為0xff. 該值可以透過seq_kcbh 屬性檢視。

BBED> set dba 7,16

DBA 0x01c00010 (29360144 7,16)

BBED> p kcbh

struct kcbh, 20 bytes @0

ub1type_kcbh @0 0x06

ub1frmt_kcbh @1 0x02

ub1spare1_kcbh @2 0x00

ub1spare2_kcbh @3 0x00

ub4rdba_kcbh @4 0x01c00010

ub4bas_kcbh @8 0x00000000

ub2wrp_kcbh @12 0x0000

ub2spare3_kcbh @18 0x0000

ub1 seq_kcbh @14 0xff

ub1flg_kcbh @15 0x04 (KCBHFCKV)

ub2chkval_kcbh @16 0x6ff4

Thereforeto reset the corrupt marker, we need to set the block sequence number to avalue other than 0xff. The sequence number is stored at offset 14. The following shows the sequence number being reset to 0x01.

BBED> modify /x 01 dba 7,16 offset 14

Thesequence number also comprises one component of the tailcheck of the block,which occupies the last 8 bytes. This also needs to be reset for Oracle torecognize the block as valid again.

這裡要注意一個問題，就是在每個block裡有一個tailcheck。該值由三部分組成，其中一部分就是seq number。所以我們在修改block sequence時，也需要tailcheck。讓2者之間的seq 對應起來。

Usingbbed we can print the tail check and see that it is 0x000006ff. However when wereset it we must remember that this value is interpreted as a single unsignedinteger. On Intel machines therefore, the value is stored low-order byte firstas the processor uses a little-endian architecture.

BBED> p tailchk

ub4tailchk @8188 0x000006ff

BBED> modify /x 01060000 dba 7,16 offset8188

File: /home/oracle/OraHome1/oradata/gctdev2/users01.dbf(7)

Block: 16 Offsets: 8188 to 8191Dba:0x01c00010

01060000

<32 bytes per line>

Nowthat the SCN sequence number and tail check has been reset, the block check sumshould be recalculated and applied.

BBED> sum dba 7,16 apply

Check value for File 7, Block 16:

current = 0x6f0a, required = 0x6ff4

Thedatabase will probably have to be bounced to recognize the modified block,since block caching applies to corrupted blocks as well. Once the database isrestarted, the block can be read again:

SQL> select * from scott.presidents;

Inthis example we only reset the block corruption marker. We did not address theunderlying cause of the corruption. If such a cause existed it would need to beaddressed before the block was re-read by Oracle, otherwise the block would bemarked as corrupt again.

--這個是資料裡的示例，本想自己動手測試一下這個示例，不過發現這個不可控因素太多。要完全實現這個困難有點大，首先如何製造壞塊的問題。可以透過bbed 修改block裡的內容，這樣oracle 會將該block 標記為corrupt。即使我們修改seq 標記，在下次讀這個block時，還是會將block 標記為corrupt。所以這裡僅瞭解一下這個用法。

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

Blog： http://blog.csdn.net/tianlesoftware

Weibo:

Email: dvd.dba@gmail.com

DBA1 群：62697716(滿); DBA2 群：62697977(滿) DBA3 群：62697850(滿)

DBA 超級群：63306533(滿); DBA4 群： 83829929(滿) DBA5群： 142216823(滿)

DBA6 群：158654907(滿) 聊天群：40132017(滿) 聊天2群：69087192(滿)

--加群需要在備註說明Oracle表空間和資料檔案的關係，否則拒絕申請

Oracle bbed 五個實用示例

一. 示例：修改Data 內容

1.2 檢視要修改的內容

1.3 查詢關鍵字Dave，確定其在block中的偏移量offset。

1.4 修改block，將Dave 換成DMM

1.5 應用變更

二. 示例：恢復delete 的rows

三. 示例：Recoveringdeleted/damaged Data

四. 示例：File Header Reset

五. 示例：Uncorrupting a Block

相關文章

Oracle bbed 五個 實用示例

一. 示例： 修改Data 內容

1.2 檢視要修改的內容

1.3 查詢關鍵字Dave，確定其在block中的偏移量offset。

1.4 修改block，將Dave 換成DMM

1.5 應用變更

二. 示例：恢復delete 的rows

三. 示例：Recoveringdeleted/damaged Data

四. 示例：File Header Reset

五. 示例：Uncorrupting a Block

相關文章

Oracle bbed 五個實用示例

一. 示例：修改Data 內容