Oracle Advanced Queuing - Troubleshooting

Debugging can be done by the following steps:

1. Check if messages are being propagated at all or the propagation is slow

• queue-to-dblink: The propagation delivers messages or events from the source queue to all subscribing queues at the destination database identified by the dblink. A single propagation schedule is used to propagate messages to all subscribing queues. Hence any changes made to this schedule will affect message delivery to all the subscribing queues. 
• queue-to-queue: This propagation mode delivers messages or events from the source queue to a specific destination queue identified on the database link. This allows the user to have fine-grained control on the propagation schedule for message delivery. This new propagation mode also supports transparent failover when propagating to a destination Oracle RAC system. With queue-to-queue propagation, you are no longer required to re-point a database link if the owner instance of the queue fails on Oracle RAC. This mode supports multiple propagations to the same target database if the target queues are different.

select TOTAL_NUMBER 
from DBA_QUEUE_SCHEDULES 
where QNAME=’<source_queue_name>’;


If TOTAL_NUMBER is increasing, then propagation is most likely functioning, although it may be slow.

2. Check if the database link to the destination database has been set up properly. 

3. Check Message State and Destination. Find the queue table for a given queue

select QUEUE_TABLE 
from DBA_QUEUES 
where NAME = &queue_name;

4. Check for messages in the source queue with

select count (*) 

from AQ$<source_queue_table>  

where q_name = 'source_queue_name';

5. Check for messages in the destination queue.

select count (*) 
from AQ$<destination_queue_table>  
where q_name = 'destination_queue_name';

6. Check to see who is using job queue processes.

7. Check which jobs are being run by querying dba_jobs_running. It is possible that other jobs are starving the propagation jobs.

8. Check to see that the queue table sys.aq$_prop_table_instno exists in DBA_QUEUE_TABLES. The queue sys.aq$_prop_notify_queue_instnomust also exist in DBA_QUEUES and must be enabled for enqueue and dequeue.

9. In case of Oracle Real Application Clusters (Oracle RAC), this queue table and queue pair must exist for each Oracle RAC node in the system. They are used for communication between job queue processes and are automatically created.

10. Check that the consumer attempting to dequeue a message from the destination queue is a recipient of the propagated messages.

11. Turn on propagation tracing at the highest level using event 24040, level 10.

12. Debugging information is logged to job queue trace files as propagation takes place. You can check the trace file for errors and for statements indicating that messages have been sent.

Posted in: Oracle Advanced Concepts,Oracle Notebook,Technical