Search This Blog

Monday, April 2, 2007

Offline/Online Database Verification Utility

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.

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


1.Using DBVERIFY to Validate Disk Blocks of a Single Datafile



C:\>dbv FILE=c:\ORA101\system01.dbf FEEDBACK=100

DBVERIFY: Release 10.1.0.2.0 - Production on Mon Apr 2 20:44:51 2007

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

DBVERIFY - Verification starting : FILE = c:\ORA101\system01.dbf
................................................................................

................................................................................

................................................................................

................................................................................

................................................................................

................................................................................

..............................................................................

DBVERIFY - Verification complete

Total Pages Examined : 56320
Total Pages Processed (Data) : 33533
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 6125
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1567
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 15095
Total Pages Marked Corrupt : 0
Total Pages Influx : 0

C:\>dbv FILE=c:\ORA101\users01.dbf FEEDBACK=100

DBVERIFY: Release 10.1.0.2.0 - Production on Mon Apr 2 20:45:55 2007

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

DBVERIFY - Verification starting : FILE = c:\ORA101\users01.dbf
.......

DBVERIFY - Verification complete

Total Pages Examined : 640
Total Pages Processed (Data) : 85
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 18
Total Pages Failing (Index): 0
Total Pages Processed (Other): 122
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 415
Total Pages Marked Corrupt : 0
Total Pages Influx : 0



Note : above example based on OFFLINE BACKUP.

ONLINE datafile Verification.


C:\>dbv file=c:\oracle\product\10.1.0\oradata\ORA101\users01.dbf feedback=100

DBVERIFY: Release 10.1.0.2.0 - Production on Tue Apr 3 08:42:33 2007

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

DBVERIFY - Verification starting : FILE = c:\oracle\product\10.1.0\oradata\ORA10
1\users01.dbf
.......

DBVERIFY - Verification complete

Total Pages Examined : 640
Total Pages Processed (Data) : 85
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 18
Total Pages Failing (Index): 0
Total Pages Processed (Other): 122
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 415
Total Pages Marked Corrupt : 0
Total Pages Influx : 0



C:\>dbv file=c:\oracle\product\10.1.0\oradata\ORA101\temp01.dbf feedback=100

DBVERIFY: Release 10.1.0.2.0 - Production on Tue Apr 3 08:49:57 2007

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

DBVERIFY: Skipping temporary file c:\oracle\product\10.1.0\oradata\ORA101\temp01
.dbf




2.Using DBVERIFY to Validate a Segment
If we want to check segment level you can use SEGMENT_ID


for Segment_id = tsn.file_header_block
we can get all three values from below query.

select t.ts#, s.header_file, s.header_block
from v$tablespace t, dba_segments s
where s.owner = 'FGC'
and s.segment_name='ACLEDGER'
and s.segment_name='SMSETUP'
and t.name = s.tablespace_name



SQL> select t.ts#, s.header_file, s.header_block
2 from v$tablespace t, dba_segments s
3 where s.owner = 'FGC'
4 and s.segment_name='ACLEDGER'
5 and t.name = s.tablespace_name
6 /

TS# HEADER_FILE HEADER_BLOCK
---------- ----------- ------------
6 5 11601

SQL> EXIT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\>DBV USERID=FGC/ORACLE SEGMENT_ID=6.5.11601

DBVERIFY: Release 10.1.0.2.0 - Production on Tue Apr 3 12:25:47 2007

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

DBVERIFY - Verification starting : SEGMENT_ID = 6.5.11601


DBVERIFY - Verification complete

Total Pages Examined : 15356
Total Pages Processed (Data) : 15355
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 0
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 0
Total Pages Marked Corrupt : 0
Total Pages Influx : 0

2 comments:

Atif Khan said...

HI TAJ, I GETTING THE FOLLOWING ERROR WHILE RUNNING DBV:

SQL> host dbv FILE='E:\WMSDB_BAK_28.12\DBF\SSAADMINIDX.DBF' FEEDBACK=100

DBVERIFY: Release 9.2.0.1.0 - Production on Tue Jan 8 11:19:59 2008

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


DBV-00103: Specified BLOCKSIZE (2048) differs from actual (8192)

Mohammed Taj said...

Dear Atif,

Use BLOCKSIZE parameter with DBV utility.

BLOCKSIZE=8192

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

- Chapter 12 of the Oracle9i Database Utilities Guide.

Regards
Taj
http://tech.groups.yahoo.com/group/oracleclub/