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;

Do you want to learn Oracle E-Business Suite ( EBS ) for Junior Apps DBA, then read the following articles.

https://ittutorial.org/oracle-e-business-suite-ebs-tutorials-for-junior-oracle-apps-dba/