Oracle Advanced Queue, as the name suggests has all the queue features. The added advantage which oracle AQ offers is the oracle DB, means we can have a persistent queue which will be stored in db.
Oracle AQ follows some basic structure to store the message in queue.
first of all we need to create AQ table which will be having queues and then can schedule propagation and create subscribers.
Just to quote the definition from Oracle
"Oracle Advanced Queuing (AQ) is a database-integrated messaging infrastructure
in Oracle Database 11g. AQ leverages the functionality of the Oracle database to
store messages in persistent queues. All operational benefits of the Oracle database
such as High Availability, Scalability and Reliability are applicable to the messages
and queues in AQ. "
When I had to work on AQ the basic confusion I had was
how propagation and subscribers are different ?
and when to use what ?
well the basic difference that I understood was, Creating subscribers is a way to tell the corresponding queue can be dequeued by the declared subscriber. So that if there is any rule or transformation required for that subscriber then it will be done while dequeuing operation only.
Propagation is pretty straightforward. As soon as message comes or as per settings(time and delay) the message will be propagated to other queue, this queue can be in the same database or it exists in some remote db.
Sample troubleshooting steps:
Common AQ Errors(copied from Oracle docs):
This white paper contains all the basic info about AQs:
http://www.oracle.com/technetwork/database/features/data-integration/oracle-aq-tech-wp11-2-191324.pdf
Oracle AQ follows some basic structure to store the message in queue.
first of all we need to create AQ table which will be having queues and then can schedule propagation and create subscribers.
Just to quote the definition from Oracle
"Oracle Advanced Queuing (AQ) is a database-integrated messaging infrastructure
in Oracle Database 11g. AQ leverages the functionality of the Oracle database to
store messages in persistent queues. All operational benefits of the Oracle database
such as High Availability, Scalability and Reliability are applicable to the messages
and queues in AQ. "
When I had to work on AQ the basic confusion I had was
how propagation and subscribers are different ?
and when to use what ?
well the basic difference that I understood was, Creating subscribers is a way to tell the corresponding queue can be dequeued by the declared subscriber. So that if there is any rule or transformation required for that subscriber then it will be done while dequeuing operation only.
Propagation is pretty straightforward. As soon as message comes or as per settings(time and delay) the message will be propagated to other queue, this queue can be in the same database or it exists in some remote db.
Sample troubleshooting steps:
1. 1. Check if the
database link to the destination database has been set up properly. Make sure
that the queue owner can use the database link. You can do this with:
Select count(*)
from table_name@dblink_name;
2. 2. Check whether Queues are enabled for
enqueue/dequeue:
Select * from USER_QUEUES where name = 'QUEUE_NAME';
2.1 If the queues are disabled for enqueue/dequeue,
then we can start it by using following sample command:
EXECUTE
DBMS_AQADM.START_QUEUE(QUEUE_NAME => QUEUE_NAME');
commit;
3. 3. Check messages in the queue:
select * from AQ$QUEUE_TABLE_NAME;
4. 4. Check the subscribers for the queue :
select * from ALL_QUEUE_SCHEDULES;
Common AQ Errors(copied from Oracle docs):
ORA-1555
You might get this error when using the
NEXT_MESSAGE
navigation option for dequeue. NEXT_MESSAGE
uses the snapshot created during the first dequeue call. After that, undo
information may not be retained.
The workaround is to use the
FIRST_MESSAGE
option to dequeue the message. This reexecutes
the cursor and gets a new snapshot. FIRST_MESSAGE does not perform as well as
NEXT_MESSAGE, so Oracle recommends that you dequeue messages in batches: FIRST_MESSAGE
for one, NEXT_MESSAGE
for the next 1000 messages, then FIRST_MESSAGE
again,
and so on.
ORA-24033
This error is raised if a message is enqueued to a multiconsumer
queue with no recipient and the queue has no subscribers (or rule-based
subscribers that match this message). This is a warning that the message will
be discarded because there are no recipients or subscribers to whom it can be
delivered.
ORA-25237
When using the Oracle Streams Advanced Queuing navigation
option, you must reset the dequeue position by using the
FIRST_MESSAGE
option if you want to continue dequeuing
between services (such as xa_start
and xa_end
boundaries). This is because XA cancels the
cursor fetch state after an xa_end
. If you
do not reset, then you get an error message stating that the navigation is used
out of sequence.
ORA-25307
Flow control has been enabled for the message sender. This means
that the fastest subscriber of the sender's message is not able to keep pace
with the rate at which messages are enqueued. The buffered messaging
application must handle this error and attempt again to enqueue messages after
waiting for some time.
This white paper contains all the basic info about AQs:
http://www.oracle.com/technetwork/database/features/data-integration/oracle-aq-tech-wp11-2-191324.pdf