AQ PL/SQL Notification No Longer Work Due To "register_driver()" Jobs Not Terminating (Doc ID 331372.1)

Symptoms

Advanced Queueing (AQ) PL/SQL notification is being used.

The plsql notification procedure is attempting to dequeue a message from the associated application queue using the supplied message descriptor information.

In versions up to and including 10.2 the register_driver() jobs in dba_jobs_running run forever and do not terminate or process messages.  In versions starting from 11.1, the register_driver() job is now moved to come under scheduler control ; in these versions this job, now observed in dba_scheduler_running_jobs, runs forever and does not terminate or process messages.

Message ids to be notified are placed in the notification queue. These messages ids correspond to the actual message located in the Application queue . The message id of the message for which the register_driver job is executing is not found in the application queue. The register_driver activity does the work of looking up the message in the notification queue and then dequeuing it from the Application queue.

The notification mechanism does not handle the exception being passed back to it correctly and so repeatedly calls the PLSQL callback procedure with the same message id which is not present in the Application queue. This may also cause associated jobs related processes to exhibit more CPU than expected . As a result of these orphan messages in the notification queue the mechanism will stall and prevent further activity. If there are orphan messages, then a review of the PL/SQL notification code should be made.

11.2 introduces one notification queue per instance. This is primarily to partition activity in RAC environments to reduce cross instance communication. The Emon secondary processes and dequeue job activity work on the notification queue associated with its instance.

This means that starting with 11.2 , on a single instance environment , the notification queue and queue table are now : AQ_SRVNTFN_TABLE_Q_1 and AQ_SRVNTFN_TABLE_1. On a RAC environment, the queues will be AQ_SRVNTFN_TABLE_Q_N and the queue tables : AQ_SRVNTFN_TABLE_N - where N is the instance id.  In earlier versions, they were AQ_SRVNTFN_TABLE_Q and AQ_SRVNTFN_TABLE.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.