EBS Workflow Configuration & SSL Setup

SQL> select target_node from fnd_concurrent_queues where concurrent_queue_name like 'WFMLRSVC%';

SQL> SELECT b.component_name, 
       c.parameter_name, 
       a.parameter_value
FROM fnd_svc_comp_param_vals a, fnd_svc_components b, fnd_svc_comp_params_b c
WHERE b.component_id = a.component_id 
     AND b.component_type = c.component_type 
     AND c.parameter_id = a.parameter_id
     AND c.encrypted_flag = 'N'
     AND b.component_name like '%Mailer%'
     AND c.parameter_name in ('OUTBOUND_SERVER', 'REPLYTO')
ORDER BY c.parameter_name;

SMTP Telnet Test:
telnet [outbound server] 25
EHLO [mailer node]
MAIL FROM: [reply_to address]
RCPT TO: [my_test_email_address]
DATA
Subject: Test message

Test message body
.
quit

Verify SMTP server:
$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Dserver=test.domain.com -Dport=25 -Daccount=<user_account> -Dconnect_timeout=120 oracle.apps.fnd.wf.mailer.Mailer

Verify IMAP server:
$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Dserver=test.domain.com -Dport=143 -Daccount=<user_account> -Dpassword=<password> -Dconnect_timeout=120 -Dfolder=INBOX oracle.apps.fnd.wf.mailer.Mailer

 

Workflow SSL Configuration in R12.2:

Issue Description:

We enabled TLS in Oracle EBS R12.2 and configured the Workflow Mailer (SMTP), the services started but the email was not sending. Workflow Mailer log file recorded SSL certificate not valid error.

Solution:

Step 1: Take a backup of cacerts file
$ cd $OA_JRE_TOP/lib/security/
$ cp cacerts cacerts_bkp


Step 2: Import the ca.crt
$ keytool -import -alias email_cert -file ca.crt -trustcacerts -v -
keystore cacerts  

Enter keystore password: [default password changeit]

Trust this certificate? [no]:  Yes
Certificate was added to keystore
[Storing cacerts]


Step 3: Verify the certificate listed in cacerts (OPTIONAL STEP)
keytool -list -v -keystore $OA_JRE_TOP/lib/security/cacerts -storepass changeit > cacertslist01.txt 


Step 4: Update MAILER_SSL_TRUSTSTORE using the below script
sqlplus apps @$FND_TOP/sql/afsvcpup.sql
>> 10010
>> 10223
>> $OA_JRE_TOP/lib/security/cacerts