DBVERIFY Datafile Corruption
- 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
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;
"We are all apprentices in a craft where no one ever becomes a master."