BPEL dehydration data store tables and their significance.

Dehydration store is the database where BPEL engine stores all BPEL processes meta data and run time instance data.This data store is installed under db schema- ORABPEL

Meta data includes bpel process descriptor (bpel.xml), human task modelling data etc..

Run time instance data includes process instance records, process activities execution data, invoke and call back xml messages etc..

Table name	Description
CUBE_INSTANCE	Contains one entry for each BPEL instance created. It stores instance meta data information like creation date,last modified date, current state, process id etc. Following are processes state codes and their meaning State Code Closed and Aborted 8 Closed and Cancelled 7 Closed and Completed 5 Closed and Faulted 6 Closed and (Pending or Cancel) 4 Closed and Stale 9 Initiated 0 Open and Faulted 3 Open and Running 1 Open and Suspended 2
CUBE_SCOPE	Stores the scope data for an instance. It stores BPEL scope variable values
INVOKE_MESSAGE	Stores incoming (invocation) messages (messages that result in the creation of an instance). This table only stores the meta data for a message (for example, current state, process identifier, and receive date). Following are message states and their meanings State Description Code CANCELLED Message Processing Cancelled 3 HANDLED Message is processed 2 RESOLVED Message is given to BPEL PM but not yet processed 1 UNRESOLVED Message is not yet given to BPEL PM 0
DLV_MESSAGE	Call back messages are stored here
WORK_ITEM	Stores activities created by an instance. All activities in a BPEL flow have a work_item table. This table includes the meta data for the activity (current state, label, and expiration date (used by wait activities))
SCOPE_ACTIVATION	Scopes that need to be routed/closed/compensated are inserted into this table. In case of system failure, we can pick up and re-perform any scopes that should have been done before the failure
DLV_SUBSCRIPTION	Stores delivery subscriptions for an instance. Whenever an instance expects a message from a partner (for example, the receive or onMessage activity) a subscription is written out for that specific receive activity. Once a delivery message is received the delivery layer attempts to correlate the message with the intended subscription
AUDIT_TRAIL	Stores record of actions taken on an instance. As an instance is processed, each activity writes events to the audit trail as XML
AUDIT_DETAILS	Stores details for audit trail events that are large in size. Audit details are separated from the audit_trail table due to their large size. The auditDetailThreshold property in Oracle BPEL Control under Manage BPEL Domain > Configuration is used by this table. If the size of a detail is larger than the value specified for this property, it is placed in this table. Otherwise, it is placed in the audit_trail table
XML_DOCUMENT	Stores process input and output xml documents. Separating the document storage from the meta data enables the meta data to change frequently without being impacted by the size of the documents
WI_EXCEPTION	Stores exception messages generated by failed attempts to perform, manage or complete a work item. Each failed attempt is logged as an exception message
PROCESS_DESCRIPTOR	Stores BPEL processes deployment descriptor(bpel.xml) Record of events (informational, debug, error) encountered while interacting with a process.
INVOKE_MESSAGE_BIN	Stores invoke payload of a process. This table has foreign key relationship with INVOKE_MESSAGE table
DLV_MESSAGE_BIN	Stores received payload of a call-back process.This table has foreign key relationship with DLV_MESSAGE
WFTASK	Stores human workflow tasks run time meta data like taskid,title,state,user or group assigned, created and updated dates
WFTASKMETADATA	Stores task meta data. Content in this table comes from '.task' file of BPEL project
WFASSIGNEE	Stores task assignee information
WFMESSAGEATTRIBUTE	Stores task input payload parameters
WFATTACHMENT	Stores task attachments
WFCOMMENTS	Stores task comments

Configuring JDev and SOA server 10134 for MS SQL server database connections

Jdeveloper configuration:
By default Jdeveloper works with Oracle databases.This article explains configuring Jdeveloper to work with MS SQL server

