dbverify tool for detecting block corruption- complete reference for the new oracle dba





The advantage of the dbverify command is that blocks are not loaded into SGA during the verification and that it can run using offline files. If dbverify is used with offline files, there will be no affect on the production SAP system. Using offline files also has the advantage that a verification will be always correct. If dbverify is run with online data files, which means that the database is online during the verification, dbverify can report a block corruption even if the block is not corrupted.

dbv FIlE=users01.dbf FEEDBACK=100 blocksize=8192

The reason for this is in 9i (i believe) you can now have datafiles with different blocksizes.

dbv checks the blocks of a given Oracle file for correctness.
I also tried to use it, but was disapointed.
The best method to verify the consistency of a DB is the full Export of a DB sending
the output to a dummi device/file.
This method catches also diferences between the version of the software and the version of the catalag that
is not done by dbv. This hidden and very serious defect (after upgrade of DB) happens very often
and is not clear understood by DBAs because of misleading oracle instructions and a serious lack of coordination
in the Oracle Corporation

Now to answer: " Is it better to verify the db before or after a cold backup"

I would probably run it on the files from the cold backup (assuming the cold backup went to disk and not tape).  This can be done because as schwertner hinted at:  DBV doesn’t know anything about the database the file came from of what it is used for.  All it does is verify the blocks are where they are supposed to be.  It doesn’t check what’s in them.

dbv is used for offline database verification.

Try this

dbv file=ifsapp_data01.dbf blocksize=191789 logfile=c:ora.txt

DBVERIFY: Offline Database Verification Utility

DBVERIFY is an external command-line utility that performs a physical data structure integrity check on an offline database. It can be used against backup files and online files (or pieces of files). You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored or as a diagnostic aid when you have encountered data corruption problems.

Because DBVERIFY can be run against an offline database, integrity checks are significantly faster.

DBVERIFY checks are limited to cache-managed blocks (that is, data blocks). Because DBVERIFY is only for use with datafiles, it will not work against control files or redo logs.

There are two command-line interfaces to DBVERIFY. With the first interface, you specify disk blocks of a single datafile for checking. With the second interface, you specify a segment for checking. The following sections provide descriptions of these interfaces:

  • Using DBVERIFY to Validate Disk Blocks of a Single Datafile
  • Using DBVERIFY to Validate a Segment

Note:

The command used to invoke DBVERIFY is dependent on your operating system (for example, on Sun/Sequent systems, the command is dbv). See your Oracle operating system-specific documentation.


 

Using DBVERIFY to Validate Disk Blocks of a Single Datafile

In this mode, DBVERIFY scans one or more disk blocks of a single datafile and performs page checks.

Syntax

The syntax for DBVERIFY when you want to validate disk blocks of a single datafile is as follows:

Text description of the illustration dbv.gif

Parameters

Descriptions of the parameters are as follows:

Parameter  Description 
FILE The name of the database file to verify.
START The starting block address to verify. Specify block addresses in Oracle blocks (as opposed to operating system blocks). If you do not specify START, DBVERIFY defaults to the first block in the file.
END The ending block address to verify. If you do not specify END, DBVERIFY defaults to the last block in the file.
BLOCKSIZE BLOCKSIZE is required only if the file to be verified does not have a block size of 2 KB. If the file does not have block size of 2 KB and you do not specify BLOCKSIZE, you will receive the error DBV-00103.
LOGFILE Specifies the file to which logging information should be written. The default sends output to the terminal display.
FEEDBACK Causes DBVERIFY to send a progress display to the terminal in the form of a single period (.) for n number of pages verified during the DBVERIFY run. If n = 0, there is no progress display.
HELP Provides online help.
PARFILE Specifies the name of the parameter file to use. You can store various values for DBVERIFY parameters in flat files. This allows you to customize parameter files to handle different types of datafiles and to perform specific types of integrity checks on datafiles.

Command-Line Interface

The following example shows a sample use of the command-line interface to this mode of DBVERIFY.

% dbv FILE=t_db1.dbf FEEDBACK=100

Sample DBVERIFY Output

The following example is sample output of verification for the file t_db1.dbf. The feedback parameter has been given the value 100 to display one period (.) for every 100 pages processed:

% dbv FILE=t_db1.dbf FEEDBACK=100
 
DBVERIFY: Release 9.2.0.1.0 - Production on Wed Feb 27 13:55:26 2002
 
(c) Copyright 2002 Oracle Corporation.  All rights reserved.

DBVERIFY - Verification starting : FILE = t_db1.dbf
 
................................................................................

 
DBVERIFY - Verification complete
 
Total Pages Examined         : 9216
Total Pages Processed (Data) : 2044
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 733
Total Pages Failing   (Index): 0
Total Pages Empty            : 5686
Total Pages Marked Corrupt   : 0
 
Total Pages Influx           : 0

Notes:

  • Pages = Blocks
  • Total Pages Examined = number of blocks in the file
  • Total Pages Processed = number of blocks that were verified (formatted blocks)
  • Total Pages Failing (Data) = number of blocks that failed the data block checking routine
  • Total Pages Failing (Index) = number of blocks that failed the index block checking routine
  • Total Pages Marked Corrupt = number of blocks for which the cache header is invalid, thereby making it impossible for DBVERIFY to identify the block type
  • Total Pages Influx = number of blocks that are being read and written to at the same time. If the database is open when DBVERIFY is run, DBVERIFY reads blocks multiple times to get a consistent image. But because the database is open, there may be blocks that are being read and written to at the same time (INFLUX). DBVERIFY cannot get a consistent image of pages that are in flux.

Using DBVERIFY to Validate a Segment

In this mode, DBVERIFY allows you to specify a table segment or index segment for verification. It checks to make sure that a row chain pointer is within the segment being verified.

This mode requires that you specify a segment (data or index) to be validated. It also requires that you log on to the database with SYSDBA privileges, because information about the segment must be retrieved from the database.

During this mode, the segment is locked. If the specified segment is an index, the parent table is locked. Note that some indexes, such as IOTs, do not have parent tables.

Syntax

The syntax for DBVERIFY when you want to validate a segment is as follows:

Text description of the illustration dbv2.gif

Parameters

Descriptions of the parameters are as follows:

Parameter Description
USERID Specifies your username and password.
SEGMENT_ID Specifies the segment that you want to verify. You can identify the tsn, segfile, and segblock by joining and querying the appropriate data dictionary tables, for example, USER_TABLES and USER_SEGMENTS.
LOGFILE Specifies the file to which logging information should be written. The default sends output to the terminal display.
FEEDBACK Causes DBVERIFY to send a progress display to the terminal in the form of a single period (.) for n number of pages verified during the DBVERIFY run. If n = 0, there is no progress display.
HELP Provides online help.
PARFILE Specifies the name of the parameter file to use. You can store various values for DBVERIFY parameters in flat files. This allows you to customize parameter files to handle different types of datafiles and to perform specific types of integrity checks on datafiles.

Command-Line Interface

The following example shows a sample use of the command-line interface to this mode of DBVERIFY.

dbv USERID=username/password SEGMENT_ID=tsn.segfile.segblock
Author: admin