Saturday, May 9, 2015

Oracle Database Health Check Script

Let me introduce to you dbdailychk.sh shell script, this script performs database health check and monitoring tasks on all Oracle databases (On Linux environments) such as:

> CHECKING ALL DATABASES ALERT LOGS FOR ERRORS.
> CHECKING ALL LISTENERS ALERT LOGS FOR ERRORS.
> CHECKING CPU UTILIZATION.
> CHECKING FILESYSTEM UTILIZATION.
> CHECKING TABLESPACES UTILIZATION ON ALL DATABASES.
> CHECKING BLOCKING SESSIONS ON ALL DATABASES.
> CHECKING UNUSABLE INDEXES ON ALL DATABASES.
> CHECKING INVALID OBJECTS ON ALL DATABASES.
> REPORTING FAILED LOGIN ATTEMPTS ON ALL DATABASES.
> REPORTING AUDIT RECORDS ON ALL DATABASES.
> REPORTING CORRUPTED BLOCKS ON ALL DATABASES.
> REPORTING FAILED JOBS ON ALL DATABASES.
> REPORTING ADVISORS RECOMMENDATIONS & DATABASE GROWTH FOR ALL DATABASES.

This script was tested on Linux environment.

How to use the script?
==================

Step 1: Download the script from this link:
-------
https://www.dropbox.com/s/k6dt3zeow4ztbcl/dbdailychk.sh?dl=0

Step 2: Customize the script to your environment:
-------
Change the E-mail address to your email address in line number 56
MAIL_LIST="youremail@yourcompany.com"

Note: sendmail service should be up and running and well configured on the target machine.

Customize the defined thresholds under THRESHOLDS section, as per your preferences:
Modify the numbers in red color:

# #########################
# THRESHOLDS:
# #########################
# Send an E-mail for each THRESHOLD if been reached:
# ADJUST the following THRESHOLD VALUES as per your requirements:

FSTHRESHOLD=98    # THRESHOLD FOR FILESYSTEM %USED [OS]
CPUTHRESHOLD=95    # THRESHOLD FOR CPU %UTILIZATION [OS]
TBSTHRESHOLD=98    # THRESHOLD FOR TABLESPACE %USED [DB]
UNUSEINDXTHRESHOLD=1    # THRESHOLD FOR NUMBER OF UNUSABLE INDEXES  [DB]
INVOBJECTTHRESHOLD=1     # THRESHOLD FOR NUMBER OF INVALID OBJECTS   [DB]
FAILLOGINTHRESHOLD=1      # THRESHOLD FOR NUMBER OF FAILED LOGINS   [DB]
AUDITRECOTHRESHOLD=1     # THRESHOLD FOR NUMBER OF AUDIT RECORDS       [DB]
CORUPTBLKTHRESHOLD=1    # THRESHOLD FOR NUMBER OF CORRUPTED BLOCKS[DB]
FAILDJOBSTHRESHOLD=1      # THRESHOLD FOR NUMBER OF FAILED JOBS   [DB]


Step 3: Schedule the script to run in the crontab:
--------
By Oracle user:

# crontab -e
#Add the following line to schedule the execution of dbdailychk.sh script. let's say every morning:
0 6 * * * /home/oracle/dbdailychk.sh

Note: /home/oracle/dbdailychk.sh is the full path pointing to dbdailychk.sh script.

In case you want to run the script from root's user crontab instead of oracle's crontab:
By root user:
# crontab -e
#Add this line to schedule the run of dbdailychk.sh script every morning:
0 6 * * * su - oracle -c /home/oracle/dbdailychk.sh

From now on, you will get an email, if any of the defined thresholds reached for ALL DATABASES ON THE SERVER.

Also feel free to download the whole DBA bundle that includes dbdailychk.sh script plus more other scripts that help in daily database administration tasks:
http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html 

DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS". 

Please make sure that your suggestions are very welcome.

In case the download link is not working, please find below the full code: