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:


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):

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.
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.
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.

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:

Fundamentals of BPEL

To Start with, BPEL (Business Process Execution Language), is an OASIS standard executable language for specifying actions within business processes with web services. Source Wiki

So if we say BPEL we mean a standard language, whose basics will not change as per the implementing companies/product. All we need is BPEL engine to run the BPEL code. This engine can be open source or licensed one, depending on the additional features we may require for the product/service to be developed.

For example we have few companies IBM, Oracle, WSO2 which strictly follow BPEL (OASIS) standards and provide additional features on top of the basic BPEL.

Latest version of BPEL is 1.2 -->2.0 which was revised from 1.1.

Few differences are :

New Activities 2.0

  • activity to repeat the set of activities . Replace the FlowN activity in BPEL 1.1 version.
  • - Use this activity if the body of an activity must be performed at least once. The XPath expression condition in the repeatUntil activity is evaluated after the body of the activity completes.
  •  -- - Replaces the switch activity in BPEL 2.0 - This activity enables you to define conditional behavior for specific activities to decide between two or more branches. Only one activity is selected for execution from a set of branches.
  • Changed to
  •  - to compensate the specified child scope
  • A activity has been added to fault handlers this activity enables you to rethrow a fault originally captured by the immediately enclosing fault handler.
New activity types:- repeatUntil, validate, forEach (parallel and sequential), rethrow, extensionActivity, compensateScope 
Renamed activities:- switch/case renamed to if/else, terminate renamed to exit 
Termination Handler:- added to scope activities to provide explicit behavior for termination
Variable initializationportType attribute:- The attribute "portType" of messaging activities such as "receive," "invoke," "reply," "pick," and "onEvent" is no longer mandatory and can be omitted


Just To summarise and revise ;) 

Messaging:- In BPEL 1.1 there is no defined behavior for a process that receives a message for a request-response operation and finishes without replying. In BPEL 2.0, such a scenario would have trigged a new BPEL standard fault called "missingReply" fault.

Source :

Coming back to BPEL, since I am from Oracle SOA suite background I would share my learnings in BPEL in terms of features and extensions provided by oracle.

Oracle extensions: 


Java Embedding activity:
we can embed Java code snippets directly into the BPEL process using the Java BPEL exec extension.
When we drag a Java Embedding activity into a BPEL process in Oracle BPEL Designer, the  element and bpelx:exec tag are automatically added.




Dehydration Store :As discussing this will require more than one blog..there is one informative blog for dehydration you can refer to :

Over the life cycle of a BPEL instance, the instance with its current state of execution may be saved in a database. When a BPEL instance is saved to a database, the instance is known as being dehydrated. The database where the BPEL instance is saved is called a dehydration store.

Skipping dehydration
  • For Synchronous processes that do not need to be durable, you can turn off the dehydration mechanism by setting the following properties at process or engine level:
  • Skipping dehydration will increase performance with a cost of auditing.
  • By Default, all BPEL processes are dehydrated regardless of whether they are Synchronous or Asynchronous process.
  • Set inMemoryOptimization to true.
  • Set completionPersistPolicy property to faulted or Off.
  • Asynchronous process invocation messages are always saved to the dehydration store.
  • Durable process are always dehydrated regardless of the settings of the persistence properties.
Dehydration Tables
  1. Cube_Instance: Stores the information about the composite instance that gets created.
  2. Cube_scope: Stores information about the scopes and variables declared and used.
  3. DLV_Message: All the incoming payload details are stored.
  4. Invoke_Message: All the outgoing message payload details are stored.
  5. Audit_Trail: Used to store information of the xml that is rendered in EM console.

Link Referred :