Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







dbverify tips

Oracle Tips by Burleson Consulting

November 19, 2011

DBVerify is a simplistic external command line utility which performs a very critical task: it does either an offline or online check or verification as to the validity of data files. The two basic modes of operation it offers are file level and segment level. The offline check is quicker when referential integrity checks are involved. Here are the table level verification mode's parameters:





File Name


Block Address


Block Address






File Name

Table 6.127: Table Level Verification Mode Parameters

And here is a simple check of the USERS tablespace's data file.

C:\Temp> dbv userid=bert/bert file=C:\Oracle\oradata\ORDB1\USERS.DBF blocksize=4096

DBVERIFY: Release - Production on Tue Jul 8 15:13:42 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

DBVERIFY - Verification starting : FILE = C:\Oracle\oradata\ORDB1\USERS.DBF

DBVERIFY - Verification complete

Total Pages Examined : 51200

Total Pages Processed (Data) : 610

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 815

Total Pages Failing (Index): 0

Total Pages Processed (Other): 362

Total Pages Processed (Seg) : 52

Total Pages Failing (Seg) : 0

Total Pages Empty : 49361

Total Pages Marked Corrupt : 0

Total Pages Influx : 0

Total Pages Encrypted : 0

Highest block SCN : 1466473 (0.1466473)


The segment level check has fewer parameters, but the segment_id parameter is a little more complex, meaning it requires a three-part value to be specified which requires a data dictionary query to resolve. Here are its parameters.




File Name


Tablespace Name.Segment File.Segment Block




File Name

Table 6.128: Segment_id Parameters

The segment_id requires a simple query as shown here followed by the call to invoke DBVERIFY for those values. Note that this verification mode requires SYSDBA privileges:

SQL> select tablespace_name, segment_name, TABLESPACE_ID, HEADER_FILE, HEADER_BLOCK

from sys.sys_user_segs

   tablespace_name='USERS' and SEGMENT_NAME like 'JUNK%';


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

USERS JUNK 4 1024 10278

USERS JUNK2 4 1024 10534

C:\Temp> dbv userid=bert/bert segment_id=4.1024.10278

DBVERIFY: Release - Production on Tue Jul 8 15:13:42 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

DBVERIFY - Verification starting : SEGMENT_ID = 4.1024.10278

DBVERIFY - Verification complete

Total Pages Examined : 32

Total Pages Processed (Data) : 28

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 0

Total Pages Processed (Seg) : 3

Total Pages Failing (Seg) : 1

Total Pages Empty : 0

Total Pages Marked Corrupt : 0

Total Pages Influx : 0

Total Pages Encrypted : 0

Highest block SCN : 1466473 (0.1466473)

For additional information on handling corruption, see the BC expert notes on corruption and see MOSC Note 1088018.1 - Handling Oracle Database Corruption Issues

About the Oracle dbverify Utility

You can prevent and manage such problems as block corruption by using the DBVERIFY tool. For example, to detect a block corruption problem, run the utility mentioned above against the example tablespace for Oracle 11g on Linux platform as shown here:


[oracle@raclinux1 backupset]$ dbv
DBVERIFY: Release - Production on Mon Oct 27 00:30:07 2008
Copyright (c) 1982, 2007, Oracle.  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)

The syntax to use DBVERIFY with check for block corruption is shown below:


dbv file=example01.dbf blocksize=8192

Make sure that the OS user account has read and write permissions or an error will occur with Oracle 11g Release 1 due to a bug with DBVERIFY.

In addition, Oracle provides block corruption detection and repair with the Oracle 11g Recovery Manager (RMAN) utility during backup and recovery processing.

Block corruption can also be detected by querying the v$database_block_corruption dynamic performance view. To repair block corruption, the dbms_repair package can be used with Oracle 11g.

Executing dbv and Interpreting the Output

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

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

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.

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.


# Oracle Utilities

# dbv automation script



. oraenv



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

       set echo off feedback off verify off pages 0 termout off    

           linesize 150

       spool dbv.cmd
       see dbv script download

       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     


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

Notice in the dbv.cmd file above that the blocksize 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

--------------------------- ----------- -------
db_block_size integer 8192

In Oracle9i 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 - 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


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.

More db_verify tips:

In the final section of this lesson I want to cover the DB_VERIFY utility. The DB_VERIFY utility is an external command line-based utility that is used to perform a physical structure integrity check on an off-line (shutdown) database. The utility can be used against backup files and on-line files or pieces of on-line files. The utility is used to be sure a backup database or data file is valid before recovery.

The db verify utility can also serve as a diagnostic aid when corruption is suspected. Since it runs against a shutdown database it can perform checks significantly faster than export or other utilities. The utility is named differently on different platforms; for example, it may be called dbv (on SUN/Sequent) or something else on your system. Verify its name with the system-specific documentation you should have received (if you didn't, call your Oracle rep and complain). The utility only verifies cache-managed blocks.

The DB_VERIFY utility has the following general syntax:

dbf|dbverify|dbverf80 keyword=value,keyword=value?

Where the keywords are shown in table 1.





File to Verify



Start Block

(First Block of File)


End Block

(Last Block of File)


Logical Block Size



Output Log



Display Progress


Table 1: DBVERIFY Commands

The following shows some example runs of the DB_VERIFY against an Oracle 7.3 database.

The following example shows how to get on-line help:

D:\Oracle2\Ortest1\Data>dbv help=y

DBVERIFY: Release - Production on Fri Feb 4 11:59:26 2000
(c) Copyright 1999 Oracle Corporation.  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 (2048)
LOGFILE   Output Log         (NONE)
FEEDBACK  Display Progress   (0)

This is sample output of verification for the file, sys1ortest1.dbf. The feedback parameter has been given the value 1000 to display one dot on screen for every 1000 blocks processed:

D:\Oracle2\Ortest1\Data>dbv file=sys1ortest1.dbf blocksize=8192 feedback=1000 

DBVERIFY: Release - Production on Fri Feb 4 11:47:13 2000 

(c) Copyright 1999 Oracle Corporation.  All rights reserved. 

DBVERIFY - Verification starting : FILE = sys1ortest1.dbf

DBVERIFY - Verification complete 

Total Pages Examined         : 12800
Total Pages Processed (Data) : 3419
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 1304
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 527
Total Pages Empty            : 7550
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0


Utility New in Oracle8i is the DBMS_REPAIR utility. The DBMS_REPAIR utility consists of a stored package of procedures and functions that allow the DBA to detect and repair corrupt blocks in tables and indexes. This functionality has been sorely needed in Oracle for a long time. Now, instead of having to go through a complex recovery procedure should a single block become corrupted the DBA has the option of attempting repair using the DBMS_REPAIR utility.

DBMS_REPAIR Enumeration Types

In Oracle you can define constants in a package that can then be used throughout the package and database. Oracle has recently taken to calling these constants "enumeration types". Essentially an enumeration type is a global variable which defines to a constant numeric value. Enumeration types are used to assign values to specific function and procedure IN type variables. The DBMS_REPAIR package has the following enumeration types as shown in table 2.











Table 2: Enumeration Types For DBMS_REPAIR Package

The default table_name is REPAIR_TABLE when table_type is REPAIR_TABLE and will be ORPHAN_KEY_TABLE when table_type is ORPHAN.

DBMS_REPAIR Exceptions

The DBMS_REPAIR package can raise several self-declared exceptions. The DBMS_REPAIR self-declared exceptions are all in the 24000 number range and are shown in table 3.




This is raised by the package procedures if an invalid parameter is passed.


This is raised by the package if an incorrect block range is specified in BLOCK_START or BLOCK_END.


An unimplemented feature was called.


An invalid action was specified.


The target object has been dropped or truncated since DBMS_REPAIR.CHECK_OBJECT was last run.


The TABLESPACE parameter was specified with an action other than CREATE_ACTION.


A partition name was specified for an object that isn't partitioned.


A table name parameter was passed without the appropriate prefix.


The orphan table specified for the repair doesn't exist.


The orphan table specified doesn't have a proper definition.


Table names are limited to 30 characters, the name specified is longer than this.


Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.