Oracle Utilities and Tools

DB Verify

DBV

D:\oracle\Ora92\bin>dbv help=yes

DBVERIFY: Release 9.2.0.6.0 - Production on Wed Dec 22 16:40:02 2004

Copyright (c) 1982, 2002, 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)
PARFILE
Parameter File
(NONE)
USERID
Username/Password
(NONE)
SEGMENT_ID
Segment ID
(tsn.relfile.block)
(NONE)
HIGH_SCN
Highest Block
SCN To Verify (scn_wrap.scn_base
OR scn)

(NONE)

DBVERIFY is an external command-line utility that performs a physical data structure integrity check. It can be used on offline or online databases, as well on backup 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. Both interfaces are started with the dbv command. The following sections provide descriptions of these interfaces

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.

If the file you are verifying is an Automatic Storage Management (ASM) file, you must supply a USERID. This is because DBVERIFY needs to connect to an Oracle instance to access ASM files.

Using DBVERIFY to Validate a Segment
In this mode, DBVERIFY enables 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.

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 enables you to customize parameter files to handle different types of datafiles and to perform specific types of integrity checks on datafiles.

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 that has to be resolved.

Options to resolve the Data block Corruptions:

Drop and re-create the corrupted object – if the data loss is not cared.

In Data Warehouses, the data can be reloaded from external sources and the loss of data is minor.

However, in OLTP environments, no data loss is entertained.

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_id 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.

DBMS_REPAIR:

dbms_repair is a package supplied by Oracle that identifies and repairs block corruption

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 validate structure command and other (_) 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

Command-Line Interface

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

DBV when the database is shutdown

D:\oracle\Ora81\BIN>dbv file=D:\ORACLE\ORADATA\SRIDEVI\INDEXES.DBF blocksize=8192

DBVERIFY: Release 8.1.6.3.0 - Production on Wed Dec 22 17:05:01 2004

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

DBVERIFY - Verification starting : FILE = D:\ORACLE\ORADATA\SRIDEVI\INDEXES.DBF


DBVERIFY - Verification complete

Total Pages Examined : 3200
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 39
Total Pages Failing (Index): 0
Total Pages Processed (Other): 47
Total Pages Empty : 3114
Total Pages Marked Corrupt : 0
Total Pages Influx : 0

D:\oracle\Ora81\BIN>

Online using of the DBV

dbv USERID=username/password SEGMENT_ID=tsn.segfile.segblock


D:\oracle\Ora92\bin>dbv userid=sys/sys SEGMENT_ID=9.9.59489

DBVERIFY: Release 9.2.0.6.0 - Production on Wed Dec 22 17:34:31 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

DBVERIFY - Verification starting : SEGMENT_ID = 9.9.59489
Specified SEGMENT_ID does not exist

DBV-00100: Specified FILE (XXXX) not accessible

Symptom:
Using the verification tool ‘dbverfNN’ produces the error:

DBV-00100: Specified FILE (XXXX) not accessible

Where ‘XXXX’ is the name of the file that ‘dbv’ was invoked on.

Possible Causes and Remedies

The file does not exist or was incorrectly specified. Check that the specified file exists.

The datafile to be verified must end with ‘.dbf’. Try renaming the file (be careful, the file must be renamed and Oracle informed that it has been renamed).

The file is in use, and is therefore locked and so the utility cannot open it. Shutdown Oracle and try again. (Possibly NT only.)

If the database has been shutdown then it is possible that that Oracle service has the file locked.

Try stopping the service ‘OracleService<SID>’. (NT only.)

DBV may spin when SEGMENT_ID & USERID specified if there are multiple files in a tablespace, and the first file is not the one in which extents exist for the segment.