Why does OracleAQ Leave Dead Subscribers in the Queue?

Question

This is Oracle 11.2.0.3.

We've got a problem where we use Oracle's JMS over OracleAQ. This works fine except we started noticing the queue getting filled with 1000s, then millions of messages over time. Some of these are in the PROCESSED state, but most are READY. We traced down this behavior to "zombie" or dead subscribers to the topic. When a Java process is terminated and doesn't get the chance to unregister itself, it leaves the subscriber record in the queue and ORacle doesn't seem to detect that it is dead. So much so that MONTHS later, a new message sent into our multi-subscriber queue will then get multiplied by the # of subscribers, which it thinks is much higher than it actually is. (We first noticed this when we reached the maximum subscriber limit.)

We've got the qmon processes running - I even tried increasing the minimum # of processes to no effect. The queue clean-up happens really nicely as long as there are no dead subscribers in the queue.

Anyone see this before, and hopefully found a solution?

Answer 1

Ok, So I could not have a better solution than this:

1) Create your subscriber with a name and keep track of the subscriber's name.

2) Make sure that you have a shutdown hook to application to execute below procedure, which will unsubscribe and de-register the subscriber.

3) In case of unexpected shutdown/crash, when un-subscription could not be done, there must be a cleanup task 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 make sure that your system remains full-proof.