Script to Monitor Long Running Concurrent Requests

#!/bin/bash
#############################################################################################
# Script Name : LongRunningReq.sh                                                           #
#                                                                                           #
# Description:                                                                              # 
# Script to check Concurrent requests which are running for more than given threshold (mins)#
# to send alert notification with the details to DBA Team                                   #
#                                                                                           #
# Usage : sh <script_name> <ORACLE_SID> <Time in mins>                                      #
# For example : sh LongRunningReq.sh ORCL 30                                                #
#                                                                                           #
# Created by : Kiran Jadhav - (https://h2hdba.blogspot.com)                                 #
#############################################################################################
# Initialize variables
INSTANCE=$1
THRESHOLD=$2
HOST_NAME=`hostname | cut -d'.' -f1`
PROGRAM=`basename $0 | cut -d'.' -f1`
export DBA_SCRIPTS_HOME=$HOME/DBA_MON
APPS_ID=`echo $INSTANCE | tr '[:lower:]' '[:upper:]'`
LOG_DIR=$DBA_SCRIPTS_HOME/logs/$HOST_NAME
OUT_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.html.out
LOG_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.log
ERR_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.err
LOG_DATE=`date`
# Source the env
. $HOME/.bash_profile
. $DBA_SCRIPTS_HOME/.sysenv
if [ $? -ne 0 ]; then
   echo "$LOG_DATE" > $LOG_FILE
   echo "Please pass correct environment : exiting the script  n" >> $LOG_FILE
   cat $LOG_FILE
   exit
fi
if [ -s $OUT_FILE ]; then
 echo "$LOG_DATE" > $LOG_FILE
 echo "Deleting existing output file $OUT_FILE" >> $LOG_FILE
 rm -f $OUT_FILE
 cat $LOG_FILE
fi
# If there is a plan downtime then create $ORACLE_SID.down file in $DBA_SCRIPTS_HOME to silent the alerts during maintenance window.
if [ -f $DBA_SCRIPTS_HOME/`echo $ORACLE_SID`.down ]; then
 echo "$LOG_DATE" >> $LOG_FILE
        echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
        cat $LOG_FILE
 exit
fi
if [ $DOWNTIME_MODE = "Y" ]; then
 echo "$LOG_DATE" >> $LOG_FILE
 echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
 cat $LOG_FILE
 exit
fi
usage()
{
  echo "$LOG_DATE" > $LOG_FILE
        echo "Script To Check Long Running Concurrent Requests"  >> $LOG_FILE
        echo "Usage   : sh <script_name> <ORACLE_SID> <Time in mins>" >> $LOG_FILE
  echo "For example : sh $PROGRAM.sh $ORACLE_SID 30" >> $LOG_FILE
        echo
}
if [ $# -lt 2 ] || [ "$INSTANCE" != "$ORACLE_SID" ]; then
    usage
    echo "Error : Insufficient arguments." >> $LOG_FILE
 cat $LOG_FILE
    exit
fi
get_count()
{
 sqlplus -s '/as sysdba' <<!
 set heading off
 set feedback off
 select count(1) from
 (
  select distinct qt.user_concurrent_queue_name "Queue Name"
      ,c2.user_concurrent_program_name "Program Name"
     ,a.request_id "Request Id"
     ,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent Req"
     ,FLVP.meaning "Phase"
     ,FLVS.meaning "Status"
     ,a.argument_text " Arguments "
     ,b.os_process_id "OS Process"
     ,vs.sid "DB SID"
     ,vp.spid "DB PID"
     ,TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') "Start Date"
     ,round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) "Time Spent (Min)"
     ,u.user_name "Who Submitted"
     ,u.email_address "Submitted By - Email"
  from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
   ,applsys.fnd_concurrent_queues q
   ,applsys.fnd_concurrent_queues_tl qt
   ,APPLSYS.fnd_concurrent_programs_tl c2
   ,APPLSYS.fnd_concurrent_programs c
   ,APPLSYS.FND_LOOKUP_VALUES FLVP
   ,APPLSYS.FND_LOOKUP_VALUES FLVS
   ,APPLSYS.FND_USER u
   ,gv$session vs
   ,gv$process vp
  where a.controlling_manager = b.concurrent_process_id
    and a.concurrent_program_id = c.concurrent_program_id
    and a.program_application_id = c.application_id
    and c2.concurrent_program_id = c.concurrent_program_id
    and a.phase_code in ('I','P','R','T')
    and u.user_id=a.requested_by
    and a.phase_code=FLVP.Lookup_Code
    and FLVP.Lookup_Type='CP_PHASE_CODE'
    and FLVP.language='US'
    and a.status_code=FLVS.Lookup_Code
    and FLVS.Lookup_Type='CP_STATUS_CODE'
    and FLVS.language='US'
    and FLVS.view_application_id=0
    and b.queue_application_id = q.application_id
    and b.concurrent_queue_id = q.concurrent_queue_id
    and q.application_id = qt.application_id
    and qt.language='US'
    and q.concurrent_queue_id = qt.concurrent_queue_id
    and c2.language = 'US'
    and vs.process (+) = b.os_process_id
    and vs.paddr = vp.addr (+)
    and a.status_code='R'
    and c2.USER_CONCURRENT_PROGRAM_NAME not in ('Planning Manager','Cost Manager')
    and vs.inst_id=vp.inst_id
    and round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) > $THRESHOLD
  order by 11 desc
 );
 exit;
