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:

31 comments:

Quý Linh said...

This is agreat script but i have this error when run script.
################
[oracle@fcsdb DBA_BUNDLE2]$ sh dbdailychk.sh
df: `/root/.gvfs': Permission denied
df: `/root/.gvfs': Permission denied
##########

Can u help me fix it ?

Mahmmoud ADEL said...

Well, the fastest solution for this error is to ignore it :-)
This error will not affect the filesystem monitoring function inside the script.

In case you don't like to see this error, you can easily replace "df -h" command inside the script with "df -hx fuse.gvfs-fuse-daemon"

For further help, please let me know.
Mahmmoud ADEL

Anonymous said...

Not work for Solaris based database

Anonymous said...

bash-3.2$ ./dbdailychk.sh
chown: /export/home/oratest/BUNDLE_Logs: Not owner
chown: alert_test_new.log: Not owner
./dbdailychk.sh: line 267: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 293: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 305: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 316: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 390: /bin/sqlplus: No such file or directory
cat: cannot open /export/home/oratest/BUNDLE_Logs/tablespaces_DBA_BUNDLE.log
./dbdailychk.sh: line 400: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 423: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 424: /export/home/oratest/BUNDLE_Logs/blocking_sessions.log: No such file or directory
./dbdailychk.sh: line 437: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 439: [: -ge: unary operator expected
./dbdailychk.sh: line 465: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 467: [: -ge: unary operator expected
./dbdailychk.sh: line 505: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 507: [: -ge: unary operator expected
./dbdailychk.sh: line 546: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 548: [: -ge: unary operator expected
./dbdailychk.sh: line 614: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 616: [: -ge: unary operator expected
./dbdailychk.sh: line 643: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 645: [: -ge: unary operator expected
./dbdailychk.sh: line 696: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 706: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 715: [: -gt: unary operator expected
./dbdailychk.sh: line 1020: /bin/sqlplus: No such file or directory

Mahmmoud ADEL said...

The script was tested on Solaris and it's working fine.
You have to run the script under the Owner of the Oracle binaries e.g. oracle user.

Taner Taştan said...
This comment has been removed by the author.
Mahmmoud ADEL said...

Taner,
Most probably the script cannot get your ORACLE_HOME, because there is no record for it in /etc/oratab file. In this case, I suggest you to explicitly export your ORACLE_HOME in the VARIABLES section, so it will be like this:

# #########################
# Variables:
# #########################
export ORACLE_HOME=

Anonymous said...

I am not able to run..My os is HP-UX. I had replaced the email id and am getting below error.

#######################################################
Please EDIT line# 51 in dbdailychk.sh script and change
myself@@gmail.com to your E-mail address.
#######################################################

Script Terminated !

Please suggest where is the issue??

Mahmmoud ADEL said...

Thanks for passing by.

Although this script is not tested on HP-UX environment, but you shouldn't modify this line:
case ${MAIL_LIST} in "myself@gmail.com")

In order to make the script work for you, you have to modify it like this:

MAIL_LIST="myself@gmail.com"
case ${MAIL_LIST} in "youremail@yourcompany.com")

OraDetector said...

Hi Adil ,

does the script supports RAC ?

Mahmmoud ADEL said...

Thanks for your question.
Actually instance based checks like: DATABASES/LISTENERS ALERTLOGS, CPU/FILESYSTEM UTILIZATION will not run on all nodes. It will just run against the local node. The rest of checks are running against the database which doesn't matter whether it's a RAC DB or not.

Honestly speaking, On my production RAC environments, I run the health check script dbdailychk.sh on one node only. Because I'm already scheduling dbalarm script to run every 5 minutes on each node in order to report other issues at the nearest time.

You can download dbalarm script from this post:
http://dba-tips.blogspot.ae/2014/02/database-monitoring-script-for-ora-and.html

OraDetector said...

Thanks Adil , are you gong to update the script to support RAC related Checks ?

like crsstat , ocrcheck , votedisk , .... etc ,

Mahmmoud ADEL said...

Thanks OraDetector for that suggestion.
I've already added RAC support feature to this script and included your nick name in the feature description.

Added checking RAC status feature. [Recommended by: OraDetector]

I wonder if you can send me your name so I can update the description with your good name.

Ravi said...

I am trying to run in Oracle Linux and get below error. when i run it directly, it succeeds. can you suggest what could be the issue?
++ /mnt/u01/app/oracle/product/12.1.0.2/dbhome_1/bin/sqlplus -S '/ as sysdba'
+ VAL1='
SELECT name from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0'

Mahmmoud ADEL said...

Seems the script didn't manage to pick up the right ORACLE_SID on your environment. Honestly don't know the reason why without having a live look. but as a dirty fix you can export the right ORACLE_SID inside the script by doing the following:

Replace this block:

# #########################
# Setting ORACLE_SID:
# #########################
for ORACLE_SID in $( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
do
export ORACLE_SID


With this block:

# #########################
# Setting ORACLE_SID:
# #########################
for ORACLE_SID in YOUR_GOOD_ORACLE_SID
do
export ORACLE_SID


... So YOUR_GOOD_ORACLE_SID will be replaced with the right ORACLE_SID
e.g.
for ORACLE_SID in ERPDB1

Also it will give more clear picture if you can provide me with the output of this command:
ps -ef|grep pmon

Oscar said...

Sir I had this problem:
Active Incidents:
^^^^^^^^^^^^^^^^^
LAST_OCCURENCE FROM V$DIAG_PROBLEM WHERE LASTINC_TIME > SYSDATE -10
*
ERROR at line 2:
ORA-00942: table or view does not exist

This view V$DIAG_PROBLEM not exist.

Mahmmoud ADEL said...

May I know your database version if you don't mind?

CowtownDBA said...

Thank you for providing this health check script! It provides so much useful information.

Anonymous said...

How this script actually performs on RAC (two node)? Having this entries in /etc/oratab on node 1(I've added comments for every entry just here, not really in oratab :) ):

-MGMTDB:/u01/app/12.1.0.2/grid:N - ok, excluded in EXL_DB
+ASM1:/u01/app/12.1.0.2/grid:N - ok, excluded in EXL_DB
dwhcdb:/u01/app/oracle/product/12.1.0.2/dbhome_1:N - db_unique_name - added by installer
dwhcdb1:/u01/app/oracle/product/12.1.0.2/dbhome_1:N - instance_name - added manually by dba

When i run dbalert.sh on that node, i got email with message that "dwhcdb1" is down, but that is false:

The Following Instances are POSSIBLY Down on [*hostname removed by me] :
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
dwhcdb1

If above instances are permanently offline, please add their names to 'EXL_DB' parameter at line# 90 or hash their entries in /etc/oratab to let the script ignore them in the next run.

Current Running Instances:
************************
oracle 44402 1 0 Mar23 ? 00:00:58 asm_pmon_+ASM1
oracle 155269 1 0 Mar23 ? 00:01:16 ora_pmon_dwhcdb1


INS_NAME STATUS DB_STATUS LOGINS BLOCKED STARTUP_TIME
---------------- ------------ ----------------- ---------- ------- -------------------
dwhcdb1 OPEN ACTIVE ALLOWED NO 23-MAR-17 08:39:36

Thank you for sharing this set of dba scripts - great job!
Ivan

Anonymous said...

also got error on scriptexecution for nfs mounted filesystem:

./dbalarm.sh: line 125: [: 192.168.xx.xx:/nfs/Backup: integer expression expected

Regards,
Ivan

OraDetector said...

Thanks Adil for adding my Name to the description of your script

my name is : Samer ALJazzar

for some reason the AWK block for CRS check hangs for ever , i think its a syntax issue i'm not able to figure it out

thanks and regards,
Sam

Mahmmoud ADEL said...

Hi Ivan,
Thanks for passing by. As this DB is a RAC one, you will need to do one of the following:
- Include the DB_NAME in the EXL_DB parameter inside the script e.g.:
EXL_DB="\-MGMTDB|ASM|dwhcdb"
- Hash out the oratab entry which refers to the DB_NAME (not the instance name) e.g.:
#dwhcdb:/u01/app/oracle/product/12.1.0.2/dbhome_1:N - db_unique_name - added by installer

Mahmmoud ADEL said...

Hi Samer,
Thanks for your update. I'll update your good name inside the script once I publish the new release.
May I ask what is the Linux version you are trying to run the script on?

OraDetector said...

i have two main OS used

Solaris 11.3
Linux Redhat

Regards,
Samer

Anonymous said...

Hi Mahmmud,

Regarding my rac db - still cannot configure dbalarm script to work properly. If i include DB_NAME (dwhcdb) in EXL_DB list and hash out DB_NAME in oratab, script exits on "Getting ORACLE_SID" block and alert mail is sent:

subject:
ALARM: No Databases Are Running on Server: myserver !!!

and mail body:
Current running INSTANCES on server [myserver]:
***************************************************
oracle 44402 1 0 Mar23 ? 00:03:05 asm_pmon_+ASM1
oracle 159940 1 0 Apr12 ? 00:00:56 ora_pmon_dwhcdb1

Current running LISTENERS on server [myserver]:
***************************************************
oracle 55774 1 0 Mar23 ? 00:01:28 /u01/app/12.1.0.2/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
oracle 55813 1 0 Mar23 ? 00:01:24 /u01/app/12.1.0.2/grid/bin/tnslsnr LISTENER_SCAN1 -inherit

Tnx for helping,
Ivan


OraDetector said...

Mahmoud this work will be perfect if you configure the output to be in html format

Anonymous said...

Hi,
Script is really impressive :) and I am going to use it. I did some simple modifications. However I am still getting an errors like:
./dbreport.sh: line 1: :set: command not found
./dbreport.sh: line 889: [: 65,3127083: integer expression expected
./dbreport.sh: line 889: [: 92,4768884: integer expression expected
./dbreport.sh: line 889: [: 1,83010189: integer expression expected
./dbreport.sh: line 889: [: 95,3972318: integer expression expected
./dbreport.sh: line 889: [: ,1: integer expression expected
./dbreport.sh: line 889: [: 96,2540417: integer expression expected
./dbreport.sh: line 889: [: 73,8235938: integer expression expected
./dbreport.sh: line 889: [: 30,0611055: integer expression expected
./dbreport.sh: line 889: [: ,003051759: integer expression expected
./dbreport.sh: line 909: [: selected.: integer expression expected

I tried to use round functions and few other solutions but it seems to be a different problem. Anybody has any idea, what is wrong ?

Thanks and Best Regards
humble oracle dba

Mahmmoud ADEL said...

I can see from the list of errors that you have errors starting from line number 1, of course the script should be impressive in this way :-))
Actually I've know idea what kind of updates you have done to the script, and thus I'll not be able to tell what went wrong. If you don't mind, send a copy of your script after the modification and I'll try to have a look into it.

Thanks,
Mahmmoud ADEL

Anonymous said...

Hi Mahmmoud,

Thank you for quick answer. I appreciate :).

1. Everywhere where is ps -ef|grep pmon i added "| grep caalbtb", to find only this database name.

e.g.

for ORACLE_SID in $( ps -ef|grep pmon|grep caalbtb|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )

2. And changed the oratab "/oracle/home/zw/oratab" to my own including only one database name caalbtb

last errors:

oracle@caredbs28:caalbdb$ ./dbalarm.sh
./dbalarm.sh: line 900: [: 65,3127083: integer expression expected
./dbalarm.sh: line 900: [: 92,4768884: integer expression expected
./dbalarm.sh: line 900: [: 1,82972042: integer expression expected
./dbalarm.sh: line 900: [: 95,3972318: integer expression expected
./dbalarm.sh: line 900: [: ,1: integer expression expected
./dbalarm.sh: line 900: [: 96,2540417: integer expression expected
./dbalarm.sh: line 900: [: 73,8235938: integer expression expected
./dbalarm.sh: line 900: [: 30,037208: integer expression expected
./dbalarm.sh: line 900: [: ,003051759: integer expression expected
./dbalarm.sh: line 920: [: selected.: integer expression expected

Script was too long to attach it :(.

Many Thanks and Best Regards
humble oracle dba

Mahmmoud ADEL said...

Well, generally speaking, you will get that error when the variable value which is getting used in "if" or "case" condition is a float value "xx.xx", not an integer "xx".

e.g. In case, the variable value during the script execution will be 65.3 you will get above error, and thus you have to convert that float 65.3 to an integer number 65 to get rid of ".3" to be able to use that variable inside "if" and "case" conditions. in order to do so you have to use the following:

INTEGER_VAR1=${VAR1%.*}

e.g.
$ VAR1=65.3
$ INTEGER_VAR1=${VAR1%.*}

$echo $INTEGER_VAR1
65

I hope I have clearly explained it. This is why I hate math :-)

Anonymous said...

Hi Mahmmoud, many thanks for your help. It helped :).