Diagnose SOA composite performance using sql queries

Even though the Enterprise manager console along with the weblogic diagnostic framework provides different ways of monitoring the Oracle SOA suite the below queris can come in handy for analyzing the performance of SOA composite applications and start looking into the composite design issues.

The following query gets the details from CUBE_INSTANCE table for the BPEL instances and provide informations like instances, their state,average, minimum & maximum durations

in seconds and the total count after the previous purge or deployment.

SELECT DOMAIN_NAME PARTITION,COMPONENT_NAME,

DECODE(STATE,'1','RUNNING','5','COMPLETED','6',

'FAULTED','9','STALE') STATE,

TO_CHAR(AVG((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),12,2))*60*60) +

(TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),15,2))*60) +

TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),18,4))),'999990.000') AVG,

TO_CHAR(MIN((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),12,2))*60*60) +

(TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),15,2))*60) +

TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),18,4))),'999990.000') MIN,

TO_CHAR(MAX((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),12,2))*60*60) +

(TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),15,2))*60) +

TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),18,4))),'999990.000') MAX,

COUNT(1) COUNT

FROM <DB_PREFIX>.CUBE_INSTANCE where COMPONENT_NAME like 'XX%'

GROUP BY DOMAIN_NAME, COMPONENT_NAME, STATE

ORDER BY COMPONENT_NAME, STATE

The following query gets the details from MEDIATOR_INSTANCE table for the mediator instances and provide informations like instances, their state,average, minimum & maximum

durations in seconds and the total count.

SELECT SUBSTR(COMPONENT_NAME, 1, INSTR(COMPONENT_NAME,'/')-1)PARTITION,

SUBSTR(COMPONENT_NAME, INSTR(COMPONENT_NAME,'/')+1,

INSTR(COMPONENT_NAME,'!')-INSTR(COMPONENT_NAME,'/')-1) COMPONENT,

SOURCE_ACTION_NAME ACTION,

DECODE(COMPONENT_STATE,'0','COMPLETED','2',

'FAULTED','3','ABORTED','4','RECOVERY

NEEDED','8','RUNNING','16','STALE') STATE,

TO_CHAR(AVG((TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),12,2))*60*60) +

(TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),15,2))*60) +

TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),18,4))),'999990.000') AVG,

TO_CHAR(MIN((TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),12,2))*60*60) +

(TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),15,2))*60) +

TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),18,4))),'999990.000') MIN,

TO_CHAR(MAX((TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),12,2))*60*60) +

(TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),15,2))*60) +

TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),18,4))),'999990.000') MAX,

COUNT(1) COUNT FROM <DB_PREFIX>._SOAINFRA.MEDIATOR_INSTANCE WHERE COMPONENT_NAME like 'XYZ%'

GROUP BY COMPONENT_NAME, SOURCE_ACTION_NAME, COMPONENT_STATE

ORDER BY STATE ,COMPONENT_NAME, SOURCE_ACTION_NAME, COMPONENT_STATE

Below sql statement can be run to query the virtual table v$sql in Oracle to find the recently executed top slowest  queries and needs tuning.

SELECT * FROM (SELECT sql_fulltext, sql_id,child_number,disk_reads,executions,first_load_time,last_load_time FROM v$sql ORDER BY elapsed_time DESC)
WHERE ROWNUM < 15

The above queries are seen working fine upto PS6(11.1.1.7).

DB Adapter fails to return nVarchar Type from stored procedure with MS SQLServer 2005

When you are using DbAdapter to access MS SQLServer 2005 Stored Procedures that return resultsets as dynamic row sets with nVarchar column types, a class type mapping exception is thrown.

You will see a stack trace as follows:

<faultstring>Exception occured when binding was invoked.
Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation '***' failed due to: Unimplemented string conversion.
Conversion of JDBC type to String is not supported.
An attempt was made to convert a Java object to String using an unsupported JDBC type: .
Use a data type with a supported JDBC type.
".
The invoked JCA adapter raised a resource exception.
Please examine the above error message carefully to determine a resolution.</faultstring>

The issue can be resolved by switching the driver from  com.microsoft.sqlserver.jdbc.SQLServerDriver c to weblogic.jdbc.sqlserver.SQLServerDriver

But there is a limitation of maximum size  4000 characters that can be returned by any column of the stored procedure while using this driver.

SSO Logout for Oracle eBusiness Suite integrated with OAM 11g

We had set up single sign on for Oracle EBusiness Suite R12 using OAM 11g.We used WNA to enaele seamless SSO uisng the windows logged in credentials.

But when the users clicked on logout it was again redirecting to the home page and a new session was created in the database.

To overcome the issue we did the below changes.

Search for the OAMLogin.jsp in the deployed war file of the access gate and change the redirect URL as below.

Path:$EBS_DOMAINHOME/servers/AdminServer/tmp/_WL_user/<access_gate>/24wo2p/war

 if ("CookieCleanup".equals(request.getParameter("phase")))  
    {              
         response.sendRedirect(request.getContextPath()+"/logout.html");  
         return;  
    }  

Copy the logout.html to the same directory as OAMLogin.jsp. The logout html file can be found in the exploded war file which redirects to the Logout.jsp

Path for Logout.jsp $OAMDomain/servers/oam_server1/tmp/_WL_user/oam_server/xrd2uw/war/pages

This page can be customized according to the needs.So now if the user clicks on logout they will be redirected to the logout page and the session gets killed in the database.

OSR Installation

Oracle Service Registry is fully V3-compliant implementation of UDDI (Universal Description, Discovery and Integration), and is a key component of a Service Oriented Architecture (SOA). Oracle Service Registry is an easy-to-use, standards- based mechanism for publishing and discovering Web services and related resources like XML Schemas or XSLT transformations.

Below are the steps for installing Oracle Service registry in a stand alone mode which can be used without any other dependent registries.It is a single node installation in the same domain as of the Oracle Enterprise repository.

If there is a database available  a new user can be created for holding the OSR metadata.

  CREATE USER OSR

  IDENTIFIED BY ******

  DEFAULT TABLESPACE USERS

  TEMPORARY TABLESPACE TEMP

  PROFILE DEFAULT

  ACCOUNT UNLOCK;

  

  -- 1 Role for OSR

  GRANT RESOURCE TO OSR;

  ALTER USER OSR DEFAULT ROLE ALL;

  -- 4 System Privileges for OSR

  GRANT UNLIMITED TABLESPACE TO OSR;

  GRANT CREATE VIEW TO OSR;

  GRANT CREATE SESSION TO OSR;

  GRANT CREATE TABLE TO OSR;

Start the installer using the below command.

 java -jar oracle-service-registry-11.1.1.6.jar 

The default installation installs a standalone registry and enables the creation of a new registry database. 

Select the installation directory.

You need to provide the administrator's account name and password, so you can log in later and adjust the Oracle Service Registry configuration using Oracle Service Registry tools.

Select your database creation method on the following panel.

Enter path to JDBC Drivers on the panel 

Start the Configuration Wizard in graphical mode.

In this case we will be selecting the existing domain used by OER.

Undeploy SOA Composite using WLST

Sometimes we might not be able to undeploy or redeploy composites from the EM console.This can happen when the project is not loaded properly during server startup.In such cases the composite can be undeployed using WLST script.

Run the WLST script in the below location:

$ORACLE_HOME/common/bin/

Running the wlst script in the server path can result in NameError when SOA specific commands are run. 

