Re: workflow mailer notifications out queue stuck

Posted by arieiska3087 on Nov 17, 2017; 10:43am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/workflow-mailer-notifications-out-queue-stuck-tp4505p4513.html

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