EBS 12.2 -- Notification Mailer Outbound/Inbound & SSL problem diagnostics + a real life example

Notification mailer is a key workflow component for both sending emails and processing emails sitting on the  INBOX of the associated Imap account..
Notification mailer is a java program running on the concurrent tier.. Note that : Notification Mailer in the old 11i relaeses was based on C.. In 12.2, we are continuing to use java..
It is running from a a bundled jdk32 as a Service component in Oracle Applications GSM type.
You can see its running from the operating system command line;

[applmgr@ermanprod config]$  ps -ef |grep -i fndcp 
->applmgr   8094  7944  0 Aug23 ?        00:03:14 /apps/fs2/EBSapps/comn/util/jdk32/bin/java -Dxdo.xliff.source=EBS -DCLIENT_PROCESSID=8094 -Dhttp.proxyHost= -Dhttp.proxyPort= -Dhttp.nonProxyHosts= -Djava.awt.headless=true -server -Xmx384m -Doracle.apps.fnd.common.Pool.leak.mode=stderr:off -verbose:gc -Ddbcfile=/apps/fs2/inst/apps/dberp_erpprod/appl/fnd/12.0.0/secure/dberp.dbc -Dcpid=8845 -Dconc_queue_id=1139 -Dqueue_appl_id=0 -Dlogfile=/apps/fs_ne/inst/dberp_erpprod/logs/appl/conc/log/FNDCPGSC8845.txt -DLONG_RUNNING_JVM=true -DOVERRIDE_DBC=true -DFND_JDBC_BUFFER_MIN=1 -DFND_JDBC_BUFFER_MAX=2 oracle.apps.fnd.cp.gsm.GSMSvcComponentContainer  --> "This is the Workflow Agent Listener Service"
->applmgr   8109  7944  0 Aug23 ?        00:03:05 /apps/fs2/EBSapps/comn/util/jdk32/bin/java -Dxdo.xliff.source=EBS -DCLIENT_PROCESSID=8109 -Dhttp.proxyHost= -Dhttp.proxyPort= -Dhttp.nonProxyHosts= -Djava.awt.headless=true -server -Xmx384m -Doracle.apps.fnd.common.Pool.leak.mode=stderr:off -verbose:gc -Ddbcfile=/apps/fs2/inst/apps/dberp_erpprod/appl/fnd/12.0.0/secure/dberp.dbc -Dcpid=8846 -Dconc_queue_id=1259 -Dqueue_appl_id=0 -Dlogfile=/apps/fs_ne/inst/dberp_erpprod/logs/appl/conc/log/FNDCPGSC8846.txt -DLONG_RUNNING_JVM=true -DOVERRIDE_DBC=true -DFND_JDBC_BUFFER_MIN=1 -DFND_JDBC_BUFFER_MAX=2 oracle.apps.fnd.cp.gsm.GSMSvcComponentContainer  --> "This is the Workflow Document Web Services Service"
->applmgr   8128  7944  0 Aug23 ?        00:08:12 /apps/fs2/EBSapps/comn/util/jdk32/bin/java -Dxdo.xliff.source=EBS -DCLIENT_PROCESSID=8128 -Dhttp.proxyHost= -Dhttp.proxyPort= -Dhttp.nonProxyHosts= -Djava.awt.headless=true -server -Xmx384m -Doracle.apps.fnd.common.Pool.leak.mode=stderr:off -verbose:gc -Ddbcfile=/apps/fs2/inst/apps/dberp_erpprod/appl/fnd/12.0.0/secure/dberp.dbc -Dcpid=8847 -Dconc_queue_id=1140 -Dqueue_appl_id=0 -Dlogfile=/apps/fs_ne/inst/dberp_erpprod/logs/appl/conc/log/FNDCPGSC8847.txt -DLONG_RUNNING_JVM=true -DOVERRIDE_DBC=true -DFND_JDBC_BUFFER_MIN=1 -DFND_JDBC_BUFFER_MAX=2 oracle.apps.fnd.cp.gsm.GSMSvcComponentContainer  -> "This it the Notification Mailer"

As you see the command line are almost the same for all the 3 processes. So the difference in their behavior must be based on their control queues..

Anyways;

Notification mailer services are running as concurrent managers at the same time.
Oracle Workflow provides three predefined containers in which you can create components, the Workflow Mailer Service, the Workflow Agent Listener Service, and the Workflow Document Web Services Service.
                           

Okay lets analyze the diagram above;

So;

We use OAM to control Notification mailer.
OAM uses control queues to communicate with GSM container.
Notification Mailer(a GSM process) is controlled by the GSM container using the messages that it degueues from the Control Queue..
GSM Process monitor delivers the control events to OAM using OAM..So, It is working in the opposite direction..
Notification Mailer is managed by the concurrent tier.
Concurrent Manager spawns a GSM container and GSM container invokes Notification Mailer Service, which is referred as a GSM process -- an instance of the GSM..

