1、DBVERIFY语法及使用
DBVERIFY工具的主要目的是为了检查数据文件的物理结构,包括数据文件是否损坏,是否存在逻辑坏块,以及数据文件中包含何种类型的数据。DBVERIFY工具可以验证ONLINE或OFFLINE的数据文件。不管数据库是否打开,都可以访问数据文件。DBVERIFY不能够检查控制文件和redo日志文件。DBVERIFY检查数据库文件是以只读模式去检查数据文件不会对数据造成损坏。dbv也可以验证数据文件的备份(仅限拷贝)。这个拷贝指的是通过RMAN的COPY命令或者操作系统命令cp拷贝的数据文件,而不是RMAN生成的备份集格式。
dbv检查数据库文件是否存在坏块。
[oracle@server~]$ dbv file=/home/oracle/orabase/oradata/ORACLE/users01.dbf blocksize=8192
DBVERIFY:Release 10.2.0.1.0 - Production on Wed Aug 28 10:01:16 2013
Copyright(c) 1982, 2005, Oracle. All rightsreserved.
DBVERIFY- Verification starting : FILE =/home/oracle/orabase/oradata/ORACLE/users01.dbf
DBVERIFY- Verification complete
TotalPages Examined : 640
TotalPages Processed (Data) : 0
TotalPages Failing (Data) : 0
TotalPages Processed (Index): 0
TotalPages Failing (Index): 0
TotalPages Processed (Other): 8
TotalPages Processed (Seg) : 0
TotalPages Failing (Seg) : 0
TotalPages Empty : 632
TotalPages Marked Corrupt : 0
TotalPages Influx : 0
Highestblock SCN : 10662 (0.10662)
[oracle@server~]$
dbv检查数据库备份是否存在坏块。
[oracle@serverORACLE]$ rman target /
RecoveryManager: Release 10.2.0.1.0 - Production on Wed Aug 28 09:54:16 2013
Copyright(c) 1982, 2005, Oracle. All rightsreserved.
connectedto target database: ORACLE (DBID=1692376488)
RMAN>BACKUP AS COPY DATAFILE'/home/oracle/orabase/oradata/ORACLE/users01.dbf' FORMAT'/home/oracle/users01.cpy';
Startingbackup at 28-AUG-13
usingtarget database control file instead of recovery catalog
allocatedchannel: ORA_DISK_1
channelORA_DISK_1: sid=307 devtype=DISK
channelORA_DISK_1: starting datafile copy
inputdatafile fno=00004 name=/home/oracle/orabase/oradata/ORACLE/users01.dbf
outputfilename=/home/oracle/users01.cpy tag=TAG20130828T095524 recid=2stamp=824637325
channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finishedbackup at 28-AUG-13
StartingControl File Autobackup at 28-AUG-13
piecehandle=/home/oracle/orabase/flash_recovery_area/ORACLE/autobackup/2013_08_28/o1_mf_n_824637326_91tp4g64_.bkpcomment=NONE
FinishedControl File Autobackup at 28-AUG-13
RMAN>exit
RecoveryManager complete.
[oracle@server]$ cd /home/oracle/
[oracle@server~]$ dbv file=users01.cpy blocksize=8192
DBVERIFY:Release 10.2.0.1.0 - Production on Wed Aug 28 09:56:23 2013
Copyright(c) 1982, 2005, Oracle. All rightsreserved.
DBVERIFY- Verification starting : FILE = users01.cpy
DBVERIFY- Verification complete
TotalPages Examined : 640
TotalPages Processed (Data) : 0
TotalPages Failing (Data) : 0
TotalPages Processed (Index): 0
TotalPages Failing (Index): 0
TotalPages Processed (Other): 8
TotalPages Processed (Seg) : 0
TotalPages Failing (Seg) : 0
TotalPages Empty : 632
TotalPages Marked Corrupt : 0
TotalPages Influx : 0
Highestblock SCN : 10662 (0.10662)
[oracle@server~]$
备注:
·Pages = Blocks
·Total Pages Examined = number ofblocks in the file
·Total Pages Processed = number ofblocks that were verified (formatted blocks)
·Total Pages Failing (Data) = numberof blocks that failed the data block checking routine
·Total Pages Failing (Index) = numberof blocks that failed the index block checking routine
·Total Pages Marked Corrupt = numberof blocks for which the cache header is invalid, thereby making it impossiblefor DBVERIFY to identify the block type
·Total Pages Influx = number ofblocks that are being read and written to at the same time. If the database isopen when DBVERIFY is run, DBVERIFY reads blocks multiple times to get aconsistent p_w_picpath. But because the database is open, there may be blocks thatare being read and written to at the same time (INFLUX). DBVERIFY cannot get aconsistent p_w_picpath of pages that are in flux.
可以看到,dbverify给出的结果包括数据文件包括的BLOCK数量,其中包括多少个数据块,多少个索引块,多少空块,多少个已经被标志为坏块的块,多少个坏块。
2、DBV工具可以检查制定段,仅能在数据库打开的情况下使用的
用法: dbv USERID=username/passwordSEGMENT_ID=tsn.segfile.segblock
验证指定段需要以sysdba权限查看段id号,查阅SYS_USER_SEGS
视图获取TABLESPACE_ID
,HEADER_FILE
, andHEADER_BLOCK
:
SQL>CONN /AS SYSDBA
已连接。
SQL> selectTABLESPACE_ID,HEADER_FILE, HEADER_BLOCKfrom sys_dba_segs where segment_name='DIAGNOSES';
TABLESPACE_ID HEADER_FILE HEADER_BLOCK
------------- ----------- ------------
9 9 611
这种方法需要查询TABLESPACE_ID、段头所在的HEADER_FILE和以及HEADER_BLOCK,要获取这个信息也可以通过SYS用户查询SYS_DBA_SEGS视图。需要注意的是,Oracle文档给出的SYS_USER_SEGS视图只能查询SYS用户的段,要查询普通用户的段信息,需要访问SYS_DBA_SEGS。如下:
[oracle@server~]$ dbv userid=admin/admin SEGMENT_ID=9.9.611
DBVERIFY:Release 10.2.0.1.0 - Production on Wed Aug 28 10:57:54 2013
Copyright(c) 1982, 2005, Oracle. All rightsreserved.
DBVERIFY- Verification starting : SEGMENT_ID = 9.9.611
DBVERIFY- Verification complete
TotalPages Examined : 8
TotalPages Processed (Data) : 5
TotalPages Failing (Data) : 0
TotalPages Processed (Index): 0
TotalPages Failing (Index): 0
TotalPages Processed (Other): 2
TotalPages Processed (Seg) : 1
TotalPages Failing (Seg) : 0
TotalPages Empty : 0
TotalPages Marked Corrupt : 0
TotalPages Influx : 0
Highestblock SCN : 2655202(0.2655202)
[oracle@pacsserver~]$
3、限制及特殊注意事项
(1)、dbv可以检查ASM文件,但是数据库必须处于打开状态且使用参数userid。
例如 : dbvfile=+DG1/ORCL/datafile/system01.dbf userid=system/sys
(2)、对于DBVERIFY工具,高版本可以自动识别低版本数据库,比如11g的dbv访问9i的数据库,但是低版本的dbv访问高版本会报错.
(3)、On most releases on Unix DBV expects a filename extension.
This means that DBV cannot be used againstdatafiles with no
filename suffix, or against RAW devices.
The workaround is to create a symbolic linkto 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
(4)、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.dbfEND=<last_block_number>"
If you get the END value too high DBV canreport the last page/s of the
file as corrupt as these are beyond the endof the Oracle portion of
the raw device.
You can find value for END from theV$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 parameterdb_block_size
NAME TYPE VALUE
------------------------------------------ ------
db_block_size integer 2048
SVRMGRL> select BYTES/2048 fromv$datafile where FILE#=5;
BYTES/2048
----------
5120
So the command would be:
dbv file=/dev/rdsk/r1.dbf blocksize=2048 END=5120