The Database Verify Utility

dbv

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.

Although it can be used against open data files, the primary purpose of dbv is to verify the integrity of cold datafiles that would be used for a backup. If used against online datafiles, intermittent errors can occur and the utility should be executed again against the same file to verify accuracy. The utility can only be used against datafiles however, not control files or archived redo logs.

dbv Command Line Options

· File – The name of the Oracle datafile to verify. The file must be specified with full path name and file name including extension (.dbf).

· Start – The block within the file to begin the verification. If none is specified, dbv will begin at the first block in the file. This parameter should be used when processing large files in which the entire file does not need scanning.

· End – The last block in the data file to verify. If none is specified, dbv will process to the end of the file. This parameter should be used when processing large files in which the entire file does not need scanning.

· Blocksize – The database blocksize of the file that needs verification. This must be set to the v$datafile.db_block_size value for the data file.

If the blocksize is not specified, it will default to 2K. If the blocksize for the datafile does not equal the blocksize specified, dbv will terminate and print an error message:

dbv-00103: Specified BLOCKSIZE (2048) differs from actual (8192)

· Logfile – The name of the file to direct the dbv output. If none is specified, the output will be sent to the terminal. When scheduling a dbv-based shell script, it will be this file that needs to be checked for corruption errors.

· Feedback – A progress meter that displays a dot for n pages examined in the file (FEEDBACK=10000). Use this to provide a status indicator when dbv is executed against large files. This is needed to indicate that the dbv process is actively processing a file. This option is obviously not needed when executing dbv through a scheduled shell script.

· Parfile - A parameter file that can contain any of these options. The parfile should be created once and used with every dbv command.

· Segment_ID – A parameter that will scan a segment regardless of the number of files it spans. The format is segment_id=tsn.segfile.segblock

· Userid – Used only in combination with segment_id to specify the username/password for the database connection.

Executing dbv and Interpreting the Output

dbv can be executed by specifying the file name and blocksize of the datafile. All other parameters are optional.

dbv file=/usr/oracle/asg920xr/datafiles/ASG920xrsys.dbf

blocksize=8192

Once executed, dbv provides the following output for each file it verifies:

Total Pages Examined : 52480

Total Pages Processed (Data) : 36617

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 4430

Total Pages Failing (Index): 0

Total Pages Processed (Other): 1664

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 9769

Total Pages Marked Corrupt : 0

Total Pages Influx : 0

The output from dbv is not intuitive at first glance. Below are the definitions for each data item.

· Total Pages Examined – The number of blocks inspected by dbv. If the entire file was scanned, this value will match the BLOCKS column for the file in v$datafile.

· Total Pages Processed (Data) –The number of blocks inspected by dbv that contained table data.

· Total Pages Failing (Data) – The number of table blocks that have corruption.

· Total Pages Processed (Index) –The number of blocks inspected by dbv that contained index data.

· Total Pages Failing (Index) – The number of index blocks that are corrupted.

· Total Pages Processed (Seg) – This output is new to 9i and allows the command to specify a segment that spans multiple files.

· Total Pages Failing (Seg) – The number of segment data blocks that are corrupted.

· Total Pages Empty – Number of unused blocks discovered in the file.

· Total Pages Marked Corrupt – This is the most important one. It shows the number of corrupt blocks discovered during the scan.

· Total Pages Influx – The number of pages that were re-read due to the page being in use. This should only occur when executing dbv against hot datafiles and should never occur when running dbv against cold backup files.

Executing dbv against a Particular Segment

In order to execute dbv against a particular segment, the tablespace name, header file, and header block are needed. This command is useful when verifying particular objects that span multiple files. This is the only case, however, in which dbv requires a database connection.

SQL> select t.ts#, s.header_file, s.header_block

2 from v$tablespace t, dba_segments s

3 where s.owner = 'SCOTT'

4 and s.segment_name='DEPARTMENT'

5 and t.name = s.tablespace_name;

TS# HEADER_FILE HEADER_BLOCK

---------- ----------- ------------

15 12 9

Once these three data items are retrieved, the dbv command can be applied to a particular segment:

dbv userid=scott/tiger segment_id=15.12.9

Total Pages Examined : 4

Total Pages Processed (Data) : 3

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

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.

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

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

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.

Alternative Block Checking Mechanisms

analyze table … validate structure – The analyze command can do things that dbv cannot and vice-versa. The analyze command can validate that tables and indexes are in sync with each other. However, the analyze command only processes an object up to the point of its high water mark (HWM), whereas dbv processes all blocks in a file. Block corruption can occur in blocks above the HWM.

The analyze command would have to be executed against an open database for each object in the database. dbv can work against offline files and is much faster since it is strictly at the file level. In addition, the analyze table command places an exclusive lock on the object being analyzed. Alternatively, dbv works outside of the database in “read only” mode against the datafiles and does not lock anything. Any errors encountered by the analyze table command are reported in the session trace file in the user dump destination directory.

db_block_checking=true – The default is false for this configuration parameter which is system modifiable:

ALTER SYSTEM SET DB_BLOCK_CHECKING = TRUE;

When true, Oracle performs block checks for all data blocks making sure that all data in the block is consistent.

Block checking provides early detection of block corruption, however, it costs between 1-10% in overhead on the database. The more block writes that occur on a system (INSERT, UPDATE, DELETE), the more costly it becomes. Any errors encountered by block checking result in an ORA-600 level message. Before setting this parameter to TRUE, first execute dbv against the datafiles to make sure they are free of corruption. Even when FALSE, Oracle still provides block checking for the system tablespace.

Handling Corruption

Some errors reported by dbv are transient in nature. Therefore, the utility should be executed on the suspect file again to confirm block corruption. If problems are again reported in the same page locations, then the file is indeed corrupt.

Once one or more corrupted blocks are detected, the DBA must resolve the issue. Below are some options available to the DBA to address block corruption:

· Drop and re-create the corrupted object – If the loss of data is not an issue, this is the preferred approach. For Data Warehouses, the data can be reloaded from external sources and the loss of data is minor. However, for OLTP tables (customer_orders), no data can be lost without a serious negative impact on the business.

If the object is an index, rebuild it. If a few blocks are corrupt, determine which object(s) are causing the corruption. This can be done in the query below by mapping the physical file location to an object(s) contained in the file.

select tablespace_name, segment_type, owner,

segment_name

from dba_extents

where file_id = <corrupted file id>

and <Block #> between block_id AND block_id + blocks-1;

· Restore the file from a backup – The tried and true method for restoring good blocks back into the datafiles.

· Use dbms_repair – Dealing with block corruption is always a risky proposition so limit the use of dbms_repair to extreme situations. dbms_repair is a package supplied by Oracle that identifies and repairs block corruption (described in next section).

If options 1 and 2 are unacceptable, using dbms_repair can resolve some block corruption issues.

dbv is a useful utility to inspect datafiles for block corruption. It should be used primarily against offline datafiles on a regular basis. In should be used in combination with other corruption detection mechanisms, including the analyze table command and init.ora parameters.

For online checking, the configuration parameter db_block_checking should be enabled, provided the overhead incurred on the database is at an acceptable level. Finally, when corrupted blocks are detected, the DBA should choose the most appropriate method of recovery – be it a restore, a rebuild of the object, or utilizing the dbms_repair utility.