Oracle Database Health Check Script

Do you want to receive an email report summarize the health status of your all databases running on the same server?
dbdailychk.sh script will do the job for you, it performs the following health checks each time it runs against all up & running instances 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
# RMAN BACKUP CHECK.
# REPORT Newly Created Objects [in the last 24h].
# REPORT Long Running Jobs.
# REPORT UNRECOVERABLE DATABASE FILES that don't have a valid backup.

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 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:

HTMLENABLE=Y    # Enable HTML Email Format [DB]

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]
JOBSRUNSINCENDAY=1          # THRESHOLD FOR JOBS RUNNING LONGER THAN N DAY  [DB]
NEWOBJCONTTHRESHOLD=1  # THRESHOLD FOR NUMBER OF NEWLY CREATED OBJECTS  [DB]
LONG_RUN_QUR_HOURS=1     # THRESHOLD FOR QUERIES RUNNING LONGER THAN N HOURS  [DB]
CLUSTER_CHECK=Y                   # CHECK CLUSTERWARE HEALTH   [OS]
CHKAUDITRECORDS=Y            # CHECK DATABASE AUDIT RECORDS [increases CPU Load]   [DB]
SHOWSQLTUNINGADVISOR=Y  # SHOW SQL TUNING ADVISOR RESULTS IN THE REPORT  [DB]
SHOWMEMORYADVISORS=Y    # SHOW MEMORY ADVISORS RESULTS IN THE REPORT   [DB]
SHOWSEGMENTADVVISOR=Y   # SHOW SEGMENT ADVISOR RESULTS IN THE REPORT   [DB]
SHOWJOBS=Y                          # SHOW DB JOBS DETAILS IN THE REPORT     [DB]
SHOWHASHEDCRED=N        # SHOW DB USERS HASHED VERSION CREDENTIALS IN THE REPORT  [DB]

REPORTUNRECOVERABLE=Y   # REPORT UNRECOVERABLE DATAFILES. [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 summarize the health status of ALL databases running on the that server, also you will receive a separate e-mail if any of defined thresholds been hit.

By default the script will send the report in HTML format if it founds "sendmail" package installed on your system otherwise it will revert to TEXT format, If you don't have "sendmail" installed on your system and you still enthusiastic to receive the E-mail report in HTML format then follow this link:
http://dba-tips.blogspot.com/2017/11/oracle-database-health-check-report-in.html

In case you are looking for a script for real-time databases & listeners monitoring plus  CPU & filesystem monitoring plus monitoring other components, you may have a look at this link:
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 database administration scripts that can help you in your day to day 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 below: