Why is OracleAQ keeping dead subscribers in line?
This is Oracle 11.2.0.3.
We have a problem when we use Oracle JMS for OracleAQ. This works great, except that we started noticing that the queue was filling up with 1000 and then millions of messages over time. Some of them are in PROCESSED state, but most of them are READY. We've traced this behavior back to "zombies" or dead subscribers for the topic. When a Java process exits and does not get a chance to unregister, it leaves the subscriber entry in the queue and ORacle does not seem to detect that it is dead. So much so that MONTHS later on, a new message sent to our multi-subscriber queue will then multiply by the number of subscribers, which it thinks is much higher than it actually is. (We first noticed this when we hit the maximum subscriber limit.)
We have qmon processes running - I even tried to increase the minimum number of processes without effect. Clearing the queue happens very nicely as long as there are no dead subscribers in the queue.
Has anyone seen this before and hopefully found a solution?
source to share
So, I couldn't have a better solution than this:
1) Create your follower with name and track subscriber name.
2) Make sure you have a shutdown faucet to complete below procedure, which unsubscribes and unregisters the subscriber.
3) In case of unexpected shutdown / crash where subscription cannot be completed, a cleanup task must be performed to execute below code:
DECLARE
aqAgent SYS.AQ$_AGENT;
BEGIN
for idx in (select consumer_name from
DBA_QUEUE_SUBSCRIBERS a where a.queue_name = '<Your Oracle AQ Name>') loop
aqAgent := SYS.AQ$_AGENT(idx.consumer_name, NULL, NULL);
DBMS_AQADM.REMOVE_SUBSCRIBER('<Your Oracle AQ Name>', aqAgent);
end loop;
END;
This will ensure that your system stays complete.
source to share