Traceback (innermost last): File “”, 1,=”" ?<=”" in=”" line=”" span=”">”,> NameError: sca_undeployComposite

 wls:/offline>  
 wls:/offline> connect()  
 Please enter your username :weblogic  
 Please enter your password :  
 Please enter your server URL [t3://localhost:7001] :t3://soahost:8001  
 Connecting to t3://soahost:8001 with userid weblogic ...  
 Successfully connected to managed Server 'wls_soa1' that belongs to domain 'soa_domain'.  
 Warning: An insecure protocol was used to connect to the  
 server. To ensure on-the-wire security, the SSL port or  
 Admin port should be used instead.  
 wls:/soa_domain/serverConfig> sca_undeployComposite("http://soahost:8001", "SOAExp", "1.0",user='weblogic', password='*******', partition='test')  
 serverURL = http://soahost:8001  
 user = weblogic  
 partition = test  
 compositeName = SOAExp  
 revision = 1.0  
 timeout= -1  
 set user and password...  
 compositeDN = test/SOAExp!1.0  
 INFO: Creating HTTP connection to host:soahost, port:8001  
 INFO: Received HTTP response from the server, response code=200  

If the SOA server is running with a virtual ip then the corresponding host name or VIP need to  be used while connecting to the server.

Undeploying SOA Composite while soa-infra is down. 

Mainly this task is required when starting a soa server and the soa-infra application won't come up due to a corrupt composite.  The solution also applies in the case where the server itself is failing to come up / stay up due to a bad deployment.


Follow the below document from Oracle support.

MDS-00054: Undeploying a SOA Composite when Soa-Infra is Down (Doc ID 1380835.1)

Oracle SOA Enable or Disable endpoint test of web service

Sometimes it is required to disable testing of web service through the browser. This can be achieved by setting "Endpoint Test Enabled " to False from the Enterprise manager.

1.Login to the Enterprise Manager and open the composite.

2.From the context menu of the project navigate to Service/Reference properties and then the service.This will open the properties tab of the composite.

3.Change the "Endpoint Test Enabled " to False.

4.Click Apply. Now you should be getting 403 forbidden error if the web service URL is opened in the browser.

Getting JCA header property while invoking DB Adapter from Spring Bean

We had a requirement of calling a DB Adapter from the spring bean. Also the DB adapter should connect to different databases that had same table structure and the database to connect will be manipulated at the run time. If we are invoking DB adapter from BPEL we could have achieved this by setting the  header property of invoke activity as below.

When we are calling DB adapter from Spring Bean this need to be set in invoke activity of the bean and Oracle SOA Suite provides  predefined spring beans to get the values in the bean.

headerHelperBean  -- For getting and setting header properties
instanceHelperBean --  For getting the information of running instance like id & DN


The following properties need to be injected through the spring context file.

<property name="headerHelper" ref="headerHelperBean"/><property name="instanceHelper" ref="instanceHelperBean"/>

And the value can be set in the spring bean as follows.

headerHelper.setHeaderProperty("jca.db.XADataSourceName",arr[3]);

Unable to login to OAM 11g Console after LDAP Authentication Module Is Changed To Use a New Identity Store

We were unable  login to the OAM console  after changing the identity store used by the LDAP authenication scheme. In our case it was IdentityStore1  and got changed to ADStore. So we had to revert the values in oam-config.xml to login to the console  again.

Weblogic server maintains multiple back ups of the file in the below location.

DOMAIN_HOME/config/fmwconfig/oam-config.xml

We found there was a backup file existing  before the change happened. So by restoring the file and restarting the servers we were able to login to the console.

If you cannot find the any backup of the file in the folder just edit the oam-config.xml and setting the correct ldap id. For example,

 <Setting Name="ldapid" Type="xsd:string">UserIdentityStore</Setting>

Simple DVM sample in SOA 11g

DVM enables you to map from one vocabulary used in a given domain to another vocabulary used in a different domain.In this post we will go through how to create a light weight lookup service using DVM and mediator.In this example the service will return Nationality based on the country name.

1.Create a new SOA project with mediator.

2.Create a mediator with synchronous interface based on the below schema.

 <?xml version="1.0" encoding="UTF-8"?>  
 <schema attributeFormDefault="unqualified"  
       elementFormDefault="qualified"  
       targetNamespace="http://com.sample/LookUpUtilProcess/v1.0"  
       xmlns="http://www.w3.org/2001/XMLSchema">  
       <element name="process">  
             <complexType>  
                   <sequence>  
                         <element name="country" type="string"/>  
                   </sequence>  
             </complexType>  
       </element>  
       <element name="processResponse">  
             <complexType>  
                   <sequence>  
                         <element name="nationality" type="string"/>  
                   </sequence>  
             </complexType>  
       </element>  
 </schema>  

3.Right click on the project -->New -->Domain Value Map(DVM)

4.Provide a name for the DVM and put the initial entries  as below.

5.More Domains and domain valus can be added by opening the .dvm file in jdeveloper

6.Open the mediator and click on and add "static routing rule "

7.Select "Echo"

8.Click on Assign and use the dvm:lookupValue method to get the value from DVM.

For example, dvm:lookupValue("Nationality.dvm","Country",$in.request/inp1:process/inp1:country,"Nationality","Nationality Not Found")

Usage: dvm:lookupValue(dvmLocation as string, sourceColumnName as string, sourceValue as string, targetColumnName as string, defaultValue as string, (qualifierColumnName as String, qualifierValue as string)*))

9.Deploy the project and Test it with different inputs.

10.The SOA Composer is available in the below URL. http://hostname:port/soa/composer

11.The DVM can be opened and value can be changed at runtime from the soa composer without moving objects.