📧 Workflow

# Oracle EBS Workflow Mailer SMTP Recovery Runbook

⏱ 20–60 min EBS 12.1 / 12.2 Updated 2026-05-15

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

⚠️ Modern SMTP relay requirements
Google Workspace and Office 365 now require OAuth2 or app-specific passwords for SMTP AUTH. If you're relaying through either, configure an app password or use a dedicated SMTP relay service (SendGrid, AWS SES, Postfix relay) to avoid OAuth complexity.

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

✅ Workflow Mailer health checklist

- Monitor Workflow Mailer status in TuneVault health checks
- Alert when Workflow Mailer shows as Inactive or Error
- Use a dedicated SMTP relay instead of direct MX delivery
- Rotate SMTP credentials with calendar reminders (before they expire)
- Run “Workflow Background Process” daily to catch stuck items
- Purge old notifications: run “Purge Obsolete Workflow Runtime Data” weekly
- Monitor `WF_DEFERRED` queue depth weekly

### Stop finding out from users that Workflow Mailer is down

TuneVault monitors Workflow Mailer status and notification queue depth on every health check run, with alerts before it becomes a user-facing problem.

Run a Free Health Check →