|Oracle9i Application Developer's Guide - Advanced Queuing
Release 1 (9.0.1)
Part Number A88890-02
Managing AQ, 9 of 10
Propagation makes use of the system queue
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
X is the instance number of the instance where the source queue of a schedule resides.
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_AQADM packages on the remote database.
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.
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):
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.
Note that AQ does not support propagation from object queues that have BFILE or REF attributes in the payload.
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:
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.
You can do this by doing
select count(*) from @.
Look for the entry in
aq$_schedules. Check that it has a '
aq$_schedules, and that there is an entry in
dbms_jobs with that jobno.
select count(*) fromwhere
q_name = '<queue_name>';
Is it possible that the propagation job is being starved of processing time by other jobs?
sys.aq$_prop_table_ exists in
dba_queue_tablesand that queue
dba_queues(used for communication between job queue processes).
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>';