There are several ways to check for corruption in an Oracle database

1. Using DBVERIFY command
2. Using ANALYZE command

1. Using DBVERIFY command

• The primary tool for checking for corruption in an Oracle database is DBVERIFY.
• It can be used to perform a physical data structure integrity check on datafiles whether the database is online or offline.
• The big benefit of this is that DBVERIFY can be used to check backup data files without adding load to the database server.
• DBVERIFY does not limit concurrency or DML while it is running, and it can be run against a database backup
• You should perform a DBVERIFY validation on the backup file before beginning the recovery.

• You can also use DBVERIFY to validate a single data or index segment. To do this you must be logged onto the database with SYSDBA privileges. During the verification the segment is locked; if the segment is an index then the parent table is also locked.

• You invoke DBVERIFY from the operating system command line:

1

%dbv file=data01.dbf logfile=verify.log blocksize=8192 feedback=100

• In this example data01.dbf is the data file to check, and the tablespace this file belongs to has a block size of 8192 bytes.
• The feedback parameter tells DBVERIFY to draw a period on the screen after every 100 pages (blocks) of the file are verified.
• If you want to verify only a portion of a data file, you can specify a starting and ending block when running DBVERIFY.
• If you want to verify the entire database, you can generate a short shell script to run DBVERIFY on every data file in the database

1
2
3
4
5

SQL> CONNECT SYSTEM/SRDC
SQL> SPOOL C:\dbv_on_all_files.sql
 
SQL> SELECT 'dbv file='||file_name||' logfile=C:\file' || ROWNUM ||
     '.log blocksize=8192' FROM dba_data_files 
SQL> SPOOL OFF;

Output of DBVERIFY script

1
2
3
4
5

dbv file=C:\ORACLE_DATA_FILES\DEVTEST\USERS01.DBF logfile=C:\file1.log 
dbv file=C:\ORACLE_DATA_FILES\DEVTEST\SYSAUX01.DBF logfile=C:\file2.log 
dbv file=C:\ORACLE_DATA_FILES\DEVTEST\UNDOTBS01.DBF logfile=C:\file3.log
dbv file=C:\ORACLE_DATA_FILES\DEVTEST\SYSTEM01.DBF logfile=C:\file4.log 
dbv file=C:\ORACLE_DATA_FILES\DEVTEST\EXAMPLE01.DBF logfile=C:\file5.log

After running the shell script you can quickly scan all of the DBVERIFY log files.

1
2
3
4
5
6
7
8
9
10
11

Windows commands:
------------------ 
FIND "Failing" c:\file*.log 
FIND "Corrupt" c:\file*.log 
FIND "Influx" c:\file*.log 
 
Unix commands:
------------------------ 
$grep Failing file*.log
$grep Corrupt file*.log
$grep Influx file*.log

2. Using ANALYZE command

• If you want to check one table and all of its indexes , you can use the ANALYZE statement to read every row of the table, read every entry in each of the table’s indexes , and make sure the table and index data are consistent with each other:
• This will lock the table, preventing DML on the table, unless you specify the ONLINE keyword. Online validation reduces the amount of validation performed to allow for concurrency.

1

SQL> ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;