Oh, Oracle session, let me know
Oracle. Love it or hate it, it’s all over the place and the basis of many enterprise solutions. While there may be commerical system monitoring and analysis solutions, sometimes (due to cost, speed or simplicity) you just need a quick and dirty solution to make sure you know what’s going on in your database. In this post I am going to discuss how to use a script that I’ve written that lets you monitor the session metric in Oracle’s v$sysmetric view and sends an email if the session metric is over some prescribed limit.
For this to work we need a few things:
- Root access to the database server to setup the cron job (well we could set it up using another account, like the oracle server account, but who wants to try to find random crontabs all over the place in a few years when we inevitably forget about this?)
- Local database system access to get access to the v$sysmetric database view
- Access to the sqlplus command at a bash prompt
- The mail client mailx installed and accessible at a command prompt
- An open SMTP server (ok this is not necessary as mailx supports lots of secure mail services, but I’m assuming you have some internal accessible only SMTP server available)
First, let us download the check_oracle_session.sh script from my github script_stuff repository.
In my test environment I have a user, oracle with a home folder in /home/oracle/.
Next we place the downloaded check_oracle_session.sh script in the /home/oracle/ folder and make it executable for the oracle user. We can make it executable (and leave it read-only) for everyone by executing:
chmod 555 check_oracle_session.sh
If all is setup properly you can run the script and see the script options by executing:
./check_oracle_session.sh -h
Below are the options that the script accepts to execute.
NAME:
check_oracle_session.sh - Automatic Oracle Database Instance Session Checker v1.0
SYNOPSIS:
check_oracle_session.sh [options]
Run automated check for the number of sessions for a oracle database instance.
The script will execute a notification once the number of sessions exceeds
a specified limit.
OPTIONS:
-h, --help
Show this message
-l, --limit integerlimit
Specify the session limit you would like to use so that notifications go
out when the number of sessions meet or exceed this number. E.g. -l 100
-s, --smtpserver smtpserver
Specify the smtp server to use when sending notifications
E.g. -s mail.mycompany.com
-f, --from fromemailaddress
Specify the email address the email will appear to come from when there
are notifications as a result of the limit being met or exceeded
E.g. -f noreply@mycompany.com
-t, --to toemailaddress
Specify the email address to email when limit has been met or exeeded
E.g. -t me@mycompany.com
-v, --verbose
Verbose output. Note: This will send an email even if the limit has not
been met or exceeded
An example invocation that is checking if the session limit has met or exceeded 50 sessions, using the SMTP server mail.mycompany.com, with the email coming from noreply@mycompany.com and the email being sent to me@mycompany.com:
./check_oracle_session.sh -l 50 -s mail.mycompany.com -f noreply@mycompany.com -t me@mycompany.com
This script assumes that the oracle environment has been setup so that you can use sqlplus at a command prompt and connect as the system DBA.
In my environment, I also have a seperate oracle.bash file that I source to set the ORACLE_SID, the TNS_ADMIN home etc. You may or may not have a similar configuration, however whatever steps you need to get sqlplus working at a command prompt, that’s what you need to do to test sqlplus.
[oracle@mydatabase ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 2 10:52:36 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL>
We can easily test our script to ensure the notifications work by asking it to send an email with a low limit, like 0.
./check_oracle_session.sh -l 0 -s mail.mycompany.com -f noreply@mycompany.com -t me@mycompany.com
Once that works, we can now schedule the script to run every 5 minutes via cron.
In the crontab we change to the oracle user (su - oracle) and execute the command (-c) in double quotes.
In my test environment I source the oracle.bash to set the environment variables and then run the script with the parameters tested earlier.
*/5 * * * * su - oracle -c "source /home/oracle/oracle.bash > /dev/null && /home/oracle/check_oracle_session.sh -l 50 -s mail.mycompany.com -f noreply@mycompany.com -t me@mycompany.com"
If your oracle user has the environment variables set already and you don’t need to source any configuration scripts you can use the below crontab entry instead.
*/5 * * * * su - oracle -c "/home/oracle/check_oracle_session.sh -l 50 -s mail.mycompany.com -f noreply@mycompany.com -t me@mycompany.com"
Once complete you should get confirmation that the crontab has been saved and installed.
[root@mydatabase ~]# crontab -e no crontab for root - using an empty one crontab: installing new crontab [root@mydatabase ~]#
And Voila! The script will run every 5 minutes and notify you via email if the session limit has been exceeded.
You can also track the individual runs of the script by looking at the /var/log/check_oracle_session.log.
[root@mydatabase ~]# tail -f /var/log/check_oracle_session.log 2018-10-02T12:05:01-0500 Limit is: 50 and No. Of Sessions is: 48 2018-10-02T12:10:01-0500 Limit is: 50 and No. Of Sessions is: 50 2018-10-02T12:15:01-0500 Limit is: 50 and No. Of Sessions is: 50 2018-10-02T12:20:01-0500 Limit is: 50 and No. Of Sessions is: 49 2018-10-02T12:25:01-0500 Limit is: 50 and No. Of Sessions is: 48 2018-10-02T12:30:01-0500 Limit is: 50 and No. Of Sessions is: 48 2018-10-02T12:35:01-0500 Limit is: 50 and No. Of Sessions is: 48 2018-10-02T12:40:02-0500 Limit is: 50 and No. Of Sessions is: 48 2018-10-02T12:45:01-0500 Limit is: 50 and No. Of Sessions is: 48 2018-10-02T12:50:01-0500 Limit is: 50 and No. Of Sessions is: 48
You can now extend this script if desired and make modifications to monitor multiple metrics, possibly add logic to check the average value of the metric (using v$sysmetric_summary) and only give an alert if the new value for the metric is, say, 10% over the average instead of a hard coded value. You can also add more notification options, such as notifications to a slack channel or even a text message when you are out of wireless (but not cell network) range depending on the API interfaces you have available.
