In previous blogs I have discussed the use of the auto-recovery to re-submit asynchronous messages for delivery. I am currently working with a client that wants to know how many undelivered messages they have, and if it reaches a certain threshold then they wants to alert the operator. To do this they plan on using the Enterprise Manager alert functions, but first they needs to know how many undelivered instances are out there.
Undelivered asynchronous messages are stored in the INVOKE_MESSAGE table with a RECEIVE_DATE timestamp marking when they were placed in the table and a STATE to indicate if they have not yet been processed (DELIVERY_PENDING 0). So to query how many messages have not been processed after 10 minutes we can run the query
select count(state) from invoke_message where state = '0' and receive_time < (current_timestamp - Interval '10' Minute)
This counts “count(state)” the number of unprocessed messages “state = '0'” that have been waiting for more than 10 minutes “receive_time < (current_timestamp - Interval '10' Minute)”
We want to allow a delay, in the example 10 minutes, to give the BPEL engine time to start processing the messages.
We can use this query to check that our BPEL instance is processing messages.
Oracle SOA Suite Best Practices Guide 10.1.3.3 – Excellent explanation of async delivery messages in here and also discussion of invoke_message table.
Threading in 10.1.3.4 – Blog entry I wrote explaining 10.1.3 threading
Oracle BPEL PM – Dehydration Store? – Blog entry that describes database structures used by BPEL
Description of the STATE Values for Tables in the BPEL Dehydration Store - Oracle Support Note