Wednesday, February 1, 2012

Oracle SOA Suite 11g - Important SQL queries to get the details about composites from SOAINFRA schema


Oracle SOA Suite 11g - Important SQL queries to get the details about composites from SOAINFRA schema

Query to find the execution time of the BPEL instances:-

SELECT * FROM (SELECT COMPOSITE_NAME COMPOSITENAME,A.CMPST_ID COMPOSITE_INSTANCE_ID,creation_date BEGIN_TIME,modify_date END_TIME ,(extract(day from modify_date - creation_date)*86400+ extract(hour from modify_date - creation_date)*3600+ extract(minute from modify_date - creation_date)*60+ extract(second from modify_date - creation_date)) duration_in_second,A.* FROM cube_instance A where state = 5 and CREATION_date BETWEEN TO_DATE('19/01/2012 00:58:00','dd/mm/yyyy HH24:MI:SS')AND TO_DATE('19/01/2012 23:59:59','dd/mm/yyyy HH24:MI:SS') AND COMPOSITE_NAME IN (<<Composite Name>>)) ORDER BY COMPOSITE_NAME,duration_in_second DESC

Query to find the execution time of the Mediator instances:

SELECT * FROM (SELECT COMPONENT_NAME COMPONENTNAME,A.COMPOSITE_INSTANCE_ID INSTANCE_ID,created_time BEGIN_TIME,updated_time END_TIME ,(extract(day from updated_time - created_time)*86400+ extract(hour from updated_time - created_time)*3600+ extract(minute from updated_time - created_time)*60+ extract(second from updated_time - created_time)) duration_in_second,A.* FROM mediator_instance A where CREATED_TIME BETWEEN TO_DATE('30/01/2012 00:58:00','dd/mm/yyyy HH24:MI:SS')AND TO_DATE('31/01/2012 23:59:59','dd/mm/yyyy HH24:MI:SS') AND A.COMPONENT_NAME like '%<<Meditor Composite Name>>%') ORDER BY COMPONENT_NAME,duration_in_second DESC

Query to find the count of Composite instances created on particular time:-
BPEL instances:

select * from (select count(COMPONENT_NAME) count,COMPONENT_NAME from CUBE_INSTANCE where CREATION_DATE BETWEEN TO_DATE('30/01/2012 12:58:00','dd/mm/yyyy HH24:MI:SS')AND TO_DATE('31/01/2012 23:00:00','dd/mm/yyyy HH24:MI:SS') /*and component_state<5*/ group by COMPONENT_NAME ) order by count desc

Mediator Instances:

select * from (select count(COMPONENT_NAME) count,COMPONENT_NAME from MEDIATOR_INSTANCE where CREATED_TIME BETWEEN TO_DATE('30/01/2012 12:58:00','dd/mm/yyyy HH24:MI:SS')AND TO_DATE('31/01/2012 23:00:00','dd/mm/yyyy HH24:MI:SS') group by COMPONENT_NAME ) order by count desc

Getting the count of instances in different state for the BPEL Components:

select composite_name,count(*),DECODE(cube_instance.STATE,

0, 'STATE_INITIATED',

1, 'STATE_OPEN_RUNNING',

2, 'STATE_OPEN_SUSPENDED',

3, 'STATE_OPEN_FAULTED',

4, 'STATE_CLOSED_PENDING_CANCEL',

5, 'STATE_CLOSED_COMPLETED',

6, 'STATE_CLOSED_FAULTED',

7, 'STATE_CLOSED_CANCELLED',

8, 'STATE_CLOSED_ABORTED',

9, 'STATE_CLOSED_STALE',

10,'STATE_CLOSED_ROLLED_BACK','unknown') state from CUBE_INSTANCE group by state,composite_name order by composite_name;

Getting the count of instances in different state for the Mediator Components:

select COMPONENT_NAME,count(*),DECODE(mediator_instance.COMPONENT_STATE,

0, 'STATE_CLOSED_COMPLETED',

2, 'STATE_OPEN_FAULTED',

4,'STATE_RECOVERY_REQUIRED',

8, 'STATE_OPEN_RUNNING',

16, 'STATE_CLOSED_STALE',

48, 'STATE_TERMINATED',

50, 'STATE_TERMINATED','unknown') STATE from mediator_instance group by component_state,COMPONENT_NAME order by COMPONENT_NAME;

Querying the call back message status from dlv_message table:

Error to deliver the call back to the receive activity:

select count(*) from dlv_message where dlv_type = 2 and state=1

Callback arrived but did not get resolved:

select count(*) from dlv_message where dlv_type = 2 and state=0

Callback successfully delivered to receive:

select count(*) from dlv_message where dlv_type = 2 and state=2

Querying recoverable invoke messages :

select * from dlv_message where dlv_type = 1 and state = 0;

To check for rejected messages for a specific composite:

select count(*) from rejected_message where composite_dn like '%<<Composite Name>>%';



Tuesday, January 31, 2012

