About the Oracle dbvverify 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:

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:

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.
  
  see dbv script download 
  
  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 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 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.
  
  see dbv script download 

Guarantee your Success! Oracle is the world's most complex, robust and flexible database, considered impossible to master without a mentor. That's why all BC Oracle trainers are working professionals, experts in Oracle who share their tips and secrets.