Oracle bbed 五个 实用示例

在之前的blog 里介绍了BBED 工具的语法部分。 参考：

Oracle BBED 工具 说明

http://blog.csdn.net/xujinyang/article/details/6832709

在这篇主要看一下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,

3dbms_rowid.rowid_relative_fno(rowid)rel_fno,

4dbms_rowid.rowid_block_number(rowid)blockno,

5dbms_rowid.rowid_row_number(rowid) rowno

6 from dvd;

ROWID REL_FNO BLOCKNOROWNO

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

AAAN9VAABAAAcKiAAA 1115362 0

AAAN9VAABAAAcKiAAB 1115362 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: 115362Offsets: 8176 to 8191Dba: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: 115362Offsets: 8176 to 8191Dba: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: 16fb: --H-FL--lb: 0x1 cc: 1

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

tab 0, row 1, @0x1f7b

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

col0: [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: 2fb: --HDFL--lb: 0x1

tab 0, row 1, @0x1f7b

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

col0: [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: 115362Offsets: 8176 to 8191Dba: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: 115362Offsets: 8176 to 8191Dba: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: 115362Offsets: 8175 to 8191Dba: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: 115362Offsets: 8172 to 8191Dba: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

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

DMMis 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

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

DMMis 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.dbfundotbs01.dbf

control02.ctl example01.dbf redo02.logsystem01.dbf undotbs02.dbf

control03.ctl huaining01.dbf redo03.logtemp01.dbf users01.dbf

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

[oracle@db2 dave2]$ ls

control01.ctl dave01.dbf huaining01.dbf redo03.logtemp01.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 BLOCKNOROWNO

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

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';

OWNERSEGMENT_NAME HEADER_FILEHEADER_BLOCK BLOCKS

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

SYSDVD 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#NameSize(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 to511 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

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

DMMis 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/xujinyang/article/details/6830525

我们这里采用offlinedatafile 的方式来实现。

alterdatabase datafile offline drop 与 alter tablespace drop datafile 区别

http://blog.csdn.net/xujinyang/article/details/6829166

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

42148027679

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# NameSize(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: 1Offsets: 484 to 547Dba:0x01800001

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

df2f0880 0000abbf 47ff3d2d0100bd0c l ß/....«¿G.=-..½.

da000000 fe310000 10000000 02000000 lÚ...þ1..........

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

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 --> 1f4d0880

ub4 kcvcptim @492 0x2d3e0d81 -->810d3e2d

ub4 kcvfhcpc @140 0x00000125 -->25010000

ub4 kcvfhccc @148 0x00000124 -->24010000

这个可以通过dump 对应的offset 进行确认

BBED> d /v dba 1,1 offset 484

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

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

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

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

de000000 02000000 1000abbf 02000000 lÞ.........«¿....

00000000 00000000 00000000 00000000 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: 1Offsets: 492 to 555Dba:0x00400001

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

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

1000abbf 02000000 00000000 00000000 l..«¿............

00000000 00000000 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: 1Offsets: 140 to 203Dba:0x00400001

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

25010000 ccde3d2d24010000 00000000 l %...ÌÞ=-$.......

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

00000000 00000000 00000000 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: 1Offsets: 148 to 211Dba:0x00400001

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

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

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

00000000 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: 1Offsets: 484 to547 Dba:0x01800001

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

1f4d0880 0000abbf 47ff3d2d 0100bd0c

da000000 fe310000 10000000 02000000

00000000 00000000 00000000 00000000

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 203Dba:0x01800001

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

25010000 ccde3d2d e7000000 00000000

00000000 00000000 00000000 00000000

00000000 00000000 00000000 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 to211 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的例子

http://dbsnake.com/2009/10/bbed-recover-off-df.html

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/xujinyang/article/details/6829884

重建完控制文件之后：

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-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/xujinyang/article/details/6822947

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

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

DMMis 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 re­calculated 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。 所以这里仅了解一下这个用法。