Oracle Workflow Mailer Service Component Auto Restart – Crontab

Oracle Workflow Mailer Service Component Restart

What is Oracle Workflow for and Why we need this solution ?

Oracle Workflow Notification Mailer is a service component that sends e-mail notifications to users through a mail application, and processes e-mail responses.

Workflow have many components and complex structure to do this easy workflow process.

Why I needed this solution ;

In order for the issues below to be minimized

  • The log file of Workflow Mailer Service will grow several gigabytes large and eventually the OS will error due to I/O file handling.
  • Some SMTP servers and IMAP servers used for outbound and inbound processing respectively, have time limits to the sessions connected to them. If the mailer reaches those time limits it will encounter a connection error and will be abnormally brought down. ( Sometime Max Error Count Useless )
  • The size of the INBOX folder in the IMAP server won’t get expunged as long as the mailer does not refreshes the session. For a purged INBOX folder it takes the mailer to restart – and for parameter Expunge Inbox on Close to be set to Yes.
  • This can be done by using the event schedule feature provided in the Workflow Mailer configuration. But This will only restart workflow service not the components.

 

To Restart Only Workflow Service Use Below steps

First, connect to EBS using the responsibility Workflow Administrator Web Applications and go to Workflow Manager

Then edit the Notification Mailer configuration. Continue going through the train stages until you reach the Schedule Events stage:

 

To Restart Workflow Service Components use below scripts automatically via crontab or manually via cli

 

Here is crontab job I use ;

0 */1 * * * sh /home/applprod/scripts/Restart_WF_Comp.sh ################# Restart Oracle Workflow with all Components

 

cat /home/applprod/scripts/Restart_WF_Comp.sh

sqlplus apps/<appspassword> @Stop_WF_Srv_Comp.sql

sleep 90

sqlplus apps/<appspassword> @Start_WF_Srv_Comp.sql

 

This script is modified to run via crontab, What we do here is so easy, we just put our answers into the stop and start sqls.

 

To Deactivate Workflow Components

Comment or delete below 2 lines in Stop_WF_Srv_Comp.sql

— Option value from user

accept l_option default ‘1’ prompt ‘Enter Workflow Service Container Operation[1]: ‘

Replace

l_operation varchar2(30) := &l_option; to l_operation varchar2(30) := 2;

To Activate Workflow Components

Replace Value in Start_WF_Srv_Comp.sql

Replace

l_operation varchar2(30) := &l_option; to l_operation varchar2(30) := 1;

 

To Run Script manually via CLI, Copy and Paste below script

REM Script Name : Restart_WF_Srv_Comp.sql

REM

REM This is an admin script to start and stop the Workflow Service Containers.

REM – WFMLRSVC : Workflow Mailer Service

REM – WFALSNRSVC : Workflow Agent Listener Service

REM – WFWSSVC : Workflow Document Web Services Service

REM

REM Usage sqlplus apps/ @Restart_WF_Srv_Comp.sql

REM

REM

REM Type Number to Perform Operation:

REM

REM 1. Activate

REM 2. Deactivate

REM 3. Abort

REM

REM +=============================================================================+

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

WHENEVER OSERROR EXIT FAILURE;

SET VERIFY OFF

SET SERVEROUTPUT ON SIZE 200000

select to_char(rownum)|| ‘. ‘ || decode(meaning,

‘Activated’, ‘Activate’,

‘Deactivated’, ‘Deactivate’,

‘Terminated’, ‘Abort’

) meaning

from fnd_lookups

where lookup_type=’CP_CONTROL_CODE’ and meaning in (‘Activated’, ‘Terminated’, ‘Deactivated’) order by meaning;

— Option value from user

accept l_option default ‘1’ prompt ‘Enter Workflow Service Container Operation[1]: ‘

Declare

l_qaid number;

l_queue number;

spid number;

errbuf varchar2(300);

l_cqname varchar2(300);

l_operation varchar2(30) := &l_option;

cursor wfsrv_ctl is

select application_id, CONCURRENT_QUEUE_ID, CONCURRENT_QUEUE_NAME

from fnd_concurrent_queues where CONCURRENT_QUEUE_NAME in (‘WFMLRSVC’, ‘WFALSNRSVC’, ‘WFWSSVC’);

Begin

fnd_global.apps_initialize(0,20420,1);

for l_rec in wfsrv_ctl loop

l_queue := l_rec.CONCURRENT_QUEUE_ID;

l_cqname := l_rec.CONCURRENT_QUEUE_NAME;

if l_queue > 0 and l_operation = ‘1’ then

spid:=fnd_request.submit_svc_ctl_request( command => ‘ACTIVATE’,

service => l_cqname,

service_app => ‘FND’);

elsif l_queue > 0 and l_operation = ‘2’ then

spid:=fnd_request.submit_svc_ctl_request( command => ‘DEACTIVATE’,

service => l_cqname,

service_app => ‘FND’);

else

spid:=fnd_request.submit_svc_ctl_request( command => ‘ABORT’,

service => l_cqname,

service_app => ‘FND’);

end if;

if spid = 0 then

errbuf := fnd_message.get; DBMS_OUTPUT.put_line(errbuf);

end if;

end loop;

end;

/

commit;

exit;

Cihan Gedik

I am currently working as Senior Oracle Application Database Administrator. I have experienced many EBS database upgrade, migrations, Fresh EBS installations, Solaris to Linux, Windows to Linux replatform migration projects in medium and large companies and also experienced core database migration projects for one of the biggest bank of Katar.With my colleagues we decided to run this platform to share our knowledge and also create network in all over the world.

2 thoughts on “Oracle Workflow Mailer Service Component Auto Restart – Crontab

  • May 21, 2019 at 8:21 am
    Permalink

    Thanks for the sensible critique. Me and my neighbor were just preparing to do a little research about this. We got a grab a book from our local library but I think I learned more clear from this post. I’m very glad to see such magnificent info being shared freely out there.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *