一，介绍
DBV(DBVERIFY)是Oracle提供的一个命令行工具，它可以对数据文件物理和逻辑两种一致性检查。但是这个工具不会检查索引记录和数据记录的匹配关系，这种检查必须使用analyze validate structure命令。
这个工具有如下特点：
- 以只读的方式打开数据文件，在检查过程中不会修改数据文件的内容。
- 可以在线检查数据文件，而不需要关闭数据库。
- 不能检查控制文件和日志文件，只能检查数据文件。
- 这个工具可以检查ASM文件，但数据库必须Open状态，并且需要通过USERID指定用户，比如：dbv file=+DG1/ORCL/datafile/system01.dbf userid=system/sys
- 在许多UNIX平台下，DBV要求数据文件有扩展名，如果没有可以通过建立链接的方法，然后对链接的方法，然后对链接文件进行操作，比如：ls -n /dev/rdsk/mydevice /tmp/mydevice.dbf
- 某些平台，DBV工具不能检查超过2GB的文件，如果碰到DBV-100错误，请先检查文件大小，MOS Bug 710888对这个问题有描述。
- DBV只会检查数据块的正确性，但不会关系数据块是否属于哪个对象。
- 对于祼设备建议指定END参数，避免超出数据文件范围。比如：dbv FILE=/dev/rdsk/r1.dbf END=<last_block_number>。可以在v$datafile视图中用bytes字段除以块大小来获得END值。
参数 | 含义 | 缺省值 |
FILE | 要检查的数据文件名 | 没有缺省值 |
START | 检查起始数据块号 | 数据文件的第一个数据块 |
END | 检查的最后一个数据块号 | 数据文件的最后一个数据块 |
BLOCKSIZE | 数据块大小，这个值要和数据库的DB_BLOCK_SIZE参数值一致 | 缺省值8192 |
LOGFILE | 检查结果日志文件 | 没有缺省值 |
FEEDBAK | 显示进度 | 0 |
PARFILE | 参数文件名 | 没有缺省值 |
USERID | 用户名、密码 | 没有缺省值 |
SEGMENT_ID | 段ID，参数格式<tsn.segfile.segblock> | 没有缺省值 |
二，简单使用
[oracle@oracle01 oracle01]$ dbv file=test01.dbf
--最好是绝对路径，这里是进入到对应目录下，所以用相对路径
DBVERIFY: Release 11.2.0.4.0 - Production on Mon May 13 15:21:42 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/oracle01/test01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 1280 --( 检查总页数)
Total Pages Processed (Data) : 5 --(处理的总页数（数据）)
Total Pages Failing (Data) : 0 --(总页数失败（数据）)
Total Pages Processed (Index): 0 --(处理的总页数（索引）)
Total Pages Failing (Index): 0 --(总页面失败（索引）)
Total Pages Processed (Other): 136 --(处理的总页数（其他）)
Total Pages Processed (Seg) : 0 --(处理的总页数（Seg）)
Total Pages Failing (Seg) : 0 --(总页数失败（Seg)
Total Pages Empty : 1139 --(总页数空)
Total Pages Marked Corrupt : 0 --(总页数标记为损坏)
Total Pages Influx : 0 --(总页面数量)
Total Pages Encrypted : 0 --(加密总页数)
Highest block SCN : 11638862 (0.11638862) --(最高块SCN)
这个工具报告使用的是page作为单位，含义和data block相同。从上面的检查结果Total Pages Marked Corrupt : 0可以看出文件没有坏块。
除了检查数据文件，这个工具还允许检查单独的Segment，这时参数值的格式为<tsn.segfile.segblock>
查看对象的tsn,segfile,segblock属性：
SQL> select t.ts#,s.header_file,s.header_block
from v$tablespace t,dba_segments s
where s.segment_name='T'
and t.name=s.tablespace_name; 2 3 4
0 1 96544
从上面的查询结果可行参数值为0.1.96544。检查Segment：
[oracle@oracle01 oracle01]$ dbv userid=system/123456 segment_id=0.1.96544
DBVERIFY: Release 11.2.0.4.0 - Production on Mon May 13 15:27:53 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : SEGMENT_ID = 0.1.96544
DBVERIFY - Verification complete
Total Pages Examined : 2
Total Pages Processed (Data) : 1
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 0
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 0
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 11645088 (0.11645088)
三，创建坏块
创建数据：
SQL> create table bbed (id number,name varchar2(20)) tablespace TT1;
Table created.
SQL> insert into bbed values(1,'zhaoxu');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into bbed values(1,'kingle');
1 row created.
[oracle@oracle01 oracle01]$ dbv file=test01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Mon May 13 15:40:14 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/oracle01/test01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 1280
Total Pages Processed (Data) : 5
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 136
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1139
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 11638862 (0.11638862)
寻找数据块位置：
SQL> select id,name,dbms_rowid.rowid_relative_fno(rowid)file#,dbms_rowid.rowid_block_number(rowid) block# from bbed;
1 zhaoxu 6 157
1 kingle 6 157
BBED连接数据库
[oracle@oracle01 BBED]$ bbed password=blockedit parfile=par.bbd
BBED: Release 2.0.0.0.0 - Limited Production on Mon May 13 15:47:32 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set filename '/u01/app/oracle/oradata/oracle01/test01.dbf'
FILENAME /u01/app/oracle/oradata/oracle01/test01.dbf
BBED> map
File: /u01/app/oracle/oradata/oracle01/test01.dbf (6)
Block: 1 Dba:0x01800001
------------------------------------------------------------
Data File Header
struct kcvfh, 860 bytes @0
ub4 tailchk @8188
BBED> show all
FILE# 6
BLOCK# 1
OFFSET 0
DBA 0x01800001 (25165825 6,1)
FILENAME /u01/app/oracle/oradata/oracle01/test01.dbf
BIFILE bifile.bbd
LISTFILE bbed.txt
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
BBED> map
File: /u01/app/oracle/oradata/oracle01/test01.dbf (6)
Block: 1 Dba:0x01800001
------------------------------------------------------------
Data File Header
struct kcvfh, 860 bytes @0
ub4 tailchk @8188
BBED> set dba 6,157
DBA 0x0180009d (25165981 6,157)
BBED> find /c kingle --这就是我们找到的字符位置
File: /u01/app/oracle/oradata/oracle01/test01.dbf (6)
Block: 157 Offsets: 8169 to 8191 Dba:0x0180009d
------------------------------------------------------------------------
6b696e67 6c652c01 0202c102 067a6861 6f787501 06c3cd
<32 bytes per line>
更改数据：
BBED> dump /v dba 6,157 offset 8169 count 32
File: /u01/app/oracle/oradata/oracle01/test01.dbf (6)
Block: 157 Offsets: 8169 to 8191 Dba:0x0180009d
-------------------------------------------------------
6b696e67 6c652c01 0202c102 067a6861 l kingle,...????.zha
6f787501 06c3cd l oxu..č
<16 bytes per line>
BBED> modify 100 dba 6,157;
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File: /u01/app/oracle/oradata/oracle01/test01.dbf (6)
Block: 157 Offsets: 8169 to 8191 Dba:0x0180009d
------------------------------------------------------------------------
64696e67 6c652c01 0202c102 067a6861 6f787501 06c3cd
<32 bytes per line>
BBED> dump /v dba 6,157 offset 8169 count 32
File: /u01/app/oracle/oradata/oracle01/test01.dbf (6)
Block: 157 Offsets: 8169 to 8191 Dba:0x0180009d
-------------------------------------------------------
64696e67 6c652c01 0202c102 067a6861 l dingle,...????.zha
6f787501 06c3cd l oxu..č
<16 bytes per line>
BBED> exit
查看数据块：
[oracle@oracle01 oracle01]$ dbv file=test01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Mon May 13 15:54:14 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/oracle01/test01.dbf
Page 157 is marked corrupt
Corrupt block relative dba: 0x0180009d (file 6, block 157)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x0180009d
last change scn: 0x0000.00b1cdc3 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xcdc30601
check value in block header: 0x81c0
computed block checksum: 0xf00
DBVERIFY - Verification complete
Total Pages Examined : 1280
Total Pages Processed (Data) : 9
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 139
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1131
Total Pages Marked Corrupt : 1 -- ！！！！！！！！！！--表示存在坏块
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 11652516 (0.11652516)
[oracle@oracle01 oracle01]$
有问题了已经。
查询数据验证问题：
SQL> select * from bbed;
1 zhaoxu
1 kingle
--查询正常，因为在buffer_cache中缓存了块，而修改的是文件中的块。两个块现在不一致，清空buffer cache后再次查询测试表。
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 # 6, block # 157)
ORA-01110: data file 6: '/u01/app/oracle/oradata/oracle01/test01.dbf'
校验坏块：
SQL> analyze table bbed validate structure cascade online
2 ;
analyze table bbed validate structure cascade online
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 157)
ORA-01110: data file 6: '/u01/app/oracle/oradata/oracle01/test01.dbf'
rman校验坏块：
[oracle@oracle01 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon May 13 16:04:06 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORACLE01 (DBID=2594375557)
RMAN> backup check logical validate database;
Starting backup at 13-MAY-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=149 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=18 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=153 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=30 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=152 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=20 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=125 device type=DISK
allocated channel: ORA_DISK_9
channel ORA_DISK_9: SID=29 device type=DISK
allocated channel: ORA_DISK_10
channel ORA_DISK_10: SID=146 device type=DISK
allocated channel: ORA_DISK_11
channel ORA_DISK_11: SID=26 device type=DISK
allocated channel: ORA_DISK_12
channel ORA_DISK_12: SID=154 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/oracle01/sysaux01.dbf
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/oracle01/system01.dbf
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/oracle01/example01.dbf
channel ORA_DISK_4: starting compressed full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/oracle01/undotbs01.dbf
channel ORA_DISK_5: starting compressed full datafile backup set
channel ORA_DISK_5: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_6: starting compressed full datafile backup set
channel ORA_DISK_6: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/oracle01/test01.dbf
channel ORA_DISK_7: starting compressed full datafile backup set
channel ORA_DISK_7: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/oracle01/test101.dbf
channel ORA_DISK_8: starting compressed full datafile backup set
channel ORA_DISK_8: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/oracle01/users01.dbf
channel ORA_DISK_9: starting compressed full datafile backup set
channel ORA_DISK_9: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_9: backup set complete, elapsed time: 00:00:00
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
channel ORA_DISK_5: backup set complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
Control File OK 0 2166
channel ORA_DISK_7: backup set complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 0 1142 1280 10234981
File Name: /u01/app/oracle/oradata/oracle01/test101.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 5
Index 0 0
Other 0 133
channel ORA_DISK_8: backup set complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 18 673 4459656
File Name: /u01/app/oracle/oradata/oracle01/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 91
Index 0 44
Other 0 487
channel ORA_DISK_6: backup set complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 FAILED 0 1131 1280 11652516
File Name: /u01/app/oracle/oradata/oracle01/test01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 1 10
Index 0 0
Other 0 139
validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/oracle01_oracle01/oracle01/trace/oracle01_ora_32029.trc for details
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:27
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 33725 44321 10634562
File Name: /u01/app/oracle/oradata/oracle01/example01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 6602
Index 0 1149
Other 0 2844
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:37
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 1 37120 11655690
File Name: /u01/app/oracle/oradata/oracle01/undotbs01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 37119
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:57
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 23694 144688 11655688
File Name: /u01/app/oracle/oradata/oracle01/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 49306
Index 0 41475
Other 0 30165
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:57
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 15655 103751 11655690
File Name: /u01/app/oracle/oradata/oracle01/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 65027
Index 0 13867
Other 0 9131
Finished backup at 13-MAY-19
RMAN> backup check logical validate datafile 6;
Starting backup at 13-MAY-19
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
using channel ORA_DISK_9
using channel ORA_DISK_10
using channel ORA_DISK_11
using channel ORA_DISK_12
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/oracle01/test01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 FAILED 0 1131 1280 11652516
File Name: /u01/app/oracle/oradata/oracle01/test01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 1 10
Index 0 0
Other 0 139
validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/oracle01_oracle01/oracle01/trace/oracle01_ora_32010.trc for details
Finished backup at 13-MAY-19
RMAN>
------RMAN的检查结果放在v$database_block_corruption
SQL> select file#,block#,blocks from v$database_block_corruption;
6 157 1
BBED验证：
[oracle@oracle01 BBED]$ bbed password=blockedit parfile=par.bbd
BBED: Release 2.0.0.0.0 - Limited Production on Mon May 13 16:10:39 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> verify dba 6,157;
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/oracle01/test01.dbf
BLOCK = 157
Block 157 is corrupt
Corrupt block relative dba: 0x0180009d (file 0, block 157)
Bad check value found during verification
Data in bad block:
type: 6 format: 2 rdba: 0x0180009d
last change scn: 0x0000.00b1cdc3 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xcdc30601
check value in block header: 0x81c0
computed block checksum: 0xf00
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 : 0
Message 531 not found; product=RDBMS; facility=BBED
EXPDP验证：
（需要备份文件）
--格式
exp system/123456 file=/home/oracle/ltest.dmp tables=bbed
需要开始对单表备份才能操作出结果
四，坏块恢复：
有备份的情况下：
RMAN> blockrecover datafile 6 block 157 from backupset;
Starting recover at 13-MAY-19
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
using channel ORA_DISK_9
using channel ORA_DISK_10
using channel ORA_DISK_11
using channel ORA_DISK_12
searching flashback logs for block images
finished flashback log search, restored 0 blocks
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/13/2019 16:32:13
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 6 found to restore
RMAN-06023: no backup or copy of datafile 6 found to restore
没有备份：
查询定位坏块：
SQL> select segment_name,partition_name,segment_type,owner,tablespace_name
from sys.dba_extents
where file_id=&AFN
and &bad_block_id between block_id and block_id + blocks-1; 2 3 4
Enter value for afn: 6
old 3: where file_id=&AFN
new 3: where file_id=6
Enter value for bad_block_id: 157
old 4: and &bad_block_id between block_id and block_id + blocks-1
new 4: and 157 between block_id and block_id + blocks-1
SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME SEGMENT_TYPE OWNER
------------------------------ ------------------ ------------------------------
TABLESPACE_NAME
------------------------------
BBED
TABLE SYS
TT1
----算了跳过坏块吧
重建
