Workflow Mailer Automatic Bounce script

Application:- 11.5.10.2
Database:- 10.2.0.4
OS:- Solaris 10

One of the task for Apps DBA is to montior mailer and bring it up through OAM.

How about automating this task?

Below script will be helpful if you want to automate task for bringing up your mailer automatically through scripting.

Before starting please collect value of COMPONENT_ID from FND_SVC_COMPONENTS
against 'Workflow Notification Mailer' and place it in below script.

select COMPONENT_ID from fnd_svc_components where COMPONENT_NAME = 'Workflow Notification Mailer';

#!/bin/ksh
#set -x
################################################################################
#
# Script Name: Mailer_Monitor.ksh
#
# Directory:
#
# Description:
#
#
#
# Arguments:
#
# Output/log:
#
# called by:
#
# called scripts:
#
# Created By: Deepak Jha
#
# Modification History:
#
# DATE: DBA: CR#/SR#: Modification Description
# __________ ___________ __________ ________________________
#
#
################################################################################
export ORACLE_SID=TEST
export ORACLE_HOME=/opt/oracle/${ORACLE_SID}/db/10.2
HOSTNAME=`hostname`
DBREP=/tmp/mailerdown.rep
EMAIL_ADDRESS=abc@xyz.com
rm -f $DBREP 2>/dev/null
${ORACLE_HOME}/bin/sqlplus -s 'apps/apps_pass'<set heading off
set pages 0
set feedback off
spool /tmp/mailerstats.rep
SELECT COMPONENT_STATUS from FND_SVC_COMPONENTS
where COMPONENT_ID= '';
spool off
EOF

MONITOR=`grep -v "SQL>" /tmp/mailerstats.rep`
if [ $MONITOR != "RUNNING" ]
then
echo "Mailer is not running and will be started" >> $DBREP
cat $DBREP | mailx -s "$HOSTNAME:$ORACLE_SID: MAILER DOWN" "$EMAIL_ADDRESS"
${ORACLE_HOME}/bin/sqlplus -s 'apps/apps_pass'<update fnd_concurrent_queues
set control_code = 'A'
where concurrent_queue_name = 'WFMLRSVC';
commit;
EOF
else
exit 0;
fi
#exit


Note:- Above script has scope of enhancement.
Thanks,
Deepak