环境:
OS:Centos 6.9
DB:11.2.0.4
1.创建表
connect hxl/oracle
create table bbed (id number,name varchar2(20)) tablespace TPS_HXL;
SQL> insert into bbed values(1,'zhaoxu');
SQL> commit;
SQL> insert into bbed values(1,'kingle');
SQL> commit;
2.寻找数据块位置
SQL> select id,name,dbms_rowid.rowid_relative_fno(rowid)file#,dbms_rowid.rowid_block_number(rowid) block# from bbed;
ID NAME FILE# BLOCK#
---------- -------------------- ---------- ----------
1 zhaoxu 5 168327
1 kingle 5 168327
3.生成listfile
listfile 文件每行的格式为：file_id file_name file_bytes，如下
vi /home/oracle/my_listfile.txt
1 /u01/app/oracle/oradata/ora11g/system01.dbf 786432000
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 587202560
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 519045120
4 /u01/app/oracle/oradata/ora11g/users01.dbf 5242880
5 /u01/app/oracle/oradata/ora11g/tps_hxl01.dbf 1457520640
6 /u01/app/oracle/oradata/ora11g/tps_goldengate01.dbf 104857600
可以使用SQL语句生成
select file#||' '||name||' '||bytes from v$datafile;
4.准备bbed.par文件
[oracle@node3 ~]$ vim /home/oracle/bbed.par
listfile=/home/oracle/my_listfile.txt
mode=edit
5.使用BBED
[oracle@ora11g ~]$ bbed password=blockedit parfile=/home/oracle/bbed.par
BBED: Release 2.0.0.0.0 - Limited Production on Wed Sep 13 09:19:49 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set filename '/u01/app/oracle/oradata/ora11g/tps_hxl01.dbf'
FILENAME /u01/app/oracle/oradata/ora11g/tps_hxl01.dbf
BBED> map
File: /u01/app/oracle/oradata/ora11g/system01.dbf (1)
Block: 1 Dba:0x00400001
------------------------------------------------------------
Data File Header
struct kcvfh, 860 bytes @0
ub4 tailchk @8188
BBED> show all
FILE# 1
BLOCK# 1
OFFSET 0
DBA 0x00400001 (4194305 1,1)
FILENAME /u01/app/oracle/oradata/ora11g/system01.dbf
BIFILE bifile.bbd
LISTFILE /home/oracle/my_listfile.txt
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
6.定位到数据行对应的文件和文件块
BBED> set dba 5,168327
DBA 0x01429187 (21139847 5,168327)
BBED> find /c kingle --这就是我们找到的字符位置
File: /u01/app/oracle/oradata/ora11g/tps_hxl01.dbf (5)
Block: 168327 Offsets: 8169 to 8191 Dba:0x01429187
------------------------------------------------------------------------
6b696e67 6c652c01 0202c102 067a6861 6f787501 062b8e
<32 bytes per line>
更改数据
BBED> dump /v dba 5,168327 offset 8169 count 32
File: /u01/app/oracle/oradata/ora11g/tps_hxl01.dbf (5)
Block: 168327 Offsets: 8169 to 8191 Dba:0x01429187
-------------------------------------------------------
6b696e67 6c652c01 0202c102 067a6861 l kingle,...á..zha
6f787501 062b8e l oxu..+.
<16 bytes per line>
BBED> modify 100 dba 5,168327;
File: /u01/app/oracle/oradata/ora11g/tps_hxl01.dbf (5)
Block: 168327 Offsets: 8169 to 8191 Dba:0x01429187
------------------------------------------------------------------------
64696e67 6c652c01 0202c102 067a6861 6f787501 062b8e
<32 bytes per line>
BBED> dump /v dba 5,168327 offset 8169 count 32
File: /u01/app/oracle/oradata/ora11g/tps_hxl01.dbf (5)
Block: 168327 Offsets: 8169 to 8191 Dba:0x01429187
-------------------------------------------------------
64696e67 6c652c01 0202c102 067a6861 l dingle,...á..zha
6f787501 062b8e l oxu..+.
<16 bytes per line>
7.查看是否有坏块
[oracle@ora11g rmanbak]$ dbv file=/u01/app/oracle/oradata/ora11g/tps_hxl01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Wed Sep 13 08:45:12 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/ora11g/tps_hxl01.dbf
Page 168327 is marked corrupt
Corrupt block relative dba: 0x01429187 (file 5, block 168327)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x01429187
last change scn: 0x0000.002d8e2b seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x8e2b0601
check value in block header: 0xc2b4
computed block checksum: 0xf00
DBVERIFY - Verification complete
Total Pages Examined : 177920
Total Pages Processed (Data) : 143369
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 15260
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1165
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 18125
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 2985849 (0.2985849)
8.校验
RMAN> backup validate datafile 5;
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5 168327 1 0 CHECKSUM
SQL> select * from bbed;
ID NAME
---------- --------------------
1 zhaoxu
1 kingle
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from bbed;
select * from bbed
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 168327)
ORA-01110: data file 5: '/u01/app/oracle/oradata/ora11g/tps_hxl01.dbf
发现无法访问了
校验块
SQL> analyze table bbed validate structure cascade online;
analyze table bbed validate structure cascade online
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 168327)
ORA-01110: data file 5: '/u01/app/oracle/oradata/ora11g/tps_hxl01.dbf'
10.恢复坏块
RMAN>recover datafile 5 block 168327;