Script To Monitor “Gather Schema Statistics” concurrent request

#!/bin/bash

#############################################################################################################
# Script Name : MonGSSRequest.sh                                                                                                    #
#                                                                                                                                                            #
# Description:                                                                                                                                       #
# Script To Monitor “Gather Schema Statistics” concurrent request.                                                  #
# If error-out then send notification to DBA Team                                                                              #
#                                                                                                                                                            #
# Usage : sh <script_name> <ORACLE_SID> <History_No_Days>                                                   #
#                                                                                                                                                              #
#                                                                                                                                                              #
#                                                                                                           #
#############################################################################################################

# 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 monitor Gather Schema Statistics Conc. request”  >> $LOG_FILE
        echo “Usage   : sh <script_name> <ORACLE_SID> <no_days> ” >> $LOG_FILE
  echo “For example : sh $PROGRAM.sh $ORACLE_SID 7” >> $LOG_FILE
        echo
}

if [ $# -lt 2 ] || [ “$INSTANCE” != “$ORACLE_SID” ]; then
    usage
    echo “Error : Insufficient arguments.” >> $LOG_FILE
 cat $LOG_FILE
    exit
fi

get_err_count()
{
 sqlplus -s ‘/as sysdba’ <<!
 set heading off
 set feedback off
 select count(*) from
  (
  SELECT a.request_id “Req Id”
      ,substr(u.user_name,1,25) “Submitted by”
      ,decode(a.PHASE_CODE,’C’,’Completed’,’I’,’Inactive’,’P’,’Pending’,’R’,’Running’,’NA’) “Phasecode”,
      decode(a.STATUS_CODE, ‘A’,’Waiting’, ‘B’,’Resuming’, ‘C’,’Normal’, ‘D’,’Cancelled’, ‘E’,’Error’, ‘F’,’Scheduled’, ‘G’,’Warning’, ‘H’,’On Hold’, ‘I’,’Normal’, ‘M’,
      ‘No Manager’, ‘Q’,’Standby’, ‘R’,’Normal’, ‘S’,’Suspended’, ‘T’,’Terminating’, ‘U’,’Disabled’, ‘W’,’Paused’, ‘X’,’Terminated’, ‘Z’,’Waiting’) “Status”
      , a.argument_text ” Arguments “
      , TO_CHAR(actual_start_date, ‘DD-MON-YYYY HH24:MI:SS’) “Start Date”
      , TO_CHAR(actual_completion_date, ‘DD-MON-YYYY HH24:MI:SS’) “Completion Date”
      , ctl.user_concurrent_program_name “Conc Program Name”,a.COMPLETION_TEXT “Error/Warning”
      FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
      ,applsys.fnd_concurrent_queues q
      ,APPLSYS.fnd_concurrent_programs c
      ,APPLSYS.fnd_concurrent_programs_tl ctl
      ,APPLSYS.fnd_user u
      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 a.status_code in (‘D’,’E’,’G’,’H’,’M’,’S’,’T’,’U’,’X’,’W’)  — for the status ERROR
      AND a.phase_code = ‘C’ — Phase code COMPLETED
      AND actual_start_date >= sysdate – $THRESHOLD
      AND b.queue_application_id = q.application_id
      AND b.concurrent_queue_id = q.concurrent_queue_id
      AND ctl.concurrent_program_id = c.concurrent_program_id
      AND ctl.LANGUAGE = ‘US’
      AND a.REQUESTED_BY=u.user_id
      AND ctl.user_concurrent_program_name = ‘Gather Schema Statistics’
  );

 exit;
!
}

err_count=`get_err_count`
#echo $err_count

echo “$LOG_DATE” > $ERR_FILE
get_err_count >> $ERR_FILE
ERR_FILE_COUNT=`grep “ORA-” $ERR_FILE |wc -l`

if [ $ERR_FILE_COUNT -gt 0 ]; then
 cat $ERR_FILE | mailx -s “<ERROR> Alert: $APPS_ID – Gather Schema Statistics Request not completed normal on $HOST_NAME ” $DBA_EMAIL_LIST
 exit
fi

if [ $err_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 Gather Schema Statistics request not completed normal, take necessary action ASAP.
 PROMPT

 SELECT a.request_id “Req Id”
    ,substr(u.user_name,1,25) “Submitted by”
    ,decode(a.PHASE_CODE,’C’,’Completed’,’I’,’Inactive’,’P’,’Pending’,’R’,’Running’,’NA’) “Phasecode”,
    decode(a.STATUS_CODE, ‘A’,’Waiting’, ‘B’,’Resuming’, ‘C’,’Normal’, ‘D’,’Cancelled’, ‘E’,’Error’, ‘F’,’Scheduled’, ‘G’,’Warning’, ‘H’,’On Hold’, ‘I’,’Normal’, ‘M’,
    ‘No Manager’, ‘Q’,’Standby’, ‘R’,’Normal’, ‘S’,’Suspended’, ‘T’,’Terminating’, ‘U’,’Disabled’, ‘W’,’Paused’, ‘X’,’Terminated’, ‘Z’,’Waiting’) “Status”
    , a.argument_text ” Arguments “
    , TO_CHAR(actual_start_date, ‘DD-MON-YYYY HH24:MI:SS’) “Start Date”
    , TO_CHAR(actual_completion_date, ‘DD-MON-YYYY HH24:MI:SS’) “Completion Date”
    , ctl.user_concurrent_program_name “Conc Program Name”,a.COMPLETION_TEXT “Error/Warning”
    FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
    ,applsys.fnd_concurrent_queues q
    ,APPLSYS.fnd_concurrent_programs c
    ,APPLSYS.fnd_concurrent_programs_tl ctl
    ,APPLSYS.fnd_user u
    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 a.status_code in (‘D’,’E’,’G’,’H’,’M’,’S’,’T’,’U’,’X’,’W’)  — for the status ERROR
    AND a.phase_code = ‘C’ — Phase code COMPLETED
    AND actual_start_date >= sysdate – $THRESHOLD
    AND b.queue_application_id = q.application_id
    AND b.concurrent_queue_id = q.concurrent_queue_id
    AND ctl.concurrent_program_id = c.concurrent_program_id
    AND ctl.LANGUAGE = ‘US’
    AND a.REQUESTED_BY=u.user_id
    AND ctl.user_concurrent_program_name = ‘Gather Schema Statistics’;

    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 – Gather Schema Statistics Request not completed normal 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 “`date`” > $LOG_FILE
echo “Details sent through an email” >> $LOG_FILE
cat $LOG_FILE

else
    echo “`date`” > $OUT_FILE
 echo “Gather Schema Statistics Request completed normal in last $THRESHOLD Days” >> $OUT_FILE
fi

  • December 31, 2018 | 17 views
  • Comments