Manish Kumar Gupta

Create multiple SoaSuite10g instance under one Database Instance

2011-09-13T06:55:00.000-07:00

Problem Description:

During installation of SOA Suite 10.1.3.5.1(not limited to this version, since I have tested personally on this version therefore I have mentioned this version number here), the SOA Suite Installer first run the IRCA (Integration Repository Creation Assistant) internally which create three schema orabpel, oraesb, orawsm schema in given Database instance. A Database Software and Database Instance should be installed before running SOA suite 10g installer, its prerequisite for SOA Suite10g. IRCA will create above three schema and will create all product tables inside it and user can access BPEL console, esb console, owsm console after installation but now problem comes when requirement comes to install multiple SOA Suite instance in same machine because team needs multiple SOA Suite environment to continue their development, ST testing, SIT testing, UAT Testing, Pre Production etc.

Each SOA Suite Instance need a separate database instance coz during SOA Suite installation, installer won't allow user to change the name of orabpel, oraesb, orawsm schema and these schema name already exist in previous database instance. So the same schema name can’t be used in same Database instance name. e.g DBA first installed the Database software after that he/she created an Database instance called “XE”. In next steps Fusion infrastructure person run the SOA Suite10g installer and created three above schema in it without any problem and one SOASuite10g environment is ready.

Now for second SOASuite10g environment when user will again run the SOA Suite10 installer then user won’t be able to continue with same database instance XE since all above three schemas already exist in XE database. The only option remain is to create another database instance XE1 which will consume more primary and secondary memory and will be useless coz these all just testing environment.

Figure 1: SOA Schema Connect info.

Solution:
To overcome above problem there is one option available which we came to know after lots of effort.
IRCA also provide a command line utility and shell .sh script for unix/solaris which support an attribute called –overwrite which help to rename the existing schema name to new schema name. So orabpel schema name now can be changed to orabpel_ST, orabpel_dev etc. and then when user install second instance or SOA Suite10g on same machine under same Database instance it won’t get conflict with existing schema names since existing one got renamed and won’t create any problem during installation and second instance of SOA Suite10g will get installed successfully on same machine using same XE database instance.

Command to run irca.sh or irca.bat

Syntax:
irca.sh [all "<db_host> <db_port> <db_service_name>" <sys_password> overwrite] newSchemaName newSchemaPWD

e.g.
irca.sh all "183.293.291.1 1521 XE" welcome –overwrite orabpel_dev welcome1
irca.cmd orabpel “183.293.291.1 1521 XE" welcome –overwrite orabpel_dev welcome1

above command will rename the existing orabpel/all (three) schema name to orabpel_dev and another SOA Suite10g instance can be installed under same Database instance XE.

Note: After making changes, datasource configuration at application server also need to change at Weblogic or Oracle application server whichever has been used then only new schema name will be linked to newly created SOASuite10g environment.

Objective: This post will help to maximize the usage of hardware, reduce the cost of installing multiple hardware for multiple environments. In every project implementation multiple environment of SOASuite is required for smooth implementation of project for all the phases. This post will help to remove the technical hurdle which comes with SOASuite installer as showed in above screenshot coz user can’t change or select any other schema name for SOASuite10g installation name. SOASuite10g has predefined the schema name for installation purpose but using above steps that situation can be worked out.

Get the list of BPEL Instance and Corelation Identifier value using Corelation identifier name

2011-09-12T22:06:00.000-07:00

Problem: For asynchronous BPEL process where 2 way communication happening, couple of time due to various issue the response which was expected to come at BPEL for an order it won’t come and BPEL process is keep waiting and won’t get resumed until unless will get the response.

To elaborate this problem more in details I want to give one example here e.g. there is one business scenario in prepay/post-paid connection provisioning, Customer has to pass the Credit Check before order provisioning.

Flow is like that BPEL will invoke CRM system (SAP/Siebel/PSFT) and will get immediate response Customer account Id and will set as Customer account id value as correlation id and then will wait for further notification from CRM system for Credit Check Completion. In the mean time CRM system will further interact with Decision Point system which exactly does Credit check then Decision point will send the response to CRM system and CRM system will send the response to BEPL but due to multiple issue CRM to BPEL notification won’t reach till BPEL and order in BPEL is keep waiting.

Solution:

To fix all these orders which are waiting in BPEL for external system response, external system again need to send the notification back to BPEL, for that external system might ask the list of correlation id values for all the orders which is waiting in BPEL, so that external system can send the notification again.

In BPEL any instance which is waiting for response a subscription will be written out in dlv_subscription table along with correlation identifier and its value. Below query will pull out correlation id value based on correlation identifier name for all the waiting instance in BEPL.

select CBI.CIKEY as "BPEL_INSTANCE_ID",

substr(Properties, instr(Properties,'CustId',1,2)+7,9) as "Customer_ACCOUNT_ID",

CBI.Modify_date as "LAST_MODIFY_DATE"

from ORABPEL.CUBE_INSTANCE CBI,

ORABPEL.DLV_SUBSCRIPTION DS

where DS.STATE =0 and

CBI.process_id = 'cm_cpm_submitcreditCheck' and CBI.Revision_tag='1.1.0' and

