Oracle Apps DBA: Workflow Notification Mailer - Mails Sitting in Inbox

This is the other situation that i talked about in my earlier post with Notification Mailer.

Situation:

Mails are sitting in the inbox and Production Workflow Notification Mailer is not processing them.

Symptoms:

1. Most of the mails are getting processed without any issues.
2. Some of the mails are not getting processed. They are not moved to Discard folder either.
3. Out going emails are working fine.


Troubleshooting:

The starting point in this case too, was to pick up a NID from the one of the mails sitting in the inbox. This was specially puzzling because there were no errors logged in the workflow notification mailer logs. The log level was set to error. There were no obvious discrepancies in the wf_notification or wf_notification_out or wf_notification_in queues.

After some desperate troubleshooting techniques like bouncing the mailer failed, I changed the log level to 'statement'. This is when the first clue emerged. I saw some entries in the mailer log something like 'mailer will not process these NIDs'. I cant recollect the exact error message, but it sounded something like that. Then i decided to take a closer look as to why the mailer refused to process those notification.

The answer lied in the previous week end patching that was performed in production environment. I realized that autoconfig was run as part of patching and this was the key to the issue. In our environment, we have modified the node names to match wf. The default value that oracle brings in WFMAIL. We have two production instances and hence we have renamed this to read WFOIAP and WFOGAP. This was reset to WFMAIL after autoconfig was run.

The notification ids contain the following section at the bottom of the notification:

NID[5369273/218241628447419580901891042449889261398@WFOIAP]

In the above example, WFOIAP is the mailer that would process this notification. Since the mailer name got changed to WFMAIL in production, it would not process.

Hence, to sort out the issue, we changed the node name back to WFOIAP and the mails were cleared in no time.

But this also led to another issue - the mails that were generated with node name as WFMAIL in the problem window started piling up in inbox as WFOIAP would not process them.

This situation was handled manually to get past the issue.

- Aravind Kamath