Friday, February 14, 2014

Database | Filesystem | CPU Monitoring Script

The backbone monitoring solution for Oracle databases in my environment is dbalarm.sh script. I've developed it many years back and it proved itself successful on each complex/critical environment I've used on.

It reports ORA- errors and TNS- errors on ALL databases & Listeners ALERTLOGs resides on the same server, along with monitoring TABLESPACES, FLASH RECOVERY AREA, FILESYSTEM, CPU utilization and blocking sessions.

This script is coded to send you the new errors that appear since the last script execution,
in other words, it will not report the already reported errors unless they get logged again in the log files, and this is one of the key strengths of that script.

How dbalarm script works?

This script is very smart and very easy to use, just follow these three steps:

Step 1:
Download the script from this link:

Step 2:
Open the script and change the E-mail address to your email address in the line# 22
MAIL_LIST="youremail@yourcompany.com"

Note: sendmail service should be configured on your server to allow emails to be sent from the machine.

Step 3:
By Oracle user:
In the crontab, schedule the script to run at least every 5 minutes:
# crontab -e
#Add this line:
*/5 * * * * /home/oracle/dbalarm.sh
Note: /home/oracle/dbalarm.sh is the full path that points to dbalarm script where /home/oracle is the Oracle user home directory.

In case you will schedule this script to run from root user crontab:
# crontab -e
#Add this line to schedule the run of dbalarm.sh script every 5minutes:
*/5 * * * * su - oracle -c /home/oracle/dbalarm.sh
Now the only thing remaining is to set back and relax and the script will report to you the errors breached threshold once it happens.

One thing more, you can adjust the threshold inside the script as per your preferences by altering the below red colored values in the THRESHOLDS section inside the script:

# #########################
# THRESHOLDS:
# #########################
# Modify the THRESHOLDS to the value you prefer:

FSTHRESHOLD=95 # THRESHOLD FOR FILESYSTEM %USED [OS]
CPUTHRESHOLD=95 # THRESHOLD FOR CPU %UTILIZATION [OS]
TBSTHRESHOLD=95 # THRESHOLD FOR TABLESPACE %USED [DB]
FRATHRESHOLD=95 # THRESHOLD FOR FRA %USED [DB]
ASMTHRESHOLD=95 # THRESHOLD FOR ASM DISK GROUPS [DB]
BLOCKTHRESHOLD=1         # THRESHOLD FOR BLOCKED SESSIONS#[DB]

# Break down to DB Active sessions when CPU hit the threshold: [RECOMMENDED TO SET =N on BUSY ALL OF THE TIME environments]
CPUDIGMORE=Y # RUN more checks on DB side [DB]




This script has been tested on Linux and SUN environments.

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

More and more of smart and "easy to use" scripts for database administration tasks can be found in this link:

http://dba-tips.blogspot.ae/2014/02/oracle-database-administration-scripts.html

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



3 comments:

hari said...

Hi GIThub,
I think this script DBAALARM is for Linux only.Is it compatible with AIX also?
Hari

Balajee said...

Excellent work. This is working fine

Thanks,
Balajee

Anonymous said...

Hi, I'm using your script. But ORA errors that get through the mail can not read.For example: ORA-01110: ТЮИК ДЮММШУ 228.