Work-Flow Mailer stopped with Error

One day I found WFM Notification is down in one of my server. When i try to start that component again it failed with the same issue. Below are the sequence of steps I followed to rectify that issue.

Navigate to Site Map > Administration > Generic Services and press View All button

Select Generic Service Component Container and start the Workflow Mailer Service.

Once it is started successfully, try to start the WF Notification Mailer service component. Most of the time, it will be started without any issue.

If still facing the same issue, check the appropriate log file for the issue and take necessary steps to solve the issue. Here I worked on my issue. If you face the same exact issue, then follow the remaining steps.

Checked the recent Workflow Mailer and Agent Listener services log file and found the below issue.

You can find the log file using the command 'ls -lrt $APPLCSF/APPLLOG/FNDCPGSC*.txt'

[May 14, 2019 11:41:03 AM IST]:1557814263809:-1:-1:r12smokeapp.sify.net:172.31.17.89:-1:-1:1:20420:SYSADMIN(0):-1:Thread[BES Dispatch Thread,5,main]:172.31.17.89:25923:1557814253708:26:ERROR:[SVC-GSM-WFMLRSVC-35154 : oracle.apps.fnd.cp.gsc.SvcComponentContainer.onBusinessEvent(BusinessEvent)]:Error in handling component event, oracle.apps.fnd.cp.gsc.SvcComponent.start, for component 10006
[May 14, 2019 11:41:03 AM IST]:1557814263810:-1:-1:r12smokeapp.sify.net:172.31.17.89:-1:-1:1:20420:SYSADMIN(0):-1:Thread[BES Dispatch Thread,5,main]:172.31.17.89:25923:1557814253708:26:ERROR:[SVC-GSM-WFMLRSVC-35154 : oracle.apps.fnd.cp.gsc.SvcComponentContainer.onBusinessEvent(BusinessEvent)]:java.lang.RuntimeException: Connection refused
at oracle.apps.fnd.wf.mailer.MailerUtils.isSocketConnectableNew(MailerUtils.java:300)
at oracle.apps.fnd.wf.mailer.SMTPUtils.isValidOutbound(SMTPUtils.java:521)
at oracle.apps.fnd.wf.mailer.Mailer.validateParameterValues(Mailer.java:1299)
at oracle.apps.fnd.cp.gsc.SvcComponent.performValidateParameterValues(SvcComponent.java:233)
at oracle.apps.fnd.cp.gsc.SvcComponent.start(SvcComponent.java:314)
at oracle.apps.fnd.cp.gsc.SvcComponentContainer.handleComponentEvent(SvcComponentContainer.java:2223)
at oracle.apps.fnd.cp.gsc.SvcComponentContainer.onBusinessEvent(SvcComponentContainer.java:301)
at oracle.apps.fnd.wf.bes.DispatchThread.run(Unknown Source)

[May 14, 2019 11:41:03 AM IST]:1557814263810:-1:-1:r12smokeapp.sify.net:172.31.17.89:-1:-1:1:20420:SYSADMIN(0):-1:Thread[BES Dispatch Thread,5,main]:172.31.17.89:25923:1557814253708:26:UNEXPECTED:[SVC-GSM-WFMLRSVC-35154 : oracle.apps.fnd.cp.gsc.SvcComponentContainer.onBusinessEvent(BusinessEvent)]:An unexpected RuntimeException or other Throwable occurred -> java.lang.RuntimeException: Connection refused
[May 14, 2019 11:41:03 AM IST]:1557814263810:-1:-1:r12smokeapp.sify.net:172.31.17.89:-1:-1:1:20420:SYSADMIN(0):-1:Thread[BES Dispatch Thread,5,main]:172.31.17.89:25923:1557814253708:26:EXCEPTION:[SVC-GSM-WFMLRSVC-35154 : oracle.apps.fnd.cp.gsc.SvcComponentStateMachine.updateComponentStatus(Logger, Connection, int, String, String)]:BEGIN (Logger{mLog=[$Header: AppsLog.java 120.5.12020000.3 2014/05/14 19:17:27 rsantis ship $ @926776138 {oracle.apps.fnd.profiles.ExtendedProfileStore@2569539b}],mUniqueId=SVC-GSM-WFMLRSVC-35154,mLevel=4}, oracle.jdbc.driver.T4CConnection@30d091c0, 10006, DEACTIVATED_SYSTEM, This automatic Service Component has been restarted the maximum of 10 times after stopping with error. Thus, it has been system deactivated -> java.lang.RuntimeException: Connection refused) 

