How to handle corruption in Oracle Database using DBVERIFY

If you have encountered Oracle database corruption problems then you can use 'DBVERIFY' command-line utility to fix corruption. DBVERIFY performs a physical data structure integrity check to find corruption in oracle database. It is an external command-line utility that can be use on offline or online databases. We can only DBVERIFY with datafiles, it is not work against control files. Here are some examples of common Oracle Database Corruption Error messages & Solutions:  

• The file does not exist or was incorrectly specified. In this case you should first check that the specified file exists.
• The datafile to be verified must end with ‘.dbf’. In this case, you should try renaming the file  
• The file is in use. In this case, you should shutdown Oracle and try again.
• If the database gets sudden shutdown then in this case you should try to stop the Oracle service ‘OracleService<SID>.

These are some general solutions which may work or not. Through DBVERIFY utility, you can fix most of the corruption error messages & can also prevent and manage block corruption.

How it works: DBVERIFY works in to two command-line interfaces: 

1. In the first interface, you specify disk blocks of a single datafile for checking: In this interface, DBVERIFY scans single datafile disk blocks & then performs page checks.

Syntax: 

The mean of all Parameters in this diagram are: 

USERID	As you can also see in the diagram that USERID=username+password. It is only necessary if the files to verify are Oracle ASM files.
FILE	The database file name to verify.
START	The starting block address to verify.
END	The ending blocks address to verify.
BLOCKSIZE	It is only required only if the file block size to be verified is not have 2 KB.
HIGH_SCN	This parameter is optional.
LOGFILE	Specifies the name of file to which logging information should be written.
FEEDBACK	Causes DBVERIFY to send a progress display to the terminal.
HELP	It provides online help.
PARFILE	Specifies the parameter file name to use.

Sample use of DBVERIFY Command-Line Interface for this mode: 

% dbv FILE=t_db1.dbf FEEDBACK=100

2. In the second interface, you specify a segment for checking: In this interface, DBVERIFY specify a table or index segment for verification. Through this check, it make sure that a row chain pointer is within the segment being verified.

Syntax:

 All parameters are same meaning except SEGMENT_ID. 

SEGMENT_ID

Specifies the segment to verify.

Sample use of DBVERIFY Command-Line Interface for this mode: 

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

Sample DBVERIFY Output for both mode: 

•Total Pages Examined– The number of blocks.

•Total Pages Processed (Data)–The number of blocks that contains table data.

•Total Pages Failing (Data) – The number of table blocks that have corruption.

•Total Pages Processed (Index)-The number of blocks inspected that contains index data.

•Total Pages Failing (Index) – The number of index blocks that are corrupted.

•Total Pages Processed (Seg) – The command to specify a segment that spans multiple files.

•Total Pages Failing (Seg) – The number of segment data blocks that are corrupted.

•Total Pages Empty– Number of unused blocks discovered in the file.

•Total Pages Marked Corrupt– It shows the number of corrupt blocks discovered during the scan.

•Total Pages Influx– The number of pages that were re-read due to the page being in use.  

If the corruption is not fixed by this utility then, the file gets severely corrupted. For severely corrupted database, you should follow steps in this given article.