Script to Monitor Current Database Size and its Growth Since Last Run

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 Current Database Size and its Growth Since Last Run. 

Download(MonDBSizeGrowth.sh)

Download MonDBSizeGrowth.sh and save it under $HOME/DBA_MON/bin/
$ chmod 755 MonDBSizeGrowth.sh

#!/bin/bash

###################################################################################
# Script Name :  MonDBSizeGrowth.sh                                               #
#                                                                                 #
# Description:                                                                    #
# Script to Monitor Current Database Size and its Growth Since Last Run           #
#                                                                                 #
# Usage : sh <script_name> <ORACLE_SID>                                           #
# For example : sh MonDBSizeGrowth.sh ORCL                                        #
#                                                                                 #
# Note : Initially Run this script for 2 Times                                    #
#                                                                                 #
# 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
PREV_PHY_SIZE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.prevphy
PREV_LOGI_SIZE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.prevlogi
CURR_PHY_SIZE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.currphy
CURR_LOGI_SIZE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.currlogi
LOG_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.log
ERR_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.err
LOG_DATE=`date`
LAST_CAPTURED_DATE_TIME=`grep "Current Database Size" $OUT_FILE |cut -d'-' -f2`

# 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 [ -s $CURR_PHY_SIZE ]; then
        echo "$LOG_DATE" > $LOG_FILE
        echo "Moving $CURR_PHY_SIZE to Previous $PREV_PHY_SIZE" >> $LOG_FILE
        mv $CURR_PHY_SIZE $PREV_PHY_SIZE
        #cat $LOG_FILE
fi

if [ -s $CURR_LOGI_SIZE ]; then
        echo "$LOG_DATE" > $LOG_FILE
        echo "Moving $CURR_LOGI_SIZE to Previous $PREV_LOGI_SIZE" >> $LOG_FILE
        mv $CURR_LOGI_SIZE $PREV_LOGI_SIZE
        #cat $LOG_FILE
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

# 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

usage()
{
 echo "$LOG_DATE" > $LOG_FILE
    echo "Script To Monitor Current Database Size and its Growth Since Last Run"  >> $LOG_FILE
    echo "Usage   : ksh <script_name> <ORACLE_SID> " >> $LOG_FILE
 echo "For example : ksh $PROGRAM $ORACLE_SID" >> $LOG_FILE
    echo
}

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


sqlplus -s '/as sysdba' <<EOF

 SET ECHO OFF
 SET pagesize 1000
 set feedback off
 set lines 180

 set heading off;
 spool $CURR_PHY_SIZE

 SELECT round(SUM (BYTES / (1014 * 1024 ))) "PHYSICAL_SIZE(MB)" FROM dba_data_files;

 SPOOL OFF;

 spool $CURR_LOGI_SIZE

 SELECT "PHYSICAL_SIZE(MB)" - "FREE_SPACE(MB)" "LOGICAL_SIZE(MB)"
   FROM (SELECT (SELECT round(SUM (BYTES / (1014 * 1024 )))
       FROM dba_data_files) "PHYSICAL_SIZE(MB)", (SELECT round(SUM (BYTES / (1024 * 1024 )))
                   FROM dba_free_space) "FREE_SPACE(MB)"
     FROM DUAL);

 spool off;
 exit;
EOF

CURR_PHY_SIZE_V=`cat $CURR_PHY_SIZE`
CURR_LOGI_SIZE_V=`cat $CURR_LOGI_SIZE`
PREV_PHY_SIZE_V=`cat $PREV_PHY_SIZE`
PREV_LOGI_SIZE_V=`cat $PREV_LOGI_SIZE`

#echo $CURR_PHY_SIZE_V
#echo $CURR_LOGI_SIZE_V
#echo $PREV_PHY_SIZE_V
#echo $PREV_LOGI_SIZE_V

PHY_GROWTH=`expr $CURR_PHY_SIZE_V - $PREV_PHY_SIZE_V`
#echo $PHY_GROWTH

LOGI_GROWTH=`expr $CURR_LOGI_SIZE_V - $PREV_LOGI_SIZE_V` 
#echo $LOGI_GROWTH


sqlplus -s '/as sysdba' <<EOF
 SET ECHO OFF
 SET pagesize 1000
 set feedback off
 set lines 180

 set heading on;
 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 Current Database Size - `date`:
 PROMPT

    SELECT Name "DB_NAME","PHYSICAL_SIZE(GB)", "PHYSICAL_SIZE(GB)" - "FREE_SPACE(GB)" "LOGICAL_SIZE(GB)", "FREE_SPACE(GB)"
  FROM (SELECT (SELECT round(SUM (BYTES / (1014 * 1024 * 1024)))
                  FROM dba_data_files) "PHYSICAL_SIZE(GB)", (SELECT round(SUM (BYTES / (1024 * 1024 * 1024)))
                                                               FROM dba_free_space) "FREE_SPACE(GB)"
          FROM DUAL),v\$database;

 PROMPT <br>
 PROMPT <br>
 PROMPT <br>
 PROMPT Database Growth (in MB) Since $LAST_CAPTURED_DATE_TIME  

 select Name "DB_NAME",$PHY_GROWTH "PHYSICAL_GROWTH(MB)", $LOGI_GROWTH "LOGICAL_GROWTH(MB)"  from dual,v\$database;

 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: Report : $APPS_ID - Current Database Size and its Growth 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
#Taking Backup of Output File To keep History On Server
cp $OUT_FILE $OUT_FILE.`date +"%m-%b-%Y:%T"`

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 MonDBSizeGrowth.sh DEV11G

