Sunday, February 8, 2015

Retrieve audit trail from Dehydration Store 11g Oracle SOA Suite composite

Purpose:
How to retrieve the audit trail from 11g dehydration store


Environment:
Oracle 11g SOA suite
Windows environment
Access to the dehydration store DB

How to
I will try to retrieve the audit trail without using the java API. As per documentation the AUDIT_TRAIL table contains the audit trail for the message.

I have assumed we already have the instance id for the message. Execute the below query to retrieve the details.

select CIKEY from CUBE_INSTANCE 
 where CMPST_ID = [INSTANCE_ID] order by CREATION_DATE desc;

select * from AUDIT_TRAIL AT1
    
WHERE AT1.CIKEY = [CIKEY]
    ORDER 
BY AT1.COUNT_ID;



Save all the LOG columns into a file in order. Say instance_id.0, instance_id.1, instance_id.2 etc. The number of columns depend upon the size of the audit trail.

Open command prompt in windows and execute the below command. Edit the command to add all the LOG columns in order as saved in previous step.


copy /b instance_id.0 + instance_id.1 + instance_id.2 instance_id.zip



The output file instance_id.zip can be unzipped to retrieve the text file that contains the audit trail.