Oracle EBS Workflow Mailer After an SMTP Migration: Why Mail Still Used the Old Relay

Hostnames, IP addresses, usernames, email addresses, and notification IDs in this post are masked with # characters.

Alhamdulillah, this investigation started with a simple question: after changing the SMTP server in Oracle E-Business Suite, why was mail still going through the old relay?

It turned up three separate findings. The EBS Workflow Mailer configuration pointed to the new SMTP server. OS-level Sendmail still used the old relay. Some failed Workflow notifications were associated with a disabled recipient preference, and the reason for that preference is not yet established.

This post documents the checks, the configuration change, and the evidence collected during validation. It is an interim investigation: the checks still open are listed in section 9.

Environment

• Oracle E-Business Suite R12.2
• Oracle Database 11.2.0.4
• Linux 7 application tier
• Sendmail 8.14.7
• systemd service management

1. The initial problem

The Workflow Mailer’s outbound SMTP server had been changed to the new server (####################). Network connectivity to the new server was successful, but the logs showed messages being relayed through the old relay (####################) before the Sendmail configuration change.

At the same time, two notifications for one user showed MAIL_STATUS = FAILED.

The first step was to verify the EBS configuration, then trace the actual mail path through the application and operating-system logs.

2. Finding 1: EBS configuration and OS-level Sendmail did not match

Verify the Workflow Mailer configuration

SET LINESIZE 200
COL parameter_name   FORMAT A20
COL parameter_value  FORMAT A30
COL last_update_date FORMAT A22
SELECT RPAD(p.parameter_name,20) parameter_name,
       RPAD(NVL(v.parameter_value,'-'),30) parameter_value,
       TO_CHAR(v.last_update_date,'DD-MON-YY HH24:MI:SS')
           last_update_date
FROM   fnd_svc_comp_param_vals v,
       fnd_svc_comp_params_b p,
       fnd_svc_components c
WHERE  v.component_id = c.component_id
AND    v.parameter_id = p.parameter_id
AND    c.component_name = 'Workflow Notification Mailer'
AND    p.parameter_name = 'OUTBOUND_SERVER';

OUTBOUND_SERVER showed the new SMTP server. The Sendmail log showed a different route:

sendmail[####]: ##############:
from=<wfmailer@#######################>,
msgid=<########.####.#############.JavaMail.wfmailer@#######################>,
proto=ESMTP, daemon=MTA,
relay=####################### [###.###.###.###]
sendmail[####]: ##############:
to=<##################>, mailer=relay,
relay=#################### [###.###.###.###],
dsn=2.0.0, stat=Sent

The logs confirmed that messages submitted to local Sendmail were forwarded through the old smart host. The sender and the JavaMail.wfmailer message ID point to messages generated by the Workflow Mailer. Oracle’s Workflow documentation describes the Mailer sending notification messages by SMTP to the SMTP server configured for it (see Outbound Notification Mailer Processing, from an older Workflow release), and notes that a UNIX host running Sendmail can serve as that SMTP server (see the Administrator’s Guide). The Mailer’s previous OUTBOUND_SERVER value was not captured, so the logs do not show why it handed these messages to local Sendmail. Further tracing was needed to establish the route used by the affected Workflow notifications.

Check the Workflow Mailer process start time

SET LINESIZE 200
COL queue_name FORMAT A20
COL status     FORMAT A10
COL os_pid     FORMAT A10
COL started    FORMAT A22
SELECT RPAD(q.concurrent_queue_name,20) queue_name,
       RPAD(NVL(p.process_status_code,'-'),10) status,
       RPAD(NVL(TO_CHAR(p.os_process_id),'-'),10) os_pid,
       TO_CHAR(p.process_start_date,'DD-MON-YY HH24:MI:SS')
           started
FROM   fnd_concurrent_processes p,
       fnd_concurrent_queues q
WHERE  p.concurrent_queue_id = q.concurrent_queue_id
AND    p.queue_application_id = q.application_id
AND    q.concurrent_queue_name = 'WFMLRSVC'
AND    p.process_status_code = 'A';

The process had started 11 days before the parameter change. An OS-level check with ps -o pid,lstart,cmd -p <os_pid> showed the same start date.

That raised the possibility that the Workflow Mailer had not been restarted after the change. The previous parameter value was not captured, though, so the start time alone could not show which SMTP server the running process had been using.

Lesson: After changing Workflow Mailer parameters, check the active service process and follow the real message route in the logs. The database parameter alone doesn’t prove which server handled a message.

Query note: last_update_date on the fnd_svc_components row is not the time a parameter changed. Use last_update_date on fnd_svc_comp_param_vals, as in the query above.

3. Finding 2: Some failed notifications were associated with a disabled recipient preference

Two notifications remained OPEN with MAIL_STATUS = FAILED. The recipient’s notification preference was DISABLED.

SET LINESIZE 220
COL recipient_role FORMAT A20
COL mail_status    FORMAT A12
COL status         FORMAT A10
COL message_name   FORMAT A30
COL email_address  FORMAT A30
COL notif_pref     FORMAT A12
SELECT n.notification_id,
       RPAD(n.recipient_role,20) recipient_role,
       RPAD(NVL(n.mail_status,'-'),12) mail_status,
       RPAD(n.status,10) status,
       RPAD(NVL(n.message_name,'-'),30) message_name,
       RPAD(NVL(r.email_address,'-'),30) email_address,
       RPAD(NVL(r.notification_preference,'-'),12) notif_pref
FROM   wf_notifications n,
       wf_roles r
WHERE  n.notification_id IN (#######,#######)
AND    r.name = n.recipient_role;

Example output:

NOTIFICATION_ID RECIPIENT_ROLE  MAIL_STATUS  STATUS  MESSAGE_NAME            EMAIL_ADDRESS        NOTIF_PREF
--------------- --------------- ------------ ------- ----------------------- -------------------- ----------
        ####### ######          FAILED       OPEN    PO_PO_HAS_BEEN_APPROVE  ##################   DISABLED
        ####### ######          FAILED       OPEN    PO_REQ_APPROVED         ##################   DISABLED

The preference was also found in WF_LOCAL_ROLES and traced to the Workflow MAILTYPE preference in FND_USER_PREFERENCES. Both notifications remained in the user’s Worklist.

A DISABLED preference does not by itself explain a FAILED status. Oracle documents that when every address for a recipient is invalid and delivery fails, the Mailer sets the notification’s mail status to FAILED, sets the recipient’s preference to DISABLED, and notifies SYSADMIN (see the Oracle EBS technology blog). So DISABLED can be the result of an earlier delivery failure, not only a choice the user made.

I did not check when or why this preference was set, or whether the failures began before it was set. I also have not yet confirmed whether these two messages ever reached Sendmail. Both checks are listed in section 9.

4. Finding 3: Historical failures need to be separated from the migration issue

Before attributing every failure to the migration, I collected a baseline of notification statuses.

SET LINESIZE 200
COL mail_status FORMAT A12
COL cnt         FORMAT 99999999
SELECT RPAD(NVL(mail_status,'-'),12) mail_status,
       COUNT(*) cnt
FROM   wf_notifications
WHERE  mail_status IN ('MAIL','FAILED','ERROR')
GROUP BY mail_status;

The dates showed that these records were not all from the migration period. The counts show association, not cause: a disabled preference can be a cause or a consequence of a failure. Historical records, missing roles, disabled preferences and SMTP delivery issues need separate investigation.

5. Changing the Sendmail smart host

The change was limited to the Sendmail configuration. These commands illustrate the procedure used here. Run them only after confirming the approved SMTP target, any maintenance requirements and the rollback plan.

Pre-change checks

date
systemctl status sendmail --no-pager
mailq
grep -ni "SMART_HOST" /etc/mail/sendmail.mc
grep -ni "^DS" /etc/mail/sendmail.cf
nc -vz -w 5 #################### 25

Back up the configuration

cp -p /etc/mail/sendmail.mc \
  /etc/mail/sendmail.mc.bak_$(date +%Y%m%d_%H%M%S)
cp -p /etc/mail/sendmail.cf \
  /etc/mail/sendmail.cf.bak_$(date +%Y%m%d_%H%M%S)

Update the smart host

Edit /etc/mail/sendmail.mc and change only the active SMART_HOST definition:

define(`SMART_HOST', `####################')dnl

Regenerate the configuration and verify:

cd /etc/mail
make
grep -ni "^DS" /etc/mail/sendmail.cf

Expected output:

101:DS####################

Restart Sendmail:

systemctl restart sendmail
systemctl status sendmail --no-pager

The service returned to active (running) and was accepting connections.

The change did not touch /etc/hosts, /etc/mail/access or /etc/hosts.allow. Those files serve other purposes and shouldn’t be edited just to change the smart host, unless a separate requirement justifies it.

Rollback: restore sendmail.mc from its backup, run make, confirm the DS line, and restart Sendmail.

6. Verify delivery through the new SMTP server

A controlled test mail was sent from the application server to an approved test mailbox.

echo "Test email from application server after SMTP migration" \
  | mail -s "SMTP Migration Test" ####################

The relevant log entries:

STARTTLS=client, relay=####################.,
verify=FAIL, cipher=ECDHE-RSA-AES256-GCM-SHA384
to=<####################>, mailer=relay,
relay=####################. [###.###.###.###],
dsn=2.0.0, stat=Sent

ما شاء الله, the test message went through the new relay on the first attempt.

What does stat=Sent mean? The next SMTP server accepted the message for delivery. It does not confirm arrival in the recipient’s inbox. Check the mailbox, and use the SMTP server’s message trace if delivery fails later.

What does verify=FAIL mean? Sendmail did not verify the server certificate under its current settings, even though the SMTP transaction succeeded. Successful delivery is not proof that certificate validation is healthy, so review it separately with the mail or security team.

7. What the investigation proved

Confirmed by configuration and logs:

• The EBS Workflow Mailer was configured with the new SMTP server.
• OS-level Sendmail still had the old smart host before the change, and messages submitted to it were forwarded through that host.
• The Sendmail configuration was updated, regenerated, and restarted successfully.
• A controlled test message was relayed through the new SMTP server and accepted for delivery.
• The two example notifications had MAIL_STATUS = FAILED, remained OPEN, and had a recipient preference of DISABLED.

Not established by these checks:

• Which SMTP server the running Workflow Mailer process was using internally before it was restarted, and why it handed messages to local Sendmail.
• Whether the two failed notifications ever reached Sendmail.
• When and why the recipient’s preference became DISABLED, and whether the failures began before it.
• The cause of every historical FAILED or ERROR notification.
• Whether the original affected notifications were delivered after the Sendmail change.
• Whether the recipient received the test email.
• The cause of the STARTTLS certificate-verification warning.

A successful test email validates one segment of the mail path. The original Workflow notification still needs its own test.

8. Practical lessons

1. Check both EBS and OS-level SMTP settings. The Workflow Mailer parameter and the Sendmail smart host can differ.
2. Compare the parameter change time with the process start time. Restart the Workflow Mailer component when the change procedure requires it, and confirm the new start time.
3. Trace a real message end to end. Use the notification record, the Mailer logs, the Sendmail logs and recipient confirmation.
4. Don’t assume a failed notification is either an SMTP problem or a preference problem. A disabled preference can be a cause or a consequence of a delivery failure, so check when it was set, along with the recipient role, email address and notification status.
5. Capture a baseline before changing anything. Old FAILED and ERROR records can have unrelated causes.
6. Verify the handoff and the final delivery separately. stat=Sent is evidence of SMTP acceptance, not of inbox delivery.
7. Review certificate verification on its own. Successful delivery doesn’t remove the need to investigate TLS warnings.

9. Status and remaining checks

The Sendmail change is complete and the test message was accepted by the new relay. These checks are still open:

1. Failed notifications: search the Sendmail log for the affected recipient to establish whether the two failed messages ever reached Sendmail.
2. Preference history: establish when and why the recipient’s MAILTYPE preference became DISABLED (who last updated it, and whether the first FAILED notification came before or after), and whether SYSADMIN received an invalid-address notice.
3. Workflow Mailer process: verify the active WFMLRSVC process against the parameter change time, and restart the component under the approved change procedure if needed.
4. End-to-end Workflow test: submit a test notification, then check the Mailer log and the Sendmail log to confirm it was relayed through the new server.
5. Certificate verification: investigate verify=FAIL separately with the mail or security team.

Until those checks are done, this post is an interim investigation. It does not claim that the original Workflow notifications have been fixed.

Conclusion

Changing the SMTP server in Oracle EBS may not be the whole job. OS-level Sendmail has its own smart-host setting, and it needs checking when the mail logs show messages leaving through an unexpected relay.

The Sendmail test confirmed that the new relay accepts mail from the application server. The checks in section 9 will show whether the Workflow Mailer path itself works end to end, and why the affected notifications failed.

Disclaimer: This article is based on a troubleshooting scenario. All hostnames, IP addresses, usernames, email addresses and notification IDs are masked. Validate all commands and configuration changes against your own environment and change-management procedures before using them in production.