Bulk Undeployment in Oracle Soa Suite

This script is for Soa Suite 11g but you can easly modify it to 12c. I think there are a few differences in namespaces. You should have a backup table and a function to access deployed-composites.xml. After running plsql block you should create a zip with the file deployed-composites/deployed-composite.xml and upload it to your mds. Restart is needed to afterwards. Before restart you should avoid doing any change to composites from console which can modify deployed-composites.xml so it's better to restart afterwards. Be sure to upload a valid xml file, otherwise your soa suite will give error while starting. We have close to 1000 composites and a large dehydration store. Undeploying in normal way takes too long even for one composite and doing undeployment manuelly gives us flexibility to redeploy the composites easily if needed (with same deployment id's etc). You have to create 3 useful views in order to run script or you can work on it and combine into a single view as well. 

create table TMP_DEPLOYEDXML
(
  id               NUMBER,
  deployed_xml_org XMLTYPE,
  deployed_xml_mod XMLTYPE,
  created_time     DATE default sysdate,
  updated_time     DATE
)

create or replace function get_composite_data(fullpath in varchar2)
return clob is
vchunk varchar (32000);
vexport number(1);
pi number;
pd number;
pc number;
pv number;
pt number;
retClob clob;
begin
select PATH_PARTITION_ID,PATH_DOCID,PATH_CONTENTID,PATH_VERSION,PATH_CONTENT_TYPE pt into pi,pd,pc,pv,pt from your_mds.mds_paths  p 
where
p.path_fullname=fullpath
and path_high_cn is null;
if(pt=0) then
your_mds.mds_internal_shredded.exportDocumentById(vchunk,vexport,pi,pd,pc,pv,1);
retClob:=retClob||vchunk;
while (vexport=0)
  loop
   your_mds.mds_internal_shredded.exportDocumentById(vchunk,vexport,1,null,pc,null,null);
   retClob:=retClob||vchunk;
   end loop;
 return retClob;
 
 else 
   SELECT to_clob(SD_CONTENTS) into retClob FROM your_mds.MDS_STREAMED_DOCS WHERE SD_CONTENTID=pc AND SD_PARTITION_ID=pt;   
    return retClob;
 end if;
end;

create or replace view composite_list_all as

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[1]/@deployedTime') deployedtime

 from

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

);

create or replace view composite_list_default as

select

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

 from

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

create or replace view composite_list as

select a.compositexml,a.dn,a.state,a.cmode,a.deployedtime,substr(a.dn,0,instr(a.dn,'/')-1) PARTITION_NAME,substr(a.dn,instr(a.dn,'/')+1,instr(a.dn,'!')-instr(a.dn,'/')-1) COMPOSITE_NAME,

substr(a.dn,instr(a.dn,'!')+1,length(a.dn)-instr(a.dn,'!')) COMPOSITE_REVISION,

decode(b.dn,null,'NO','YES') is_default from composite_list_all a,composite_list_default b

where

a.dn=b.dn(+);

declare
deployed xmltype;
n number;
vloop number:=0;
vmod number:=0;
cnt number;
tmpcnt number;
vscnt number:=0;
begin
select xmltype(get_composite_data('/deployed-composites/deployed-composites.xml')) into deployed from dual;
select nvl(max(id),0)+1 into n from tmp_Deployedxml;
insert into tmp_deployedxml(id,deployed_xml_org) values (n,deployed);
for x in (select * from composite_list where is_default!='YES')
  loop
    vloop:=vloop+1;
    dbms_application_info.set_client_info('deployedxml: '||vloop||'/'||vmod);
    cnt:=0;

    for comp in (select   
                extractvalue(a.column_value,'/component/@name','xmlns="http://xmlns.oracle.com/sca/1.0"') component_name
                from table(xmlsequence(extract(xmltype(get_composite_data(x.compositexml)),
                                                 '/composite/component',
                                                 'xmlns="http://xmlns.oracle.com/sca/1.0"')))a 
                                                 )
    loop
      select  count(1) into tmpcnt from your_soainfra.cube_instance where domain_name=x.partition_name and composite_name=x.composite_name
      and component_name=comp.component_name and composite_revision=x.composite_revision and state=1;
      cnt:=cnt+tmpcnt;
    end loop;
    if(cnt=0) then 

/* be sure that you dont have any waiting instance - you can comment this part and set vscnt to 0 if you don't update composite_revision column of running instances manually */
      select count(1)
        into vscnt
        from your_soainfra.dlv_subscription s
       where s.composite_name = x.composite_name
         and s.composite_revision = x.composite_revision
         and s.domain_name = x.partition_name and s.state=0;