You can see the concurrent manager information using the query below ;

SELECT concurrent_queue_name manager, fcp.last_update_date, fcp.concurrent_process_id pid, meaning, fcp.logfile_name  
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup  
WHERE concurrent_queue_name in ('WFMLRSVC', 'WFALSNRSVC')  
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'

Also you can check Administer Concurrent Manager form to check the situation of the concurrent managers related to Notification mailer.

Okay, after the introduction. Lets talk about the duties of Notification mailers briefly.
As you know, Mailer has Outbound and Inbound capabilities. Outbound is the side for the smtp operations and the Inbound has the responsibility of the Imap operations..
In the Smtp side, we can configure our mailer to use an account based authentication to login to our SMTP server.. Also, we can use a relay based authentication , which will not require mailer to pass user/pass information..
On the other hand, we must have a dedicated account in the IMAP side.. Mailer must connect to the imap server with its account and check the INBOX for the incoming messages that may require an action to be taken..

So how does it work?
Lets explore the smtp and imap processing of the smtp server a little more;

The flow diagram of smtp activities related with the Notification Mailer is as follows;

• Workflow process initiates a request for a notification (an insert into WF_NOTIFICATIONS)
• Request for a notification is enqueued onto the WF_DEFERRED queue
• Notification Mailer Agent Listener picks up the message off the WF_DEFERRED queue for processing. (processed messages will appear on WF_NOTIFICATION_OUT or WF_ERROR)
• The notification request is processed and the notification XML has been generated and enqueued to the WF_NOTIFICATION_OUT queue.
• The notification mailer dequeues the XML notification for dispatch.
• The notification mailer transforms the XML into a MIME message and dispatches the message through the SMTP server..
• "Content that contains Oracle Applications Framework regions is obtained from the Application Server" --> An error  encountered in this stage, is the main reason makes me writing this post bytheway :)
• The message is dispatched to the SMTP server for delivery to the recipient(s)
• The SMTP Server forwards the message on to the wider network (LAN, WAN or Internet)
• The message is delivered to the users email server
• The user receives the email.

The flow diagram of imap activities related with the Notification Mailer is as follows;

• User response to a notification through email
• Email Server forwards the message to the LAN/WAN
• The message is received by the IMAP server
• Notification mailer retrieves the message from the mail store
• The mailer will check the status of a notification to determine if it needs to actually process the response any further
• The response is parsed and transformed into an XML document
• The response, now encoded in XML is enqueued to the WF_NOTIFICATION_IN queue
• The Workflow Inbound Notification Agent Listener picks up the reponse for processing.
• The response attributes are applied to the notification activity and the response is processed by the Workflow Engine

As you see above, there is a complex process there.. Notification mailer, agent listeners and workflow engines play roles on these activities. Note that : Both outbound and inbound can work with ssl.

Notification mailer is cabaple of speaking both ssl-enabled smtp and ssl-enabled imap..
Mailer interacts with the Mail Server using the standard smtp and imap commands in the backend.
Mailer makes some http/https calls during its process cycle.. (Look above .. SMTP flow ->  Content that contains Oracle Applications Framework regions is obtained from the Application Server)
So in other words; when the outbound needs to process OAF region, it obtains the content at a URL...
As I mention above, the flow is a complex one, and problems may be encountered in any step.. Obtaining the content using htttp/https is one these steps, which makes me writing this post actually. This post would be only about a certificate error encountered during the https call of the notification mailer, but I just couldnt stop myself from giving a detailed introduction about the underlying technology .. :)

Okay, lets take a closer look to the http/https activities of the Notification Mailer..
When there is a need to embed Oracle Applications Framework reqions in to the notification emails, Notification mailer's outbound connects to the Web Server of EBS from its web url ( hostname:port) and generates the content..
The host and port of the Webserver is derived from the profile option named : Workflow Mailer Framework Web Agent
The profile option may be set in the form of http://erman.domain.com:8000 or if EBS is SSL enabled then - > https://erman.domain.com:8000
Note that we can still set an http url even if our EBS is SSL enabled.. We can set an http url as long as our web server's http port is open..
So, notification mailer reads the value of this profile option and requests the contents using a url like this : https://erman.domain.com:8000/OA_HTML/OA.jsp?page=/oracle/apps/fnd/wf/worklist/webui/NotifMailerPG&WFRegion=NtfDetail&NtfId=4428&dbc=dberp&OARF=email&language_code=TR&oas=nl-v1pXd61XlMlUZJkG3gw..

