Enterprise who usually run an Oracle SOA Suite BPEL environment to process workflows manage production and multiple test environments where they may need to mass abort instances. Especially to reset the Oracle SOA BPEL environment before running a non-regression or performance test.

Below is the script I usually set up for my clients to close running instances before running new tests or to fully reset the environment.

Implementing a working Purge script to get rid of old Oracle SOA BPEL instances is one of the most important tasks to maintain a working Oracle SOA Suite environment in production. Forgetting to do so will have the whole environment slow down as Oracle SOA Bpel database get bigger.

Depending on your business requirement, I usually advise purging instances data older than 2 months. Instances data are useful to troubleshoot issues with BPEL processes and shouldn’t be kept once those are successfully completed.

You may also need to update failed instances status to force them in a purgeable state. Especially for unrecoverable instances.(See at the bottom of the gist)

SQL queries to troubleshoot and monitor your Oracle SOA Suite BPEL purge scripts:

Oracle SOA BPEL infrastructures are notoriously hard to scale. Once your production Oracle SOA BPEL Database grows, you’ll often find that your whole Oracle SOA BPEL infrastructure performance may hinder. To fix it, you then need to focus on :

• Implementing the right database purge strategy
• Monitor large instances to improve your composite performances (see https://cedricleruth.com/useful-monitoring-sql-queries-for-oracle-soa-bpel/)
• Implement the proper Oracle Database partitioning

How to partition for performance your Oracle SOA BPEL Database?

Below is an example of partitioning scripts written after helping two of my clients running an Oracle SOA BPEL 11g Infrastructure. This script is provided “as is” without warranty of any kind. Do not use this directly in your production environment. Please read it, test it, and update it to fit your specific instances.

If you run Oracle SOA BPEL on-premise and at scale, you know how easier it can be to monitor your SOA BPEL processes through SQL queries. Those SQL queries get more profitable the more SOA BPEL instances you get, as the Enterprise Manager console usually doesn’t work for big payloads. You’ll find below a couple of SQL queries to :

• Identify BPEL Errors from the last 10 minutes 
• Identify Business faults from the previous 10 minutes
• Identify instance not purged after the retention period (here 70 days)
• Get the number of process and their states for each composite name and revision (useful to identify composites due to be undeployed)
• Multiple SQL queries to monitor the database size
• Get the list of composite, revision, and date of their last instances

Useful SQL queries for monitoring Oracle SOA BPEL:

Implementing a global common fault policy is essential when building a SOA BPEL Composites architecture. As your number of production BPEL instances grows, the risk of losing critical data due to a BPEL process crashing in production increases. Especially with large architecture where unexpected usage surge can trigger slowness and failures. Implementing a simple retry fault policy allows the faulted instances to continue once the environment returns to normal.

Implementing a global common fault policy is essential when building a SOA BPEL Composites architecture.

Below is an example of a simple retry global fault policy that will retry up to 7 days for any BPEL instances in error due to a technical issue. To do so, update those three files as follow :