Export Audit Trail from Database SOA 10g and 11g

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.

Advertisements

4 thoughts on “Export Audit Trail from Database SOA 10g and 11g

  1. Friedrich Ober-Hongsermeier 06/03/2014 / 11:10 AM

    Hello Gilbert,

    the GET_AUDIT_TRAIL function worked for me after doing the following changes:

    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;

    Thanks a lot and best regards
    Friedrich

    • gibaholms 03/12/2015 / 3:08 PM

      Thanks a lot Friedrich, including your remarks the code is able to work properly on SOA 10g. I already updated the post and included your fix on the procedure version 1.1.
      Regards.

  2. Regis Suzano 09/01/2016 / 12:00 PM

    Hi Giberto,
    I made some changes on your code to be able to get the BPMN audit trail too.
    Basically it’s a new function based on yours that I added on the SOA_UTIL package to get data fom BPM_AUDIT_QUERY table

    If you’re interested..

    FUNCTION BPM_GET_AUDIT_TRAIL(P_SCHEMA_NAME VARCHAR2, P_QUERY_ID NUMBER) RETURN CLOB
    AS
    V_AUDIT_BLOB BLOB;
    V_AUDIT_CLOB CLOB;
    V_CUR_AUDIT SYS_REFCURSOR;

    TYPE TP_AUDIT_RECORD IS RECORD(
    COMPOSITE_INSTANCE_ID VARCHAR2(255),
    AUDIT_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 COMPOSITE_INSTANCE_ID,AUDIT_LOG FROM ‘|| P_SCHEMA_NAME ||’.BPM_AUDIT_QUERY where QUERY_ID= :query_id AND AUDIT_LOG IS NOT NULL’ USING P_QUERY_ID;
    FETCH V_CUR_AUDIT BULK COLLECT INTO V_AUDIT_ARRAY;
    CLOSE V_CUR_AUDIT;

    FOR j IN 1..V_AUDIT_ARRAY.COUNT LOOP

    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).AUDIT_LOG);
    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;
    IF DBMS_LOB.GETLENGTH(V_AUDIT_CLOB) > 0 THEN
    RETURN V_AUDIT_CLOB;
    ELSE
    RETURN NULL;
    END IF;

    END;

    And que SQL query to get audit is:

    SELECT * FROM (
    SELECT COMPOSITE_INSTANCE_ID,COMPONENT_NAME,PROCESS_NAME,SOA_UTIL.BPM_GET_AUDIT_TRAIL(‘FMW11116_SOAINFRA’,QUERY_ID) AS AUDIT_LOG
    FROM FMW11116_SOAINFRA.BPM_AUDIT_QUERY WHERE COMPOSITE_INSTANCE_ID=180027 ORDER BY QUERY_ID) WHERE AUDIT_LOG IS NOT NULL;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s