简介
Oracle 数据库运行过程中由于服务器硬件故障或操作系统故障导致Oracle无法识别的数据块损坏。坏块可以分为介质损坏（物理坏块）和逻辑坏块。
块检查
何时检查数据块
当一个数据块被读取或写的时候，将对块的进行一致性检查。
数据块一致性检查的内容
- 块的版本
- 比较块在cache和buffer cache中的数据块地址
- 根据要求进行检验(checksum)
与块损坏相关的属性和工具
| 特性 | 坏块检查类型 | 修复坏块 |
|---|---|---|
| dbverify | 物理坏块 | 否 |
| analyze | 逻辑坏块 | 否 |
| db_block_checking | 逻辑 | 否 |
| db_block_checksum | 物理 | 否 |
| exp/expdb | 物理 | 否 |
| flashback | 逻辑 | 是 |
| dbms_repair | 逻辑 | 是 |
| block media recovery | 是 | |
| bbed(verify命令) | 否 | |
| rman(validate命令) |
DBVERIFY工具
DBVERIFY(DBV) ：只能用于检查 Oracle 数据文件或rman备份的镜像副本的工具，不能校验redo log files 和 控制文件，归档日志，rman备份集。用于检查数据文件是否损坏，是否存在逻辑坏块及数据文件中包含何种数据类型的数据。数据文件的可以是ONLINE或OFFLINE。
用法
$ORACLE_HOME/bin/dbv help=y
# %ORACLE_HOME%/bin/dbv.exe
DBVERIFY: Release 11.2.0.4.0 - Production on Fri Aug 6 11:35:43 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Keyword Description (Default)
----------------------------------------------------
FILE File to Verify (NONE)
START Start Block (First Block of File)
END End Block (Last Block of File)
BLOCKSIZE Logical Block Size (8192)
LOGFILE Output Log (NONE)
FEEDBACK Display Progress (0)
PARFILE Parameter File (NONE)
USERID Username/Password (NONE)
SEGMENT_ID Segment ID (tsn.relfile.block) (NONE)
HIGH_SCN Highest Block SCN To Verify (NONE)
(scn_wrap.scn_base OR scn)
示例
校验online，offline数据文件
[oracle@progs ~]$ $ORACLE_HOME/bin/dbv file=/ups/data/oradata/O19C/users01.dbf
DBVERIFY: Release 19.0.0.0.0 - Production on Fri Aug 6 11:38:50 2021
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /ups/data/oradata/O19C/users01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 640 # 校验的总页面数，一个页面即是一个数据块
Total Pages Processed (Data) : 60 # 已处理的数据页面数
Total Pages Failing (Data) : 0 # 已处理数据页面的失败数
Total Pages Processed (Index): 15 # 已处理的索引页面数
Total Pages Failing (Index): 0 # 已处理索引页面失败数
Total Pages Processed (Other): 464 # 已处理的其它页面数
Total Pages Processed (Seg) : 0 #
Total Pages Failing (Seg) : 0
Total Pages Empty : 101
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1252497 (0.1252497)
[oracle@progs ~]$
校验指定段（表）
先获得段所在表空间的ID，段所在数据文件的ID，段的头部ID
-- 先获取segment_id
select owner,tablespace_id,tablespace_name,header_file,header_block, tablespace_id ||'.' || header_file || '.' || header_block segment_id from sys_dba_segs where segment_name='JOBS';
$ORACLE_HOME/bin/dbv USERID=scott/tiger segment_id=4.4.1082
验证复制的数据文件或验证备份的镜像副本
使用RMAN备份镜像副本
RMAN> backup as copy datafile 5;
$ORACLE_HOME/bin/dbv file='/ups/app/oracle/o11g/database/11.2/db_1/dbs/data_D-O11G_I-490532280_TS-SYSAUX_FNO-2_0205r7th'
测试校验online redo logfile
$ORACLE_HOME/bin/dbv file='/ups/data/oradata/o11g/redo01.log' BLOCKSIZE=512
提示一堆介质错误信息，不支持检验redo日志文件
测试校验控制文件
$ORACLE_HOME/bin/dbv file='/ups/data/oradata/o11g/control01.ctl' BLOCKSIZE=16384
附录
参考文档
DBVERIFY - Database file Verification Utility (Doc ID 35512.1)
Introduction
~~~~~~~~~~~~
This article describes the basic details of the DBVERIFY (or DBV)
utility which can be used to check Oracle datafiles for signs of
corruption. The article gives summary details of how to use
DBV and gives an indication of what output to expect, along with
notes on how to interpret the output. There is also an example at the
end of the article.
Availability
~~~~~~~~~~~~
The DBV utility is supplied with Oracle7 release 7.3.2 onwards and
with all Oracle8 / 8i releases.
DBV can be used against data files from earlier Oracle releases
but it must be executed from the ORACLE_HOME environment in which it
is installed - you CANNOT just copy the executable about.
Eg: DBVERIFY 7.3 can check Oracle 7.1 data files provided it is run
from the 7.3 $ORACLE_HOME
Purpose
~~~~~~~
DBV checks Oracle datafiles to ensure that:
- The datafile has a valid header
- Each datablock in the file has a special "wrapper" which identifies
the block - this "wrapper" is checked for correctness
- DATA (TABLE) and INDEX blocks are internally consistent
- From 8.1.6 onwards: That various other block types are internally
consistent (such as rollback segment blocks)
The tool can be used to give some degree of confidence that a
datafile is free from corruption. It opens files in a read only mode
and so cannot change the contents of the file being checked.
Usage
~~~~~
DBV can be run against datafiles which are currently opened by a
database instance - there is no need to shutdown the database.
Datafiles are opened read-only by DBV so it cannot corrupt
the contents. There was a bug on 8.0.4 where DBV could not be used
on opened datafiles on Windows NT but that bug was fixed on 8.1.6.
Bug:727547
Unix:
Any release: dbv FILE=filename [options]
Windows NT:
7.3: DBVERF73 FILE=filename [options]
8.0: DBVERF80 FILE=filename [options]
8.1: DBV FILE=filename [options]
VMS:
In versions less than 9.2.0 DBV cannot be used on VMS systems against files which are currently
opened by an instance.
Any release: DBV FILE=filename [options]
MVS:
7.3: Does not exist
8.0/8.1: DBV FILE=/DSN/filename
Options:
Keyword Description Meaning
--------- ------------------ -----------------
FILE File to Verify This is the name of the file to verify.
See "Limitations" below if your datafile
name has no suffix.
START Start Block This is the first datablock to check in
the file. This defaults to the first
block in the file and need only be
specified if you want to check just
a portion of a given file.
END End Block This is the last datablock to check in the
file. This defaults to the last block of
the file but may need specifying for RAW
devices (See "Limitations" below)
BLOCKSIZE Logical Block Size This is the database block size of the
datafile you wish to scan. The value
defaults to "2048".
This parameter must be set to the
DB_BLOCK_SIZE of the datafile to be
scanned.
LOGFILE Output Log This is the name of file to output the
results to. The default is "NONE" and
output is sent to terminal.
FEEDBACK Display Progress If set to a value above 0 (the default)
then DBV outputs a "." for every N pages
of the datafile checked. This is useful
to see that DBV is working through the
file.
PARFILE Parameter file Parameters can be specified in a
parameter file and PARFILE used to cause
the file contents to be used as input
parameters. The PARFILE can contain any
of the above options.
HIGH_SCN Scn Highest Block SCN To Verify
(scn_wrap.scn_base OR scn)
Find the blocks exceeding the SCN.
Available in version 9.2.0.6 and above.
USERID Username/Password If the file you are verifying is an
Automatic Storage Management (ASM) file,
you must supply a USERID. This is because
DBVERIFY needs to connect to an Oracle
instance to access ASM files.
SEGMENT_ID TS#.FILE#.BLOCK# Specifies the segment that you want to verify.
For more info, review Note:139962.1
For help on command line parameters in a given version type
"dbv help=y" at the command line.
Limitations and Special Notes
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- As DBV performs checks at a block level it cannot detect problems
such as INDEX versus TABLE mismatches which can be detected by the
'ANALYZE TABLE .. VALIDATE STRUCTURE CASCADE' command.
- This utility can ONLY be used against DATA files.
It CANNOT be used to verify redo log files or control files.
- You can use DBV to verify an Automatic Storage Management (ASM) file.
However, the database must be opened and the option USERID has to be used
Example : dbv file=+DG1/ORCL/datafile/system01.dbf userid=system/sys
DBV checks the userid/password for ASM managed files, which is not possible when database is not open.
- On most releases on Unix DBV expects a filename extension.
This means that DBV cannot be used against datafiles with no
filename suffix, or against RAW devices.
The workaround is to create a symbolic link to the raw device where
the link name MUST have an extension.
Eg: ln -s /dev/rdsk/mydevice /tmp/mydevice.dbf
Now use DBV against /tmp/mydevice.dbf
- For RAW devices you should use the END parameter to avoid running
off the end of the Oracle file space.
eg: "dbv FILE=/dev/rdsk/r1.dbf END=<last_block_number>"
If you get the END value too high DBV can report the last page/s of the
file as corrupt as these are beyond the end of the Oracle portion of
the raw device.
You can find value for END from the V$DATAFILE view by dividing the
BYTES value by the database block size.
Eg: To find out the END value to use for file#=5:
SVRMGRL> show parameter db_block_size
NAME TYPE VALUE
----------------------------------- ------- ------
db_block_size integer 2048
SVRMGRL> select BYTES/2048 from v$datafile where FILE#=5;
BYTES/2048
----------
5120
So the command would be:
dbv file=/dev/rdsk/r1.dbf blocksize=2048 END=5120
- DBV may not be able to scan datafiles larger than 2Gb and
may report "DBV-100". This is reported in Bug:710888 for Unix and
Bug:1372172 for 8.1.6 on NT. This problem is platform and release
specific so if you get DBV-100 errors check the filesize first.
- DBV from 8.1.6 onwards may report spurious errors for rollback segment
blocks if the database has been migrated from Oracle7. See Bug:1359160
and Note:118008.1.
- DBV only checks a block in isolation - it does not know if the block
is part of an existing object or not.
- DBV is broken on SCO Unix - see Bug:814249
- DBV of a lower version should not be used against a higher DB version.
- Running DBV on Standby database in MOUNT mode will fail with:
DBV-00111: OCI failure (4159) (ORA-00604: error occurred at recursive SQL level 1
ORA-01219: database not open: queries allowed on fixed tables/views only
ORA-06512: at "SYS.X$DBMS_DBVERIFY", line 22
As DBV needs the database to be open.