Saturday, May 9, 2015

Oracle Database Health Check Script

Do you want to receive an email summarizing the health status of your databases running on the same server without the need to login in each DB in your environment? dbdailychk.sh script is your friend. It performs the following health checks each time it runs against all up & running instance on the server and sends you an e-mail report with the results for each database:

# 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.
# CHECKING 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.
# CHECKING MONITORED INDEXES.
# CHECKING REDOLOG SWITCHES.
# CHECKING MODIFIED INITIALIZATION 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 in 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 80
MAIL_LIST="youremail@yourcompany.com"

Note: sendmail service should be well configured on the target machine in order to send emails from.

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=95    # THRESHOLD FOR FILESYSTEM %USED [OS]
CPUTHRESHOLD=95    # THRESHOLD FOR CPU %UTILIZATION [OS]
TBSTHRESHOLD=95    # 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 to run 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 execution of dbdailychk.sh script every morning:
0 6 * * * su - oracle -c /home/oracle/dbdailychk.sh

Now, everyday morning you will receive an email summarizing the health status of ALL databases running on the same server, also you will receive a separate e-mail if any of defined thresholds been hit.

In case you want to receive the health check report in HTML format, please visit this link which explains how to do so in a few easy steps:
http://dba-tips.blogspot.com/2017/11/oracle-database-health-check-report-in.html

In case you are looking for a script to real-time monitor databases & listeners alert logs for errors plus monitoring CPU & filesystem utilization and database blocking locks, you may have a look at:
http://dba-tips.blogspot.com/2014/02/database-monitoring-script-for-ora-and.html

Feel free to download the whole DBA bundle which includes this script along with other scripts that help you in day to day 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". 

Your suggestions are most welcome.

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