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:   USERID Username/Password FILE File Name START Block Address END Block Address BLOCKSIZE Integer Feedback Integer PARFILE 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 11.1.0.6.0 - 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. USERID Username/Password FILE File Name SEGMENT_ID Tablespace Name.Segment File.Segment Block Feedback Integer PARFILE 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 where    tablespace_name='USERS' and SEGMENT_NAME like 'JUNK%'; TABLESPACE_NAME SEGMENT_NAME TABLESPACE_ID HEADER_FILE HEADER_BLOCK ---------------- ---------------- ------------- ----------- ------------ USERS JUNK 4 1024 10278 USERS JUNK2 4 1024 10534 C:\Temp> dbv userid=bert/bert segment_id=4.1024.10278 DBVERIFY: Release 11.1.0.6.0 - 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 11.1.0.6.0 - 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
blocksize=8192

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.

#!/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
       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     

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

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

NAME TYPE VALUE
--------------------------- ----------- -------
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 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

..........

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.

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)

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

The DBMS_REPAIR

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.

Type	Values
Object_type	TABLE_OBJECT, INDEX_OBJECT, CLUSTER_OBJECT
Action	CREATE_ACTION, DROP_ACTION, PURGE_ACTION
Table_type	REPAIR_TABLE, ORPHAN_TABLE
Flags	SKIP_FLAG, NOSKIP_FLAG

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.

Exception	Purpose
24120	This is raised by the package procedures if an invalid parameter is passed.
24122	This is raised by the package if an incorrect block range is specified in BLOCK_START or BLOCK_END.
24123	An unimplemented feature was called.
24124	An invalid action was specified.
24125	The target object has been dropped or truncated since DBMS_REPAIR.CHECK_OBJECT was last run.
24127	The TABLESPACE parameter was specified with an action other than CREATE_ACTION.
24128	A partition name was specified for an object that isn't partitioned.
24129	A table name parameter was passed without the appropriate prefix.
24130	The orphan table specified for the repair doesn't exist.
24131	The orphan table specified doesn't have a proper definition.
24132	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.