Accessing the SOA 12c audit trail

Nicolas Fonnegra
Nicolas Fonnegra

One of the most important and popular features of the Oracle SOA Suite 12c is the audit trail. It allows the tracing of executed and running process instances using a graphical user interface. This monitoring functionality helps system administrators to trace every execution step of a given process and see how the data was transferred and transformed among the different components. The SOA Suite stores all this information in its dehydration database and in this blog I will explain how to access it using Java. But before proceeding with the explanation it is very important to state the following:

Oracle provides access to the dehydration store through the Java API (although the public API doesn’t show it, the interface Locator can be used in combination with  FlowInstanceFilter class to filter audit trail instances). Direct access to the database is not recommended, mainly because it contains sensible information and changing it can cause irreparable damage to the installation and to the stored data. Also, new versions of the SOA Suite (or in some cases even patches) alter the database schemas without providing any sort of backward compatibility.

Taking 5 steps

Having this in mind, you should be wondering now why this alternative should even be considered as an option. The reason is very simple. Many SOA solutions are designed and implemented to execute several thousand-process instances every minute with response times that are expected to be under a second. In order to achieve this, the SOA hosts have to dedicate their whole resources to the execution of the process leaving practical none left for other applications like the enterprise manager. This leaves the system administrator in a very difficult situation in case he has to trace some process instance but the enterprise manager cannot be accessed because it has become unresponsive. The Java API doesn’t provide much help either because it is also executed against the SOA host. The only available option is to go directly to the database and query the data there (desperate times require desperate measures!!).

As mentioned above, the SOA Suite’s database changes sometimes between releases and one significant change in 12c came in the way the audit is being saved. If you are using 11g, you should take a look at Gilberto Holms article and his very good audit trail exporter tool. Unfortunately his solution doesn’t work in 12c because the Audit Trail is now being serialized using POF (Portable Object Framework).

Even though Holms’ version does not work in 12c, we are going to use an almost identical process to access the database. It only differs in the deserialization of the POF objects. The strategy to access the audit trail can be summarised in the following five steps:

  • Gather and reference the required libraries
  • Query the database.
  • Decompress the binaries
  • Deserialize the Java Objects
  • Process the audit events.

Gather and reference the required libraries

Four extra JAR files and two predefined JDeveloper libraries are needed for this procedure. The first JDeveloper library, Oracle JDBC, provides the required functionality to access the database while the Coherence Runtime is being used to deserialize the POF objects. The other Jar files (Tracking-core.jar, Tracking-fabric.jar, Tracking-api.jar, orabpel.jar and Mediator.jar) can be found inside the SOA installation and are used mainly to deserialize the audit trail into Java objects.

Query the database

The connection to the database is quite straightforward. The needed table, Audit_trail, is located inside the SOAINFRA schema. The audit trail is saved inside the LOG column:

Once the query is executed, the resultset has to cast the LOG field into a Java byte:

Decompress the binaries

The binaries have to be decompressed before they can be deserialized. The class GZipInputStream can be used to achieve this:

Deserialize the Java Objects

Once the binaries are decompressed and processed in a ByteArrayOutputStream, they can be deserialized using the main POF configuration file. Thus file can be found inside the Tracking.fabric.jar under oracle/soa/tracking/fabric/audit/serializer/soa-audit-pof-config.xml :

Once we deserialized the POF objects, we can process the list of events and access the data using the Java API:


As mentioned above, Oracle provides two powerful methods to view the audit trail: the enterprise manager and the Java API. Nevertheless there are certain events in which both of these methods cannot be used, especially if the SOA host has become unresponsive due to a high load being applied to it. In those cases direct access to the dehydration database is an option to consider in order to trace mission critical processes. The solution explained above uses Java to access the database, decompress the binaries, deserialize the objects and process the different events. It is important to consider that this solution was tested against a SOA Suite It may not work on further (or previous) versions because those versions may store the audit trail in different ways.