Sunday, February 8, 2015

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

How to retrieve the audit trail from 11g dehydration store

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.

 where CMPST_ID = [INSTANCE_ID] order by CREATION_DATE desc;

select * from AUDIT_TRAIL AT1

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

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