Shell Script to Check Concurrent Request Running for more than 2 hrs and send mail alert for Oracle Apps

Shell Script to run the check the concurrent request running more than 2 hrs

cat concurrent_request_more2hrs.sh

#!/bin/bash

. ~/.bash_profile

#Source DB environment file

. FUAT_funebs122

rm -f /home/oracle/scripts/concrqst_hours.html

cd /home/oracle/scripts

sqlplus "/ as sysdba" @concrqst_hours.sql

if grep --quiet 'RQST_ID' /home/oracle/scripts/concrqst_hours.html;

then

echo "Sending email"

echo "Concurrent request running for 2 hours or more" | mutt -e "my_hdr Content-Type: text/html" -s "FUAT -- Long Running Request" support@funoracleapps.com < concrqst_hours.html

SQL script which will be called to check long-running concurrent requests

$ cat concrqst_hours.sql

SET MARKUP HTML ON SPOOL ON

set termout off

set pages 999

set pagesize 999

set feedback off

SET MARKUP HTML ON TABLE "class=sysaud cellspacing=2 border='2' width='95%' align='center' " ENTMAP OFF

spool concrqst_hours.html

set pagesize 1000

set pause off

set linesize 150

prompt

prompt FUAT - Concurrent Request Running for 2 hours or more:

prompt

column fcr.request_id format a10 heading 'RQST_ID'

column fu.user_name format a10 heading 'Username'

column fr.responsibility_name format a35 heading 'Resp Name'

column fcp.user_concurrent_program_name format a40 heading 'Program Name'

column fcr.actual_start_date format a30 heading 'Start Date'

column fcr.status.code heading 'Status'

column fcr.actual_start_date format a10 heading 'Runtime Minutes'

column fcr.os_process_id format a15 heading 'SID, SERIAL'

column fcr.os_process_id format a10 heading 'SPID'

column fcr.os_process_id format a10 heading 'OS PID'

prompt

SELECT fcr.request_id rqst_id

,fu.user_name

,fr.responsibility_name

,fcp.user_concurrent_program_name program_name

,TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS')start_datetime

,DECODE (fcr.status_code, 'R', 'R:Running', fcr.status_code) status

,ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) runtime_min

,fcr.oracle_process_id "SPID"

,fcr.os_process_id os_pid

FROM apps.fnd_concurrent_requests fcr

,apps.fnd_user fu

,apps.fnd_responsibility_vl fr

,apps.fnd_concurrent_programs_vl fcp

WHERE fcr.status_code LIKE 'R'

AND fu.user_id = fcr.requested_by

AND fr.responsibility_id = fcr.responsibility_id

AND fcr.concurrent_program_id = fcp.concurrent_program_id

AND fcr.program_application_id = fcp.application_id

AND ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) > 120

ORDER BY fcr.concurrent_program_id

,request_id DESC;

spool off

exit

crontab to schedule it every 15 mins

*/15 * * * * /home/oracle/scripts/concurrent_request_more2hrs.sh > /tmp/concurrent_request_more2hrs.log 2>&1

If you like please follow and comment

Shell Script to Check Concurrent Request Running for more than 2 hrs and send mail alert for Oracle Apps