Oracle SOA Accept messages in REST format

The SOA composites can be enabled or disabled  to accept messages in Representational State Transfer (REST) format. This can be done Enabling or Disabling RESTful Web Services from Enterprise manger.

To enable or disable Web service styles:

1. Navigate to the Web Service Endpoint page, or the Service Home page.
   
   
2. Click the Configuration tab. For SOA composites, click the Properties tab.
   
   
3. In the REST Enabled field, select True from the menu to enable REST, or select False to disable REST, and click Apply.
   
   
   
   
   
   Below is the sample SOAP request and the URL to invoke the service in restful way.
   
   

   SOAP Request

   
   
   <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
               <soap:Body>
                           <ns1:singleString xmlns:ns1="http://xmlns.oracle.com/singleString">venky</ns1:singleString>
       </soap:Body>
   
   
   </soap:Envelope>
   
   
   
   

   REST URI

   
   
   http://hostname:port/soa-infra/services/default/EchoProject/EchoMediator_ep?operationName=execute&request=venky
   
   
   This can be tested with any rest client.
   
   
   Please note the operation name to be used in the URI.
   
   
   
   

Extending a SOA Domain to Oracle Service Bus on cluster

Extending a SOA Domain to Oracle Service Bus

We had our SOA environment on a two node cluster and we had to extend this for OSB.Our SOA set up is based on the enterprise deployment guide as recommended from Oracle and below are the steps we did to extend the domain.We have installed WL_HOME and MW_HOME (binaries) on a shared storage and they are available from SOAHOST1 and SOAHOST2.

Back up the installation & Enable VIP for OSB servers

To back up the existing Fusion Middleware Home and domain run the following command on SOAHOST1

tar -cvpf fmwhomeback.tar ORACLE_BASE/product/fmw
tar -cvpf domainhomeback.tar ORACLE_BASE/admin/domain_name/aserver/domain_name

Enable the Virtual IPs VIP5 & VIP6 in the SOAHOST1 and SOAHOST2.

Extend a SOA Domain to Include Oracle Service Bus

Download & Install the OSB to create a new Oracle Service Bus home.

Extend a SOA Domain to Include Oracle Service Bus

Change directory to the location of the Configuration Wizard. This is within the Oracle Service Bus directory.
cd ORACLE_COMMON_HOME/common/bin
./config.sh


                          
Select Extend an existing WebLogic domain, and click Next




In the Select Extension Source screen, select Extend my domain automatically to support the following added products and select the following products (the components required by Oracle SOA and Oracle WSM Policy Manager should already be selected and grayed out):



Oracle Service Bus OWSM Extension - 11.1.1.7 [osb]
Oracle Service Bus - 11.1.1.0 [osb]
WebLogic Advance Web Services JAX-RPC Extension







Select the select the OSB JMS reporting Provider schema.










In the Test JDBC Component Schema screen, verify that the Oracle Service Bus JMS reporting datasources are correctly verified and click Next.






In the Select Optional Configuration screen, select the following:



Managed Servers, Clusters, and Machines
JMS Distributed Destinations
Deployments and Services
JMS File Store


Click Next.





In the Select JMS Distributed Destination Type screen leave the pre-existing JMS System Resources as they are and Select UDD from the drop down list for WseeJMSMOdule and JmsResources.
Click Next





In the Configure Managed Servers screen, add the required managed servers for Oracle Service Bus.

Select the automatically created server and click Rename to change the name to wls_osb1.

Click Add to add another new server and enter wls_osb2 as the server name.

Click Next.







In the Configure Clusters screen, add the Oracle Service Bus cluster







In the assign servers to clusters screen, assign servers to clusters as follows:

soa_cluster - if you are extending a soa domain.



wls_soa1
wls_soa2


wsm-pm_cluster:



wls_wsm1
wls_wsm2


osb_cluster:



wls_osb1
wls_osb2


click Next.







In the Assign Servers to Machines screen, assign servers to machines as follows:

ADMINHOST:

AdminServer

SOAHOST1

WLS_SOA1 (if extending a SOA domain)

WLS_WSM1

WLS_OSB1

SOAHOST2:

WLS_SOA2 (if extending a SOA domain)

WLS_WSM2

WLS_OSB2

Click Next.








In the Target Deployments to Clusters or Servers screen, ensure the following targets

Target usermessagingserver and usermessagingdriver-email only to SOA_Cluster. (The usermessaging-xmpp, usermessaging-smpp, and usermessaging-voicexml applications are optional.)
Target the oracle.sdp.*, and oracle.soa.* libraries only to SOA_Cluster.
Target the oracle.rules.* library only to AdminServer and SOA_Cluster.
Target the wsm-pm application only to WSM-PM_Cluster.
Target all Transport Provider Deployments to both the OSB_Cluster and the AdminServer.

Target this library to the SOA_Cluster also only if you are planning to deploy WebLogic WebServices to it.

Click Next.





In the Target Services to Clusters or Servers screen:
Target mds-owsm only to WSM-PM_Cluster and AdminServer.
Target mds-soa only to SOA_Cluster.

Click Next.






Enter the shared directory location specified for your JMS stores 





In the Configuration Summary screen click Extend.









Propagating the Domain Configuration to the Managed Server Directory in SOAHOST1 and to SOAHOST2

Run the pack command on SOAHOST1 to create a template pack:

cd ORACLE_COMMON_HOME/common/bin
./pack.sh -managed=true -domain=ORACLE_BASE/admin/
domain_name/aserver/domain_name -template=soadomaintemplateExtOSB.jar
 -template_name=soa_domain_templateExtOSB


Run the unpack command on SOAHOST1 to unpack the propagated template to the domain directory of the managed server:

./unpack.sh -domain=ORACLE_BASE/admin/domain_name/mserver/domain_name
-overwrite_domain=true -template=soadomaintemplateExtOSB.jar 
-app_dir=ORACLE_BASE/admin/domain_name/mserver/applications

ORA-00942: table or view does not exist ORA-06512: at "DEV_MDS.MDS_INTERNAL_SHREDDED"

The composite which is working fine may fail with the below errors.This issue was observed in SOA Suite 11.1.1.7.0 and it may error out when you try to undeploy composite using the WLST script.Also it may prevent you from deploying any new composite into the SOA domain.

MDS-00054: The File to be Loaded oramds:/deployed-composites/default/Test_rev1.0/SCA-INF/classes/META-INF/adf-config.xml Does Not Exist

ORA-00942: table or view does not exist ORA-06512: at "DEV_MDS.MDS_INTERNAL_SHREDDED", line 37 ORA-06512: at "DEV_MDS.MDS_INTERNAL_SHREDDED", line 664 ORA-06512: at line 1

This error happens due to the MDS corruption when there are multiple schemas with identical objects.In our case we had two installations of the SOA pointing to the same database with different prefix.

Connect to the MDS database as sys user and run the below script.

alter system flush shared_pool;

There is a database patch available to prevent future corruption(Doc ID 9577583.8)

Oracle SOA purge instances

Deleting Large Numbers of Instances with the Purge Scripts

Deleting thousands of instances with the Delete With Options button on the Instances page of a SOA composite application in Oracle Enterprise Manager Fusion Middleware Control takes time and may result in a transaction timeout.At times it brings down the SOA server with heap space error.Instead we can use the purge scripts for deleting instances.

Get total number of instances

The following query can be used to get the total number of instances stored in the database.

 SELECT  
 (SELECT COUNT(*) FROM DEV_SOAINFRA.CUBE_INSTANCE) AS CUBE_INST,  
 (SELECT COUNT(*) FROM DEV_SOAINFRA.COMPOSITE_INSTANCE) AS COMP_INST,  
 (SELECT COUNT(*) FROM DEV_SOAINFRA.COMPONENT_INSTANCE) AS COMPNT_INST,  
 (SELECT COUNT(*) FROM DEV_SOAINFRA.COMPOSITE_INSTANCE_FAULT) AS COMPST_INST_FLT,  
 (SELECT COUNT(*) FROM DEV_SOAINFRA.REFERENCE_INSTANCE) AS REF_INST,  
 (SELECT COUNT(*) FROM DEV_SOAINFRA.CUBE_SCOPE) AS CUBE_SCP,  
 (SELECT COUNT(*) FROM DEV_SOAINFRA.COMPOSITE_SENSOR_VALUE) AS COMP_SEN_VAL,  
 (SELECT COUNT(*) FROM DEV_SOAINFRA.XML_DOCUMENT) AS XML_DOC,  
 (SELECT COUNT(*) FROM DEV_SOAINFRA.XML_DOCUMENT_REF) AS XML_DOC_REF,  
 (SELECT COUNT(*) FROM DEV_SOAINFRA.AUDIT_TRAIL) AS AUDIT_TRAIL,  
 (SELECT COUNT(*) FROM DEV_SOAINFRA.AUDIT_COUNTER) AS AUDIT_CNTR,  
 (SELECT COUNT(*) FROM DEV_SOAINFRA.AUDIT_DETAILS) AS AUDIT_DET,  
 (SELECT COUNT(*) FROM DEV_SOAINFRA.INSTANCE_PAYLOAD) AS INST_PYLD,  
 (SELECT COUNT(*) FROM DEV_SOAINFRA.HEADERS_PROPERTIES) AS HDRS_PROPS,  
 (SELECT COUNT(*) FROM DEV_SOAINFRA.WFTASK) AS WFTASK,  
 (SELECT COUNT(*) FROM DEV_SOAINFRA.WI_FAULT) AS WI_FLT,  
 (SELECT COUNT(*) FROM DEV_SOAINFRA.DLV_MESSAGE) AS DLV_MSSG,  
 (SELECT COUNT(*) FROM DEV_SOAINFRA.DLV_SUBSCRIPTION) AS DLV_SUBC,  
 (SELECT COUNT(*) FROM DEV_SOAINFRA.DOCUMENT_DLV_MSG_REF) AS DOC_DLV_MSG_REF,  
 (SELECT COUNT(*) FROM DEV_SOAINFRA.DOCUMENT_CI_REF) AS DOC_CI_REF,  
 (SELECT COUNT(*) FROM DEV_SOAINFRA.WORK_ITEM) AS WRK_ITEM  
 FROM DUAL;  

