AQ PL/SQL Notification No Longer Work Due To "register_driver()" Jobs Not Terminating
(Doc ID 331372.1)
Last updated on JUNE 30, 2023
Applies to:Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Information in this document applies to any platform.
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.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document