Export Audit Trail from Database SOA 10g and 11g
Join the DZone community and get the full member experience.
Join For FreeFor a long time I thought that was impossible to query the SOA composite instance audit trail directly from database. Several references on internet said that only SOA Management APIs could understand the format in which this information is stored into SOA Dehydration Store.
Based on this misinformation I wrote a post and build a tool based on these APIs (which will be reviewed soon):
http://gibaholms.wordpress.com/2013/07/18/soa-11g-audit-trail-exporter-tool/
However, I finally found a way to query the audit trail directly from Database. The audit trail extraction is very fast and simple, and can be done by anyone with access to the SOAINFRA tables.
This does not invalidate the usefulness of the SOA 11g Audit Trail Exporter Tool mentioned above due to simple features provided like easy of use, remote export, no need database access to export (only an EM user), file based output, etc.
To show the extraction directly from database, I wrote a simple PL-SQL function that does the Audit Trail trick. I encapsulated in a package called SOA_UTIL that can be compiled into a controlled area:
create or replace PACKAGE SOA_UTIL AS ------------------------------------------------------------------------------------------- -- Written by : Gilberto Holms (http://gibaholms.wordpress.com/) -- Last update : 03/02/2014 -- Version : 1.0 -- Description : Utility code to handle Oracle SOA Suite tables ------------------------------------------------------------------------------------------- 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 (http://gibaholms.wordpress.com/) -- Last update : 03/02/2014 -- Version : 1.0 -- Description : Utility code to handle Oracle SOA Suite tables ------------------------------------------------------------------------------------------- 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_ARRAY IS TABLE OF BLOB; V_AUDIT_ARRAY TP_AUDIT_ARRAY; 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); OPEN V_CUR_AUDIT FOR 'SELECT 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 DBMS_LOB.APPEND (V_AUDIT_BLOB, V_AUDIT_ARRAY(j)); END LOOP; V_AUDIT_ARRAY.DELETE; V_AUDIT_BLOB := UTL_COMPRESS.LZ_UNCOMPRESS(V_AUDIT_BLOB); FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(V_AUDIT_BLOB) / V_BUFFER_LENGTH) LOOP V_BUFFER := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(V_AUDIT_BLOB, 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 simply use this function inside a main query that filter the desired instances. The best is the fact that this function works for both SOA 10g and 11g.
Usage example for SOA Suite 11g:
SELECT CI.COMPOSITE_NAME AS COMPOSITE_NAME, CI.CMPST_ID AS INSTANCE_ID, to_char(CI.CREATION_DATE, 'dd/mm/yyyy hh24:mi:ss') AS CREATION_DATE, CI.STATUS AS STEP, SOA_UTIL.GET_AUDIT_TRAIL('FMW11116_SOAINFRA', CI.CIKEY) AS AUDIT_TRAIL FROM FMW11116_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 SOA Suite 10g:
SELECT CI.PROCESS_ID AS PROCESS_ID, CI.CIKEY AS INSTANCE_ID, to_char(CI.CREATION_DATE, 'dd/mm/yyyy hh24:mi:ss') AS CREATION_DATE, CI.STATUS AS STEP, SOA_UTIL.GET_AUDIT_TRAIL('ORABPEL', CI.CIKEY) AS AUDIT_TRAIL 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;
Enjoy!
Published at DZone with permission of Gilberto Holms, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments