Workflow Notification Mailer not sending mails.

Issue:- Users reported not getting mail from oracle. OAM was showing notification mailer as up and running.


Error:- Useful error in FNDCPG* log was ' 421 4.3.0 collect: Cannot write ./dfp2ACSkLM020213 (bfcommit, uid=0, gid=25): No such file or directory^M'.

Troubleshooting:- Mailer was up at application level but user were not getting mails. We tested sending test mails from application but it never went through, on checking wf_notification table for mail with open/mail status.
Use below query to confirm it:-

select notification_id, status, mail_status, begin_date,
TRUNC(((sysdate-begin_date)/(1/24))*60) exec_time
from WF_NOTIFICATIONS
where status = 'OPEN' and mail_status = 'MAIL';

if outcome of the below query continue to give output for sometime then it means your notification mailer is facing some issue
We found out chunk of mails waiting their. I thought to check SMTP mailer on the box.Mail sent directly through server were reaching user mailbox but not through E-BIZ, bit strange. On further analysis, reason can be when you start mailer, it start from count 1, i.e. it will try to send 1st mail and then goes on. Mailer try to send mails for count 1-5(or some other figure) before going down. /var/spool/mqueue is the directory where sendmail(unix utility used by SMTP) stores the deffered messages. I am not sure but something was stopping sendmail to check /var/spool/mqueue directory and hence mailer was not able to deliver messages that were their. Timestamp of directory should also help you to know about it. Actually it should be updated quite frequently(depending on mailer usage).

Resolution:- Their might be some other resolution also but for non-live box easiest was to bounce the OS hosting database. And moreover it falls in unix admin patio so I didn’t thought much.