Send SMS using Shell Script, checking Transaction Count of Table in Oracle

Being a DBA requires us many a times to step outside our role and solve a problem in a smart way. One of the issues we faced recently with one of our application, was the application server hanging and not being able to function properly. The application team did not have any means of getting a quick alert and depending on contact center  to get alerts about the application being down. This required the application server to be monitored round the clock using SMS alerts. Now the application itself wasn’t smart enough to do this. So i did a bit of analysis on the application to figure out a way . Now the Oracle database on Exadata rarely if ever goes down (A very Good DBA team 🙂 ) So to find out the application has a problem, we figured to keep the database as a reference point of availability would be best.
The application writes to a table called authorizations. This being a payment gateway application the transaction count is very high. We receive 24/7 about 10TPS. So in a minute the transaction count will never be below 500-600 anytime . So the first step in creating this script is to create a query to check the transaction count and spool it to file called smscard.txt

select count(*) from authorizations where ltimestamp > sysdate - 5/1440 and I039_RSP_CD='00';

This will give us the transaction count in the last 5 minutes
Next We create a logic to check this count with a defined threshold. Using AWK we can remove the garbage information and get the count(*) output

####### AWK BLOCK #########
awk 'NF=5' /u03/scripts/smscard.txt > /u03/scripts/trimoutput
pattern="$(awk 'NR==5 {print;exit}' /u03/scripts/trimoutput)"
if [[ "$pattern" -lt 3 ]]
sh /u03/scripts/

First Line Removes the Extra spaces from the smscard.txt file and writes to file “trimoutput”
Second Prints Line 5 and writes to variable “pattern” from  “trimoutput” file
Third Line checks IF pattern is less than threshold. If condition is true it sends an SMS file to the SMS gateway using FTP. Our SMS gateway requires a text file with mobile numbers to be send to a FTP server.
So there are 3 scripts. A FTP script, a SQL script and the Master script. The Master script calls the SQL script and the FTP script and sends the SMS file based on the IF THEN logic. Below are the 3 scripts

FTP Script : /u03/scripts/
quote USER $USER
lcd /u03/scripts
put $FILE
exit 0
SQL Script : /u03/scripts/counttsys.sql
select count(*) from authorizations where ltimestamp > sysdate - 5/1440 and I039_RESP_CD='00';
Main/Master Script : /u03/scripts/
current_time=$(date "+%Y%m%d%H%M%S")
export ORACLE_HOME=/u03/app/oracle/product/
cd /u03/scripts
$ORACLE_HOME/bin/sqlplus xyz/******@onlnprd << EOF
whenever sqlerror exit sqlcode
set serveroutput on size 1000000
set feedback off
set line 200
spool /u03/scripts/smscard.txt
spool off
rm -f /u03/scripts/SMSTSYS.TXT
touch /u03/scripts/SMSTSYS.TXT
chmod 777 /u03/scripts/SMSTYS.TXT
echo "$current_time,ACQUIRER,33578971,30116935">/u03/scripts/output
cat /u03/scripts/output > /u03/scripts/SMSTSYS.TXT
####### AWK BLOCK #########
awk 'NF=5' /u03/scripts/smscard.txt > /u03/scripts/trimoutput
pattern="$(awk 'NR==5 {print;exit}' /u03/scripts/trimoutput)"
if [[ "$pattern" -lt 3 ]]
sh /u03/scripts/

The Final Script will generate an SMS file called SMSTYS.TXT in format with mobile numbers and send to the SMS ftp folder based on the count(*) output being less than threshold
Using a little bit of logic and the magic of Shell scripts we created a customized SMS probe for a very critical business application. Even an Email alert can be configured using a similar mechanism. A bit of innovation goes a long way to solve a business problem  🙂

Category: DatabaseShell ScriptShell Scripts


Leave a Reply

Article by: Shadab Mohammad