To know  the available table space  below query can be used.

 SELECT * FROM (SELECT C.TABLESPACE_NAME, ROUND(A.BYTES/1048576,2) MB_ALLOCATED,ROUND(B.BYTES/1048576,2)  
 MB_FREE,ROUND((A.BYTES-B.BYTES)/1048576,2) MB_USED, ROUND(B.BYTES/A.BYTES  
 * 100,2) TOT_PCT_FREE, ROUND((A.BYTES-B.BYTES)/A.BYTES,2) * 100 TOT_PCT_USED FROM (SELECT TABLESPACE_NAME, SUM(A.BYTES) BYTES FROM  
 SYS.DBA_DATA_FILES A GROUP BY TABLESPACE_NAME) A, (SELECT A.TABLESPACE_NAME,  
 NVL(SUM(B.BYTES),0) BYTES FROM SYS.DBA_DATA_FILES A, SYS.DBA_FREE_SPACE B WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME (+)  
 AND A.FILE_ID = B.FILE_ID (+) GROUP BY A.TABLESPACE_NAME) B,  
 SYS.DBA_TABLESPACES C WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) AND  
 A.TABLESPACE_NAME = C.TABLESPACE_NAME) WHERE TOT_PCT_USED >=0 AND  
 TABLESPACE_NAME='DEV_SOAINFRA' ORDER BY TABLESPACE_NAME;  

Running the Purge Scripts

This section describes how to execute the purge scripts.

To execute the purge scripts:

This section describes how to execute the purge scripts.
To execute the purge scripts:

1.In SQL*Plus, connect to the database AS SYSDBA:

CONNECT SYS AS SYSDBA

2.Execute the following SQL commands:

GRANT EXECUTE ON DBMS_LOCK TO USER;
GRANT CREATE ANY JOB TO USER;

where USER is the soainfra account to execute the scripts. These privileges are required to run the scripts.

3.Load the purge scripts by running the main purge script in the MW_HOME/SOA_ORACLE_HOME/rcu/integration/soainfra/sql/soa_purge directory.
For a parallel purge, the debug logs from the jobs spawned by a parallel purge are logged into files created in the directory named SOA_PURGE_DIR. This directory must be accessible to the Oracle database.

4.Create SOA_PURGE_DIR and grant write permissions to the soainfra user.
mkdir -p /tmp/purgelog

5.CREATE OR REPLACE DIRECTORY SOA_PURGE_DIR AS 'SERVER_DIRECTORY'
where SERVER_DIRECTORY is the name of the directory to create (for example, '/tmp/purgelog/'). Note the required single quotes around the directory path.


6.If you want to execute the scripts in debug mode, run common/debug_on.sql and set serverout to on in SQL*Plus. This step is optional.

SET SERVEROUTPUT ON

The logs from the spawned jobs are logged into the directory created in Step 4 (separate files per job). The rest of the logs are displayed on stdout(or the spool file, if configured).

There are two options for purging:

• Looped purge
• Parallel purge

Execute the purge scripts as shown below. Examples are provided for both options.
For looped purge:

 DECLARE  
   MAX_CREATION_DATE timestamp;  
   MIN_CREATION_DATE timestamp;  
   batch_size integer;  
   max_runtime integer;  
   retention_period timestamp;  
  BEGIN  
   MIN_CREATION_DATE := to_timestamp('2012-01-01','YYYY-MM-DD');  
   MAX_CREATION_DATE := to_timestamp('2012-01-31','YYYY-MM-DD');  
   max_runtime := 60;  
   retention_period := to_timestamp('2012-01-31','YYYY-MM-DD');  
   batch_size := 10000;  
    soa.delete_instances(  
    min_creation_date => MIN_CREATION_DATE,  
    max_creation_date => MAX_CREATION_DATE,  
    batch_size => batch_size,  
    max_runtime => max_runtime,  
    retention_period => retention_period,  
    purge_partitioned_component => false);  
  END;  
  /  

For parallel purge:

 DECLARE  
   max_creation_date timestamp;  
   min_creation_date timestamp;  
   retention_period timestamp;  
  BEGIN  
   min_creation_date := to_timestamp('2012-01-01','YYYY-MM-DD');  
   max_creation_date := to_timestamp('2012-01-31','YYYY-MM-DD');  
   retention_period := to_timestamp('2012-01-31','YYYY-MM-DD');  
   soa.delete_instances_in_parallel(  
    min_creation_date => min_creation_date,  
    max_creation_date => max_creation_date,  
    batch_size => 10000,  
    max_runtime => 60,  
    retention_period => retention_period,  
    DOP => 3,  
    max_count => 1000000,  
    purge_partitioned_component => false);  
  END;  

Monitoring the purge jobs

Below script can be used to get the status of the jobs.

SELECT * FROM dba_scheduler_jobs where job_name like '%SOA_PURGE%'

Reclaim Disk Space from Dehydration Table

