Tablespace and datafile healthcheck

 
It is an unavoidable scenario to encounter server panic reboots/crashes and other miscellaneous server problems in one’s career as a DBA. These mishaps causes the database to crash and sometimes even corrupt database datafiles (especially if the underlying issues are storage related).

I use the following scripts to perform a routine check on the database files to make sure that the physical aspect of the database is healthy:

Basic check to see if datafiles and tablespaces are still online:


set pages 200 lines 200
col file_name for a120
select file_name, status from dba_data_files;
select tablespace_name, status from dba_tablespaces;

Use DBV (dbverify) tool to check for physical corruption of datafiles:


Step 1:
Check the database block size used for the database.
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 16384

Step 2:
Use the value from db_block_size (i.e. 16384) on the script below.
select 'dbv file=' || file_name || ' blocksize={db_block_size}' from dba_data_files;
i.e.
select 'dbv file=' || file_name || ' blocksize=16384' from dba_data_files;

Step 3:
Once the commands are generated via sql, log out of the database and execute the commands in the unix command prompt. I recommend putting the dbv commands into a shell script and running them via nohup (These take a while especially if the database is huge).
Example:
dbv file=/james_pogi/handsome_dba/users03.dbf blocksize=16384
DBVERIFY: Release 9.2.0.2.0 - Production on Fri Mar 7 03:05:48 2014

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

DBVERIFY - Verification starting : FILE = /james_pogi/handsome_dba/users03.dbf

DBVERIFY - Verification complete

Total Pages Examined : 128000
Total Pages Processed (Data) : 6995
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 6576
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1896
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 112533
Total Pages Marked Corrupt : 0
Total Pages Influx : 0

Step 4:
Verify dbv output.
Check if there are any Failing pages. Pages failing indicate a corruption, if none are found, no physical corruption is present on the database data files.

Use database backup to validate datafiles:


I use the command below while connected to the database via rman to check if there are corruptions (physical and logical) on the database (even including archivelogs). It is recommended to create an rman.rcv file to execute as script so this can run in the background (This takes a long time to run especially for huge databases).

### for Oracle 10g
### command file contents, command file name, corruption_check.cmd
run {
BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
}

### for Oracle 11g onwards
### command file contents, command file name, corruption_check.cmd
run {
VALIDATE DATABASE;
}

#### command prompt execution (don’t forget to add catalog= if you are using backup catalog)
nohup rman target / cmdfile corruption_check.cmd &

### validates database and all archivelogs

If you need more help with database issues, feel free to send me an email (jimmycdo@yahoo.com) and I'll gladly help.

About Jimbart