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

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.
SQL> ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;
Go to top
JSN Boot template designed by JoomlaShine.com