Mastering Service-Oriented Architecture: The BPEL Cookbook

Managing a BPEL Production Environment
by Stany Blanvalet

Learn how to automate common admin tasks in a BPEL production environment using BPEL Process Manager's API and Dehydration Store.

 Review complete BPEL Cookbook index

Downloads for this article:
 Oracle BPEL Process Manager

Published January 2006

Some organizations spend as much as 70% of their IT budget to maintain and administer current IT functionality and operations. But paradoxically, managing the performance and availability of Web services is not a high priority for most companies implementing service-oriented architecture (SOA) . Thus, as organizations adopt Web services and BPEL to build SOA infrastructure, it is becoming more and more imperative to design new strategies to decrease the costs of application management and administration.

This goal is particularly important for BPEL implementations, in which multiple business processes are often deployed to the production environment. As more and more processes are deployed in the production environment, efficient administration becomes increasingly important. Every BPEL process that has completed its execution, successfully or not, is stored in the database, as is every XML message that is exchanged between different steps in the process flow. As you might expect, this process causes the database to grow in size exponentially, thereby leading to performance bottlenecks.

For these reasons, in your BPEL production environment, it's important that you have the ability to

  • Archive information about completed BPEL processes without affecting the stability of the production system
  • Remove all XML messages that have been successfully delivered and resolved from the database
  • Delete stale instances
  • Rerun failed processes

In this installment of The BPEL Cookbook, you will learn how BPEL Process Manager's APIs and Dehydration Store enable these capabilities, as well as strategies for archiving and deleting information about completed instances of a BPEL process. You will also learn how to delete stale instances and complete invoke and callback XML messages using PL/SQL and EJB. Finally, you will learn how to rerun failed process instances via the BPELTest utility.

BPEL Process Manager API and Dehydration Store

Oracle BPEL Process Manager Console provides a user-friendly, Web-based interface for management, administration, and debugging of processes deployed to the BPEL server. However, 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. But before creating action items, it's important to understand the concepts underlying the Dehydration Store and BPEL Process Manager API.

The Dehydration Store database is used to store process status data, especially for asynchronous BPEL processes. Here’s a quick overview of some its more important tables.




Instance metadata information (creation date, current state, process id)


Scope data for an instance


Audit trail information for an instance. This information can be viewed from BPEL Console.


Large detailed audit information about a process instance


Callback message metadata


Payload of callback messages


Invocation messages metadata


Payload of invocation messages


Delivery subscriptions for an instance


Tasks created for an instance (i.e. title, assignee, status, expiration)

Table 1: Key tables in BPEL Dehydration Store

The database schema can be found in the DDL script domain_oracle.dll in $ORABPEL$\integration\orabpel\system\database\scripts directory. With proper knowledge of this schema, administrators can bypass the BPEL Console and write SQL queries against the store directly.

In addition to the SQL approach, administrators can leverage the BPEL Process Manager API. This 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. (API documentation is available at $ORABPEL$\integration\orabpel\docs\apidocs\index.html.) The table below summarizes some of the most relevant classes/interfaces and corresponding methods here.



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.

Table 2: Key classes for performing administrative tasks

Next, you'll learn how to perform some of the most important administrative tasks.

Archiving Completed Instances

As explained previously, all successfully executed process instances are stored in the Dehydration Store. Currently a BPEL instance is saved in two tables after the instance is completed: cube_instance and cube_scope. The former stores the instance header information: domain, creation date, state (completed, running, stale, cancelled), priority, title, and so on. The latter stores the state of the instance, (variable values and so on). By default, both tables are used to store a completed instance.

Purging the instance information from the database can be accomplished from the BPEL Console as shown below.

Figure 1: Deleting completed instances

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. (Remember to move the information to a different location before purging it from the BPEL database.) Let's look at some examples.

Archiving with EJB. For this example we will use the following interface and method.

Interface IBPELDomainHandle



archiveInstances(WhereCondition wc, boolean deleteInstances)

(Archives all instances returned by the search condition specified by wc )

Method archiveInstances will archive and delete all completed instances. It accepts the parameter keepdays, which indicates how old a completed instance can be before it is archived.

