Guidelines for Debugging AQ Propagation Problems

This discussion assumes that you have created queue tables and queues in source and target databases and defined a database link for the destination database. Notation assumes that you will supply the actual name of the entity (without the brackets).

There is additional information in the Application Developer's Guide for Advanced Queuing. In particular, see sections on propagation in Chapters 2 and 3. There is also troubleshooting information in 8.0 Application Developer's Guide, Ch 11.

1. Turn on propagation tracing at the highest level using event 24040, level 10. Debugging information will be logged to job queue trace file(s) as propagation takes place. You can check the trace file for errors, and for statements indicating that messages have been sent.

2. Check the database link to database 2. You can do this by doing select count(*) from @.

3. Check that the propagation schedule has been created and that a job queue process has been assigned. Look for the entry in dba_queue_schedlues and aq$_schedules for your schedule. Check that it has a 'jobno' in aq$_schedules, and that there is an entry in job$ or dbms_jobs with that jobno.

4. Make sure that there are at least 1 job queue processes running. There should be at least 2 job queue processes for Oracle8i.

5. Check for messages in the source queue with select count(*) from where q_name = '';

6. Check for messages in the destination queue with the same kind of select.

7. Check to see if there are others using job queue processes. Is it possible that the propagation job is being starved of processing time by other jobs?

8. Check to see that sys.aq$_prop_table_ exists in dba_queue_tables and that queue aq$_prop_notify_ exists in dba_queues. ( is the OPS instance number). These are used for communication between job queue processes.

9. Check that the consumer attempting to dequeue a message at from the destination queue, is a recipient of the propagated messages. For 8.1 style queues, you can do

select consumer_name, deq_txn_id, deq_time, deq_user_id,

propagated_msgid from aq$

where queue = ;

For 8.0 style queues, you can obtain the same information from the history

column of the queue table:

select h.consumer, h.transaction_id, h.deq_time, h.deq_user,

h.propagated_msgid from t, table(t.history) h

where t.q_name = '';

or

select consumer, transaction_id, deq_time, deq_user,

propagated_msgid from

the(select cast(history as sys.aq$_dequeue_history_t)

from where q_name = '');