Oracle EBS Workflow Mailer SMTP Recovery Runbook

1. How Workflow Mailer Works

The Workflow Notification Mailer is a Java service component that runs inside a Generic Service Management (GSM) container on the concurrent-processing node. It does not poll WF_NOTIFICATIONS. An email travels a pipeline:

1. The workflow engine raises oracle.apps.wf.notification.send, which is enqueued on the WF_DEFERRED queue.
2. The Workflow Deferred Notification Agent Listener (container WFALSNRSVC, “Workflow Agent Listener Service”) resolves the recipient’s email and preference, renders the message, and enqueues it on WF_NOTIFICATION_OUT.
3. The Workflow Notification Mailer (container WFMLRSVC, “Workflow Mailer Service”) dequeues it and sends it over SMTP.
4. Email responses are read from an IMAP inbox, enqueued on WF_NOTIFICATION_IN, and processed by the Workflow Inbound Notifications Agent Listener.

So the first question is always where the message is waiting: ready on WF_DEFERRED points at the deferred listener; ready on WF_NOTIFICATION_OUT points at the mailer or the SMTP server.

2. Symptoms of Mailer Failure

• EBS users not receiving workflow notifications by email
• Notifications accumulating in the “Worklist” but never delivered
• Workflow Mailer shows as “Inactive” in System Administrator → Concurrent → Manager → Administer
• Alert log or Mailer log shows SMTP connection errors or authentication failures
• TuneVault EBS health check: Workflow Mailer service down or error rate elevated
• Deferred queue depth growing in WF_DEFERRED

3. Diagnose: Check Mailer Status and Queue

3a. Check Workflow Mailer service status

-- Check if Workflow Mailer is active
SELECT
  q.concurrent_queue_name,
  q.running_processes,
  q.max_processes,
  q.description
FROM apps.fnd_concurrent_queues q
WHERE q.concurrent_queue_name IN ('WFMLRSVC', 'WFALSNRSVC');  -- the two containers: mailer, agent listeners

-- Check service component status
SELECT
  component_name,
  component_type,
  component_status,
  component_status_info,  -- the error text when Stopped with Error / System Deactivated
  startup_mode
FROM apps.fnd_svc_components
WHERE component_type LIKE 'WF%';  -- mailer AND its agent listeners

3b. Check notification queue depth

-- Count unsent notifications
SELECT
  mail_status,
  COUNT(*) AS cnt,
  MIN(begin_date) AS oldest
FROM apps.wf_notifications
WHERE status = 'OPEN'
GROUP BY mail_status
ORDER BY cnt DESC;

-- Where are the messages waiting? (counts by state; READY = not yet consumed)
SELECT 'WF_DEFERRED' q, msg_state, COUNT(*) FROM applsys.aq$wf_deferred GROUP BY msg_state
UNION ALL
SELECT 'WF_NOTIFICATION_OUT', msg_state, COUNT(*) FROM applsys.aq$wf_notification_out GROUP BY msg_state;

3c. Read the Mailer log

-- Find the latest Workflow Mailer log file
-- As applmgr OS user:
-- The container log is $APPLCSF/$APPLLOG/FNDCPGSC<pid>.txt; FND records the active one:
SELECT p.logfile_name FROM apps.fnd_concurrent_processes p JOIN apps.fnd_concurrent_queues q
  ON q.concurrent_queue_id = p.concurrent_queue_id AND q.application_id = p.queue_application_id
WHERE q.concurrent_queue_name = 'WFMLRSVC' AND p.process_status_code = 'A';
-- then, as applmgr:  grep -E "ERROR:|EXCEPTION:|UNEXPECTED:" <that file>

-- Key error patterns to look for:
-- "Authentication failed" or "535 Authentication credentials invalid"
-- "Connection refused" or "Connection timed out" on SMTP port
-- "SSL/TLS required" - SMTP server requires TLS, Mailer not configured for it
-- "ORA-01017" - APPS password mismatch
-- "Mailer went to ERROR state"

4. Fix SMTP Authentication and Connectivity

4a. Test SMTP connectivity from the apps tier

-- As applmgr OS user, test SMTP connectivity:
-- telnet your-smtp-server.com 25
-- (or port 587 for STARTTLS, port 465 for SSL)

-- Expected response if SMTP is reachable:
-- 220 your-smtp-server.com ESMTP ready

-- If connection times out: firewall rule needed from apps tier to SMTP server
-- If connection refused: SMTP server is down or wrong port

4b. Update SMTP configuration in EBS

Navigate to: System Administrator → Oracle Applications Manager → Workflow → Notification Mailer

