Propagation Issues

Propagation makes use of the system queue aq$_prop_notify_X, where X is the instance number of the instance where the source queue of a schedule resides, for handling propagation run-time events. Messages in this queue are stored in the system table aq$_prop_table_X, where X is the instance number of the instance where the source queue of a schedule resides.

Caution: The queue aq$_prop_notify_X should never be stopped or dropped and the table aq$_prop_table_X should never be dropped for propagation to work correctly.

Execute Privileges Required for Propagation

Propagation jobs are owned by SYS, but the propagation occurs in the security context of the queue table owner. Previously propagation jobs were owned by the user scheduling propagation, and propagation occurred in the security context of the user setting up the propagation schedule. The queue table owner must be granted EXECUTE privileges on the DBMS_AQADM package. Otherwise, the Oracle snapshot processes will not propagate and generate trace files with the error identifier SYS.DBMS_AQADM not defined. Private database links owned by the queue table owner can be used for propagation. The user name specified in the connection string must have EXECUTE access on the DBMS_AQ and DBMS_AQADM packages on the remote database.

The Number of Job Queue Processes

The scheduling algorithm places the restriction that at least two job queue processes be available for propagation. If there are nonpropagation-related jobs, then more job queue processes are needed. If heavily loaded conditions (a large number of active schedules, all of which have messages to be propagated) are expected, you should start a larger number of job queue processes and keep in mind the need for nonpropagation jobs as well. In a system that only has propagation jobs, two job queue processes can handle all schedules. However, with more job queue processes, messages are propagated faster. Since one job queue process can propagate messages from multiple schedules, it is not necessary to have the number of job queue processes equal to the number of schedules.

Optimizing Propagation

In setting the number of JOB_QUEUE_PROCESSES, DBAs should be aware that this number is determined by the number of queues from which the messages have to be propagated and the number of destinations (rather than queues) to which messages have to be propagated.

A scheduling algorithm handles propagation. The algorithm optimizes available job queue processes and minimizes the time it takes for a message to show up at a destination after it has been enqueued into the source queue, thereby providing near-OLTP behavior. The algorithm can handle an unlimited number of schedules and various types of failures. While propagation tries to make the optimal use of the available job queue processes, the number of job queue processes to be started also depends on the existence of nonpropagation-related jobs such as replication jobs. Hence, it is important to use the following guidelines to get the best results from the scheduling algorithm.

The scheduling algorithm uses the job queue processes as follows (for this discussion, an active schedule is one that has a valid current window):

• If the number of active schedules is less than half the number of job queue processes, the number of job queue processes acquired corresponds to the number of active schedules.

• If the number of active schedules is more than half the number of job queue processes, after acquiring half the number of job queue processes, multiple active schedules are assigned to an acquired job queue process.

• If the system is overloaded (all schedules are busy propagating), depending on availability, additional job queue processes will be acquired up to one less than the total number of job queue processes.

• If none of the active schedules handled by a process has messages to be propagated, then that job queue process will be released.

• The algorithm performs automatic load balancing by transferring schedules from a heavily loaded process to a lightly load process such that no process is excessively loaded.

Handling Failures in Propagation

The scheduling algorithm also has robust support for handling failures. It may not be able to propagate messages from a queue due to various types of failures. Some of the common reasons include failure of the database link, non-availability of the remote database, non-existence of the remote queue, remote queue not started and security violation while trying to enqueue messages into the remote queue. Under all these circumstances the appropriate error messages will be reported in the DBA_QUEUE_SCHEDULES view. When an error occurs in a schedule, propagation of messages in that schedule is attempted periodically using an exponential backoff algorithm for a maximum of 16 times, after which the schedule is disabled. If the problem causing the error is fixed and the schedule is enabled, the error fields that indicate the last error date, time, and message will still continue to show the error information. These fields are reset only when messages are successfully propagated in that schedule. During the later stages of the exponential backoff, many hours or even days can elapse between propagation attempts. This happens when an error has been neglected for a long time. Under such circumstances it may be better to unschedule the propagation and schedule it again.

Propagation from Object Queues

Note that AQ does not support propagation from object queues that have BFILE or REF attributes in the payload.

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. The notation assumes that you will supply the actual name of the entity (without the brackets).

To begin debugging, do the following:

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

   Debugging information will be 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.

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_schedules and aq$_schedules. 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 at least two job queue processes are running.

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

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

7. Check to see who is 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 (used for communication between job queue processes).

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

   For 8.1-style queues, you can do the following:

           select consumer_name, deq_txn_id, deq_time, deq_user_id, 
             propagated_msgid from aq$ 
             where queue = '<queue_name>';
   
   

   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 = '<queue_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 = '<queue_name>');