Verify Physical Data Structure in Oracle.

With Oracle database it is sometime needed to check whether the physical data structure is intact or not. There are different ways to check whether physical data structure is intact or not, like to detect data block corruption.

I will try to demontrate two procedure in order to validate physical data structure.

1)With Export/Import Method.
2)With DBVERIFY Utility.

I have discussed about 1st method in other of my thread. Here I will try to explain about 2nd one.

DBVERIFY Utility
-------------------------

DBVERIFY utility can be used on offline or online databases, as well on backup files. We can use it to ensure that a backup database (or datafile) is valid before it is restored, to detect the corrupted block.

There are two command line interface of DBVERIFY utility.

A)To check disk blocks of a single datafile.
B)To check segment.

This utility is invoked by dbv command.

A)To check disk blocks of a single datafile.
--------------------------------------------------
Invoke dbv utility with the datafile name in file parameter. Like,

bash-3.00$ dbv file=/shared_disk/oradata/ARJU/data02.dbf feedback=1000

DBVERIFY: Release 10.2.0.1.0 - Production on Wed Apr 9 17:16:00 2008

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

DBVERIFY - Verification starting : FILE = /shared_disk/oradata/ARJU/data02.dbf
....................................................................

...
Page 80491 is marked corrupt
Corrupt block relative dba: 0x02013a6b (file 8, block 80491)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x02013a6b
last change scn: 0x0000.0573a224 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xa2240601
check value in block header: 0xf88
computed block checksum: 0x10
.
.
.
.

DBVERIFY - Verification complete

Total Pages Examined : 3045632
Total Pages Processed (Data) : 1620222
Total Pages Failing (Data) : 1
Total Pages Processed (Index): 586
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1384939
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 39848
Total Pages Marked Corrupt : 37
Total Pages Influx : 0
Highest block SCN : 93175751 (0.93175751)


Type dbv help=y to view the available options.

Some parameters:
--------------------
USERID:This parameter is only necessary when the files being verified are ASM files.
FEEDBACK:Display a progress report to the terminal in form of dot (.). If feedback=100 is set then after verifying 100 blocks a dot(.) will be displayed.

B)To check segment.
--------------------------

In this mode, DBVERIFY enables you to specify either a table segment or index segment for verification.Duriing this mode, the segment is locked. If the specified segment is an index, the parent table is locked.

You can invoke dbv command to validate a segment like this,

dbv USERID=prod/prod SEGMENT_ID=2.2.890


SEGMENT_ID: The id of the segment that you want to verify. It is composed of the tablespace ID number (tsn), segment header file number (segfile), and segment header block number (segblock).

You can get this information from SYS_USER_SEGS. The required columns are TABLESPACE_ID, HEADER_FILE, and HEADER_BLOCK. SYSDBA privilege is required to query SYS_USER_SEGS.

Related Documents
-----------------------
To verify Physical data corruption as well as logical data corruption you can also use
RMAN Validation