This script will send the notification with current database size and its growth since last run.

Sample Output:

Script to Monitor Server CPU Load Average

Pre-requisites to Run the script:

Make sure mailx  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 Server CPU Load Average and Send Notification if Utilization is above threshold.



Download(ServerLoadMon.sh)

Download ServerLoadMon.sh and save it under $HOME/DBA_MON/bin/
$ chmod 755 ServerLoadMon.sh

#!/bin/bash

############################################################
# Script Name : ServerLoadMon.sh                           #
#                                                          #
# Description:                                             #
# Script to Monitor Server CPU Load Average and            #
# Send Notification if Utilization is above threshold      #                                                                          
#                                                          #
# Usage : sh <script_name> <Threshold>                     #
# For example : sh ServerLoadMon.sh 10                     #
#                                                          #
# Created by : Kiran Jadhav -(https://h2hdba.blogspot.com) #
############################################################

# Initialize variables

THRESHOLD=$1
HOST_NAME=`hostname | cut -d'.' -f1`
PROGRAM=`basename $0 | cut -d'.' -f1`
export DBA_SCRIPTS_HOME=$HOME/DBA_MON
LOG_DIR=$DBA_SCRIPTS_HOME/logs/$HOST_NAME
OUT_FILE=$LOG_DIR/`echo $PROGRAM`.out
LOG_FILE=$LOG_DIR/`echo $PROGRAM`.log
ERR_FILE=$LOG_DIR/`echo $PROGRAM`.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 [ $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 Server Load"  >> $LOG_FILE
        echo "Usage   : sh <script_name> <LoadAvg> " >> $LOG_FILE
  echo "For example : sh $PROGRAM.sh 10" >> $LOG_FILE
        echo
}

if [ $# -lt 1 ]; then
    usage
    echo "Error : Insufficient arguments." >> $LOG_FILE
 cat $LOG_FILE
    exit
fi

LOADAVG=`/usr/bin/uptime|awk '{print $(NF-2)}'|cut -d. -f1`
#echo $LOADAVG
 
if [ "$LOADAVG" -ge "$THRESHOLD" ]; then
 /usr/bin/uptime > $OUT_FILE 
   cat $OUT_FILE| mailx -s "Critical : High Load ( > $THRESHOLD ) average on $HOST_NAME" $DBA_EMAIL_LIST
else 
    echo "$LOG_DATE" > $OUT_FILE
 echo "Server Load is NORMAL" >> $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> <Threshold>  

$ cd $HOME/DBA_MON/bin
$ sh ServerLoadMon.sh 10

This script will send the notification with details if Server Load Average is above 10.

Script To Monitor Filesystem (Mount Point) Space or Utilization

Pre-requisites to Run the script:

Make sure mailx  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 Filesystem space and send notification if filesystem usage% is more than given threshold..



Download(FSMon.sh)

Download FSMon.sh and save it under $HOME/DBA_MON/bin/
$ chmod 755 FSMon.sh

#!/bin/bash

############################################################
# Script Name : FSMon.sh                                   # 
#                                                          #
# Description:                                             # 
# Script to check Filesystem space and send notification   #
# if filesystem usage% is more than given threshold.       #                                                                          
#                                                          #
# Usage : sh <script_name> <Used% Threshold>               #
# For example : sh FSMon.sh 90                             #
#                                                          #
# Created by : Kiran Jadhav -(https://h2hdba.blogspot.com) #
############################################################

# Initialize variables

THRESHOLD=$1
HOST_NAME=`hostname | cut -d'.' -f1`
PROGRAM=`basename $0 | cut -d'.' -f1`
export DBA_SCRIPTS_HOME=$HOME/DBA_MON
LOG_DIR=$DBA_SCRIPTS_HOME/logs/$HOST_NAME
OUT_FILE=$LOG_DIR/`echo $PROGRAM`.out
LOG_FILE=$LOG_DIR/`echo $PROGRAM`.log
ERR_FILE=$LOG_DIR/`echo $PROGRAM`.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 [ $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 OS Filesystem"  >> $LOG_FILE
        echo "Usage   : sh <script_name> <Used% Threshold>  " >> $LOG_FILE
 echo "For example : sh $PROGRAM.sh 90" >> $LOG_FILE
        echo
}

if [ $# -lt 1 ]; then
    usage
    echo "Error : Insufficient arguments." >> $LOG_FILE
 cat $LOG_FILE
    exit
fi


/bin/df -h|grep -ivE '^Filesystem|tmpfs|cdrom|vol' | awk '{print $(NF-1),"\t",$NF}'| while read output;
do
  #echo $output
  usep=$(echo $output | awk '{ print $1}' | cut -d'%' -f1  )
 # partition=$(echo $output | awk '{ print $1 }' )
  filesystem=$(echo $output | awk '{ print $2 }' )
  if [ $usep -ge $THRESHOLD ]; then
    echo "Filesystem: \"$filesystem\"   is \"($usep%)\"   FULL" >> $OUT_FILE
  fi
done 

if [ -s $OUT_FILE ]; then
 mailx -s "Critical: Filesystem usage is more than $THRESHOLD% on $HOST_NAME" $DBA_EMAIL_LIST < $OUT_FILE
else 
 echo "Disk Space Usage is normal" > $LOG_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> <Used% Threshold>  

$ cd $HOME/DBA_MON/bin
$ sh FSMon.sh 90

This script will send the notification with details if any of the mount point usage 

reached to 90% or Above.

Script to Monitor Cost Manager and other Inventory Interface Managers

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.