About DBV Files
The Database Verify utility (dbv) provides a mechanism to validate the structure of Oracle data files at the operating system level. It should be used on a regular basis to inspect data files for signs of corruption.
Oracle Utilities Tips |
Automating dbv
DBAs should automate and execute the dbv utility on a regular basis. The following shell script (dbv.ksh) prompts for Oracle environment information, connects to the database, and produces a command file that can be executed at the convenience of the DBA. In this script, dbv is executed immediately after it’s generated.
The dbv.ksh script formats a dbv command that can be executed from the UNIX command line. The logfile for the script is dbv. ${ORACLE_SID}. The results of the SQL statement are placed in the dbv.cmd file and this file is executed at the end of the script. Notice that a feedback was specified equivalent to one dot per each 10 percent of the file processed, in order to provide a status of dbv.
The contents of the dbv.cmd file are:
$ cat dbv.cmd
dbv file=/usr/oracle/asg920xr/datafiles/ASG920xrsys.dbf blocksize=8192 feedback=3200
dbv file=/usr/oracle/asg920xr/datafiles/undo.dbf blocksize=8192 feedback=1088
dbv file=/usr/oracle/asg920xr/datafiles/ASG920xray.dbf blocksize=8192 feedback=3200
dbv file=/usr/oracle/asg920xr/datafiles/aaa/UNDO1.dbf blocksize=8192 feedback=124
dbv file=/usr/oracle/asg920xr/datafiles/bbb/UNDO2.dbf blocksize=8192 feedback=26
dbv file=/usr/oracle/asg920xr/datafiles/ccc/UNDO3.dbf blocksize=8192 feedback=38
dbv file=/usr/oracle/asg920xr/datafiles/ddd/UNDO4.dbf blocksize=8192 feedback=51
dbv file=/usr/oracle/asg920xr/datafiles/aaa/UNDO5.dbf blocksize=8192 feedback=64
dbv file=/usr/oracle/asg920xr/datafiles/zzz/UNDO6.dbf blocksize=8192 feedback=13
dbv file=/usr/oracle/asg920xr/datafiles/aaa/undo_all1.dbf blocksize=8192 feedback=576
dbv file=/usr/oracle/asg920xr/datafiles/bbb/undo_all2.dbf blocksize=8192 feedback=26
dbv file=/usr/oracle/asg920xr/datafiles/ccc/undo_all3.dbf blocksize=8192 feedback=499
dbv file=/usr/oracle/asg920xr/datafiles/ddd/undo_all4.dbf blocksize=8192 feedback=602
dbv file=/usr/oracle/asg920xr/datafiles/aaa/undo_all5.dbf blocksize=8192 feedback=614
dbv file=/usr/oracle/asg920xr/datafiles/zzz/undo_all6.dbf blocksize=8192 feedback=13
dbv file=/data1/dbxray/datafiles/undo_all7.dbf blocksize=8192 feedback=602
dbv file=/data1/dbxray /datafiles/undo_tablespace_long2.dbf blocksize=8192 feedback=166
dbv file=/usr/oracle/asg920xr/datafiles/symbolic/UNDO8.dbf blocksize=8192 feedback=13
dbv file=/usr/oracle/asg920xr/datafiles/zzz/UNDO6a.dbf blocksize=8192feedback=1
dbv file=/usr/oracle/asg920xr/datafiles/davetest.dbf blocksize=8192 feedback=26
$
Notice in the dbv.cmd file above that the block_size is included for each datafile. In Oracle versions 8.1.7 and below, the following command would indicate the block size since it had to be consistent across the database.
SQL> show parameter db_block_size NAME TYPE VALUE --------------------------- ----------- ------- db_block_size integer 8192
In version 9, each tablespace can have it’s own block size and therefore it must be included at the datafile level.
The result of the execution of the dbv.cmd file is:
dbvERIFY: Release 9.2.0.1.0 - Production on Sun Dec 29 19:15:55 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. dbvERIFY - Verification starting : FILE = /usr/oracle/asg920xr/datafiles/ASG920xrsys.dbf .......... dbvERIFY - Verification complete Total Pages Examined : 32000 Total Pages Processed (Data) : 16164 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 2520 Total Pages Failing (Index): 0 Total Pages Processed (Other): 1230 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 12086 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 dbvERIFY: Release 9.2.0.1.0 - Production on Sun Dec 29 19:16:06 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. dbvERIFY - Verification starting : FILE = /usr/oracle/asg920xr/datafiles/undo.dbf .......... dbvERIFY - Verification complete
Notice the 10 dots displayed for each datafile as it was processed. Everything looks good in this output; no pages are marked as corrupt.
The above is an excerpt from Oracle Utilities - Using Hidden Programs, Import/Export, SQL Loader, oradebug, Dbverify, Tkprof and More by Rampant TechPress (Dave Moore).
It’s only $19.95 and you can order the book and get instant access to the online Oracle utilities scripts
.