Wednesday, March 19, 2014

EBS R12 -- Notification Mailer -- Unable to relay, Stuck, Recreation Out queue

Yesterday, an issue regarding to the alert notifications was escalated to me.
The problematic alert was an periodic alert, which was designed to send emails to the relevant recipients. The alert was working properly, producing data and giving the order for an email to be sent.. The problem was as it is said.. The email was never delivered.

I analyzed the situation by querying alr and wf tables in the first place, because these alert things are a little nasty in EBS environments :).. On the other hand,  I didnt see any abnormality there..
So, I decided to take a look to the notification mailer logs.. To able to see the whole picture there, I increased the log level of the notification mailer to "statement".

While analyzing the notification mailer's log file, I saw the following ;

474:34:ERROR:[SVC-GSM-WFMLRSVC-185112-10006 : oracle.apps.fnd.wf.mailer.SMTPOutboundProcessor.send(Message)]:Problem encountered when sending to {[[eretets@blabla.com.tr], [etgtrd@blabla.com.tr]]} -> javax.mail.SendFailedException: Invalid Addresses;
  nested exception is:
        class javax.mail.SendFailedException: 550 5.7.1 Unable to relay;

The issue was clear, an email was trying to be sent but, the SMTP server didnt have the necessary relay for the domains..(blabla.com.tr)
Then I decided to find where those emails were triggered.. So I checked the custom alert definitions from the Alert Manager, and found that they were triggered from a Custom periodic alert.. Normally, I disabled this Alert, and restarted workflow notification mailer to have a clean start. Unfortuneatly, the error messages continued... It was clear that there was a stuck situation,  like a paper jam :)
That is  the notification mailer was still trying to send those emails, and it could not proceed with the other emails. In other words, Ntf mailer couldnt send any emails because of those stuck ALR notifications..

After examining carefully, I saw that those alert notifications are stuck in wf_notification_out queue. Notification mailer was trying to proceed with the email orders in wf_notification_out queue, and got stuck there.. In my opinion, there should be a bug record for this..
Anyways, when I checked the out queue, I saw that there was not any important notification orders.. That's why ; I decided to recreate the queue.. The steps I followed are as follows;

I backed up the the table , just in case;
  • Stopped the notification mailer
  • Backed up  the creation scripts of wf_notification_out table and queue.
  • created table APPLSYS.AQ$WF_NOTIFICATION_OUT_ERM as select * from APPLSYS.AQ$WF_NOTIFICATION_OUT
  • Dropped 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);
  • Recreated the wf_notification_out table & queue manually, with sqlplus using the scripts I 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;
/

  • Executed  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
  • Started up the notification mailer and the issue dissapeared as expected . 

No comments :

Post a Comment