Saturday, May 9, 2015

Oracle Database Health Check Script

Today I'll 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 ALERTLOGS FOR ERRORS.
# CHECKING ALL LISTENERS ALERTLOGS FOR ERRORS.
# CHECKING CPU UTILIZATION.
# CHECKING FILESYSTEM UTILIZATION.
# CHECKING TABLESPACES UTILIZATION.
# CHECKING FLASH RECOVERY AREA UTILIZATION.
# CHECKING ASM DISKGROUPS UTILIZATION.
# CHECKING BLOCKING SESSIONS ON THE DATABASE.
# CHECKING UNUSABLE INDEXES ON THE DATABASE.
# CHECKING INVALID OBJECTS ON THE DATABASE.
# CHECKING FAILED LOGIN ATTEMPTS ON THE DATABASE.
# CHEKCING AUDIT RECORDS ON THE DATABASE.
# CHECKING CORRUPTED BLOCKS ON THE DATABASE.
# CHECKING FAILED JOBS IN THE DATABASE.
# CHECKING ACTIVE INCIDENTS.
# CHECKING OUTSTANDING ALERTS.
# CHECKING DATABASE SIZE GROWTH.
# CHECKING OS / HARDWARE STATISTICS.
# CHECKING RESOURCE LIMITS.
# CHECKING RECYCLEBIN.
# CHECKING CURRENT RESTORE POINTS.
# CHECKING HEALTH MONITOR CHECKS RECOMMENDATIONS THAT RUN BY DBMS_HM PACKAGE.
# CHEKCING MONITORED INDEXES.
# CHECKING REDOLOG SWITCHES.
# CHECKING MODIFIED INTIALIZATION PARAMETERS SINCE THE LAST DB STARTUP.
# CHECKING ADVISORS RECOMMENDATIONS:
#          - SQL TUNING ADVISOR
#          - SGA ADVISOR
#          - PGA ADVISOR
#          - BUFFER CACHE ADVISOR
#          - SHARED POOL ADVISOR
#          - SEGMENT ADVISOR

This script was tested on Linux environments.

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

Step 1: Download the script from this link:
-------
https://www.dropbox.com/s/w1dpw3iynphm07t/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: [Recommended once a day during non-peak hours]
--------
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 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: