Re: EXPIRED messages in the WF_NOTIFICATION_OUT queue <--------------- THE SOLUTION
- From: "Jurijs Velikanovs" <j.velikanovs@xxxxxxxxx>
- To: ora-apps-dba@xxxxxxxxxxxxx
- Date: Fri, 23 Jun 2006 18:16:43 +0100
Hello People,
Today I dug deep enough to solve the problem I have described few weeks ago.
Definition:
=============================================================
There are EXPIRED messages in the WF_NOTIFICATION_OUT queue.
=============================================================Can be
checked by the following SQL:
APPS:LIVE> SELECT msg_state, count(*)
APPS:LIVE> FROM applsys.aq$wf_notification_out
APPS:LIVE> group by msg_state;
MSG_STATE COUNT(*)
------------- ----------
EXPIRED 795
PROCESSED 633
.
End users haven't got notification on different events in the system.
Why the messages have been expired? Who to deal with them?
Investigation results:
=============================================================
I. Why the messages have been expired?
REF: Note:233102.1 and Application Developer's Guide - Advanced Queuing
According to the theory messages in AQ queue can expire in the following cases:
----------------
a. The message is not being dequeued within the specified EXPIRATION
interval.
An EXPIRATION interval is something that is set during enqueuing step.
In WF_NOTIFICATION_OUT oracle doesn't use this functionality.
Can be checked using the following SQL:
select EXPIRATION from wf_notification_out;
----------------
b. If the message has been dequeued but rolled back more than the
number of times specified by the max_retries limit.
Oracle uses 5 as max_retries value for notifications (default retry
limit for AQ).
select a.name, a.queue_table, a.MAX_RETRIES
from dba_queues a where a.queue_table='WF_NOTIFICATION_OUT';
----------------
It looks like my case of NOTIFICATIONS only B is possible. I think
that a WF Mailer for some reason tried by 5 times rollbacked some
messages (in my case 795 messages). I recall that during last few
weeks we had got problems with SMTP server. Some times we restarted WF
Mailer multiple times. Possible it was the reason for EXPIRED messages
in the WF_NOTIFICATION_OUT queue.
I am still not sure but described above can be working theory this time.
The problem with WF Mailer & SMTP connectivity we have sorted out. New
EXPIRED messages don't appear any more.
It time to deal with the existing EXPIRED messages.
=============================================================
Who to deal with expired messages?
=============================================================
On the Metalink I have found some examples of how to dequeue EXPIRED
messages and who to reenqueue notifications which lost queuing
messages in underlining WF_NOTIFICATION_OUT tables.
I have slightly modified the code for the particular context.
The code below is that I have used. Please treat it as example for
educational purposes and don't use it in your production environments
without Oracle Support instructions :)))))))))))))))
Enjoy,
Yury
-------------Check how many EXPIRED messages we have got
SELECT msg_state
,COUNT (*)
FROM applsys.aq$wf_notification_out
GROUP BY msg_state;
-------------Dequeue ALL EXPIRED messages
-- Ref Note:233103.1 Dequeuing Messages from an Exception Queue
DECLARE
dequeue_options DBMS_AQ.dequeue_options_t;
message_properties DBMS_AQ.message_properties_t;
dq_msgid RAW (16);
MESSAGE SYS.aq$_jms_text_message;
r_expmsg applsys.aq$wf_notification_out%ROWTYPE;
BEGIN
dequeue_options.WAIT := DBMS_AQ.no_wait;
dequeue_options.navigation := DBMS_AQ.first_message;
FOR r IN (SELECT msg_id
FROM applsys.aq$wf_notification_out
WHERE msg_state = 'EXPIRED')
LOOP
dequeue_options.msgid := r.msg_id;
DBMS_AQ.dequeue (queue_name =>
'APPLSYS.AQ$_WF_NOTIFICATION_OUT_E'
,dequeue_options => dequeue_options
,message_properties => message_properties
,payload => MESSAGE
,msgid => dq_msgid
);
END LOOP;
END;
/
-------------Check how many EXPIRED messages we have got after dequeuing
SELECT msg_state
,COUNT (*)
FROM applsys.aq$wf_notification_out
GROUP BY msg_state;
-------------Check how many NOTIFICATIONS are with MAIL status but
have no corresponding reqcord in WF_NOTIFICATION_OUT queue
SELECT status, mail_status, notification_id
FROM wf_notifications n
WHERE 1 = 1
AND n.mail_status = 'MAIL' and status in ('OPEN', 'CANCELED')
and not exists (select * from applsys.aq$wf_notification_out o
where n.notification_id = TO_NUMBER ((SELECT str_value
FROM TABLE (o.user_data.header.properties) WHERE NAME =
'NOTIFICATION_ID'))
);
-------------- Renequeue all notifications messages in the
WF_NOTIFICATION_OUT queue
--- which have no corresponding message the queue and status in
('OPEN', 'CANCELED') and mail_status = 'MAIL'
-- Ref $FND_TOP/patch/115/sql/wfntfsnd.sql - WorkFlow NoTiFication SeND
declare
procedure re_enqueue_notification(n_id in number) as
l_paramlist wf_parameter_list_t := wf_parameter_list_t();
cursor c_ntfs is
SELECT notification_id, group_id, recipient_role, message_type
FROM wf_notifications
WHERE 1=1
AND notification_id = n_id;
l_display_name varchar2(360);
l_email_address varchar2(320);
l_notification_pref varchar2(8);
l_language varchar2(30);
l_territory varchar2(30);
l_orig_system varchar2(30);
l_orig_system_id number;
l_installed varchar2(1);
begin
for l_ntf_rec in c_ntfs loop
-- Get recipient information using Dir Service API. Select from WF_ROLES
-- may not give the right information
Wf_Directory.GetRoleInfoMail(l_ntf_rec.recipient_role,
l_display_name, l_email_address,
l_notification_pref, l_language, l_territory,
l_orig_system, l_orig_system_id, l_installed);
-- Not checking the email address, since the Role may contain
members. WF_XML.Generate takes
-- care of this condition.
Wf_Event.AddParameterToList('NOTIFICATION_ID',
l_ntf_rec.notification_id, l_paramlist);
Wf_Event.AddParameterToList('ROLE', l_ntf_rec.recipient_role,
l_paramlist);
Wf_Event.AddParameterToList('GROUP_ID', l_ntf_rec.group_id,
l_paramlist);
Wf_Event.AddParameterToList('Q_CORRELATION_ID',
l_ntf_rec.message_type, l_paramlist);
Wf_Event.Raise(p_event_name => 'oracle.apps.wf.notification.send',
p_event_key => to_char(l_ntf_rec.notification_id),
p_parameters => l_paramlist);
end loop;
end re_enqueue_notification;
begin
for v_rec in (
SELECT status
,mail_status
,notification_id
FROM wf_notifications n
WHERE 1 = 1
AND n.mail_status = 'MAIL'
AND status IN ('OPEN', 'CANCELED')
AND NOT EXISTS (
SELECT *
FROM applsys.aq$wf_notification_out o
WHERE n.notification_id =
TO_NUMBER ((SELECT str_value
FROM TABLE (o.user_data.header.properties)
WHERE NAME = 'NOTIFICATION_ID')))
) loop
re_enqueue_notification(v_rec.notification_id);
end loop;
end;
/
-------------Check how many NOTIFICATIONS are with MAIL status but
have no corresponding reqcord in WF_NOTIFICATION_OUT queue after
enquing
SELECT status, mail_status, notification_id
FROM wf_notifications n
WHERE 1 = 1
AND n.mail_status = 'MAIL' and status in ('OPEN', 'CANCELED')
and not exists (select * from applsys.aq$wf_notification_out o
where n.notification_id = TO_NUMBER ((SELECT str_value
FROM TABLE (o.user_data.header.properties) WHERE NAME =
'NOTIFICATION_ID'))
);
On 6/16/06, Jurijs Velikanovs <j.velikanovs@xxxxxxxxx> wrote:
Hello team,
Please take a look on the following output.
There are 795 messages in the WF_NOTIFICATION_OUT queue in our LIVE system.
Some of them have been put in the queue just today.
I wonder how messages in that queue become EXPIRED?
Is anybody experienced the same behavior?
Those messages are not dequeued and are not sent to end users.
What would be the right method to troubleshoot those type of messages?
Thank you in advance,
Yury
APPS:LIVE> alter session set nls_date_format='YYYY.MM.DD HH24:MI:SS';
Session altered.
APPS:LIVE>
APPS:LIVE> SELECT msg_state, count(*)
APPS:LIVE> FROM applsys.aq$wf_notification_out
APPS:LIVE> group by msg_state;
MSG_STATE COUNT(*)
------------- ----------
EXPIRED 795
PROCESSED 633
APPS:LIVE>
APPS:LIVE> SELECT o.enq_time
APPS:LIVE> FROM applsys.aq$wf_notification_out o
APPS:LIVE> where o.msg_state = 'EXPIRED'
APPS:LIVE> and trunc(o.enq_time) = trunc(sysdate);
ENQ_TIME
-------------------
2006.06.16 00:46:15
2006.06.16 00:46:15
2006.06.16 00:46:15
APPS:LIVE>
--
Yury
+44 7738 013090 (GMT)
============================================
http://otn.oracle.com/ocm/jvelikanovs.html
--
Yury
+44 7738 013090 (GMT)
============================================
http://otn.oracle.com/ocm/jvelikanovs.html
Other related posts:
- » Re: EXPIRED messages in the WF_NOTIFICATION_OUT queue <--------------- THE SOLUTION
