Call (800) 766-1884 for Oracle support & training

Automating Oracle DBV Utility

Oracle Tips by Burleson

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan). The following is an excerpt from the book.

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 is generated.

dbv.ksh

#!/bin/ksh

# Oracle Utilities

# dbv automation script

. oraenv

wlogfile=dbv.${ORACLE_SID}

SQLPLUS=${ORACLE_HOME}/bin/sqlplus

$SQLPLUS -s system/manager >> $wlogfile <<EOF

set echo off feedback off verify off pages 0 termout off

linesize 150

spool dbv.cmd

select 'dbv file=' || name || ' blocksize=' || block_size ||

' feedback=' || round(blocks*.10,0) -- 10 dots per file

from v\$datafile;

spool off

set feedback on verify on pages24 echo on termout on

EOF

ksh dbv.cmd

# End of script

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 its 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

dbvERIFY - Verification starting : FILE = /usr/oracle/asg920xr/datafiles/ASG920xrsys.dbf

..........

dbvERIFY - Verification complete

Total Pages Examined : 32000

Total Pages Processed (Data) : 16164