and sometimes it may encounter errors in this step!!
When notification mailer encounters an error in this step, the details will be written to the logfiles.. (Hint . use statement level logging for diagnosing notification mailer problems)
The error can be seen in this step is as follows;

Problem getting the HTML content -> oracle.apps.fnd.wf.mailer.NotificationFormatter$FormatterSAXException: Problem obtaining the HTML content -> oracle.apps.fnd.wf.common.HTTPClientException: Unable to invoke method HTTPClient.HTTPConnection.Get caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

This error may be seen in  a non-ssl SMTP configured Workflow Mailer, because it is actually encountered when notification mailer reaches the WEB Server.. 
On the other hand, we can directly say that  the associated Web Server is SSL-enabled...

--Note that : I find the diagnostic process we did for fixing this error very informative , and that 's why choosed to focus on this error in this post..



So, by looking the error for the first time , we can guess that there is ssl certificate problem in our system.. It may be in the Web Tier, or it may be related with the Notification mailer itself..
In this case, as we have EBS up&running and as we have no problems at all ( no browser side certificate errors or warnings) , we can narrow down the problem in Notification mailer's code or its configuration..

In this manner, we must follow a set of diagnostic actions..

The error that we must concentrate on is the following;

oracle.apps.fnd.wf.mailer.NotificationFormatter$FormatterSAXException: Problem obtaining the HTML content -> oracle.apps.fnd.wf.common.HTTPClientException: Unable to invoke method HTTPClient.HTTPConnection.Get caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target)

Especially - > unable to find valid certification path to requested target

The question came to my mind was, Is notification mailer in EBS 12.2 cabaple of raching web agent from ssl port? But, now I can say that it is quite cabaple..

Note that:

SSL in EBS was implemented using : Enabling SSL in Oracle E-Business Suite Release 12.2 ( Doc ID 1367293.1 )

So we suppose everyting in that document was performed properly..

Diagnostic :

1)   

Find JDK TOPs that EBS components are using.

grep -i jdktop $CONTEXT_FILE

Use keytool to analyze the cacerts files located in JDK_TOP

For Ex: 

keytool -list -v -keystore /apps/fs2/EBSapps/comn/util/jdk32/jre/lib/security/cacerts -storepass changeit > cacertslist32.txt
keytool -list -v -keystore /apps/fs2/EBSapps/comn/util/jdk64/jre/lib/security/cacerts -storepass changeit > cacertslist64.txt

If you are missing any certificates in one of the cacerts files, import it and retest the issue.

2)
Find Notification mailer programs.. See that they are running.
ps -ef |grep -i fndcp

If notificaiton mailer is not running, start it.

3) 

Check connection and show the certificates used for connecting to the webserver with open_ssl..

openssl s_client -connect ermanhost.domain.com:6530 -showcerts

4)

Analyze the notification mailer's log file, and find a nid that is failing..

Then run wfmlrdbg script for that notification id.

How to Run Script wfmlrdbg.sql and Collect its Output ? ( Doc ID 1364300.1 )

Check the errors in the output (if there are any).

If there is no error in the wfmlrdbg's output then, analyze the configured values for notification mailer.. Especially, the title "GSC WF_MAILER Component Parameters.."

Look for the value of MAILER_SSL_TRUSTSTORE parameter.. 

Is it set to the cacerts files that stores you web servers certificates ? 

If it is pointing to a different keystore , then  change that parameter and make it pointing to one of your cacerts files, or import your missing certificates in to the keystore that your notification mailer is configured to use.. 

(use $FND_TOP/sql/afsvcpup.sql if you want to change the keystore)

(use keytool if you want to import the missing certificates to the configured keystore . 

For Ex: keytool -import -alias Root -keystore yournewkeystore.jks -trustcacerts -file root.cer)

Once you are done, Restart application services (especially concurrent manager) and restart.

5)

Access your main web page https://<host.domain:port>.
Double-click on the padlock at the bottom of the page to view the Certificates.
If there is no padlock, then on the top toolbar:
select File->Properties->Certificates
Select the Certification Path tab and:
click on the first line and then View Certificate. - This will be the certificate for the root Certifying Authority (CA).
On Details tab click Copy to File, this will start the export wizard.
Click Next to continue.
Select Base-64 encoded X.509 (.CER) and click next.
Enter ca1 as the name and click ok to export the certificate.
epeat steps a thru e for each line on the Certification Path tab incrementing the file name each time by 1, i.e. ca2, ca3..

Analyze the certificates that exported above.. Are there missing certificates?

We should not expect any missing certificates, as  we implement SSL based on the Oracle Support document, but we check anyways.. just in case..

6)

Check the file : <10.1.2 ORACLE_HOME>/sysman/config/b64InternetCertificate.txt

