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

  1. Using DBVERIFY command
  2. Using ANALYZE command

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:
%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
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

Output of DBVERIFY script

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.

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

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.
Go to top
JSN Boot template designed by