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 Monitor Cost Manager and other Inventory Interface Managers & to send alert notification with the details to DBA Team if its INACTIVE.
Download(MonInterfaceMgr.sh)
Download MonInterfaceMgr.sh and save it under $HOME/DBA_MON/bin/ $ chmod 755 MonInterfaceMgr.sh
#!/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
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> $ cd $HOME/DBA_MON/bin $ sh MonInterfaceMgr.sh ORCL
This script will send the notification if Cost Manager or any of the Inventory Interface Manager is INACTIVE.
No comments:
Post a Comment