Sunday, November 21, 2010

Oracle BPEL PM - Dehydration Store?

BPEL is the standard for assembling a set of discrete services into an end-to-end process flow, radically reducing the cost and complexity of process integration initiatives. BPEL is an OASIS standard executable language for specifying actions within business processes with web services. Processes in Business Process Execution Language export and import information by using web service interfaces exclusively. Leaders in this arena include Oracle BPEL PM, Websphere PM etc.

Oracle BPEL PM utilizes a database to store metadata and instance data during runtime. The process of updating process state in the database is called Dehydration. This data is stored in what is known as the Dehydration store, which is simply a database schema (also called dehydration store, BPEL schema, BPEL tables). The Dehydration Store database is used to store process status data, especially for asynchronous BPEL processes, like BPEL’s metadata and instance data. This exists in x_SOAINFRA schema created by running RCU.

This is separate and independent from any database objects used by your BPEL processes for storing application or business data. For performance reasons, the BPEL schema does not utilize foreign keys and thus master-detail relationships are not obviously inferred from looking at the schema definition.These dependency relationships are maintained by the BPEL engine.

Oracle BPEL Process Manager uses the dehydration store database to maintain long-running asynchronous processes and their current state information in a database while they wait for asynchronous callbacks. Storing the process in a database preserves the process and prevents any loss of state or reliability if a system shuts down or a network problem occurs.

The database schema ddl can be found at: \Oracle_SOA1\rcu\integration\soainfra\sql\bpel. With proper knowledge of this schema, administrators can bypass the BPEL Console and write SQL queries against the store directly OR use BPEL Process Manager API.

Oracle BPEL Process Manager Console provides a user-friendly, Web-based interface for management, administration, and debugging of processes deployed to the BPEL server. BPEL Process Manager API provides an exhaustive set of classes to find, archive, delete instances in various states, delete callback/invoke messages across different domains, or query on the status of specific domain, process, or instance. In production environments, administrators need strong control over management tasks. Via a PL/SQL query or BPEL API against the BPEL Dehydration Store database, it is possible to automate most of these administrative tasks.

Key classes for performing administrative tasks are:

Class/Interface Methods
Class WhereConditionHelper Provides methods such as whereInstancesClosed(), whereInstancesStale(), and whereInstancesOpen(), which construct a where clause that search for respective instances.
Interface IBPELDomainHandle Allows the developer to perform operations on a running BPEL process domain. Provides methods such as archiveAllInstances(), deleteAllInstances(), d eleteInstancesByProcessId(), deployProcess(), and undeployPorcess(), deleteAllHandledCallback(), and deleteAllHandledInvoke().
Interface IinstanceHandle Allows the user to perform operations on an active instance. Provides methods such as isStale() , getState() , getModifyDate() , and delete() .
Class Locator Allows the user to search for processes, instances, and activities that have been deployed and instantiated within an Orabpel process domain. Provides methods such as listInstances() and listActivities() and can take where clauses as parameters.
Tables and their relationships are:

TASK table stores tasks created for an instance. The TaskManager process keeps its current state in this table. Upon calling invoking the TaskManager process, a task object is created, with a title, assignee, status, expiration date, etc. When updates are made to the TaskManager instance via the console the underlying task object in the db is changed.
Table nameDescription
CUBE_INSTANCEContains one entry for each BPEL instance created. It stores instance meta data information like creation date,last modified date, current state, process id etc.
An important column is cikey. Each BPEL instance is assigned a unique ID -  is the instance ID that you see in your BPEL console.Gets incremented in a sequence with creation of BPEL instances. This key cuts across a lot of tables in the dehydration tables.
Following are processes state codes and their meaning
Closed and Aborted8
Closed and Cancelled7
Closed and Completed 5
Closed and Faulted 6
Closed and (Pending or Cancel)4
Closed and Stale9
Initiated 0
Open and Running1
Open and Suspended2
Open and Faulted
CUBE_SCOPEStores the scope data for an instance. It stores BPEL scope variable values & some internal objects to help route logic throughout the flow.
INVOKE_MESSAGEStores incoming (invocation) messages (messages that result in the creation of an instance). This table only stores the meta data for a message (for example, current state, process identifier, and receive date). Following are message states and their meanings
CANCELLEDMessage Processing Cancelled3
HANDLEDMessage is processed2
RESOLVEDMessage is given to BPEL PM but not yet processed1
UNRESOLVEDMessage is not yet given to BPEL PM0
DLV_MESSAGECall back messages are stored here. All non-invocation messages are saved here upon receipt. The delivery layer will then attempt to correlate the message with the receiving instance. This table only stores the metadata for a message. (eg. current state, process identifier, receive date).
WORK_ITEMStores activities created by an instance. All activities in a BPEL flow have a work_item table. This table includes the meta data for the activity (current state, label, and expiration date (used by wait activities)). When the engine needs to be restarted and instances recovered, pending flows are resumed by inspecting their unfinished work items.
SCOPE_ACTIVATIONScopes that need to be routed/closed/compensated are inserted into this table. In case of system failure, we can pick up and re-perform any scopes that should have been done before the failure
DLV_SUBSCRIPTIONStores delivery subscriptions for an instance. Whenever an instance expects a message from a partner (for example, the receive or onMessage activity) a subscription is written out for that specific receive activity. Once a delivery message is received the delivery layer attempts to correlate the message with the intended subscription
AUDIT_TRAILStores record of actions taken on an instance. As an instance is processed, each activity writes events to the audit trail as XML. As the instance is worked on, each activity writes out events to the audit trail as XML which is compress ed and stored in a raw column.
AUDIT_DETAILSStores details for audit trail events that are large in size. Audit details are separated from the audit_trail table due to their large size. The auditDetailThreshold property in Oracle BPEL Control under Manage BPEL Domain > Configuration is used by this table. If the size of a detail is larger than the value specified for this property, it is placed in this table. Otherwise, it is placed in the audit_trail table
XML_DOCUMENTStores process input and output xml documents. Separating the document storage from the meta data enables the meta data to change frequently without being impacted by the size of the documents
WI_EXCEPTIONStores exception messages generated by failed attempts to perform, manage or complete a work item. Each failed attempt is logged as an exception message
PROCESS_DESCRIPTORStores BPEL processes deployment descriptor(bpel.xml)
Record of events (informational, debug, error) encountered while interacting with a process.
INVOKE_MESSAGE_BINStores invoke payload of a process. This table has foreign key relationship with INVOKE_MESSAGE table
DLV_MESSAGE_BINStores received payload of a call-back process. The metadata of a callback message is kept in the dlv_message table, this table only stores the payload as a blob. This separation allows the metadata to change frequently without being impacted by the size of the payload (which is stored here and never modified).
This table has foreign key relationship with DLV_MESSAGE
WFTASKStores human workflow tasks run time meta data like taskid,title,state,user or group assigned, created and updated dates.
WFTASKMETADATAStores task meta data. Content in this table comes from '.task' file of BPEL project
WFASSIGNEEStores task assignee information
WFMESSAGEATTRIBUTEStores task input payload parameters
WFATTACHMENTStores task attachments
WFCOMMENTSStores task comments

