Oracle SOA Suite: Find that composite instance!

When executing BPM or BPEL processes, they are usually executed in the context of a specific entity. Sometimes you want to find instances involved with a specific entity. There are different ways to make this easy. You can for example use composite instance titles or sensors and set them to a unique identifier for your entity. If they have not been used, you can check the audit trail. However, manually checking the audit trail, especially if there are many instances, can be cumbersome. Also if different teams use different standards or standards have evolved over time, there might not be a single way to look for your entity identifier in composite instances. You want to automate this.

It is of course possible to write Java or WLST code and use the API to gather all relevant information. It would however require fetching large amounts of data from the SOAINFRA database to analyse. Fetching all that data into WLST or Java and combining it, would not be fast. I've created a database package / query which performs this feat directly on the 11g SOAINFRA database (and most likely with little alteration on 12c).

How does it work

The checks which are performed in order (the first result found is returned):

• Check the composite instance title
• Check the sensor values
• Check the composite audit trail
• Check the composite audit details
• Check the BPM audit trail
• Check the Mediator audit trail
• Do the above checks except this one for every composite sharing the same ECID.

It first looks for instance titles conforming to a specific syntax (with a regular expression), next it looks for sensor values of sensors with a specific name. After that it starts to look in the audit trail and if even that fails, it looks in the audit details where messages are stored when they become larger than a set value (look for Audit Trail threshold). Next the BPM and Mediator specific audit tables are looked at and as a last resort, it uses the ECID to find other composite instances in the same flow which might provide the required information and it does the same checks as mentioned above on those composite instances. Using this method I could find for almost any composite instance in my environment a corresponding entity identifier. The package/query has been tested on 11g but not on 12c. You should of course check to see if it fits your personal requirements. The code is mostly easy to read save the audit parsing details. For parsing the audit trail and details tables, I've used the following blog. The data is saved in a file which can be imported in Excel and can be scheduled on Linux with a provided sh script.

Getting the script to work for your case

You can download the script here. Several minor changes are required to make the script suitable for  a specific use case.

• In the example script getcomposites_run.sql the identification regular expressing: AA\d\d\.\d+ is used. You should of course replace this with a regular expression reflecting the format of your entity identification. 
• In the example script getcomposites_run.sql sensors which have AAIDENTIFICATION in the name will be looked at. This should be changed to reflect the names used by your sensors.
• The getcomposites.sh contains a connect string: connect soainfra_username/soainfra_password. You should change this to your credentials.
• The getcomposites.sh script can be scheduled. In the example script, it is scheduled to run at 12:30:00. If you do not need it, you can remove the scheduling. It can come in handy when you want to run it outside of office hours because the script most likely will impact performance. 
• The selection in getcomposites_run.sql only looks at running composites. Depending on your usecase, you might want to change this to take all composites into consideration.
• The script has not been updated to 12g. If you happen to create a 12g version of this script (I think not much should have to be changed), please inform me so I can add it to the Github repository.

Considerations

If you have much data in your SOAINFRA tables, the query will be slow. It could take hours. During this period, performance might be adversely affected.

That I had to create a script like this (first try this, then this, then this, etc) indicates that I encountered a situation in which there was not a single way to link composite instances to a specific identifier. If your project uses strict standards and these standards are enforced, a script like this would not be needed. For example, you set your composite instance title to reflect your main entity identifier or use specific sensors. In such a case, you do not need to fall back to parsing audit data.