Oracle EBS Workflow Mailer SMTP Recovery Runbook

1. How Workflow Mailer Works

Oracle Workflow Mailer (also called the Notification Mailer) is a Java-based service within the Concurrent Manager infrastructure. It polls the WF_NOTIFICATIONS table for notifications with status OPEN and sends them via SMTP. It also processes inbound email responses (for email-based approvals) via IMAP/POP3.

The Mailer runs as a Concurrent Manager service: Workflow Mailer (internal name: FNDWFMLRSVC). It connects to:

• The Oracle APPS schema (for notification data)
• An SMTP server (outbound notifications)
• An IMAP or POP3 server (inbound response handling, if configured)

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 = 'FNDWFMLRSVC';

-- Check service component status
SELECT
  component_name,
  component_status,
          startup_mode,
  inbound_agent,
  outbound_agent
FROM apps.fnd_svc_components
WHERE component_type = 'WF_MAILER';

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'
  AND notification_preference NOT IN ('DISABLED', 'SUMMARY')
GROUP BY mail_status
ORDER BY cnt DESC;

-- Check WF_DEFERRED queue depth
SELECT COUNT(*) AS deferred_count
FROM apps.wf_deferred
WHERE state = 0;  -- 0 = ready, 1 = expired, 2 = undeliverable

3c. Read the Mailer log

-- Find the latest Workflow Mailer log file
-- As applmgr OS user:
-- ls -lt $APPLLOG/FNDWFMLRSVC*.log | head -5
-- tail -300 $APPLLOG/FNDWFMLRSVC_PID.log

-- 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. Via SQL

-- Deactivate Workflow Mailer
UPDATE apps.fnd_concurrent_queues
SET max_processes = 0
WHERE concurrent_queue_name = 'FNDWFMLRSVC';
COMMIT;

-- Wait 30 seconds, then reactivate
UPDATE apps.fnd_concurrent_queues
SET max_processes = 1
WHERE concurrent_queue_name = 'FNDWFMLRSVC';
COMMIT;

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 = 'FNDWFMLRSVC';

-- 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