Creating a Trust Keystore Using the keytool Utility for weblogic server


Creating a Trust Keystore Using the keytool Utility for weblogic server

Steps to create the Trust Keystore in weblogic server.

Copy the standard java keystore to create the new trust keystore since it already contains most of the root CA certificates needed. Oracle does not recommend modifying the standard Java trust keystore directly. Copy the standard Java keystore CA certificates located under the $WLSHOME/server/lib directory to the same directory as the certificates.
For example:
cp $WLSHOME/server/lib/cacerts $WLSDOMAIN/certs/appTrustKeyStore.jks
The default password for the standard Java keystore is changeit. Oracle recommends always changing the default password. Use the keytool utility to do this. The syntax is:
keytool -storepasswd -new -keystore -storepass
For example:
cd $WLSDOMAIN/certs
keytool -storepasswd -new welcome1 -keystore appTrustKeyStore.jks -storepass changeit
The CA certificate CertGenCA.der is used to sign all certificates generated by the utils CertGen tool and is located at $WLSHOME/server/lib directory. This CA certificate must be imported into the appTrustKeyStore.jks using the keytool utility. The syntax is:
keytool -import -v -noprompt -trustcacerts -alias -file -keystore -storepass
For example:
keytool -import -v -noprompt -trustcacerts -alias clientCACert –file $WLSHOME/server/lib/CertGenCA.der -keystore appTrustKeyStore.jks –storepass welcome1


Oracle SOA Suite 11g - Configuring Weblogic Servers to Use the Custom Key stores


Oracle SOA Suite 11g - Important SQL queries to get the details about composites from SOAINFRA schema:

The below WLST script will help us to configure the custom key stores for the weblogic servers.

The details should be changed accordingly based on your environment.

import sys
print "@@@ Starting the script ..."
from java.util import *
from javax.management import *
from java.io import FileInputStream
#The directory of the domain configuration
#/app/oracle/products/11g/admin/domains
wlsDomain=os.environ["WLSDOMAIN"]
print "WLSDOMAIN="+wlsDomain
adminURL='t3://'+<<Admin Server IP>>+':'+<<Admin Server Port>>
adminUserName='weblogic'
adminPassword=<<Admin Server Password>>
connect(adminUserName, adminPassword, adminURL)
edit()
startEdit()
###################Configure KeyStores for cluster####################
print 'Applying changes to cluster'
##Applying for Admin Server
cd('/Servers/AdminServer')
##Identity key Store location
cmo.setCustomIdentityKeyStoreFileName(wlsDomain+'/certs/appIdentityKeyStore.jks')
set('CustomIdentityKeyStorePassPhrase', 'welcome1')
##Trust key Store location
cmo.setCustomTrustKeyStoreFileName(wlsDomain+'/certs/appTrustKeyStore.jks')
set('CustomTrustKeyStorePassPhrase', 'welcome1')
cmo.setKeyStores('CustomIdentityAndCustomTrust')
cmo.setCustomIdentityKeyStoreType('JKS')
cmo.setCustomTrustKeyStoreType('JKS')
cd('/Servers/AdminServer/SSL/AdminServer')
##Server Private key Alias
cmo.setServerPrivateKeyAlias(<<Admin Server Host Name>>)
set('ServerPrivateKeyPassPhrase', 'welcome1')
##Applying for SOA managed server
cd('/Servers/SOA1')
cmo.setCustomIdentityKeyStoreFileName(wlsDomain+'/certs/appIdentityKeyStore.jks')
set('CustomIdentityKeyStorePassPhrase', 'welcome1')
cmo.setCustomTrustKeyStoreFileName(wlsDomain+'/certs/appTrustKeyStore.jks')
set('CustomTrustKeyStorePassPhrase', 'welcome1')
cmo.setKeyStores('CustomIdentityAndCustomTrust')
cmo.setCustomIdentityKeyStoreType('JKS')
cmo.setCustomTrustKeyStoreType('JKS')
cd('/Servers/SOA1/SSL/SOA1')
cmo.setServerPrivateKeyAlias(<<SOA1 Server Host Name>>)
set('ServerPrivateKeyPassPhrase', 'welcome1')
save()
activate()


Monday, January 30, 2012

Oracle SOA Suite - Changing the service endpoint URL dynamically through EM console

Oracle SOA Suite - Changing the service endpoint URL dynamically through EM console

Sometimes we may need to change the default endpoint location of the Oracle SOA Suite composite service through the EM console.

Steps to change the endpoint URL’s of the references through the EM console - Oracle SOA 11g

  • In EM console click on the Composite
  • Scroll down to Service and References section


  • Click on Reference link and properties tab
  • Change Endpoint Address to the desired location and click apply


The new request will be send to the new endpoint location configured through the EM console.
The new endpoint configuration can be removed to send the request to the default endpoint location configured in the WSDL.

The same approach will work in Oracle SOA Suite 12c

Endpoint_Change_through_EM