Script to Monitor Cost Manager and other Inventory Interface Managers

#!/bin/bash

###############################################################################
# Script Name : MonInterfaceMgr.sh                                                                                                #
#                                                                                                                                                         #
# Description:                                                                                                                                    #
# Script to monitor Inventory Interface Manager                                                                             #
# Cost Manager; Lot Move Transaction; Material transaction; Move transaction                          #
# to send alert notification with the details to DBA Team if its INACTIVE                                  #
#                                                                                                                                                       #
# Usage : sh <script_name> <ORACLE_SID>                                                                               #
# For example : sh MonInterfaceMgr.sh ORCL                                                                              #
#                                                                                                                                                        #
# Created by : Kiran Jadhav – (https://h2hdba.blogspot.com)                                                            #
###############################################################################

# Initialize variables

INSTANCE=$1
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 Inventory Interface Manager”  >> $LOG_FILE
        echo “Usage   : sh <script_name> <ORACLE_SID> ” >> $LOG_FILE
  echo “For example : sh $PROGRAM.sh $ORACLE_SID” >> $LOG_FILE
        echo
}

if [ $# -lt 1 ] || [ “$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(*) FROM
 (
 SELECT
   x.PROCESS_TYPE “Name”,
   decode((select ‘1’
  FROM APPS.FND_CONCURRENT_REQUESTS cr,
  APPS.FND_CONCURRENT_PROGRAMS_VL cp,
  APPS.FND_APPLICATION A
    WHERE cp.concurrent_program_id = cr.concurrent_program_id
   AND cp.CONCURRENT_PROGRAM_NAME = x.PROCESS_NAME
   AND cp.APPLICATION_ID = a.application_id
   AND a.APPLICATION_SHORT_NAME = x.PROCESS_APP_SHORT_NAME
   AND PHASE_CODE != ‘C’ and rownum=1),’1′,’Active’,’Inactive’) “Status”,
   x.WORKER_ROWS “Worker Rows”,
   x.TIMEOUT_HOURS “Timeout Hours”,
   x.TIMEOUT_MINUTES “Timeout Minutes”,
   x.PROCESS_HOURS “Process Interval Hours”,
   x.PROCESS_MINUTES “Process Interval Minutes”,
   x.PROCESS_SECONDS “Process Interval Seconds”
 FROM (
   SELECT
   MIPC.PROCESS_CODE ,
   MIPC.PROCESS_STATUS ,
   MIPC.PROCESS_INTERVAL ,
   MIPC.MANAGER_PRIORITY ,
   MIPC.WORKER_PRIORITY ,
   MIPC.WORKER_ROWS ,
   MIPC.PROCESSING_TIMEOUT ,
   MIPC.PROCESS_NAME ,
   MIPC.PROCESS_APP_SHORT_NAME ,
   A.MEANING PROCESS_TYPE ,
   FLOOR(MIPC.PROCESS_INTERVAL/3600) PROCESS_HOURS ,
   FLOOR((MIPC.PROCESS_INTERVAL –
   (FLOOR(MIPC.PROCESS_INTERVAL/3600) * 3600))/60) PROCESS_MINUTES ,
   (MIPC.PROCESS_INTERVAL – (FLOOR(MIPC.PROCESS_INTERVAL/3600) * 3600) –
   (FLOOR((MIPC.PROCESS_INTERVAL –
   (FLOOR(MIPC.PROCESS_INTERVAL/3600) * 3600))/60) * 60)) PROCESS_SECONDS ,
   FLOOR(MIPC.PROCESSING_TIMEOUT/3600) TIMEOUT_HOURS ,
   FLOOR((MIPC.PROCESSING_TIMEOUT –
   FLOOR(MIPC.PROCESSING_TIMEOUT/3600) * 3600)/60) TIMEOUT_MINUTES
   FROM
   APPS.MTL_INTERFACE_PROC_CONTROLS MIPC,
   APPS.MFG_LOOKUPS A
   WHERE
   A.LOOKUP_TYPE = ‘PROCESS_TYPE’ AND
   A.LOOKUP_CODE = MIPC.PROCESS_CODE
 ) x
 — WHERE x.PROCESS_TYPE = ‘Cost Manager’ — uncomment this to display only the cost manager; Possible Values: Cost Manager; Lot Move Transaction; Material transaction; Move transaction
 WHERE decode((select ‘1’
  FROM APPS.FND_CONCURRENT_REQUESTS cr,
  APPS.FND_CONCURRENT_PROGRAMS_VL cp,
  APPS.FND_APPLICATION A
    WHERE cp.concurrent_program_id = cr.concurrent_program_id
   AND cp.CONCURRENT_PROGRAM_NAME = x.PROCESS_NAME
   AND cp.APPLICATION_ID = a.application_id
   AND a.APPLICATION_SHORT_NAME = x.PROCESS_APP_SHORT_NAME
   AND PHASE_CODE != ‘C’ and rownum=1),’1′,’Active’,’Inactive’) <> ‘Active’
 ORDER BY 1
 );

 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> Critical : $APPS_ID – One or More Interface Managers are Inactive 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 for the Inactive Interface Managers. Please start Inactive Managers ASAP.
 PROMPT

  SELECT
   x.PROCESS_TYPE “Name”,
   decode((select ‘1’
  FROM APPS.FND_CONCURRENT_REQUESTS cr,
  APPS.FND_CONCURRENT_PROGRAMS_VL cp,
  APPS.FND_APPLICATION A
    WHERE cp.concurrent_program_id = cr.concurrent_program_id
   AND cp.CONCURRENT_PROGRAM_NAME = x.PROCESS_NAME
   AND cp.APPLICATION_ID = a.application_id
   AND a.APPLICATION_SHORT_NAME = x.PROCESS_APP_SHORT_NAME
   AND PHASE_CODE != ‘C’ and rownum=1),’1′,’Active’,’Inactive’) “Status”,
   x.WORKER_ROWS “Worker Rows”,
   x.TIMEOUT_HOURS “Timeout Hours”,
   x.TIMEOUT_MINUTES “Timeout Minutes”,
   x.PROCESS_HOURS “Process Interval Hours”,
   x.PROCESS_MINUTES “Process Interval Minutes”,
   x.PROCESS_SECONDS “Process Interval Seconds”
 FROM (
   SELECT
   MIPC.PROCESS_CODE ,
   MIPC.PROCESS_STATUS ,
   MIPC.PROCESS_INTERVAL ,
   MIPC.MANAGER_PRIORITY ,
   MIPC.WORKER_PRIORITY ,
   MIPC.WORKER_ROWS ,
   MIPC.PROCESSING_TIMEOUT ,
   MIPC.PROCESS_NAME ,
   MIPC.PROCESS_APP_SHORT_NAME ,
   A.MEANING PROCESS_TYPE ,
   FLOOR(MIPC.PROCESS_INTERVAL/3600) PROCESS_HOURS ,
   FLOOR((MIPC.PROCESS_INTERVAL –
   (FLOOR(MIPC.PROCESS_INTERVAL/3600) * 3600))/60) PROCESS_MINUTES ,
   (MIPC.PROCESS_INTERVAL – (FLOOR(MIPC.PROCESS_INTERVAL/3600) * 3600) –
   (FLOOR((MIPC.PROCESS_INTERVAL –
   (FLOOR(MIPC.PROCESS_INTERVAL/3600) * 3600))/60) * 60)) PROCESS_SECONDS ,
   FLOOR(MIPC.PROCESSING_TIMEOUT/3600) TIMEOUT_HOURS ,
   FLOOR((MIPC.PROCESSING_TIMEOUT –
   FLOOR(MIPC.PROCESSING_TIMEOUT/3600) * 3600)/60) TIMEOUT_MINUTES
   FROM
   APPS.MTL_INTERFACE_PROC_CONTROLS MIPC,
   APPS.MFG_LOOKUPS A
   WHERE
   A.LOOKUP_TYPE = ‘PROCESS_TYPE’ AND
   A.LOOKUP_CODE = MIPC.PROCESS_CODE
 ) x
 — WHERE x.PROCESS_TYPE = ‘Cost Manager’ — uncomment this to display only the cost manager; Possible Values: Cost Manager; Lot Move Transaction; Material transaction; Move transaction
 ORDER BY 1;

PROMPT
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT <b>Step To Start Interface Managers which are in Inactive State:</b>
PROMPT
PROMPT 1. Login wuth SYSADMIN -> Select Responsibility – “Inventory” ->
PROMPT 2. Navigate to “Setup” -> Transactions > Interface Managers
PROMPT 3. Go to Menu -> Tools -> Launch Manager
PROMPT
PROMPT <b>Steps to Start -> “Cost Manager”:</b>
PROMPT
PROMPT 1. Login with SYSADMIN -> Select Responsibility – “Inventory”
PROMPT 2. Navigate to “Setup” > Transactions > Interface Managers
PROMPT 3. Select “Cost Manager”
PROMPT 4. Go to Menu -> Tools -> Launch Manager
PROMPT 5. Go to Menu -> View -> Requests -> Query Name = “Cost Manager”
PROMPT
PROMPT <b>Steps to Start -> “Process transaction interface” or “Transaction Manager”:</b>
PROMPT
PROMPT 1. Login wuth SYSADMIN -> Select Responsibility – “Inventory”
PROMPT 2. Navigate to “Setup” -> Transactions > Interface Managers
PROMPT 3. Select “Material Transaction”
PROMPT 4. Go to Menu -> Tools -> Launch Manager
PROMPT 5. Go to Menu -> View -> Requests -> Query Name = “Process transaction interface”  and “”Inventory transaction worker”
PROMPT

 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: Critical : $APPS_ID – One or More Interface Managers are Inactive 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 “Interface Managers Running Fine” >> $OUT_FILE
fi

  • December 31, 2018 | 16 views
  • Comments