For some Oracle SOA Suite environments, the purge scripts may not always remove a sufficient number of composite instances to reclaim database space. This can lead to performance issues.To reclaim the space following scripts can be used.

 ALTER SESSION SET CURRENT_SCHEMA=DEV_SOAINFRA;  
 
 ALTER TABLE MEDIATOR_CASE_INSTANCE ENABLE ROW MOVEMENT;  
 ALTER TABLE MEDIATOR_CASE_INSTANCE SHRINK SPACE;  
 ALTER TABLE MEDIATOR_CASE_INSTANCE DISABLE ROW MOVEMENT;  
 ALTER TABLE MEDIATOR_AUDIT_DOCUMENT ENABLE ROW MOVEMENT;  
 ALTER TABLE MEDIATOR_AUDIT_DOCUMENT SHRINK SPACE;  
 ALTER TABLE MEDIATOR_AUDIT_DOCUMENT DISABLE ROW MOVEMENT;  
 ALTER TABLE MEDIATOR_CALLBACK ENABLE ROW MOVEMENT;  
 ALTER TABLE MEDIATOR_CALLBACK SHRINK SPACE;  
 ALTER TABLE MEDIATOR_CALLBACK DISABLE ROW MOVEMENT;  
 ALTER TABLE MEDIATOR_GROUP_STATUS ENABLE ROW MOVEMENT;  
 ALTER TABLE MEDIATOR_GROUP_STATUS SHRINK SPACE;  
 ALTER TABLE MEDIATOR_GROUP_STATUS DISABLE ROW MOVEMENT;  
 ALTER TABLE MEDIATOR_PAYLOAD ENABLE ROW MOVEMENT;  
 ALTER TABLE MEDIATOR_PAYLOAD SHRINK SPACE;  
 ALTER TABLE MEDIATOR_PAYLOAD DISABLE ROW MOVEMENT;  
 ALTER TABLE MEDIATOR_DEFERRED_MESSAGE ENABLE ROW MOVEMENT;  
 ALTER TABLE MEDIATOR_DEFERRED_MESSAGE SHRINK SPACE;  
 ALTER TABLE MEDIATOR_DEFERRED_MESSAGE DISABLE ROW MOVEMENT;  
 ALTER TABLE MEDIATOR_RESEQUENCER_MESSAGE ENABLE ROW MOVEMENT;  
 ALTER TABLE MEDIATOR_RESEQUENCER_MESSAGE SHRINK SPACE;  
 ALTER TABLE MEDIATOR_RESEQUENCER_MESSAGE DISABLE ROW MOVEMENT;  
 ALTER TABLE MEDIATOR_CASE_DETAIL ENABLE ROW MOVEMENT;  
 ALTER TABLE MEDIATOR_CASE_DETAIL SHRINK SPACE;  
 ALTER TABLE MEDIATOR_CASE_DETAIL DISABLE ROW MOVEMENT;  
 ALTER TABLE MEDIATOR_CORRELATION ENABLE ROW MOVEMENT;  
 ALTER TABLE MEDIATOR_CORRELATION SHRINK SPACE;  
 ALTER TABLE MEDIATOR_CORRELATION DISABLE ROW MOVEMENT;  
 ALTER TABLE MEDIATOR_INSTANCE ENABLE ROW MOVEMENT;  
 ALTER TABLE MEDIATOR_INSTANCE SHRINK SPACE;  
 ALTER TABLE MEDIATOR_INSTANCE DISABLE ROW MOVEMENT;  
 ALTER TABLE HEADERS_PROPERTIES ENABLE ROW MOVEMENT;  
 ALTER TABLE HEADERS_PROPERTIES SHRINK SPACE;  
 ALTER TABLE HEADERS_PROPERTIES DISABLE ROW MOVEMENT;  
 ALTER TABLE AG_INSTANCE ENABLE ROW MOVEMENT;  
 ALTER TABLE AG_INSTANCE SHRINK SPACE;  
 ALTER TABLE AG_INSTANCE DISABLE ROW MOVEMENT;  
 ALTER TABLE AUDIT_COUNTER ENABLE ROW MOVEMENT;  
 ALTER TABLE AUDIT_COUNTER SHRINK SPACE;  
 ALTER TABLE AUDIT_COUNTER DISABLE ROW MOVEMENT;  
 ALTER TABLE AUDIT_TRAIL ENABLE ROW MOVEMENT;  
 ALTER TABLE AUDIT_TRAIL SHRINK SPACE;  
 ALTER TABLE AUDIT_TRAIL DISABLE ROW MOVEMENT;  
 ALTER TABLE AUDIT_DETAILS ENABLE ROW MOVEMENT;  
 ALTER TABLE AUDIT_DETAILS SHRINK SPACE;  
 ALTER TABLE AUDIT_DETAILS DISABLE ROW MOVEMENT;  
 ALTER TABLE CI_INDEXES ENABLE ROW MOVEMENT;  
 ALTER TABLE CI_INDEXES SHRINK SPACE;  
 ALTER TABLE CI_INDEXES DISABLE ROW MOVEMENT;  
 ALTER TABLE WORK_ITEM ENABLE ROW MOVEMENT;  
 ALTER TABLE WORK_ITEM SHRINK SPACE;  
 ALTER TABLE WORK_ITEM DISABLE ROW MOVEMENT;  
 ALTER TABLE WI_FAULT ENABLE ROW MOVEMENT;  
 ALTER TABLE WI_FAULT SHRINK SPACE;  
 ALTER TABLE WI_FAULT DISABLE ROW MOVEMENT;  
 ALTER TABLE XML_DOCUMENT_REF ENABLE ROW MOVEMENT;  
 ALTER TABLE XML_DOCUMENT_REF SHRINK SPACE;  
 ALTER TABLE XML_DOCUMENT_REF DISABLE ROW MOVEMENT;  
 ALTER TABLE DOCUMENT_DLV_MSG_REF ENABLE ROW MOVEMENT;  
 ALTER TABLE DOCUMENT_DLV_MSG_REF SHRINK SPACE;  
 ALTER TABLE DOCUMENT_DLV_MSG_REF DISABLE ROW MOVEMENT;  
 ALTER TABLE DOCUMENT_CI_REF ENABLE ROW MOVEMENT;  
 ALTER TABLE DOCUMENT_CI_REF SHRINK SPACE;  
 ALTER TABLE DOCUMENT_CI_REF DISABLE ROW MOVEMENT;  
 ALTER TABLE DLV_SUBSCRIPTION ENABLE ROW MOVEMENT;  
 ALTER TABLE DLV_SUBSCRIPTION SHRINK SPACE;  
 ALTER TABLE DLV_SUBSCRIPTION DISABLE ROW MOVEMENT;  
 ALTER TABLE DLV_MESSAGE ENABLE ROW MOVEMENT;  
 ALTER TABLE DLV_MESSAGE SHRINK SPACE;  
 ALTER TABLE DLV_MESSAGE DISABLE ROW MOVEMENT;  
 ALTER TABLE REJECTED_MSG_NATIVE_PAYLOAD ENABLE ROW MOVEMENT;  
 ALTER TABLE REJECTED_MSG_NATIVE_PAYLOAD SHRINK SPACE;  
 ALTER TABLE REJECTED_MSG_NATIVE_PAYLOAD DISABLE ROW MOVEMENT;  
 ALTER TABLE INSTANCE_PAYLOAD ENABLE ROW MOVEMENT;  
 ALTER TABLE INSTANCE_PAYLOAD SHRINK SPACE;  
 ALTER TABLE INSTANCE_PAYLOAD DISABLE ROW MOVEMENT;  
 ALTER TABLE TEST_DETAILS ENABLE ROW MOVEMENT;  
 ALTER TABLE TEST_DETAILS SHRINK SPACE;  
 ALTER TABLE TEST_DETAILS DISABLE ROW MOVEMENT;  
 ALTER TABLE CUBE_SCOPE ENABLE ROW MOVEMENT;  
 ALTER TABLE CUBE_SCOPE SHRINK SPACE;  
 ALTER TABLE CUBE_SCOPE DISABLE ROW MOVEMENT;  
 ALTER TABLE CUBE_INSTANCE ENABLE ROW MOVEMENT;  
 ALTER TABLE CUBE_INSTANCE SHRINK SPACE;  
 ALTER TABLE CUBE_INSTANCE DISABLE ROW MOVEMENT;  
 ALTER TABLE BPM_AUDIT_QUERY ENABLE ROW MOVEMENT;  
 ALTER TABLE BPM_AUDIT_QUERY SHRINK SPACE;  
 ALTER TABLE BPM_AUDIT_QUERY DISABLE ROW MOVEMENT;  
 ALTER TABLE BPM_MEASUREMENT_ACTIONS ENABLE ROW MOVEMENT;  
 ALTER TABLE BPM_MEASUREMENT_ACTIONS SHRINK SPACE;  
 ALTER TABLE BPM_MEASUREMENT_ACTIONS DISABLE ROW MOVEMENT;  
 ALTER TABLE BPM_MEASUREMENT_ACTION_EXCEPS ENABLE ROW MOVEMENT;  
 ALTER TABLE BPM_MEASUREMENT_ACTION_EXCEPS SHRINK SPACE;  
 ALTER TABLE BPM_MEASUREMENT_ACTION_EXCEPS DISABLE ROW MOVEMENT;  
 ALTER TABLE BPM_CUBE_AUDITINSTANCE ENABLE ROW MOVEMENT;  
 ALTER TABLE BPM_CUBE_AUDITINSTANCE SHRINK SPACE;  
 ALTER TABLE BPM_CUBE_AUDITINSTANCE DISABLE ROW MOVEMENT;  
 ALTER TABLE BPM_CUBE_TASKPERFORMANCE ENABLE ROW MOVEMENT;  
 ALTER TABLE BPM_CUBE_TASKPERFORMANCE SHRINK SPACE;  
 ALTER TABLE BPM_CUBE_TASKPERFORMANCE DISABLE ROW MOVEMENT;  
 ALTER TABLE BPM_CUBE_PROCESSPERFORMANCE ENABLE ROW MOVEMENT;  
 ALTER TABLE BPM_CUBE_PROCESSPERFORMANCE SHRINK SPACE;  
 ALTER TABLE BPM_CUBE_PROCESSPERFORMANCE DISABLE ROW MOVEMENT;  
 ALTER TABLE WFTASK_TL ENABLE ROW MOVEMENT;  
 ALTER TABLE WFTASK_TL SHRINK SPACE;  
 ALTER TABLE WFTASK_TL DISABLE ROW MOVEMENT;  
 ALTER TABLE WFTASKHISTORY ENABLE ROW MOVEMENT;  
 ALTER TABLE WFTASKHISTORY SHRINK SPACE;  
 ALTER TABLE WFTASKHISTORY DISABLE ROW MOVEMENT;  
 ALTER TABLE WFTASKHISTORY_TL ENABLE ROW MOVEMENT;  
 ALTER TABLE WFTASKHISTORY_TL SHRINK SPACE;  
 ALTER TABLE WFTASKHISTORY_TL DISABLE ROW MOVEMENT;  
 ALTER TABLE WFCOMMENTS ENABLE ROW MOVEMENT;  
 ALTER TABLE WFCOMMENTS SHRINK SPACE;  
 ALTER TABLE WFCOMMENTS DISABLE ROW MOVEMENT;  
 ALTER TABLE WFMESSAGEATTRIBUTE ENABLE ROW MOVEMENT;  
 ALTER TABLE WFMESSAGEATTRIBUTE SHRINK SPACE;  
 ALTER TABLE WFMESSAGEATTRIBUTE DISABLE ROW MOVEMENT;  
 ALTER TABLE WFATTACHMENT ENABLE ROW MOVEMENT;  
 ALTER TABLE WFATTACHMENT SHRINK SPACE;  
 ALTER TABLE WFATTACHMENT DISABLE ROW MOVEMENT;  
 ALTER TABLE WFASSIGNEE ENABLE ROW MOVEMENT;  
 ALTER TABLE WFASSIGNEE SHRINK SPACE;  
 ALTER TABLE WFASSIGNEE DISABLE ROW MOVEMENT;  
 ALTER TABLE WFREVIEWER ENABLE ROW MOVEMENT;  
 ALTER TABLE WFREVIEWER SHRINK SPACE;  
 ALTER TABLE WFREVIEWER DISABLE ROW MOVEMENT;  
 ALTER TABLE WFCOLLECTIONTARGET ENABLE ROW MOVEMENT;  
 ALTER TABLE WFCOLLECTIONTARGET SHRINK SPACE;  
 ALTER TABLE WFCOLLECTIONTARGET DISABLE ROW MOVEMENT;  
 ALTER TABLE WFROUTINGSLIP ENABLE ROW MOVEMENT;  
 ALTER TABLE WFROUTINGSLIP SHRINK SPACE;  
 ALTER TABLE WFROUTINGSLIP DISABLE ROW MOVEMENT;  
 ALTER TABLE WFNOTIFICATION ENABLE ROW MOVEMENT;  
 ALTER TABLE WFNOTIFICATION SHRINK SPACE;  
 ALTER TABLE WFNOTIFICATION DISABLE ROW MOVEMENT;  
 ALTER TABLE WFTASKTIMER ENABLE ROW MOVEMENT;  
 ALTER TABLE WFTASKTIMER SHRINK SPACE;  
 ALTER TABLE WFTASKTIMER DISABLE ROW MOVEMENT;  
 ALTER TABLE WFTASKERROR ENABLE ROW MOVEMENT;  
 ALTER TABLE WFTASKERROR SHRINK SPACE;  
 ALTER TABLE WFTASKERROR DISABLE ROW MOVEMENT;  
 ALTER TABLE WFHEADERPROPS ENABLE ROW MOVEMENT;  
 ALTER TABLE WFHEADERPROPS SHRINK SPACE;  
 ALTER TABLE WFHEADERPROPS DISABLE ROW MOVEMENT;  
 ALTER TABLE WFEVIDENCE ENABLE ROW MOVEMENT;  
 ALTER TABLE WFEVIDENCE SHRINK SPACE;  
 ALTER TABLE WFEVIDENCE DISABLE ROW MOVEMENT;  
 ALTER TABLE WFTASKASSIGNMENTSTATISTIC ENABLE ROW MOVEMENT;  
 ALTER TABLE WFTASKASSIGNMENTSTATISTIC SHRINK SPACE;  
 ALTER TABLE WFTASKASSIGNMENTSTATISTIC DISABLE ROW MOVEMENT;  
 ALTER TABLE WFTASKAGGREGATION ENABLE ROW MOVEMENT;  
 ALTER TABLE WFTASKAGGREGATION SHRINK SPACE;  
 ALTER TABLE WFTASKAGGREGATION DISABLE ROW MOVEMENT;  
 ALTER TABLE WFTASK ENABLE ROW MOVEMENT;  
 ALTER TABLE WFTASK SHRINK SPACE;  
 ALTER TABLE WFTASK DISABLE ROW MOVEMENT;  
 ALTER TABLE COMPOSITE_SENSOR_VALUE ENABLE ROW MOVEMENT;  
 ALTER TABLE COMPOSITE_SENSOR_VALUE SHRINK SPACE;  
 ALTER TABLE COMPOSITE_SENSOR_VALUE DISABLE ROW MOVEMENT;  
 ALTER TABLE COMPOSITE_INSTANCE_ASSOC ENABLE ROW MOVEMENT;  
 ALTER TABLE COMPOSITE_INSTANCE_ASSOC SHRINK SPACE;  
 ALTER TABLE COMPOSITE_INSTANCE_ASSOC DISABLE ROW MOVEMENT;  
 ALTER TABLE ATTACHMENT ENABLE ROW MOVEMENT;  
 ALTER TABLE ATTACHMENT SHRINK SPACE;  
 ALTER TABLE ATTACHMENT DISABLE ROW MOVEMENT;  
 ALTER TABLE ATTACHMENT_REF ENABLE ROW MOVEMENT;  
 ALTER TABLE ATTACHMENT_REF SHRINK SPACE;  
 ALTER TABLE ATTACHMENT_REF DISABLE ROW MOVEMENT;  
 ALTER TABLE COMPOSITE_INSTANCE_FAULT ENABLE ROW MOVEMENT;  
 ALTER TABLE COMPOSITE_INSTANCE_FAULT SHRINK SPACE;  
 ALTER TABLE COMPOSITE_INSTANCE_FAULT DISABLE ROW MOVEMENT;  
 ALTER TABLE REFERENCE_INSTANCE ENABLE ROW MOVEMENT;  
 ALTER TABLE REFERENCE_INSTANCE SHRINK SPACE;  
 ALTER TABLE REFERENCE_INSTANCE DISABLE ROW MOVEMENT;  
 ALTER TABLE COMPONENT_INSTANCE ENABLE ROW MOVEMENT;  
 ALTER TABLE COMPONENT_INSTANCE SHRINK SPACE;  
 ALTER TABLE COMPONENT_INSTANCE DISABLE ROW MOVEMENT;  
 ALTER TABLE COMPOSITE_INSTANCE ENABLE ROW MOVEMENT;  
 ALTER TABLE COMPOSITE_INSTANCE SHRINK SPACE;  
 ALTER TABLE COMPOSITE_INSTANCE DISABLE ROW MOVEMENT;  
 -- -----------------------------------------------------------------------------  
 -- Modify LOB Segments  
 -- -----------------------------------------------------------------------------  
 ALTER TABLE AUDIT_DETAILS MODIFY LOB (BIN) (SHRINK SPACE);  
 ALTER TABLE COMPOSITE_INSTANCE_FAULT MODIFY LOB (ERROR_MESSAGE) (SHRINK SPACE);  
 ALTER TABLE COMPOSITE_INSTANCE_FAULT MODIFY LOB (STACK_TRACE) (SHRINK SPACE);  
 ALTER TABLE CUBE_SCOPE MODIFY LOB (SCOPE_BIN) (SHRINK SPACE);  
 ALTER TABLE REFERENCE_INSTANCE MODIFY LOB (ERROR_MESSAGE) (SHRINK SPACE);  
 ALTER TABLE REFERENCE_INSTANCE MODIFY LOB (STACK_TRACE) (SHRINK SPACE);  
 ALTER TABLE TEST_DEFINITIONS MODIFY LOB (DEFINITION) (SHRINK SPACE);  
 ALTER TABLE WI_FAULT MODIFY LOB (MESSAGE) (SHRINK SPACE);  
 ALTER TABLE XML_DOCUMENT MODIFY LOB (DOCUMENT) (SHRINK SPACE);  
 -- -----------------------------------------------------------------------------  
 -- Rebuild Indexes  
 -- -----------------------------------------------------------------------------  
 ALTER INDEX AD_PK REBUILD ONLINE;  
 ALTER INDEX AT_PK REBUILD ONLINE;  
 ALTER INDEX CI_CREATION_DATE REBUILD ONLINE;  
 ALTER INDEX CI_CUSTOM3 REBUILD ONLINE;  
 ALTER INDEX CI_ECID REBUILD ONLINE;  
 ALTER INDEX CI_NAME_REV_STATE REBUILD ONLINE;  
 ALTER INDEX CI_PK REBUILD ONLINE;  
 ALTER INDEX COMPOSITE_INSTANCE_CIDN REBUILD ONLINE;  
 ALTER INDEX COMPOSITE_INSTANCE_CO_ID REBUILD ONLINE;  
 ALTER INDEX COMPOSITE_INSTANCE_CREATED REBUILD ONLINE;  
 ALTER INDEX COMPOSITE_INSTANCE_ECID REBUILD ONLINE;  
 ALTER INDEX COMPOSITE_INSTANCE_ID REBUILD ONLINE;  
 ALTER INDEX COMPOSITE_INSTANCE_STATE REBUILD ONLINE;  
 ALTER INDEX CS_PK REBUILD ONLINE;  
 ALTER INDEX DM_CONVERSATION REBUILD ONLINE;  
 ALTER INDEX DLV_MESSAGE_PK REBUILD ONLINE;  
 ALTER INDEX DOC_DLV_MSG_GUID_INDEX REBUILD ONLINE;  
 ALTER INDEX DOC_STORE_PK REBUILD ONLINE;  
 ALTER INDEX DS_CONVERSATION REBUILD ONLINE;  
 ALTER INDEX DS_CONV_STATE REBUILD ONLINE;  
 ALTER INDEX DS_FK REBUILD ONLINE;  
 ALTER INDEX DLV_SUBSCRIPTION_PK REBUILD ONLINE;  
 ALTER INDEX HEADERS_PROPERTIES_PK REBUILD ONLINE;  
 ALTER INDEX INSTANCE_PAYLOAD_KEY REBUILD ONLINE;  
 ALTER INDEX REFERENCE_INSTANCE_CDN_STATE REBUILD ONLINE;  
 ALTER INDEX REFERENCE_INSTANCE_CO_ID REBUILD ONLINE;  
 ALTER INDEX REFERENCE_INSTANCE_ECID REBUILD ONLINE;  
 ALTER INDEX REFERENCE_INSTANCE_ID REBUILD ONLINE;  
 ALTER INDEX REFERENCE_INSTANCE_STATE REBUILD ONLINE;  
 ALTER INDEX REFERENCE_INSTANCE_TIME_CDN REBUILD ONLINE;  
 ALTER INDEX DM_RECEIVE_DATE REBUILD ONLINE;  
 ALTER INDEX WF_CRDATE_CIKEY REBUILD ONLINE;  
 ALTER INDEX WF_CRDATE_TYPE REBUILD ONLINE;  
 ALTER INDEX WF_FK2 REBUILD ONLINE;  
 ALTER INDEX WI_FAULT_PK REBUILD ONLINE;  
 ALTER INDEX WI_EXPIRED REBUILD ONLINE;  
 ALTER INDEX WI_STATE_KEY REBUILD ONLINE;  
 ALTER INDEX WORK_ITEM_PK REBUILD ONLINE;  
 ALTER INDEX WI_EXPIRED REBUILD ONLINE;  
 ALTER INDEX XML_DOCUMENT_REF_PK REBUILD ONLINE;  

Server failed to get a connection to the database in the past 30 seconds for lease renewal. Server will shut itself down.

If you are using a database to manage leasing information , the server renews its "lease" by updating the timestamp in the lease table.

If a migratable server fails to reach the lease table and renew its lease before    the lease expires, it terminates as quickly as possible using a Java System.exit command and you can see a error trace as below:

<Sep 3, 2013 8:22:30 AM GST> <Warning> <Cluster> <BEA-000147> <Server "wls_soa2" failed to renew lease in the database.> 
<Sep 3, 2013 8:22:30 AM GST> <Error> <Cluster> <BEA-000150> <Server failed to get a connection to the database in the past 30 seconds for lease renewal. Server will shut itself down.> 
<Sep 3, 2013 8:22:30 AM GST> <Critical> <Health> <BEA-310006> <Critical Subsystem ServerMigration has failed. Setting server state to FAILED. 
Reason: Serverwls_soa2 failed to renew lease in the database> 
<Sep 3, 2013 8:22:30 AM GST> <Critical> <WebLogicServer> <BEA-000385> <Server health failed. Reason: health of critical service 'ServerMigration' failed> 
<Sep 3, 2013 8:22:30 AM GST> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to FAILED> 
<Sep 3, 2013 8:22:30 AM GST> <Error> <> <BEA-000000>

