Pages

Tuesday, September 20, 2011

Installing Oracle SOA Suite Extension plug-in in JDeveloper11g to Develop the SCA based Composite application.


Problem:

During SOA Suite11g installation once user installed the JDeveloper 11g.x.x version, developer feels now he/she would be able to develop the Composite application but in reality JDeveloper won’t allow to develop any composite application until unless we will install the SOA Suite Extension plug-in on it.

Many books refer how to develop a new composite application in jdeveloper e.g. go to File>>New>>General>>Application>>Select SOA Application but if you are running Jdeveloper11g first time for composite application then developer won’t see “SOA Application” in above path.


In above screen shot there is no SOA Application is coming since I didn’t install the SOA Suite extension.

Solution:

In order to see the “SOA application” in jdeveloper11g developer has to install the SOA Suite extension using below steps.

Step1: Go to help menu in JDeveloper and look for “Check for Update” sub menu.


Step2:  Once clicked on “Check for Update”, JDeveloper will find out updates for you, and now developer has to check “Oracle Fusion Middleware Product” option and click on next button.



Note: Once JDeveloper try to search for update then it might failed will give error for connecting problem to internet, in such situation developer has to provide the proxy server host name, port name, proxy server authentication username and password(If required), then only user will able to connect to internet from JDeveloper and it will downloaded latest update.

Step3: once developer clicked on next button, a big list possible plug-in will be listed, out of that developer has to find out “Oracle SOA Composite Editor 11.1.1.5.0” and click on next button.


Step4: Once developer will click on next button, this plug-in will get installed on system.



Step5: Click on Finish button. JDevloper must need to restart after that.

Step6: Once restarted, again go to File>>New>>General>>Application>>Select SOA Application.


Objective:

The above info is already covered in Installation Guide for Jdeveloper11g release but its take bit harder to find out the complete process, that’s why I have put the above steps in simplest way in this blog.

Thursday, September 15, 2011

Delete/Purge the data from Orabpel Schema analysis stats

There are three possible way to delete/purge the data from orabpel schema.

1)       Delete Option at Instance Tab Page – At BPEL Console instance tab page user can select all the instances and can delete it but this option is not useful for deleting data in bulk. User can delete only 1000 instance at a time.

2)       Delete all Instance at Administration Tab Page – At BPEL console>>Administration>>Delete All Instance – This option will delete all the instances from Orabpel schema.

3)       Customize or Oracle purge script – Oracle provide the purge script for orabpel schema which is located at $SOAHOME\SOASuite101351\bpel\system\database\scripts\purge_instances_oracle.sql which will delete everything as per sql script definition.


I have tried all above three option in my test environment and want to share my result stats using this post –

Orabpel Schema imp. Tables (task related tables excluded)
RelationShip Key
Instance Tab Page
Administration Tab Page
Custom Purging or Oracle SQL
wi_fault
CIKEY
Not Deleted
Not Deleted
Deleted
audit_trail
CIKEY
Deleted
Deleted
Deleted
audit_details
CIKEY
Deleted
Deleted
Deleted
work_item
CIKEY
Deleted
Deleted
Deleted
ci_indexes
CIKEY
Not Deleted
Not Deleted
Deleted
cube_instance
CIKEY
Deleted
Deleted
Deleted
cube_scope
CIKEY
Deleted
Deleted
Deleted
dlv_subscription
CIKEY
Deleted
Deleted
Deleted
dlv_message
MASSAGE_GUID
Not Deleted
Deleted
Deleted
document_dlv_msg_ref
MASSAGE_GUID, DOCKEY
Not Deleted
Deleted
Deleted
invoke_message
MASSAGE_GUID
Not Deleted
Deleted
Deleted
xml_document
DOCKEY
Not Deleted
Deleted
Deleted


As stated above many tables which related to delivery and invoke message metadata, and xml document, records for those tables won’t get deleted using delete option at instance tab page, Administration tab page delete records from all the tables but again it exclude few tables e.g. ci_indexes, wi_fault and few other tables, the only oracle sql script is the best script or customize script is the best option to purge the data from orabpel schema.

Note: Above testing I have done on SoaSuite10g environment, database version was oracle10.0.2.4 and SOASuite10.1.3.5.1

Purging/Delete operation is very critical operation, so before doing it please make sure DBA has taken the backup of Database, so that if required he/she can restore data back.





Tuesday, September 13, 2011

Create multiple SoaSuite10g instance under one Database Instance


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


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


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.