if(vscnt=0) then
vmod:=vmod+1;
dbms_output.put_line(x.dn);
select deletexml(deployed,
'/deployed-composites/composite-series/composite-revision[@dn="'||x.dn||'"]') into deployed
from dual;
else
dbms_output.put_line('cant undeploy '||x.dn);
end if;
    end if;
  end loop;
update tmp_deployedxml set deployed_xml_mod=deployed,updated_time=sysdate where id=n;
dbms_output.put_line('select x.deployed_xml_mod.getclobval() from tmp_deployedxml x where x.id='||n);
commit;
dbms_output.put_line(vmod||' modified');
end;

 

Do you want to delete mds files easily?

Hi all,

You can delete file (document) or folder (package) by calling mds_internal_common package procedures;

declare
p1 number;
p2 number;
p3 number;
p4 number;
p5 varchar2(200):='/apps/dvm/NYS';  -- thing you want to delete
pathtype varchar2(20);
o1 number;
begin 
select p.path_partition_id,p.path_docid,p.path_low_cn,p.path_version,p.path_name,p.path_type into p1,p2,p3,p4,p5,pathtype from mds_paths p where p.path_fullname=p5 and p.path_high_cn is null;

if(pathtype='DOCUMENT')
then
mds_internal_common.deleteDocument(partitionID =>p1, docID =>p2 , lowCN => p3, version =>p4 , docName => p5, force => 1);
end if;

if(pathtype='PACKAGE')
then
mds_internal_common.deletePackage(result => o1, partitionID => p1, pathID => p2 );
dbms_output.put_line(o1);
end if;

end;

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.

Performance Issues In Soa Suite 11g (ps5+)

Do you release that after PS5 in case of working threads are full and scheduled threads are increasing then there will be locks on java threads. This will lead to a decrease in performance. To avoid that; be sure to put some limit on your work sources (such as putting minimumDelayBetweenMessages on dequeue mediators). So we can say that it is crucial to monitor your "invoke" and "worker" threads all time but it's not flexible to monitor it from EM console. Using the code parts below you can write your small java code to monitor all threads in all your servers.

1) Connect to admin server

JMXConnector m_connector;

MBeanServerConnection m_connection;

JMXServiceURL serviceURL = new JMXServiceURL("t3","192.168.1.1",7000,"/jndi/weblogic.management.mbeanservers.domainruntime");

System.out.println("Connecting to: " + serviceURL);

Hashtable h = new Hashtable();

h.put("java.naming.security.principal", "wladmin");

h.put("java.naming.security.credentials", "Welcome1");

h.put("jmx.remote.protocol.provider.pkgs", "weblogic.management.remote");

m_connector = JMXConnectorFactory.newJMXConnector(serviceURL, h);

m_connector.connect();

m_connection = m_connector.getMBeanServerConnection();

2) Read Dispatcher Trace

String result=(String)m_connection.invoke("oracle.as.soainfra.bpm:Location=SERVER_NAME,name=CubeDispatcher,type=bpel", "readXMLDispatcherTrace",null,null);

3) Query the results

DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();

DocumentBuilder builder = factory.newDocumentBuilder();

Document doc ;

XPathFactory xPathfactory = XPathFactory.newInstance();

XPath xpath = xPathfactory.newXPath();

XPathExpression expr;

String invokeScheduled="//dispatcher-trace/invokeSet/invokeQueue/scheduled/@count";

String invokeWorking="//dispatcher-trace/invokeSet/invokeQueue/working/@count";

String engineScheduled="//dispatcher-trace/engineSet/instanceQueue/scheduled/@count";   

String engineWorking="//dispatcher-trace/engineSet/instanceQueue/working/@count";   

int is=0;   

int iw=0;   

int es=0;

int ew=0;   

inStream.setCharacterStream(new StringReader(result));  

is=Integer.parseInt(xpath.evaluate(invokeScheduled,inStream)); 

System.out.println("Invoke Scheduled: "+is);   

  

inStream.setCharacterStream(new StringReader(result));  

iw=Integer.parseInt(xpath.evaluate(invokeWorking,inStream));   

System.out.println("Invoke Working: "+iw); 

  

inStream.setCharacterStream(new StringReader(result));  

es=Integer.parseInt(xpath.evaluate(engineScheduled,inStream)); 

System.out.println("Engine Scheduled: "+es);   

  

inStream.setCharacterStream(new StringReader(result));  

ew=Integer.parseInt(xpath.evaluate(engineWorking,inStream));   

System.out.println("Engine Working: "+ew);  

4) Make a loop mechanism in order to query all your servers.