Based on the above highlighted issue, I tried the below solution and it worked!.

Here I've compared my TEST and PROD environments and fixes this issue based on the error message captured in the log file.

From TEST Environment:

From PROD Environment:

Cause of the Issue:

When there is a maintenance activity like autoconfig,patching,cloning,upgrade etc which run's the autoconfig, workflow parameter SMTP (Outbound Server) is overwritten by context file parameter s_smtphost, s_smtpdomain.  If the context file parameter s_smtphost,s_smtpdomain is not pointing to correct SMTP server name(by default s_smtphost contains the application node name as value),it will be overwrite with node name which is not the correct SMTP server name.

From the above comparison, found a small difference between between the two environments. Difference is the Outbound Server Name. After changing the TEST environment's Outbound Server Name as same as PROD, it started working fine.

Ref : E-Business Suite Workflow Agent Listeners Fail To Start With Error 'Service Component Container is not Running' For Mailer Workflow Components (Doc ID 733335.1)

        

        Workflow Notification Mailer Not Starting After Running AutoConfig With Errors: Unable to make a network connection OR java.lang.RuntimeException: Connection refused (Doc ID 1565672.1)

****************************நன்றி****************************

ERROR: txkWfClone.sh exited with status 1

I faced this error while cloning R12.2.5 in my environment. Below are the step by step process, I followed to overcome this issue.

The above issue might occur if still there is some reference to the source system, or the cloning script is unable to change it to appropriate environment during the cloning process for some reasons. This is one such case, where this issue might occur. There are many other reasons which will throw the above same issue but the reason might be different. Need to drill down on the log files for exact cause for the issue.

1. Take backup of AD_APPL_TOPS table
2. Remove the entries for prod reference
3. On db tier connect with apps
4. Run "exec fnd_conc_clone.setup_clean;"
5. Run autoconfig on database tier
6. On application tier rerun "perl adcfgclone.pl appsTier"
7. Verify Clone complete successfully or not.

****************************நன்றி****************************

Workflow Mailer log location

Below query can be used to find the workflow mailer log location and the file name.

SELECT fcp.logfile_name FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup WHERE concurrent_queue_name in ('WFMLRSVC') AND fcq.concurrent_queue_id = fcp.concurrent_queue_id AND fcq.application_id = fcp.queue_application_id AND flkup.lookup_code=fcp.process_status_code AND lookup_type ='CP_PROCESS_STATUS_CODE' AND meaning='Active';

****************************நன்றி****************************

Exlpain plan for SQL Query and SQL ID in Oracle Database

Let us see how to generate explain plan for a SQL Query and SQL ID.

Pre-Req:

Create the plan table if not created already

@$ORACLE_HOME/rdbms/admin/utlxplan.sql
CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
GRANT ALL ON sys.plan_table TO public;

Explain Plan for SQL Query:

1. First identify the query for which you want to generate the plan.
2. Create explain plan for the desired query.
3. Display the explain plan generated for your selected query.

1. I have chosen to generate explain plan for the query 'SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process FROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b, v$process c,v$session d WHERE a.controlling_manager = b.concurrent_process_id AND c.pid = b.oracle_process_id AND b.session_id=d.audsid AND a.request_id = &Request_ID AND a.phase_code = 'R';'

2. Create explain plan for the above query

3. Display the above generated explain plan.

Explain Plan for SQL ID:

1. Get the SQL ID of a SQL Query you want to generate explain plan.

2. Check the sql query and the run-time memory utilized by the query so far. This is not a mandatory step, but it will give you more info to understand about the query's execution.

3. Display the explain plan which is stored already in the table DBMS_XPLAN.DISPLAY.

