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/