Pre-requisites to Run the script:
Make sure mailx and sendmail is installed on your OS, also check below environements/file is created prior to execute the scripts.
This script is tested on Linux Server.
This script needs to be deployed in database node/server.
#Base location for the DBA scripts DBA_SCRIPTS_HOME=$HOME/DBA_MON
#OS User profile where database environment file is set $HOME/.bash_profile
Create Custom .sysenv file for scripts
Download(.sysenv)
Download .sysenv file and save it under $HOME/DBA_MON $ chmod 777 .sysenv
$ cat $HOME/DBA_MON/.sysenv
export DBA_SCRIPTS_HOME=$HOME/DBA_MON export PATH=${PATH}:$DBA_SCRIPTS_HOME export DBA_EMAIL_LIST=kiran.jadhav@domain.com,jadhav.kiran@domain.com #Below parameter is used in script, whenever there is planned downtime you can set it to Y so there will be no false alert. export DOWNTIME_MODE=N
Script to check Concurrent requests which are running for more than given threshold (mins) to send alert notification with the details to DBA Team.
Download(LongRunningReq.sh)
Download LongRunningReq.sh and save it under $HOME/DBA_MON/bin/ $ chmod 755 LongRunningReq.sh
#!/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
Logs and Out files will be generated under $DBA_SCRIPTS_HOME/logs/$HOST_NAME So make sure to create logs/$HOST_NAME directory under $DBA_SCRIPTS_HOME before executing the script.
Once the script is ready, then as per the requirement please schedule it in crontab/OEM.
Execute the Script as below: Syntax : sh <script_name> <ORACLE_SID> <Time in mins> $ cd $HOME/DBA_MON/bin $ sh LongRunningReq.sh ORCL 30
This script will send the notification with Concurrent request details which are running for
more than 30 minutes.
This comment has been removed by the author.
ReplyDelete