1. I have chosen to Get the SQL ID of a SQL Query being ran by a concurrent request

2. Get the text and run time memory of the sql_id.

3. Display the explain plan

Ref : https://oracle-base.com/articles/9i/dbms_xplan

         https://dbaclass.com/article/how-to-generate-explain-plan-in-oracle/

****************************நன்றி****************************

Workflow Mailer Over-riding

Pre-Reqs:
Check the below parameters having proper values according to your environment before over-riding, otherwise issue will occur.

• Masking Email Address
• uname -n
• hostname
• grep -ie s_webhost $CONTEXT_FILE
• grep -ie s_domainname $CONTEXT_FILE
• grep -ie s_webport $CONTEXT_FILE

For e-mail address masking Click Here

Use the below query to check the details of Framework and Web agents.

select PROFILE_OPTION_NAME, PROFILE_OPTION_VALUE from fnd_profile_options a , fnd_profile_option_values b where a.APPLICATION_ID = b.APPLICATION_ID and a.PROFILE_OPTION_ID = b.PROFILE_OPTION_ID and a.PROFILE_OPTION_NAME in ( 'APPS_FRAMEWORK_AGENT', 'WF_MAIL_WEB_AGENT') and b.level_value = 0;

Values of both the agents should be same, otherwise you will receive notification for only one test notification(PL/SQL) out of two.

Suppose if 'WEB_AGENT' is having a different value means, follow either  Doc ID 1943176.1 or Doc ID 736898.1 to fix that(For step by step process Click Here ) and continue with the below given steps

Below are the step-by -step process for workflow mailer over-riding.

Start all the service components before setting over-ride mail id, to make sure all the components are running fine without any issues.

Once all the components are UP and Running, bring down all the services down before setting the over-ride mail id.

Once the components are down, select the radio button for the component 'Workflow Notification Mailer' and select View Details > Edit

Here the following things should be taken care.

1. Outbound EMail Account(SMTP) server name should be a valid one.
2. Username should be empty.
3. Inbound EMail Account(IMAP) Reply-To Address should be a valid one.
4. You can set the Reply-To Address to the OS user of your Application server along with fully qualified host name.

         For eg : If your OS username is applmgr and the hostname is test.apple.net, then the Reply-To Address should be 'applmgr@test.apple.net'

  Now go to the Advanced.

Now move to step 3 and remove username and save. Here the Outbound Server Name should be a valid(which can process and send mails) SMTP server name. Mostly in Production environments a valid SMTP mailer name will be configured. For testing purpose this mailer name can be used on a test instance to carry out mailer over-riding process(Of-course on approval)!!!

Once this settings are completed, initiate a test mail to check the WFM is working properly. Here the mail_status is sent, which means WFM is working as expected.

Now you can set the override address from both Front-End and Back-End.

From Front-End

Now come back to the details page and click on tab 'Set Override Address'

Now give the override mail id and submit.

Check with the user who owns the override mail id and get the verification code sent via mail, enter the verification code and Apply.

Once the above steps are performed successfully without any issues, start all the workflow components.

Now initiate a test mail as below

By default override mail address will be there. Choose a proper recipient role and send the test mail. If you don't know the appropriate recipient role, check with the user who owns the overriding mail id.

Once the test mail is initiated, check whether the user received the test mails. Normally two test mails will be sent to the over-riding mail id. One framework and one PLSQL message.

You can use the below queries to check the status of your notification mail status.

select * from wf_notifications order by begin_date desc;

select begin_date,mail_status,status,message_name from wf_notifications where notification_id in ('10026201','10026200'); -- Here replace your notification id's.

From Back-End

1. Run the script $FND_TOP/sql/afsvcpup.sql as APPS user
2. Enter the comp id for Workflow Mailer. Default value is 10006
3. Enter the comp param id for override address. It'll be shown as Test Address. Default value is 10093
4. When asked enter the mail id you want to set as override address.
5. Bounce the WFM and confirm the results.

Ref: How to set Workflow Mailer Override Address from Backend ? (Doc ID 1533596.1)

****************************நன்றி****************************