workflow mailer notifications out queue stuck

[arieiska3087]

Hi Erman

i see your thread post http://ermanarslan.blogspot.co.id/2014/08/ebs-workflow-notification-mailer.html

i have a similiar issue.. i have already follow your step 1 by 1 but the queue still stuck not processing any
notifications in WF_notification_out

i dont know what data is corrupt or invalid

i have a test instance with the same configuration outbound and outbound like instance production..

and the test works perfectly fine, but in the production stuck

its been 5 days

Thx
Arie

[ErmanArslansOracleBlog]

Step 1?
Those are not steps, those are just listings.
There are several things needed to be checked as documented in that blog post.
Read the whole blog post and take actions accordingly.

If you think your out queue is corrupt, follow -> "2)If you have problems sending mails (outbound), then your out queue may be logically corrupt or mailer may be stuck because of an invalid email address.. " section of te blog post.

[arieiska3087]

Dear Erman

thx for the reply

no.. i mean i follow the instruction from the beginning to  end..


Stop the notification mailer
Back up the creation scripts of wf_notification_out table and queue.
create table APPLSYS.AQ$WF_NOTIFICATION_OUT_ERM as select * from APPLSYS.AQ$WF_NOTIFICATION_OUT
Dropp the WF_NOTIFICATION_OUT queue with the API; sqlplus apps/apps
exec dbms_aqadm.stop_queue( queue_name => 'APPLSYS.WF_NOTIFICATION_OUT', wait => FALSE);
exec dbms_aqadm.drop_queue_table( queue_table => 'APPLSYS.WF_NOTIFICATION_OUT', force =>TRUE);

Recreate the wf_notification_out table & queue manually, with sqlplus using the scripts , backed up in the first place.;

BEGIN
SYS.DBMS_AQADM.CREATE_QUEUE_TABLE
(
QUEUE_TABLE => 'APPLSYS.WF_NOTIFICATION_OUT'
,QUEUE_PAYLOAD_TYPE => 'SYS.AQ$_JMS_TEXT_MESSAGE'
,COMPATIBLE => '8.1'
,STORAGE_CLAUSE => 'NOCOMPRESS
TABLESPACE APPS_TS_QUEUES
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)'
,SORT_LIST => 'PRIORITY,ENQ_TIME'
,MULTIPLE_CONSUMERS => TRUE
,MESSAGE_GROUPING => 0
,COMMENT => 'Workflow JMS Topic'
,SECURE => FALSE
);
End;


CREATE OR REPLACE SYNONYM APPS.WF_NOTIFICATION_OUT FOR APPLSYS.WF_NOTIFICATION_OUT;
GRANT SELECT ON APPLSYS.WF_NOTIFICATION_OUT TO APPS WITH GRANT OPTION;
GRANT SELECT ON APPLSYS.WF_NOTIFICATION_OUT TO EM_OAM_MONITOR_ROLE;


BEGIN
SYS.DBMS_AQADM.CREATE_QUEUE
(
QUEUE_NAME => 'APPLSYS.WF_NOTIFICATION_OUT'
,QUEUE_TABLE => 'APPLSYS.WF_NOTIFICATION_OUT'
,QUEUE_TYPE => SYS.DBMS_AQADM.NORMAL_QUEUE
,MAX_RETRIES => 5
,RETRY_DELAY => 3600
,RETENTION_TIME => 86400
,COMMENT => 'Workflow JMS Topics'
);
END;


DECLARE
aSubscriber sys.aq$_agent;
BEGIN
aSubscriber := sys.aq$_agent('WF_NOTIFICATION_OUT',
'',
0);
dbms_aqadm.add_subscriber
( queue_name => 'APPLSYS.WF_NOTIFICATION_OUT'
,subscriber => aSubscriber
,rule => '1=1');
END;
/

BEGIN
SYS.DBMS_AQADM.START_QUEUE
(
QUEUE_NAME => 'APPLSYS.WF_NOTIFICATION_OUT'
,ENQUEUE => TRUE
,DEQUEUE => TRUE
);
END;
/


BEGIN
sys.dbms_aqadm.grant_queue_privilege
('ENQUEUE', 'APPLSYS.WF_NOTIFICATION_OUT', 'APPS', TRUE);
sys.dbms_aqadm.grant_queue_privilege
('DEQUEUE', 'APPLSYS.WF_NOTIFICATION_OUT', 'APPS', TRUE);
END;
/


Execute following from the application node..sqlplus apps @$FND_TOP/patch/115/sql/wfntfqup.sql APPS apps APPLSYS
sqlplus apps/apps @$FND_TOP/patch/115/sql/wfjmsqc2.sql applsys apps
sqlplus apps/apps @$FND_TOP/patch/115/sql/wfmqsubc2.sql APPLSYS APPS
sqlplus apps/apps @$FND_TOP/patch/115/sql/wfntfqup APPS apps APPLSYS --> again


but the queue still stuck... you said it may be stuck because an invalid email address.. how do i search this email address? (in which table)

Thx
Arie

[ErmanArslansOracleBlog]

You should see the problematic mail address in WF mailer's log.
Check the EBS user's email definitions using EBS standard forms..

Send me the WF mailer logs for further diag.

[arieiska3087]

here's the log from yesterday

FNDCPGSC5979206.txt

FYI

this problem occured when the mail server in the office down...

when i check the wf mailer service is system deactivate.. so when the mail server running well again, i start the mail service.. but it wont up and running, it says connection refuse when i check the configure of smtp change suddenly (i sure i never change it) it seems oracle workflow have autoconfig, so i set the config again to the real one 10.100.21.4(my smtp host).. and the mailer is running.. but not processing any email until now.. already like 10 times rebuild the wf queue