—test用户下面新建一张表parameter
create table parameter as select name,num from v$parameter;
–获取这张表的相关物理位置信息
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 test.parameter;
ROWID REL_FNO BLOCKNO ROWNO
—————— ———- ———- ———-
AAAQR/AACAAAAGcAD9 2 412 253
AAAQR/AACAAAAGcAD+ 2 412 254
AAAQR/AACAAAAGcAD/ 2 412 255
AAAQR/AACAAAAGcAEA 2 412 256
。。。。。。。。。。。。。。。。。。。。。。。。。
AAAQR/AACAAAAGdAAA 2 413 0
AAAQR/AACAAAAGdAAB 2 413 1
AAAQR/AACAAAAGdAAC 2 413 2
这张表的行分布在2号数据文件的，412个和413个块上。
–bbed修改412这个块
–bbed准备工作：
$ cat bbed.par
blocksize=8192
listfile=list.log
mode=edit
$cat list.log
2 /ora10205/oradata/ora10205/users02.dbf 20971520
(select file_name,bytes from dba_data_files where file_id=2;得出来的结果，否则将得到 BBED-00310: no datafile specified)
—bbed修改块（–修改前已经备份了当前表空间）
[ora10205@mcdbatest log]$ bbed parfile=bbed.par
blockedit
BBED> set list 'list.log'
LISTFILE list.log
BBED> set dba 2,412
DBA 0×0080019c (8389020 2,412)
BBED> map
File: /ora10205/oradata/ora10205/users02.dbf (2)
Block: 412 Dba:0×0080019c
————————————————————
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 96 bytes @20
struct kdbh, 14 bytes @124
struct kdbt[1], 4 bytes @138
sb2 kdbr[257] @142
ub1 freespace[812] @656
ub1 rowdata[6720] @1468
ub4 tailchk @8188
BBED> dump file 2 block 412
File: /ora10205/oradata/ora10205/users02.dbf (2)
Block: 412 Offsets: 0 to 511 Dba:0×0080019c
————————————————————————
06a20000 9c018000 c504c047 9d0b0204 800f0000 01000000 7f040100 c304c047
9d0b0000 03003200 99018000 ffff0000 00000000 00000000 00000000 00809d0b
c304c047 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00010101
ffff1402 40052c03 2c030000 0101651f 4f1f3f1f 301f191f ff1eea1e cf1eb01e
…………………………
BBED> modify /x 06a20000 offset 8187
BBED> verify
DBVERIFY – Verification starting
FILE = /ora10205/oradata/ora10205/users02.dbf
BLOCK = 412
Block 412 is corrupt
Corrupt block relative dba: 0×0080019c (file 0, block 412)
Fractured block found during verification
Data in bad block:
type: 6 format: 2 rdba: 0×0080019c
last change scn: 0×0b9d.47c004c5 seq: 0×2 flg: 0×04
spare1: 0×0 spare2: 0×0 spare3: 0×0
consistency value in tail: 0×040000a2
check value in block header: 0xf80
computed block checksum: 0×365
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 : 2
BBED> sum apply
–查询损坏的数据
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from test.parameter;
select * from test.parameter
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 2, block # 412)
ORA-01110: data file 2: '/ora10205/oradata/ora10205/users02.dbf'
告警日志：
Hex dump of (file 2, block 412) in trace file /ora10205/admin/ora10205/udump/ora10205_ora_13081.trc
Corrupt block relative dba: 0×0080019c (file 2, block 412)
Fractured block found during buffer read
Data in bad block:
type: 6 format: 2 rdba: 0×0080019c
last change scn: 0×0b9d.47c004c5 seq: 0×2 flg: 0×04
spare1: 0×0 spare2: 0×0 spare3: 0×0
consistency value in tail: 0×040000a2
check value in block header: 0xce5
computed block checksum: 0×0
Reread of rdba: 0×0080019c (file 2, block 412) found same corrupted data
Tue May 14 00:49:15 CST 2013
Corrupt Block Found
TSN = 4, TSNAME = USERS
RFN = 2, BLK = 412, RDBA = 8389020
OBJN = 66687, OBJD = 66687, OBJECT = PARAMETER, SUBOBJECT =
SEGMENT OWNER = TEST, SEGMENT TYPE = Table Segment
–用rman或者dbv工具校验
(1)RMAN> backup validate datafile 2；
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
2 412 1 0 FRACTURED
(2)[ora10205@mcdbatest bdump]$ dbv file=/ora10205/oradata/ora10205/users02.dbf
DBVERIFY: Release 10.2.0.5.0 – Production on Tue May 14 01:28:52 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY – Verification starting : FILE = /ora10205/oradata/ora10205/users02.dbf
Page 412 is influx – most likely media corrupt
Corrupt block relative dba: 0×0080019c (file 2, block 412)
Fractured block found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0×0080019c
last change scn: 0×0b9d.47c004c5 seq: 0×2 flg: 0×04
spare1: 0×0 spare2: 0×0 spare3: 0×0
consistency value in tail: 0×040000a2
check value in block header: 0xce5
computed block checksum: 0×0
DBVERIFY – Verification complete
Total Pages Examined : 2560
Total Pages Processed (Data) : 2433
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 64
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 62
Total Pages Marked Corrupt : 1
Total Pages Influx : 1
Highest block SCN : 1203805782 (2973.1203805782)
(3)
analyze table test.parameter validate structure;
–恢复坏块（rman，或者直接表空间数据文件恢复）
RMAN> blockrecover datafile 2 block 412
2> ;
（RMAN> blockrecover corruption list; 该命令recover 的所有block 来自v$database_block_corruption视图。）
Starting blockrecover at 14-MAY-13
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00002
channel ORA_DISK_1: reading from backup piece /home/ora10205/backup/users02o9ione_1_1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/home/ora10205/backup/users02o9ione_1_1 tag=TAG20130514T003142
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished blockrecover at 14-MAY-13
–验证恢复
SQL>select * from test.parameter
NAME NUM
—————————————- ———-
dg_broker_config_file2 1458
olap_page_pool_size 1469
asm_diskstring 1552