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'; SQL> COUNT(*) ---------- 177
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 ]]
then
sh /u03/scripts/newftptsys.sh
fi
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/newftptsys.sh
#!/bin/bash
USER=abc
PASSWD=abc
FILE=SMSTSYS.TXT
HOST=prodlive
ftp -n $HOST <<END_SCRIPT
quote USER $USER
quote PASS $PASSWD
lcd /u03/scripts
cd /QDLS/SMSDTA
put $FILE
quit
END_SCRIPT
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/smsTSYS.sh
#!/bin/bash
current_time=$(date "+%Y%m%d%H%M%S")
export ORACLE_HOME=/u03/app/oracle/product/12.1.0.2/payapps
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
@/u03/scripts/counttsys.sql
spool off
exit
EOF
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 ]]
then
sh /u03/scripts/newftptsys.sh
fi
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
20161129124001,ACQUIRER,33578971,30116935
Summary:
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  🙂
