Notification Mailer troubleshooting (Part 2)
In Part 1 we described the main steps involved in the Notification/Email sending process in an EBS environment. Today I will give you some practical hints on how to troubleshoot the notification sending process.
Let’s say some of your EBS users report that they do not receive the emails they are supposed to receive from the system. Typically a functional maintenance team claims that the problem has nothing to do with an application/functional area and most probably it is related to the technical stack and Apps DBA should be called. In many cases at the end of the day it appears that either a workflow definition has been changed during the last delivery, or a wrong recipient for the vacation rule was specified, or this is some special case in a business process everybody forgot about etc. In any case everybody applies to an Apps DBA and would like him to fix the problem immediately. You should have a reliable way to show quickly to everybody around (including yourself) what directions the troubleshooting process has to be focused on.
The first thing I try to do being an Apps DBA is to reproduce the reported problem. At the same time in order to avoid moving to the wrong direction it would be good to simplify the test case as much as possible. In the notification sending process troubleshooting case the first thing to do is to send a test notification and check if this notification is received.
Sending an email using PL/SQL API
Fortunately there is a test mail functionality available in the both 11i and R12 EBS versions. You just open an Oracle Application Manager => Workflow Notification Mailer page, provide an EBS user name (with defined email address) and check the mailbox for the incoming email from EBS.
Test Mail 11i
Test Mail R12
There are a few disadvantages using this method:
- You have to connect to EBS and to navigate through Self-Service pages to get to the Test page. This is a manual process and it takes time. It may be annoying to go through it each time you are asked to troubleshoot the mailer process. Especially at the moments when business asking you to sort out the problem ASAP.
- Would it be good to let your monitoring system check notification mailer instead of you? The monitoring system would notify you proactively if there is a problem with sending notifications from your EBS system. However it is difficult to make a monitoring check using this test page functionality.
It appears that as for many modules in an EBS there is a PL/SQL API available to send notifications. The following simple PL/SQL block will make the trick for you:
set serverout on
DECLARE
v_res NUMBER;
BEGIN
v_res :=
wf_notification.send (ROLE => 'EBS_USER_FOR_EMAIL_MON',
msg_type => 'CS_MSGS',
msg_name => 'FYI_MESSAGE',
priority => 1
);
wf_notification.setattrtext (v_res, 'OBJECT_TYPE', 'THIS IS A SUBJECT MSG'
);
wf_notification.setattrtext (v_res,
'MESSAGE_TEXT',
'This is a message body'
|| CHR (10)
|| 'Line 2'
|| CHR (10)
|| 'Line 3'
);
COMMIT;
DBMS_OUTPUT.put_line (v_res);
END;
/
Note: Please run a proper testing in your environment before using this code.
This code sends a test email to the EBS user EBS_USER_FOR_EMAIL_MON (change the name to the user which exists in your environment). Unfortunately you can’t specify an email address directly in the code (as in any workflow related notification). You must define user with the email address before using the code.
I keep this code in my scripts library. If somebody complains about not receiving emails from EBS the very first thing I normally do is to run this little script to check if the emails coming through. The script returns a notification identification you can use in case the email isn’t going through (see the next section).
Depending on an organization monitoring routines you may consider to make this check a part of monitoring system. You may change lines 20/22 to include some monitoring system specific identification (or use existing one) in each individual test message sent and check the mailbox for this message based on this unique ID 5-10 minutes later. If no message arrives the monitoring system should notify your maintenance team about this problem.
If the test message is not coming through, it is time to dive deeper into the notification process to see where the message is and why it isn’t sent. The next section explains how to query underlying data structures to accomplish this task.
Joining WF_NOTIFICATIONS, WF_DEFERRED & WF_NOTIFICATION_OUT
As discussed in the first article there are three persistent data storage structures (tables) related to the notification sending activities (see the figure bellow).
Mailer Flow
During a notification processing each of these three structures is updated in particular order. We know the order now. The only thing we are missing is the way to retrieve information related to particular message. Each message/notification in EBS is uniquely identified by NOTIFICATION_ID. Obviously it is the ID to use for joining three tables. The problem is that both WF_DEFERRED and WF_NOTIFICATION_OUT are advanced queue tables. Notifications related data is hidden under one field - USER_DATA. All other fields in these tables are AQ functionality related. It is a bit tricky to extract NOTIFICATION_ID. The following code does it for you.
SELECT d.enq_time,
d.deq_time,
TO_NUMBER((SELECT VALUE
FROM TABLE(d.user_data.parameter_list)
WHERE NAME = 'NOTIFICATION_ID')) d_notification_id,
msg_state
FROM applsys.aq$wf_deferred d
WHERE d.corr_id = 'APPS:oracle.apps.wf.notification.send';
NOTE: I would like to pass special thanks to my friend Maris Elsins (excellent Apps DBA with developer background) who helped me finding how to join two things together.
This way we do have a message notification ID in our hands to use it for further joining activities. You could notice that the SQL is selecting from AQ$… view rather than from the queue table directly. Oracle creates some additional objects around a queue object to support AQ functionality. The AQ$… view is created for your convenience on top of a queue table. I will touch upon the AQ topic in more details in the further posts. In this article I describe the relative bits only
Let’s take a look on the other important fields returned by the query. ENQ_TIME indicates when the message has been enqueued to the queue. DEQ_TIME field is updated when the message is dequeued. Values of these two fields give us important information indicating when the message arrived to the queue and when it was processed by the next process in the notification sending flow. The following are the most common values MSG_STATE field has:
- READY – a message ready to be processed in the next dequeue call. If there are many messages in a queue it indicates that there are some problems with consumer process.
- PROCESSED –a message processed by consumer process. The record stays in a queue for keeping activity history only. Messages kept in a queue if the queue has RETENTION parameter set (see DBA_QUEUES view and DBMS_ASADM.ALTER_QUEUE procedure to change it). Be careful with setting this parameter too high as underlying queue table may get very big because of that.
- WAIT – a message is not ready for dequeuing as a delay was set during enqueuing process (or it is waiting after failed dequeuing attempt). You probably will not see notifications related messages in that state.
CORR_ID field is the only filed in an AQ table left to be used by an application. In case of the WF_DEFERRED queue is used by EBS in to set an event name the message is relative to. Strictly speaking this queue is used by EBS Business Event System for any deferred events processing and it isn’t dedicated to notifications only. The CORR_ID field is used to dequeue messages relative to a particular event name. It allows us to select the Notification Mailer relative messages only.
As I mentioned previously AQ tables are not regular tables and designed for one purpose only – to make enqueuing/dequeuing processes as efficient as possible. That means there are no indexes to make other queries running faster (including troubleshooting queries). Accordingly that means you should be careful running queries on AQ tables. Most of the queries will full scan queue tables. Executing time will be proportional to underlining AQ tables’ size. You may consider running your queries during out of business hours or move the data you would like to analyse to your test hardware if AQ tables are big in your production environments.
Now we know all the EBS side components of the notification sending process and how to join underlying tables. Let’s put all together in one query which gives us a good picture of where our notification is:
SELECT n.begin_date,
n.status,
n.mail_status,
n.recipient_role,
de.def_enq_time,
de.def_deq_time,
de.def_state,
ou.out_enq_time,
ou.out_deq_time,
ou.out_state
FROM applsys.wf_notifications n,
(SELECT d.enq_time def_enq_time,
d.deq_time def_deq_time,
TO_NUMBER((SELECT VALUE
FROM TABLE(d.user_data.parameter_list)
WHERE NAME = 'NOTIFICATION_ID')) d_notification_id,
msg_state def_state
FROM applsys.aq$wf_deferred d
WHERE d.corr_id = 'APPS:oracle.apps.wf.notification.send') de,
(SELECT o.deq_time out_deq_time,
o.enq_time out_enq_time,
TO_NUMBER((SELECT str_value
FROM TABLE(o.user_data.header.properties)
WHERE NAME = 'NOTIFICATION_ID')) o_notification_id,
msg_state out_state
FROM applsys.aq$wf_notification_out o) ou
WHERE n.notification_id = &NOTIFICATION_ID
AND n.notification_id = de.d_notification_id(+)
AND n.notification_id = ou.o_notification_id(+)
/
This single query links all together and shows you the current state of the message. As discussed in the first article there are three main steps in this process. Let’s take a look on the example below. Each row represents the results of the query at different stages of the notification processing process:
WF Notification History
- EBS user sends email (the first row) – the notification message was put in notifications table at 2009.06.29 14:07:15, and enqueued to WF_DEFERRED queue a second later. MAIL_STATUS is set to “MAIL”. STATE in the deferred queue is “READY” and the message is available to be dequeued.
- Deferred Agent Listener (the second row) – the deferred agent processed the message. It dequeued the message at 14:08:27 (1 minute 11 seconds after the EBS user initiated the sending process). The state of the message in deferred queue is “PROCESSED”. The message is enqueued in WF_NOTIFICATION_OUT queue and is ready to be processed by the next process.
- Notification Mailer (the third row)– Notification Mailer dequeued the message at 14:09:03 (1 minute 48 seconds after it was submitted for sending) and sent it out via configured SMTP process. The MAIL_STATUS field is updated to indicate the sending fact.
As you see the method described gives us detailed information on where the notification message is and what components we should focus our troubleshooting efforts on in case of any problems.
Let’s summarize what we have described in this article. The very first troubleshooting activity the Apps DBA should do in case of reported missing emails from EBS is to send the test email (noticing its ID). If it doesn’t arrive in 5-10 minutes we run described SQL using the notification id in order to find where the notification got stuck. Depending on the observation you focus your attention on the particular EBS component for further troubleshooting.
I hope you find this information useful and will use it in the Notification Mailer troubleshooting process. I am sure that your feedback will inspire me for further articles. Please do not hesitate to leave any feedback, questions or share any issue you had/have related to the topic. I will be more than happy to assist.