In a production environment, it will be necessary to archive the information before you delete the information—and to do so for hundreds of instances. Fortunately, you can achieve this goal using PL/SQL or EJB.

Datastore for Dehydration Store:
Oracle BPEL Server obtains database connections using an application server JTA data source. Oracle BPEL Server by default is configured to use the Oracle Database Lite dehydration store. For stress testing and production, Oracle recommends that you use Oracle Database 10g/11g. The same recommended when BPEL involves large attachments.

Domain and Process Configuration Property Settings

Two types of processes in Ora BPM. These processes impact the dehydration store database in different ways:
  • Transient processes: does not incur any intermediate dehydration points during process execution. If there are unhandled faults or there is system downtime during process execution, the instances of a transient process do not leave a trace in the system. Instances of transient processes cannot be saved in-flight (whether they complete normally or abnormally). Transient processes are typically short-lived, request-response style processes. Eg: synchronous process.
  • Durable processes: incurs one or more dehydration points in the database during execution because of the following activities:
    • Receive activity
    • OnMessage branch in a pick activity
    • OnAlarm branch in a pick activity
    • Wait activity
    Instances of durable processes can be saved in-flight (whether they complete normally or abnormally). These processes are typically long-living and initiated through a one-way invocation. Because of out-of-memory and system downtime issues, durable processes cannot be memory-optimized. The asynchronous process you design in Oracle JDeveloper is an example of both transient and durable processes.
Idempotent BPEL Property
A BPEL invoke activity is by default an idempotent activity, meaning that the BPEL process does not dehydrate instances immediately after invoke activities.  
  • false: activity is dehydrated immediately after execution and recorded in the dehydration store. provides better failover protection, but at the cost of some performance, since the BPEL process accesses the dehydration store much more frequently
  • true (default): If Oracle BPEL Server fails, it performs the activity again after restarting. This is because the server does not dehydrate immediately after the invoke and no record exists that the activity executed.
    This setting can be configured for each partner link in the bpel.xml file.

BPEL Process Manager API
Managing a BPEL Production Environment 
Purging strategies for dehydration store
jaisy-OrabpelInterface - JMX monitoring for Oracle Bpel Process Manager
Ora BPEL PM Performance Tuning
Ora BPEL Webinar
SOA Best Practices: The BPEL Cookbook
Pattern-based Evaluation of Oracle-BPEL - also good to understand xml block to each bpel component
Migrating dehydration for oc4j server in Ora BPM
Oracle BPEL PM - Components
Ora BPEL thru OSB
Exposing Ora BPEL processes as Web services in OSB layer
When to use OSB & BPEL?
BPEL 10g Purging Strategy
BPEL 10g Partitioning


  1. A very good article having the best information on performance tuning.

  2. Hi ,
    Please could you let me know to connect to the dehydration store?

  3. I'm testing a specific case: I have an asynchronous bpel process (one-way) with a catch implemented and I have configured several retries (retry policy). I wonder what happens with the execution while waiting for a retry. Is it stored in Database?, What tables are used in this case?

  4. I do not find invoke_message_bin table on local SOA server Could you tell me what could be the reason ? Is this table replaced with someother table ?