!
}
count=`get_count`
#echo $count
echo "$LOG_DATE" > $ERR_FILE
get_count >> $ERR_FILE
ERR_COUNT=`grep "ORA-" $ERR_FILE |wc -l`
if [ $ERR_COUNT -gt 0 ]; then
 cat $ERR_FILE | mailx -s "<ERROR> Alert: $APPS_ID - Long Running Concurrent Requests on $HOST_NAME " $DBA_EMAIL_LIST
 exit
fi
if [ $count -gt 0 ];
then
 sqlplus -s '/as sysdba' <<EOF
 SET ECHO OFF
 SET pagesize 1000
 set feedback off
 set lines 180
 SET MARKUP HTML ON SPOOL ON -
 HEAD '<title></title> -
 <style type="text/css"> -
    table { background: #eee; } -
    th { font:bold 10pt Arial,Helvetica,sans-serif; color:#b7ceec; background:#151b54; padding: 5px; align:center; } -
    td { font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding: 5px; align:center; } -
 </style>' TABLE "border='1' align='left'" ENTMAP OFF
 spool $OUT_FILE
 PROMPT Hi Team,
 PROMPT
 PROMPT Please check below concurrent requests which are running for more than $THRESHOLD Mins 
 PROMPT
 select distinct qt.user_concurrent_queue_name "Queue Name"
     ,c2.user_concurrent_program_name "Program Name" 
    ,a.request_id "Request Id"
    ,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent Req"
    ,FLVP.meaning "Phase"
    ,FLVS.meaning "Status"
    ,a.argument_text " Arguments " 
    ,b.os_process_id "OS Process"
    ,vs.sid "DB SID"
    ,vp.spid "DB PID"
    ,TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') "Start Date"
    ,round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) "Time Spent (Min)"
    ,u.user_name "Who Submitted"
    ,u.email_address "Submitted By - Email"
 from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
  ,applsys.fnd_concurrent_queues q
  ,applsys.fnd_concurrent_queues_tl qt
  ,APPLSYS.fnd_concurrent_programs_tl c2
  ,APPLSYS.fnd_concurrent_programs c
  ,APPLSYS.FND_LOOKUP_VALUES FLVP
  ,APPLSYS.FND_LOOKUP_VALUES FLVS
  ,APPLSYS.FND_USER u
  ,gv$session vs
  ,gv$process vp
 where a.controlling_manager = b.concurrent_process_id
   and a.concurrent_program_id = c.concurrent_program_id
   and a.program_application_id = c.application_id
   and c2.concurrent_program_id = c.concurrent_program_id
   and a.phase_code in ('I','P','R','T')
   and u.user_id=a.requested_by
   and a.phase_code=FLVP.Lookup_Code
   and FLVP.Lookup_Type='CP_PHASE_CODE'
   and FLVP.language='US'
   and a.status_code=FLVS.Lookup_Code
   and FLVS.Lookup_Type='CP_STATUS_CODE'
   and FLVS.language='US'
   and FLVS.view_application_id=0
   and b.queue_application_id = q.application_id
   and b.concurrent_queue_id = q.concurrent_queue_id
   and q.application_id = qt.application_id
   and qt.language='US'
   and q.concurrent_queue_id = qt.concurrent_queue_id
   and c2.language = 'US'
   and vs.process (+) = b.os_process_id
   and vs.paddr = vp.addr (+)
   and a.status_code='R'
   and c2.USER_CONCURRENT_PROGRAM_NAME not in ('Planning Manager','Cost Manager')
   and vs.inst_id=vp.inst_id
   and round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) > $THRESHOLD
 order by 11 desc;
 SPOOL OFF
 SET MARKUP HTML OFF
 exit;
EOF
(
echo "To: $DBA_EMAIL_LIST"
echo "MIME-Version: 1.0"
echo "Content-Type: multipart/alternative; "
echo ' boundary="PAA08673.1018277622/server.xyz.com"'
echo "Subject: Alert : $APPS_ID - Long Running Concurrent Requests on $HOST_NAME"
echo ""
echo "This is a MIME-encapsulated message"
echo ""
echo "--PAA08673.1018277622/server.xyz.com"
echo "Content-Type: text/html"
echo ""
cat $OUT_FILE
echo "--PAA08673.1018277622/server.xyz.com"
) | /usr/sbin/sendmail -t
echo "$LOG_DATE" > $LOG_FILE
echo "Details sent through an email" >> $LOG_FILE
cat $LOG_FILE
else 
    echo "$LOG_DATE" > $OUT_FILE
 echo "No Long Running Concurrent Requests" >> $OUT_FILE
fi
Doyensys
A technology service company that provides innovative solutions around the oracle platform With Laser focus on customer delight as the primary success parameter.