Key fields to verify:

• Outbound Server Name: SMTP server hostname or IP
• Outbound Server Port: 25 (plain), 587 (STARTTLS), or 465 (SSL)
• Use SSL: Enable if SMTP server requires TLS/SSL
• From Address: Must be a valid sending address accepted by the SMTP relay
• Username / Password: If SMTP AUTH is required (most modern email servers require this)

4c. Update SMTP settings via profile option

-- Check current Workflow Mailer SMTP settings
SELECT
  c.component_name,
  p.parameter_name,
  p.parameter_value
FROM apps.fnd_svc_comp_params_b p
JOIN apps.fnd_svc_components c ON c.component_id = p.component_id
WHERE c.component_type = 'WF_MAILER'
  AND p.parameter_name IN (
    'SMTP_HOST', 'SMTP_PORT', 'SMTP_USERNAME',
    'FROM', 'REPLY_TO', 'USE_SSL'
  )
ORDER BY p.parameter_name;

-- Update SMTP host (example)
UPDATE apps.fnd_svc_comp_params_b
SET parameter_value = 'smtp.yourdomain.com'
WHERE parameter_name = 'SMTP_HOST'
  AND component_id = (
    SELECT component_id FROM apps.fnd_svc_components
    WHERE component_type = 'WF_MAILER'
  );
COMMIT;

5. Clear Stuck Deferred Notifications

-- Check notifications stuck in ERROR or DEFERRED state
SELECT
  n.notification_id,
  n.message_type,
  n.message_name,
  n.recipient_role,
  n.mail_status,
  n.begin_date
FROM apps.wf_notifications n
WHERE n.mail_status = 'MAIL'
  AND n.status = 'OPEN'
  AND n.begin_date < SYSDATE - 1  -- stuck more than 1 day
ORDER BY n.begin_date
FETCH FIRST 20 ROWS ONLY;

-- Reset stuck notifications so Mailer retries them
UPDATE apps.wf_notifications
SET mail_status = 'MAIL'
WHERE status = 'OPEN'
  AND mail_status = 'SENT'   -- marked sent but not actually delivered
  AND begin_date < SYSDATE - 7;
COMMIT;

-- For very old notifications: mark as sent to stop retry loops
-- (prevents sending stale notifications to users after fixing Mailer)
UPDATE apps.wf_notifications
SET mail_status = 'SENT'
WHERE status = 'OPEN'
  AND mail_status = 'MAIL'
  AND begin_date < SYSDATE - 30;  -- older than 30 days
COMMIT;

6. Restart Workflow Mailer

6a. Via EBS System Administrator UI (preferred)

1. Navigate to: System Administrator → Concurrent → Manager → Administer
2. Find Workflow Mailer in the list
3. Click Deactivate and wait for the process to stop (running_processes = 0)
4. Click Activate
5. Monitor the log file for successful SMTP connection messages

6b. Via Oracle Applications Manager (OAM)

1. Navigate to: Oracle Applications Manager → Workflow → Notification Mailer
2. Click Stop
3. After status changes to Stopped, click Start

6c. From the database (the supported API)

-- Stop / start the mailer COMPONENT. Do not UPDATE FND_CONCURRENT_QUEUES or
-- FND_SVC_COMPONENTS directly — that bypasses GSM and is not a supported restart.
DECLARE
  l_id NUMBER; l_retcode VARCHAR2(10); l_errbuf VARCHAR2(4000);
BEGIN
  SELECT component_id INTO l_id FROM apps.fnd_svc_components
  WHERE component_type = 'WF_MAILER' AND ROWNUM = 1;
  fnd_svc_component.stop_component(l_id, l_retcode, l_errbuf);
  fnd_svc_component.start_component(l_id, l_retcode, l_errbuf);
  COMMIT;
END;
/
-- A System Deactivated mailer is only restarted with its CONTAINER (OAM > Generic
-- Services > Workflow Mailer Service) — after fixing the cause the log names.

7. Verify and Test

-- Confirm Mailer is running
SELECT concurrent_queue_name, running_processes, max_processes
FROM apps.fnd_concurrent_queues
WHERE concurrent_queue_name IN ('WFMLRSVC', 'WFALSNRSVC');

-- Monitor notification queue being processed
SELECT mail_status, COUNT(*) AS cnt
FROM apps.wf_notifications
WHERE status = 'OPEN'
GROUP BY mail_status;

-- Test: send a test notification from EBS
-- System Admin → Workflow → Notification Mailer → Test
-- Or submit the "Workflow Background Process" concurrent request

-- Check Mailer log for "Message sent successfully" entries

8. Prevention