There are several ways to check for corruption in an Oracle database
- Using DBVERIFY command
- 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'
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.
FIND "Failing" c:\file*.log
FIND "Corrupt" c:\file*.log
FIND "Influx" c:\file*.log
$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;