LATEST TOPICS

DBVERIFY: Do we really need a USERID ?

DBVERIFY (dbv) is the Database Verification utility that can be used to perform a physical data structure integrity check. dbv can be used against ONLINE or OFFLINE datfiles as well as backup files.

DBVERIFY is basically used to analyze a given files against any corruption.

DBVERIFY (dbv) has the following syntax

 dbv [ USERID=username/password ]
    FILE = filename
  | { START = block_address | END = block_address }
  | BLOCKSIZE = integer
  | LOGFILE = filename
  | FEEDBACK = integer
  | HELP  = { Y | N } 
  | PARFILE = filename

Running dbv against local datafiles

Running DBVERIFY against a local datafile is quite simple. We can just pass the local datafile name as the filename argument to the dbv utility as shown below.

One point to note here is that if the datafile is created using a Non-Default block size i.e if the datafile block size is not 8k in size, we must also pass the blocksize parameter value to dbv utility.

[oracle@labserver ~]$ dbv file=/workspace/data/labdb/sysaux1.dbf 

DBVERIFY: Release 12.1.0.1.0 - Production on Thu Jan 1 00:53:40 2015

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /workspace/data/labdb/sysaux1.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 104960
Total Pages Processed (Data) : 18094
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 10073
Total Pages Failing   (Index): 0
Total Pages Processed (Lob)  : 28892
Total Pages Failing   (Lob)  : 0
Total Pages Processed (Other): 20420
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 27481
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2706255 (0.2706255)

Here is the interpretation of the DBVERIFY output

Pages => Blocks 

Therefore:

Total Pages Examined => Sum of the blocks in the given datafile 
Total Pages Processed => Number of formatted data/index/lob/other/table_segment/index_segment blocks that were verified by DBVERIFY
Total Pages Failing => Number of blocks that failed the data/index/lob/other/table_segment/index_segment block checking module
Total Pages Empty => Number of unformatted blocks in the datafile.
Total Pages Marked Corrupt => Number of blocks where could not be verified by DBVERIFY and hence are marked as corrupt.
Total Pages Influx => The number of blocks that were re-read due to the block being in use. This should only occur when executing dbv against hot datafiles and should never occur when running dbv against OFFLINE files

Running dbv against ASM datafiles

To be able to run DBVERIFY against ASM datafile, we need to provide an additional argument called USERID to the dbv utility as shown below.

If we attempt to run DBVERIFY without an USERID, it would fail with errors.

[oracle@labserver ~]$ dbv file='+DATA/LABDB/DATAFILE/users.317.867766059'

DBVERIFY: Release 12.1.0.1.0 - Production on Thu Jan 1 01:16:46 2015

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.


DBV-00008: USERID must be specified for OSM files

Lets, run dbv by passing a USERID.

[oracle@labserver ~]$ dbv file='+DATA/LABDB/DATAFILE/users.317.867766059' userid=sys/oracle

DBVERIFY: Release 12.1.0.1.0 - Production on Thu Jan 1 01:15:47 2015

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = +DATA/LABDB/DATAFILE/users.317.867766059


DBVERIFY - Verification complete

Total Pages Examined         : 640
Total Pages Processed (Data) : 35
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 5
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 583
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 17
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)

One interesting fact, that I have observed here is even though Oracle has mentioned.

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.

We can provide any dummy username/password as the USERID to analyze the ASM datafile. It is not required to provide an existing username/password to analyze the ASM datafile. (tested in both 11g and 12c database versions)

It seems to be a formal syntax requirement and DBVERIFY can directly access an ASM datafile without a VALID authentication.

Here, I am running dbv using a dummy USERID

[oracle@labserver ~]$  dbv file='+DATA/LABDB/DATAFILE/users.317.867766059' USERID=asasaslasjklajsla/asassdsdsdsdsdsadadsa

DBVERIFY: Release 12.1.0.1.0 - Production on Thu Jan 1 01:25:19 2015

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = +DATA/LABDB/DATAFILE/users.317.867766059


DBVERIFY - Verification complete

Total Pages Examined         : 640
Total Pages Processed (Data) : 35
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 5
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 583
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 17
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)

As we can see, DBVERIFY is working even after specifying a non existing username/password.

DBVERIFY to analyze a segment

DBVERIFY can also be used to analyze specific table or index segment.

The syntax for dbv to analyze a segment is as follows:

 dbv USERID = username/password 
  | SEGMENT_ID = tsn.segfile.segblock
  | LOGFILE = filename
  | FEEDBACK = integer
  | HELP  = { Y | N }
  | PARFILE = filename  

SEGMENT_ID => Specifies the segment that we want to verify. It is composed of the tablespace ID number (tsn), segment header file number (segfile), and segment header block number (segblock). We can get this information from SYS_USER_SEGS.

A valid USERID is required in the case when we are analyzing a table or index segment as in that the case dbv needs access to the database in order to obtain the segment information.

Providing an incorrect USERID will lead to errors in this case as shown below.

[oracle@labserver labdb]$ dbv userid=abbas/test segment_id=0.1.40016

DBVERIFY: Release 12.1.0.1.0 - Production on Thu Jan 1 02:02:51 2015

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.


DBV-00111: OCI failure (2850) (ORA-01017: invalid username/password; logon denied
)

Lets, analyze the segment by providing a correct USERID.

[oracle@labserver labdb]$ dbv userid=abbas/abbas segment_id=0.1.40016

DBVERIFY: Release 12.1.0.1.0 - Production on Thu Jan 1 01:57:16 2015

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : SEGMENT_ID = 0.1.40016


DBVERIFY - Verification complete

Total Pages Examined         : 2
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 1
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
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)

Reference:

DBVERIFY: Offline Database Verification Utility

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed