Accessing raw Audit Trail xml directly from database

Hi Everyone, 

If you need to access the audit trail directly from database then what you have to do is simple. First query the audit_trail table. You'll see several lines for a single cikey. Convert chunks to binary and concatenate all rows for a cikey but do this for each block separately. Finally uncompress it and what you'll get is a raw audit trail xml. You can convert it to table or just search a specific keyword or even you can code your own display engine if you don't like the oracle's em console. 

For me this function is important because for an archived instance I don't have to move the instance to production in order to see the audit trail. 


Function works for both 10g and 11g. I didn't have the chance to test 12c but if audit_trail is still there then I think it will work for 12c as well..

You will need blob_to_clob function. you can get it from here; http://www.dba-oracle.com/t_convert_blob_to_clob_script.htm



create or replace function get_audit_xml(vcikey in number) return clob is
  tmpblob blob;
  vblob blob;
  audit_trail clob:='<audit_trail>';
begin
for b in (select distinct block from audit_trail where block=1 and cikey=vcikey order by 1) 
  loop
  tmpblob:=to_blob('0');
  vblob:=to_blob('0'); 
  for x in (select row_number() over (order by count_id) row_num ,to_blob(log) chunk from audit_trail where cikey=vcikey  and block=b.block)
   loop 
      if(x.row_num=1) then
        tmpblob:= x.chunk;
      else
        dbms_lob.append(dest_lob => tmpblob,src_lob => x.chunk);
      end if;
   end loop;
 utl_compress.lz_uncompress(src => tmpblob, dst => vblob);
audit_trail:=audit_trail||blob_to_clob(vblob);
end loop;
return audit_trail||'</audit_trail>';
end get_audit_xml; 

You can use this function in your posts or website but please give credit to my blog when doing this.

getting direct url to instances' audit trail page (SOA11G)

Is it very slow to access an instance audit trail from EM console. So use this function to get direct url to your audit trail. Don't forget to change the admin server,port, farm and managed server name..

create or replace function get_url(v_cikey in number) return varchar2 is
  Result varchar2(1000); 
begin
  select 
   'http://servername:serverport/em/faces/ai/soa/bpelInstance?refresh=yes&&target=/Farm_SOA-PROD-DMN/SOA-PROD-DMN/MANAGEDSERVERNAME/'||domain_name||'/'||composite_name||'%20['||composite_revision||']&&type=oracle_soa_composite&&soaContext='||domain_name||'/'||composite_name||'!'||composite_revision||'/'||component_name||'/bpel:'||cikey url into Result 
   from cube_instance where cikey=v_cikey;
  return(Result);
end get_url;

the function above was for 11.1.1.6 and I use this for 11.1.1.7

   'http://servername:serverport//em/faces/ai/soa/bpelInstance?refresh=yes&&target=/Farm_SOA-PROD-DMN/SOA-PROD-DMN/MANAGEDSERVERNAME/'||c.domain_name||'/'||c.component_name||'%20['||c.composite_revision||']&type=oracle_soa_composite&soaContext='||c.domain_name||'/'||c.component_name||'!'||c.composite_revision||'/'||c.component_name||'/bpel:'||c.cikey||'&currentTab=audit' url into Result

not working for you? So install wireshark and sniff the packets when you click a link to audit trail at EM console. Get the url and change the variable parts ;)

Using get_mds_data in order to get composite list (SOA11G)

In the previous post I give the details of get_mds_data function to you. Now let's use it in an useful way;

 

select "COMPOSITEXML","DN","STATE","CMODE","DEPLOYEDTIME" from  (

select

'/deployed-composites/'||replace(extractvalue(a.column_value,'/composite-revision/@dn'),'!','_rev')||'/composite.xml' compositexml,

extractvalue(a.column_value,'/composite-revision/@dn') dn,

extractvalue(a.column_value,'/composite-revision/@state') state,

extractvalue(a.column_value,'/composite-revision/@mode') cmode,

extractvalue(a.column_value,'/composite-revision/composite/@deployedTime') deployedtime

 from

table(xmlsequence(extract(xmltype(get_mds_data('/deployed-composites/deployed-composites.xml')),'/deployed-composites/composite-series/composite-revision'))) a

);

you can extract other elements or attributes of deployed-composite.xml depending on your needs.

ps: for my deployed-composite.xml I had a interesting case where a composite had two deployedTime so I had to add [1] to last column ; extractvalue(a.column_value,'/composite-revision/composite[1]/@deployedTime')

Accessing to MDS files from PL/SQL code (SOA 11G)

Hi All,

Accessing to MDS files sometimes can be very time consuming. 

First option; you can use IDE but let's be honest It's not very user friendly and not very fast when we talk about accessing MDS files.

Second option; we can also append mds full path to any composite's wsdl address. This is easier for me because I'm an administrator and not running the JDEV all the time :)

for example: If your composite's wsdl is @ http://www.yourserver.com/soa-infra/services/BlaBlaComposite/client?wsdl 

then you can change it to 

http://www.yourserver.com/soa-infra/services/BlaBlaComposite/apps/WSDL/3rdParty/abc.xsd

and put the address to any browser in order to access abc.xsd.


But still for me it's not easy. So I investigated a little and found that engine uses exportDocumentById method while accessing the files. But I had to debug a little more because there is a 32000 byte limit in a single call. So I have to make a loop and also find correct input for the exportDocumentById  function in order to get consecutive chunks. 

With this function you can get your wsdls, xsds or your bpel, mediator code from MDS.


create or replace function get_mds_data(fullpath in varchar2)
return clob is
  vchunk varchar (32000);
  vexport number(1);
  pd number;
  pc number;
  pv number;
  retClob clob;
begin
select path_docid,path_contentid,path_version  into pd,pc,pv 
       from mds_paths p 
        where
       p.path_fullname=fullpath and path_high_cn is null;    
 mds_internal_shredded.exportDocumentById(vchunk,vexport,1,pd,pc,pv,1);
 retClob:=retClob||vchunk;
 while (vexport=0)
  loop
    mds_internal_shredded.exportDocumentById(vchunk,vexport,1,null,pc,null,null);
    retClob:=retClob||vchunk;
   end loop;
 return retClob;
end;

So this is the easiest and fastest way for me to access MDS files;

select get_mds_data('/apps/WSDL/3rdParty/abc.xsd') from dual

ps: compile this function at your mds schema or if you want to compile it at another schema then add schema names to tables and functions and also give grants . 


You can use this function in your posts or website but please give credit to my blog when doing this.