DS.CIKEY=CBI.CIKEY

Properties: This is column name in dlv_subscription table which store the correlation identifier and its value but the format is very messy and user has to use the string format function to exactly pull out the correlation identifier values.

Also in above query correlation identifier name need to change based upon project design.

Process_Id: BPEL processName and Revision tag also need to change based up project design.

Note: In the above query many thing are project perspective which need be modified for other project. The objective of this post is to give the idea how to pull the CIKEY details where BPEL process is waiting for the response from external system.

Get the list of Stuck orders from orabpel schema

2011-09-12T20:24:00.000-07:00

Problem: For any project for any kind of order processing in BPEL, developer develops multiple BPEL process to perform business process functionality, so that these business process can be reused in other project too.

But I personally seem a situation in production in multiple implementations at different places where order processing get stuck due to message lost scenario. e.g. Once BPEL(A) process will say I have responded but another BPEL(B) process which is waiting to get the response from BPEL A still waiting.

Solution:

Fusion Developer may find many post or blogs where they will get the solution to deal the orders which got stuck in BPEL engine due to message lost scenario.

Out of box BPEL provide an feature to recover a callback message using this path at BPEL Console. BPELConsole>>Administration>>Recover(Callback)

In reality finding a lost message for an order in this GUI will take lots of time coz there is no rich searching option available on this webpage. An operation person who support failed order for Production he/she just want to find out an lost message based upon order id, so that he/she can resubmit that transaction for stuck order but due to limited searching capability in this GUI its very hard to do that. The only search option available to select BPEL process name which is not much useful at all. Also I am sure in many production environment the list of message in this console is very huge, so user can't resubmit everything from this queue.

In best of my knowledge I personally knew few scenarios where an asynchronous BPEL process is waiting for some response from external system and external system already sent the response and corresponding BPEL got resumed based on correlation id and BPEL process is completed successfully but external system keep sending the expected response for same order id using same correlation id again and again, the incoming subsequent message which coming to bpel engine won't get processed since first response for that order id is already processed when second response comes it sit here in this queue and message in this queue get piled up. Its a kind of defect and depend on environment to environment.

Many times due to bulk orders resubmission in BPEL engine many orders get stuck during processing coz delivery thread won’t be able to process all the orders. Message will come and will sit in dlv_message table with the undelivered status and never get picked up. When Customer escalate about his/her orders then Failed order member look into that order and realized this order is the victim of message lost scenario.

To fix this problem there are two ways once way to resubmit all the message from recovery queue but that again will cause the performance issue on production or other option to re trigger the stuck order from BPEL console again.

In case of re trigger the order from BPEL console, first requirement comes to get the list of stuck order which is not easy to get from Cube_Instance table. The reason behind is Cube_Instance table store state value 1 for Running/Waiting/Failed/Stuck instance but user need only stuck orders.

For all above four different state at database level instance value will always be 1 only. Now if any DB user even pull out the instance list select * from orabpel.cube_instance where state=1 will not give the list of stuck orders.

To get the final list of stuck order what need to do that’s what I want to cover here.

In Fusion an order either could be Running/Failed/Waiting/Faulted/Stuck/Aborted/Stale state.

If user will pull out all the orders list from cube_instance where state =1 then all running instance will come out then out of that subtract the orders which are failed as per BAU and has created activity in work_item table. Once order get failed it create an activity in BPEL which can be viewed using Activities console and data stored in Work_item table. Just subtract the list of instance from work_item table which have pending activity using this query select * from orabpel.work_item where state=13. Then finally subtract the list of instance which are waiting for response from external system or from other bpel process and an subscription is written out in dlv_subscription table. User can get the list of waiting instance from dlv_subscription table using this query select * from orabpel.dlv_subscription where state !=1

The final resulted CIKEY will be the list stuck order, based upon index setting as per user project final result can be refined. e.g if BPEL process is setting up order number an index in CI_indexes table then result can be refined using Index_1 column.

Here is the query which will give the stuck orders in orabpel schema.

select cbi.cikey,cbi.process_id, cbi.creation_date, cbi.modify_date,cbi.state, cbi.status, cbi.title,ci.index_1
from orabpel.cube_instance cbi, orabpel.ci_indexes ci,
(select cikey from
(select cikey from orabpel.cube_instance where state =1
minus
select cikey from orabpel.work_item where state =13 ) firstSub
minus
select cikey from orabpel.dlv_subscription where state !=1) secondSub
where secondSub.Cikey=cbi.cikey
and cbi.cikey=ci.cikey and trunc(cbi.modify_date) <= to_timestamp('2011-09-12 23:59:59','YYYY-MM-DD HH24:MI:SS')

The above query might need some changes based up on environment and project design. This query will be helpful for the projects where index_1 has been utilized to store order number. Also modify_date need to be adjusted based upon requirement. Or some db expert might can refine tune the query for best performance.

The objective or this post to give idea how to pull out the list of stuck orders since orabpel.cube_instance table state column won’t store any separate state for stuck orders. State=1 represent multiple state as result it was not easy to pull out only stuck orders list.