public static int archiveInstances(Locator locator, String processId, int keepdays)
 throws ORABPELAccessException {
                try {
                        WhereCondition wc = WhereConditionHelper.whereInstancesClosed();
                        WhereCondition tmpWhere = new WhereCondition();
                        NonSyncStringBuffer buf = new NonSyncStringBuffer();
                        if (!"*".equals(processId)) {
                                tmpWhere.setClause(buf.append(" AND ").append(
                                                SQLDefs.AL_ci_process_id).append(" = ? ").toString());
                                tmpWhere.setString(1, processId);
                        Calendar cal = Calendar.getInstance();
                        cal.add(Calendar.DATE, -keepdays);
                        tmpWhere.setClause(buf.append(" AND ").append(
                                SQLDefs.AL_ci_modify_date).append(" <= ? ").toString());
                        tmpWhere.setTimestamp(1, cal.getTime());
                        IBPELDomainHandle domain = locator.lookupDomain();
                        return domain.archiveInstances(wc, true);
                } catch (ServerException se) {
                        throw new ORABPELAccessException(se.getMessage());

Archiving with PL/SQL. Administrators can also leverage PL/SQL to accomplish this goal. Before deleting the records from the database, the DBA can move the record to a different database/table per their requirements. Here's an example:

        AND BPELMNG.DOMAIN.DOMAIN_ID = 'XXX'            � XXX is the name of the domain
        AND STATE = 5                                   � '5' mean COMPLETED
        AND MODIFY_DATE < SYSDATE-X                     � X is the number of day of history 
        AND PROCESS_ID = 'XXX';                 � XXX is the process name
        For each CIKEY, call COLLAXA.delete_ci(CIKEY); oracle procedure.


In the next section, you'll learn how to delete callback and invoke messages.

Deleting Callback and Invoke Messages

Whenever an instance expects a message from a partner ( receive, onMessage, and so on), a subscription is issued for that specific receive activity. Once a delivery message is received, the delivery layer attempts to correlate the message with the intended subscription. Successfully subscribed messages continue to remain in the database. These messages are deleted via the collaxa.delete_ci(CIKEY) stored procedure (as performed previously for instance archiving).

The same is true for all callback and invocation XML messages. All such messages remain in the database even if they have been successfully resolved and delivered.

You can use the following methods to delete all callbacks, invokes, and subscriptions.

Interface IBPELDomainHandle



deleteAllHandledCallback ()

(Deletes all callback messages delivered to this domain that have been successfully resolved and delivered)


DeleteAllHandledInvoke ()

(Deletes all invocation messages delivered to this domain that have been successfully resolved and delivered)


DeleteAllHandledSubscription ()

(Deletes all message subscribers in this domain that have been successfully resolved and handled--that is, have had a message delivered to them)

You can also use PL/SQL to do the same thing, as illustrated by the following stored procedures. I recommend that your DBA use this script as a starting point and modify it to suit specific archiving needs (deleting, moving to another database, altering the where clause to include a selection based on business criteria, and so on.)

* Procedure to clean invocation messages for a particular domain.
* Invocation messages are stored in invoke_message and invoke_message_bin table 
* It will select all the invocation messages from invoke_message table.
* For each message which has been delivered or resolved, delete it from
* invoke_message and invoke_message_bin table
    procedure delete_invoke( p_domain_ref in integer ) 
      cursor c_invoke_message is
      select message_guid
      from invoke_message
      where ( state = 2 or state = 3 )
          and domain_ref = p_domain_ref
      for update;
                   for r_invoke_message in c_invoke_message loop
                           delete from invoke_message_bin where message_guid = r_invoke_message.message_guid;
                   delete from invoke_message where current of c_invoke_message;
                        end loop;
        end delete_invoke;

* Procedure to clean callback messages for a particular domain.
* Callback messages are stored in dlv_message and dlv_message_bin table 
* It will select all the invocation messages from dlv_message table.
* For each message which has been delivered or resolved, delete it from
* dlv_message and dlv_message_bin table
    procedure delete_callback( p_domain_ref in integer ) 
    cursor c_dlv_message is
        select message_guid
        from dlv_message
        where ( state = 2 or state = 3 )
                and domain_ref = p_domain_ref
        for update;
        for r_dlv_message in c_dlv_message loop
        delete from dlv_message_bin
        where message_guid = r_dlv_message.message_guid;
        delete from dlv_message 
        where current of c_dlv_message;
                end loop;
    end delete_callback;


Next, let's overview how to delete stale instances.

Deleting Stale Instances

Using BPEL Console, you can identify all stale instances and kill them as shown below.

Figure 2: Killing stale instances in BPEL Console

Unfortunately, these stale instances can be searched only for a specific domain. But in a production environment, multiple processes will probably be deployed across different domains—resulting in a huge administrative burden. Besides, purging all stale instances at once within a specific domain is not possible.

As an alternative approach, you can find a unique cube instance key ( cikey) for every stale instance via this SQL query.

SELECT CUBE_INSTANCE.cikey, CUBE_INSTANCE.root_id, CUBE_INSTANCE.process_id,            

Once you have identified a cikey for each stale instance, you can use that key to delete stale instances from CUBE_INSTANCE and other tables that reference the cube instances by invoking delete_ci(CIKEY) for every cube instance found via the SQL query. delete_ci(CIKEY) is a stored procedure that takes cube instance as a parameter and deletes cube instance and all rows on other BPEL tables that reference the cube instance.

One benefit of this approach is that stale instances can be deleted across multiple domains (if you remove the domain clause in where condition). It also permits mass purging of stale instances. These two benefits make PL/SQL a strong candidate for managing the production environment.

The sample code below demonstrates how these methods can be used in an EJB to list and delete stale instances.

WhereCondition where = WhereConditionHelper.whereInstancesStale();
      IInstanceHandle[] instances = getLocator(domainId, domainPassword).listInstances(where);
           for(int i=0; i<instances.length; i++){

First two lines return an array of IInstanceHandle bean for all stale instances per domain. You then use IInstanceHandle.delete() to delete all these instances. You can easily extend this code to cover all domains.

Archiving old instances and deleting stale instances and handled XML messages will help reduce the size of the tables. It turns out that unless you either truncate or rebuild the table, just deleting rows does not put the free space back into the table space.

Use the following commands to regain that free space; similar commands can be executed for other tables in the Dehydration Store.
alter table cube_scope enable row movement;
alter table cube_scope shrink space compact;
alter table cube_scope shrink space;
alter table cube_scope disable row movement;

In addition, to prevent future growth, the DBA can manipulate PCTFREE, PCTUSED, and PCTVERSION parameters.

Finally, let's see how to deal with instances that fail during run-time.

Rerunning Failed Instances

BPEL processes may fail for multiple reasons—such as wrong input, incorrect process design, or unavailable external application, just to name a few. In any case, the process will not successfully complete its execution and end up in a ‘fault’ state. If the process has failed due to incorrect design, you will have to modify the process design and redeploy the BPEL process.

However, if a process has failed incorrect input or external issues (network failure, unavailable external application), you would want to rerun the process once input has been corrected or external issues have been addressed. Ideally, you would rerun the process with the old inputs so that the original request is handled properly.

This goal can be accomplished using BPELTest (available in 10.1.3 and later), an important utility designed to create and run test cases on BPEL Processes for unit and integration testing. BPELTest can simulate partner invocation, perform assertions, and provide information on various test results. (For more information, view the BPELTest webinar replay listed here.)

The most relevant aspect of BPELTest is its ability to create a test case from an audit trail, also known as Automatic Test Generation. For example, consider a situation where a process instance fails in its execution because the external application is down. Ideally, you would like to rerun the process in the exact same scenario when the external application is back online. This approach could be cumbersome however; it would involve reconfiguring external dependencies that may or may not be controlled by the process owner.

Instead, using BPELTest, you can generate the basic test case from the audit trail of a failed instance. The generated test case will contain commands to emulate partners exactly as the failed instance had. In the case of incorrect data, you could modify the test case for correct data. If the failed instance were due to unavailable external application, you could simply run the test case whenever the application is back up.


As you can see, it is possible to partially automate the management of a BPEL production environment using the BPEL Process Manager API and EJB or PL/SQL against the BPEL Dehydration Store. As you deploy more and more BPEL processes, it will become imperative to design a repository of utilities that automate everyday tasks and pro-actively address potential production issues.

Stany Blanvalet Stany Blanvalet is a BPEL and J2EE consultant. Previously, working as a Java EE architect, Stany introduced and administered Belgacom's BPEL-based DSL provisioning application, a mission-critical BPEL production system. He is a contributor to the jaisy-OrabpelInterface project, an open-source JMX monitoring tool for Oracle BPEL Process Manager.

Send us your comments