It is a file stores certificates in Base64 format.

Your root and intermediate certificate must be imported to that txt file..

We should not expect any missing certificates in this file too, as  we implement SSL based on the Oracle Support document.. Anyways, we check b64InternetCertificate.txt just to be sure..

If some certificates are missing , import them.. 

For ex:

cat ca.crt >> <10.1.2 ORACLE_HOME>/sysman/config/b64InternetCertificate.txt

cat intca.crt >> <10.1.2 ORACLE_HOME>/sysman/config/b64InternetCertificate.txt

7)
Check the agent profiles and check the node names..

select p.profile_option_name,v.profile_option_value,
decode(v.level_id,
10001,'SITE',
10002, (select 'App:'||a.application_short_name from fnd_application a
where a.application_id = v.level_value),
10003, (select 'Resp:'||f.RESPONSIBILITY_KEY from fnd_responsibility f
where f.responsibility_id = v.level_value),
10004, (select 'User:'||u.user_name from fnd_user u
where u.user_id = v.level_value),
10005, (select 'Server:'||n.node_name from fnd_nodes n
where n.node_id = v.level_value),
10006, (select 'Org:'||org.name from hr_operating_units org
where org.name = v.level_value),
'NOT SET') PROF_LEVEL
from
fnd_profile_options p,
fnd_profile_option_values v
where
p.profile_option_id = v.profile_option_id (+)
and p.application_id = v.application_id (+)
and p.profile_option_name in ('WF_MAIL_WEB_AGENT','APPS_FRAMEWORK_AGENT','ICX_FORMS_LAUNCHER');

select substr(node_name, 1, 20) node_name, server_address, substr(host, 1, 15) host,
substr(domain, 1, 20) domain, substr(support_cp, 1, 3) cp, substr(support_web, 1, 3) web,
substr(SUPPORT_DB, 1, 3) db, substr(VIRTUAL_IP, 1, 30) virtual_ip from fnd_nodes; 

****

Okay we have seen almost all the diagnostics related with this error.

So these diagnostics should will take us to the solution ...

Lastly, I will mention a real life example, that I have faced recently ..

Environment:

It was a 12.2 EBS, running on Redhat Linux 6..

EBS was working with SSL. (https)

Notification mailer's outbound was working without SSL. (SMTP server was non-ssl)

Notification mailer's inbound was working with SSL.  (IMAP server was ssl enabled)

Problem:

There were errors in the notification mailers log file:

oracle.apps.fnd.wf.common.HTTPClientException: Unable to invoke method HTTPClient.HTTPConnection.Get caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

First impression:

Thought that the IMAP was the problem.. Because we had a ssl enabled IMAP..
Then, saw that the problem lines were created by the outbound and also realized that this EBS was SSL enabled. So there should be some errors encountered during the way while Notification mailer's outbound process was reaching the web agent using https.

Workaround:

I set WF_MAIL_WEB_AGENT profile to "http://ermanhost.domain.com.tr:8000" 

(originally it was set to "https://ermanhost.domain.com.tr:6530)

This action made the NTF mailer to reach the web agent through http, and the problem dissapeared.

But this was a workaround not the fix.. (what will happen when the customer decide to close http port on the EBS server?)

Diagnostics:

Checked cacerts files

Checked mailers log file

Checked b64InternetCertificate.txt

Checked SSL actions which were taken while implementing SSL with EBS 12.2.

Checked SSL actions which were taken while implementing SSL on IMAP in Notification Mailer -- EBS 12.2.

Checked wfmlrdbg.sql output ->> found the problem in here.. The mailer was using /home/applmgr/.keystore file as the keystore.. (actually it should use the cacerts file located in the jdk top)

Cause:

SSL implementation was done firstly. Immediately afterwards, IMAP-SSL was enabled.

SSL implementation document states using cacerts file, but while implemeting IMAP-SSL a .keystore file was created and workflow notification mailer' keystore was set to .keystore file..

Because .keystore file does not include the server certificates, notification mailer can not reach the ssl enabled EBS web agent. On the other hand, as .keystore file includes IMAP server's ssl certificate, Notification mailer inbound could run without any problems..

Fix:

Alternative 1)

Import the missing server,ca and intermediate certificates in to .keystore file using keytool -import

Alternative 2)

Change workflow mailer's keystore parameter to " the cacerts file located in the jdk top"

But then, Inbound will not able to work properly, because this time cacerts file does not include IMAP server's ssl certificate .. That's why import IMAP server's ssl certificate in to the cacerts file , as well.

That's all about the notification mailer. I think , I will not write anything about Notification Mailer for a while , as I feel myself oversaturated with it.. :)
I hope you find this document useful.