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: ‘
l_operation varchar2(30) := &l_option; to l_operation varchar2(30) := 2;
To Activate Workflow Components
Replace Value in Start_WF_Srv_Comp.sql
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: ' 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;