Tag Archives: BPEL

Purging BPEL and ESB instances

The question on the ability to purge instances for both Oracle BPEL and Oracle ESB keeps recurring so I decided to come up with a small overview.

Oracle BPEL

  • FMW version and up, there is a new version of the purge_soainfra_oracle.sql. Ensure that you run the purge setup scripts from the RCU or RCU location, respectively, as this contains the latest purge details. Details are in the SOA Admin Guide
  • FMW version and up, there is a purge_soainfra_oracle.sql script in the RCU_HOME/rcu/integration/soainfra/sql/purge directory. In the release before you need to apply patch 10297850 to obtain the updated purge scripts. More details are in the FMW Admin Guide on Managing Database Growth. Mark Kelderman posted on how to improve the performance of this script by adding a specific index.
  • From BPEL in SOA Suite version and up, there is a purge_instances_oracle.sql script in the SOA_HOME/bpel/system/database/scripts directory.
  • For BPEL in SOA Suite version you need patch 7162289. After applying this patch purge_instances_oracle.sql will be located at SOA_HOME/bpel/system/database/scripts.
  • In case you’re still running BPEL check Oracle support document 398578.1 and document 341102.1

Oracle ESB

  • For Mediator in FMW 11g refer to the scripts and links mentioned in the BPEL paragraph.
  • To purge ESB instances in you need to apply at least MLR#4 to have purge scripts available. There are 3 scripts available:
  • $ORACLE_HOME/integration/esb/sql/other/purge_by_date.sql

Update 2011-06 SOA Suite 11gR1 PS3

Recently the SOA Suite Engineering Team published a presentation on Purging strategies in Oracle SOA Suite 11gR1 PS3. Besides the information presented in this post, the presentation goes into targeted deletion from the EM and looks into looped and parallel purges. It also mentions the use of database partitioning. That can be very useful when you have long running instances and a large installation (the presentation gives more than 500GB total OR more than 10GB daily depending on environment specifics).

Update 2011-07 Purging the Extreme Edition

Mark Kelderman posted a blog on Purging SOA Suite 11g Extreme Edition. He give a SQL script that deletes data from Mediator, BPEL, BPM, Workflow and Composite, and after that reclaims disk space.

Update Purge Oracle Service Bus

Marc Kelderman posted on his blog on Purging OSB Report Data. Besides the default way to delete the data using the console. He also gives a SQL script to purge directly in the database.

SOA 11g database growth strategies

In January of 2012 the Oracle Fusion Middleware whitepaper on database growth strategies was published. The advice presented should facilitate better dialog between SOA and Database administrators when planning database and host requirements. An important aspect of the whitepaper is purging of the SOA database.

Related documentation:

Updated purging strategies on Oracle Support

purging in OFM 11gAs part of the Oracle Fusion Middleware (FMW) SOA 11g Infrastructure Database: Installation, Maintenance and Administration Guide [ID 1384379.1] A purging guide has been published to offer strategies and procedures for managing database growth in a production SOA 11g implementation.
Purging is not supported for MS SQL Server out of the box or through a patch in any version of 11g. The provided scripts can, however, be used as a solid base from which to create usable purge scripts for SQL Server.

Bridging the BPMN – BPEL gap

First a short note on both BPMN and BPEL. BPMN is a modeling notation for business processes. OMG on it’s BPMN pages puts it:

The primary goal of BPMN is to provide a notation that is readily understandable by all business users, from the business analysts that create the initial drafts of the processes, to the technical developers responsible for implementing the technology that will perform those processes, and finally, to the business people who will manage and monitor those processes.

BPEL is an execution language. There is for example no standard graphical notation in the BPEL standard. The main focus is not on readability, it is on execution.
So BPMN and BPEL aim for different goals. It should come as no surprise that there is gap. There are several resources that describe the mapping, provide translation, or transforming BPMN to BPEL.


Here are some guidelines that help you bridge the BPMN to BPEL gap:

  • Be as specific as possible in BPMN diagrams (In Oracle Business Process Analysis Suite you can use e.g. automated activity, notification , and human tasks.).
  • Add additional information in BPMN activities. Sure this doesn’t enable automatic transformation, but it does reduce the need for additional design documentation. This will improve documentation consistency, and reduce the required governance.
  • Use templates in your BPMN activities. Structure and check completeness of your descriptions.
  • Use a Service repository. This will enable both designers and developer to communicate about “the same thing”
  • Use a GUI components reposity, for the same reasons as you use a Service repository