asynchronous_error_monitor script to monitor async messages for any errors

#! /bin/bash
set -vx
##########################################################################################
# Name    : asynchronous_error_monitor
# Author  : Zafrulla Khan
# Date    : Sep/23/2015
# Usage   : asynchronous_error_monitor.ksh <ORACLE_SID>
# Deployed: Sep/23/2015
##########################################################################################

. $HOME/.bash_profile
ORACLE_SID=$1
HOST_NAME=$(hostname | awk -F_ '{ print $1}')
#UNIX_NODE=$(echo $HOST_NAME|tr 'a-z' 'A-Z')
MAILLIST=$(cat /home/oracle/scripts/ibmessages/DBA_EMAIL_IDS.txt)


# Get access Details from the file

INSTANCEFILE=/home/oracle/scripts/ibmessages/accessdetails.lis

export USERID=$(cat $INSTANCEFILE|grep "${HOST_NAME}"|awk -F: '{print $3}' -)
export PASSWORD=$(cat $INSTANCEFILE|grep "${HOST_NAME}"|awk -F: '{print $4}' -)
export CONNECT=$(cat $INSTANCEFILE|grep "${HOST_NAME}"|awk -F: '{print $1}' -)

echo $ORACLE_SID
echo $USERID
echo $PASSWORD
echo $CONNECT


SCRIPT_DIR=/home/oracle/scripts/ibmessages/queues/
LOG_DIR=/home/oracle/scripts/ibmessages/logs
OUTPUT_FILE=$SCRIPT_DIR/LOGS/sync_errors.log
LAST_CHECKED_DATE_TIME_FILE=${SCRIPT_DIR}/last_date_time_checked.lis
CURRENT_TIMESTAMP=$(/bin/date +%d-%b-%Y" "%H:%M:%S|tr '[:lower:]' '[:upper:]'); echo $CURRENT_TIMESTAMP


/u01/app/oracle/product/11.2.0/db_1/bin/sqlplus -s "$USERID/$PASSWORD@$CONNECT" <<EOT > /home/oracle/scripts/ibmessages/logs/IB_OPERATIONNAME.log
set heading off
set feedback off
select distinct IB_OPERATIONNAME from PSAPMSGSUBCON;
exit;
EOT

cat /home/oracle/scripts/ibmessages/logs/IB_OPERATIONNAME.log|grep -v ^$ | while read LAST_CHECKED_DATE_TIME_FILE
do
if [ ! -f ${SCRIPT_DIR}${LAST_CHECKED_DATE_TIME_FILE} ]
 then echo "01-JAN-2015 00:00:00" > ${SCRIPT_DIR}${LAST_CHECKED_DATE_TIME_FILE}
fi

LAST_RUN_TIMESTAMP=$(cat ${SCRIPT_DIR}$LAST_CHECKED_DATE_TIME_FILE|grep -v ^$)

RETVAL_SQL=0;
RETVAL_SQL1=0;
  RETVAL_SQL=$(
sqlplus -s $USERID/$PASSWORD@$CONNECT <<-EOS
set echo off heading off pagesize 1000  feedback off linesize 150 serveroutput on
select count(*) from PSAPMSGSUBCON where CREATEDTTM between TO_DATE('${LAST_RUN_TIMESTAMP}','DD-MON-YYYY HH24:MI:SS') AND TO_DATE('${CURRENT_TIMESTAMP}','DD-MON-YYYY HH24:MI:SS') AND IB_OPERATIONNAME='${LAST_CHECKED_DATE_TIME_FILE}' and STATUSSTRING='ERROR';
EOS
)
 
    RETVAL_SQL1=$(echo $RETVAL_SQL|grep -v ^$)
 
if [ $RETVAL_SQL1 != 0 ]
then
/u01/app/oracle/product/11.2.0/db_1/bin/sqlplus -s $USERID/$PASSWORD@$CONNECT <<-EOS
 spool /home/oracle/scripts/ibmessages/logs/async_errors.log
 set echo off heading on pagesize 100  feedback on linesize 200 serveroutput on;
 col LASTUPDDTTM format a32;
 col CREATEDTTM format a32;
 select IBTRANSACTIONID, IB_OPERATIONNAME, CREATEDTTM,LASTUPDDTTM, STATUSSTRING from PSAPMSGSUBCON  where CREATEDTTM between TO_DATE('${LAST_RUN_TIMESTAMP}','DD-MON-YYYY HH24:MI:SS') AND TO_DATE('${CURRENT_TIMESTAMP}','DD-MON-YYYY HH24:MI:SS') AND IB_OPERATIONNAME='${LAST_CHECKED_DATE_TIME_FILE}' and STATUSSTRING='ERROR';
 spool off;
EOS

/bin/mailx -s " $ORACLE_SID - [ERROR ASYNC IB MESSAGE] - ${LAST_CHECKED_DATE_TIME_FILE} ERRORS = $RETVAL_SQL1" -r noreply@prologis.com $MAILLIST <<-EOF
Hello PSFT ADMIN Team,

Asynchronous error monitoring script for Production

Found $RETVAL_SQL1 synchronous error(s) in ${LAST_CHECKED_DATE_TIME_FILE} queue

==============================================================================
Between DateTime Interval
------------------------------------------------------------------------------
From : ${LAST_RUN_TIMESTAMP}
To   : ${CURRENT_TIMESTAMP}

`cat /home/oracle/scripts/ibmessages/logs/async_errors.log`

==============================================================================


==============================================================================
Asynchronous error Monitoring script
==============================================================================
This script is located in the directory $(cd $(dirname $0);echo $PWD)
Host Name : $UNIX_NODE
Script Name : "${0##*/}"
Version  : 2.0
Script Author : "PwC Env Team"
===============================================================================

I'm back on Job.
synchronous error monitor script version 2.0

EOF

/u01/app/oracle/product/11.2.0/db_1/bin/sqlplus -s $USERID/$PASSWORD@$CONNECT <<-EOS > ${SCRIPT_DIR}${LAST_CHECKED_DATE_TIME_FILE}
set echo off heading off feedback off;
SELECT TO_CHAR(max(CREATEDTTM)+1/(24*60*60),'DD-MON-YYYY HH24:MI:SS') from PSAPMSGSUBCON where IB_OPERATIONNAME='${LAST_CHECKED_DATE_TIME_FILE}' and STATUSSTRING='ERROR';
EOS

fi

done