For a while I thought that was impossible to query the SOA composite instance audit trail directly from database. Several references on internet say that only SOA Management APIs can understand the format in which this information is stored into SOA Dehydration Store. Based on this, I posted about a java tool which does the audit export based on these APIs: https://gibaholms.wordpress.com/2013/07/18/soa-11g-audit-trail-exporter-tool/
The “SOA 11g Audit Trail Exporter Tool” still very useful due to simple features like the easy-of-use, remote export, no need database access to export (only a valid EM user) and file output. However, I finally found a way to query the audit trail XML directly from Database. The audit trail extraction is very fast and simple, and can be done by anyone with access to the SOAINFRA tables.
Edit 03-12-2015: New package version 1.1 available, with a bug fix to work properly on SOA 10g (thanks FRIEDRICH OBER-HONGSERMEIER for the contribution).
To perform this extraction directly from database, I wrote a simple PL-SQL function that does the Audit Trail tricks. I encapsulated it into a package called SOA_UTIL which can be compiled into a controlled area with some governance:
CREATE OR REPLACE PACKAGE SOA_UTIL AS
-------------------------------------------------------------------------------------------
-- Written by : Gilberto Holms (https://gibaholms.wordpress.com/)
-- Last update : 07/11/2014
-- Version : 1.1
-- Description : Utility code to handle Oracle SOA Suite tables
-- Release notes:
-- 1.0: Initial release
-- 1.1: Bug fix to work properly on SOA 10g
-------------------------------------------------------------------------------------------
FUNCTION GET_AUDIT_TRAIL(P_SCHEMA_NAME VARCHAR2, P_CIKEY NUMBER) RETURN CLOB;
END SOA_UTIL;
/
CREATE OR REPLACE PACKAGE BODY SOA_UTIL AS
-------------------------------------------------------------------------------------------
-- Written by : Gilberto Holms (https://gibaholms.wordpress.com/)
-- Last update : 07/11/2014
-- Version : 1.1
-- Description : Utility code to handle Oracle SOA Suite tables
-- Release notes:
-- 1.0: Initial release
-- 1.1: Bug fix to work properly on SOA 10g
-------------------------------------------------------------------------------------------
FUNCTION GET_AUDIT_TRAIL(P_SCHEMA_NAME VARCHAR2, P_CIKEY NUMBER) RETURN CLOB
AS
V_AUDIT_BLOB BLOB;
V_AUDIT_CLOB CLOB;
V_CUR_AUDIT SYS_REFCURSOR;
TYPE TP_AUDIT_RECORD IS RECORD(
BLOCK NUMBER(38,0),
LOG BLOB
);
TYPE TP_AUDIT_ARRAY IS TABLE OF TP_AUDIT_RECORD;
V_AUDIT_ARRAY TP_AUDIT_ARRAY;
V_AUDIT_COMPLETE BLOB;
V_BUFFER_LENGTH PLS_INTEGER := 32767;
V_BUFFER VARCHAR2(32767);
V_READ_START PLS_INTEGER := 1;
BEGIN
DBMS_LOB.CREATETEMPORARY(V_AUDIT_BLOB, TRUE);
DBMS_LOB.CREATETEMPORARY(V_AUDIT_CLOB, TRUE);
DBMS_LOB.CREATETEMPORARY(V_AUDIT_COMPLETE, TRUE);
OPEN V_CUR_AUDIT FOR 'SELECT BLOCK, LOG FROM ' || P_SCHEMA_NAME || '.AUDIT_TRAIL WHERE CIKEY = :cikey ORDER BY COUNT_ID' USING P_CIKEY;
FETCH V_CUR_AUDIT BULK COLLECT INTO V_AUDIT_ARRAY;
CLOSE V_CUR_AUDIT;
FOR j IN 1..V_AUDIT_ARRAY.COUNT LOOP
IF j = 1 THEN
DBMS_LOB.APPEND (V_AUDIT_BLOB, V_AUDIT_ARRAY(j).LOG);
ELSE
IF ( V_AUDIT_ARRAY(j).BLOCK = V_AUDIT_ARRAY(j-1).BLOCK ) THEN
DBMS_LOB.APPEND (V_AUDIT_BLOB, V_AUDIT_ARRAY(j).LOG);
ELSE
DBMS_LOB.APPEND (V_AUDIT_COMPLETE, UTL_COMPRESS.LZ_UNCOMPRESS(V_AUDIT_BLOB));
DBMS_LOB.CREATETEMPORARY(V_AUDIT_BLOB, TRUE);
DBMS_LOB.APPEND (V_AUDIT_BLOB, V_AUDIT_ARRAY(j).LOG);
END IF;
END IF;
END LOOP;
DBMS_LOB.APPEND (V_AUDIT_COMPLETE, UTL_COMPRESS.LZ_UNCOMPRESS(V_AUDIT_BLOB));
V_AUDIT_ARRAY.DELETE;
FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(V_AUDIT_COMPLETE) / V_BUFFER_LENGTH) LOOP
V_BUFFER := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(V_AUDIT_COMPLETE, V_BUFFER_LENGTH, V_READ_START));
DBMS_LOB.WRITEAPPEND(V_AUDIT_CLOB, LENGTH(V_BUFFER), V_BUFFER);
V_READ_START := V_READ_START + V_BUFFER_LENGTH;
END LOOP;
RETURN V_AUDIT_CLOB;
END;
END SOA_UTIL;
/
Then you can use this function inside a main query which filters the desired instances. This function works for both SOA 10g and 11g.
Usage example for Oracle SOA Suite 11g:
SELECT
CI.COMPOSITE_NAME AS COMPOSITE_NAME,
CI.CMPST_ID AS INSTANCE_ID,
CI.CREATION_DATE AS CREATION_DATE,
CI.STATUS AS STEP,
SOA_UTIL.GET_AUDIT_TRAIL('FMW11116_SOAINFRA', CI.CIKEY) AS AUDIT_TRAIL_XML
FROM
SOAINFRA.CUBE_INSTANCE CI
WHERE
CI.COMPOSITE_NAME = 'MyComposite'
AND CI.CREATION_DATE BETWEEN (sysdate - 1) AND (sysdate)
ORDER BY CI.CREATION_DATE DESC;
Usage example for Oracle SOA Suite 10g:
SELECT
CI.PROCESS_ID AS PROCESS_ID,
CI.CIKEY AS INSTANCE_ID,
CI.CREATION_DATE AS CREATION_DATE,
CI.STATUS AS STEP,
SOA_UTIL.GET_AUDIT_TRAIL('ORABPEL', CI.CIKEY) AS AUDIT_TRAIL_XML
FROM
ORABPEL.CUBE_INSTANCE CI
WHERE
CI.PROCESS_ID = 'MyBPEL'
AND CI.CREATION_DATE BETWEEN (sysdate - 1) AND (sysdate)
ORDER BY CI.CREATION_DATE DESC;
Event Details
Edit 03-12-2015: Added Event Details topic.
The audit trail is a large XML containing an array of events. The event XML usually contains all the message and details, like show below:
<event ...> <message>...</message> <details><![CDATA[ ... ]]></details> </event>
However, for some very large XML payload, these event details are stored outside this structure, causing the event XML looks like this:
<event ...> <message>...</message> <details id="0" /> </event>
As you can see above, there is no payload inside the details tag, but there is a detail “id”.
So, if you are parsing the audit trail and you are interested on the details payload, every time you face this situation for some specific instance, you must make a separate call to a table named “AUDIT_DETAILS”, like shown below:
SELECT UTL_COMPRESS.LZ_UNCOMPRESS(BIN) AS AUDIT_DETAILS_XML FROM SOAINFRA.AUDIT_DETAILS WHERE CIKEY = '162524' -- put the cikey here AND DETAIL_ID = 0; -- put the details id here
I used this technique in the company I work for, and by using the concepts shown in this article, I was able to do a full extract and parsing from SOA 10g and 11g databases getting all the information I needed to my use case. I hope you find it useful too.
Gilberto Holms 