Wednesday 26 November 2014

Basics of Oracle AQ

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:

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_MESSAGEagain, 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

No comments:

Post a Comment