1. Download MS SQL Server 2005 JDBC driver or suitable JDBC driver for your MS SQL server
2. Extract  this into your machine (assume program files). It should create folder  called  "Microsoft SQL Server 2005 JDBC Driver" or folder name be different based on your driver version.
3. Search for sqljdbc.jar from above folder and  copy this file into JDeveloper\JDBC\lib folder
4. Close your Jdeveloper if it is opened
5. Open JDeveloper/jdev/bin/jdev.conf file add following entry.
   AddJavaLibPath C:/Program files/Microsoft SQL Server 2000 Driver for JDBC/lib.
Here path points to sqljdbc.jar file. If sqljdbc.jar is residing in other folder than above mentioned, then copy the correct folder path
6. Open command prompt, go to JDEV_HOME/JDev/bin and execute following command
   
   jdev -verbose (This will open JDeveloper)
7. Now go to JDeveloper > Connections > Database Connections > New Database Connection
8. Select Third Party JDBC
9. Specify MS Sql Server User Name, password and optional  Role parameter
10. In connection page specify following
    
    
    - Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
    
    - For class path browse to C:/Program files/Microsoft SQL Server 2000 Driver for JDBC/lib folder, select sqljdbc.jar add it as library.
11. Specify URL as following.
    
    
    jdbc:sqlserver://DBHOSTNAME:;databaseName=MSSQLDBNAME
    
12. Now test the connection

SOA server configuration:
We need to set server CLASSPATH to include database driver jar file. This can be achieved in many ways.

• For standalone installation type, please make an entry into Oracle_Home/bpel/system/appserver/oc4j/j2ee/home/config/server.xml
• For middle tier installation type,please make an entry into Oracle_Home/j2ee/OC4J_BPEL/config/server.xml
  Or
• For standalone installation type, please drop jar file into Oracle_Home/bpel/system/appserver/oc4j/j2ee/home/applib
• For middle tier installation type,please drop jar file into
  Oracle_Home/j2ee/OC4J_BPEL/applib
  

                               

Migrating in-flight BPEL instances from one environment to another

This article applies to Oracle BPEL Process Manager - Version: 10.1.3.3 to 10.1.3.4 and describes how to migrate BPEL processes including in-flight instances running on one environment to a completely new environment.

Lets take below use case to understand how migration can take place.

Use-case:

BPEL is running on a single instance mid-tier against a single instance Oracle database 10.2.0.3.

This will be moved to a new mid-tier server running against a new RAC Oracle database 10.2.0.3.

Please follow below steps to achieve the goal.

1. Install SOA schemas on RAC Database by running IRCA scripts.

2. Install mid-tier and patch it to 10.1.3.3 or above.

3. Shutdown the mid-tier and database.

4. Replace the new ORABPEL Schema (on RAC) with the original ORABPEL schema where BPEL Process instances are Dehydrated.

This can be done by database export and import.

5. Start the mid-tier and database.

You should now be able to see all BPEL process in new BPEL console and the temporary files for BPEL processes are regenerated.

6. All BPEL processes (including one with Human Tasks running with OracleAS Single Sign-On) should have been successfully migrated.

All opened instances should be able to finish and new instances could be started from the same BPEL processes.

- before step (5) you have to edit the BPEL table PROCESS_DESCRIPTOR, field DESCRIPTOR and change the property name wsdlLocation to the new server:port values.

- The whole procedure will work just fine for new instances of deployed BPEL Processes. However for old instances we had to open in the old BPEL Server theHTTP Port where WSDL were publish because migrated opened instances were always trying to reach that server:port when the main BPEL Process call other (inner)BPEL Processes defined on it.

eg: Main BPEL Process "A" is invoked in new server:port WSDL Location (this instance was migrated from the previous server; new instances work just fine).

When the process "A" is dehydrated and calls a second BPEL Process, say process "B", it does the call over the old server:port WSDL location.

We could not figure out how to resolve this issue so we started a listener process (iptables) on the old server that redirect all the trafic to the new server:port. So invocation of old WSDL locations are redirected to the new server:port values. This configuration will have to exist until old instances migrated from the previous server are finished. New instances of BPEL processes do not have this problem.

In step (6) "All opened instances should be able to finish and new instances could be started from the same BPEL processes." is not completely true. Is there any other way to get this working fine without using redirection from old server to new one.