To resolve  this issue please ensure that same time is set on all backend RAC database  nodes to which leasing datasource is connecting to.

This can also happen due to slow database connections and the timeout can be modified using the below properties.

By default a migratable server renews its lease every 30,000 milliseconds—the

product of two configurable ServerMBean properties:

– HealthCheckIntervalMillis, which by default is 10,000.

– HealthCheckPeriodsUntilFencing, which by default is 3.

Consensus leasing can be a option since it doesn't have the database dependency.

setCompositeInstanceTitle Mediator

Setting the title for instances would be handy at runtime to identify them in the Enterprise manager. This can be achieved by using a assign activity and setting the property med:setCompositeInstanceTitle. But the property is not available in using the designer.This can be set in the .mplan file and below is a sample snippet.

 <operation name="executeAuthenticateUser" deliveryPolicy="AllOrNothing"  
        priority="4" validateSchema="false">  
    <switch>  
      <case executionType="direct"  
         name="CallSpAuthenticateUser.CallSpAuthenticateUser">  
       <action>  
         <transform>  
          <part name="$out.InputParameters"  
             function="xslt(xsl/InputParameters_To_InputParameters_2.xsl, $in.request)"/>  
         </transform>  
       <assign>  
       <copy target="$out.property.tracking.compositeInstanceTitle"  
          expression="med:setCompositeInstanceTitle('executeAuthenticateUser')"  
          xmlns:med="http://schemas.oracle.com/mediator/xpath"/>  
       </assign>  
         <invoke reference="CallSpAuthenticateUser"  
             operation="CallSpAuthenticateUser">  
          <onReply>  
            <transform>  
             <part name="$out.reply"  
                function="xslt(xsl/OutputParameters_To_OutputParameters_2.xsl, $in.OutputParameters)"/>  
            </transform>  
            <reply/>  
          </onReply>  
         </invoke>  
       </action>  
      </case>  
    </switch>  
   </operation>  

Below is the screenshot showing same.

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.