WF-Mailer Fails to Send Email Notifications after cloning

转自：http://mtalmasri.blogspot.tw/2012/08/mailer-fails-to-send-email.html

After cloning, mails were not floating through workflow mailer although all agent listener and notification mailer was up and running.

Issue:-
After some time we checked latest mailer log FNDCPGSCXXXXX.txt at $APPLCSF/$APPLLOG.
Below error was their in logs:-
'Problem obtaining the HTML content oracle.apps.fnd.wf.common.HTTPClientException: Unable to invoke method HTTPClient.HTTPConnection.Get caused by: java.net.ConnectException: Connection refused'

Investigation:-
what i got to know after bit of investigation is at the time we brought down source instance for cold back up, some of the workflows were in queue and they might be using source instance webserver address. After clone Since the mailer still had some events with the old url in the queue, the mailer fails with connection refused error and goes down after 10 attempts.

Solution:-
Solution is to purge the wf_notification_out outbound message queue and repopulates from the WF_NOTIFICATION table.

PFB complete solution:-

1. Stop the Workflow mailer.

2. Purge the wf_notification_out queue and recreate it out of the wf_notifications table by executing:
cd $FND_TOP/patch/115/sql
sqlplus APPS/ @wfntfqup.sql APPS APPLSYS

3. Start the mailer

4. Retest the issue by submitting the same workflow again.