SOA@Oracle SCA, BPEL, BPM & Service Bus: 3/1/08

Friday, March 21, 2008

Purging BPEL instances

Here is a script that can be used to purge Oracle ESB instances from the ORAESB schema. I created the script that it can be used by calling a Pl/SQL procedure, to delete instances older than a 'x' days; for a particular bpel process ; version and domain:

REM
REM Name:
REM
REM purge_bpel_dehydration.sql
REM
REM Description:
REM
REM This script purges data from the BPEL dehydration store
REM
REM Usage:
REM
REM purge_bpel_dehydration
REM
REM p_domain DEFAULT '%' - the name of the BPEL domain
REM p_process_name DEFAULT '%' - the name of the BPEL process
REM p_revision DEFAULT '%' - the version of the BPEL process
REM p_older_than DEFAULT 999 - delete instances older than # days
REM p_state DEFAULT -1 - the state of the process
REM
REM Example:
REM
REM sqlplus orabpel/pwd@database
REM exec purge_bpel_dehydration(10);
REM commit;
REM

CREATE OR REPLACE
PROCEDURE purge_bpel_dehydration
(
p_domain IN VARCHAR2 DEFAULT '%'
, p_process_name IN VARCHAR2 DEFAULT '%'
, p_revision IN VARCHAR2 DEFAULT '%'
, p_older_than IN NUMBER DEFAULT 999
, p_state IN NUMBER DEFAULT -1
)
IS
CURSOR c
(
b_domain IN VARCHAR2
, b_process_name IN VARCHAR2
, b_revision IN VARCHAR2
, b_older_than IN NUMBER
, b_state1 IN NUMBER
, b_state2 IN NUMBER
)
IS
SELECT
cie.cikey cikey
, dmn.domain_id domain_id
, cie.process_id process_id
, cie.revision_tag revision_tag
, cie.modify_date modify_date
, cie.domain_ref domain_ref
, cie.state process_status
FROM
cube_instance cie
, domain dmn
WHERE cie.domain_ref = dmn.domain_ref
AND cie.state >= b_state1
AND cie.state <= b_state2
--
-- the name of the domain
AND dmn.domain_id LIKE b_domain
--
-- code 5 means completed
AND cie.modify_date < TRUNC(sysdate)-b_older_than
--
AND cie.process_id LIKE b_process_name
AND cie.revision_tag LIKE b_revision;

l_state1 number;
l_state2 number;
BEGIN
IF p_state = -1
THEN
l_state1 := -1;
l_state2 := 999;
ELSE
l_state1 := p_state;
l_state2 := p_state;
END IF;
FOR r in c
(
p_domain
, p_process_name
, p_revision
, p_older_than
, l_state1
, l_state2
)
LOOP
DBMS_OUTPUT.PUT_LINE
(
'Purge '||r.process_id||'('||r.revision_tag||')'||
' at '||to_char(r.modify_date, 'YYYY-MM-DD HH24:MI:SS')
);
collaxa.DELETE_CI(r.cikey);
--
delete from wftask wfn where wfn.instanceid = r.cikey;
--
END LOOP;
--
-- delete invoke calls
-- invoked messages
--
DELETE FROM invoke_message ime
WHERE ime.domain_ref in
(
SELECT dmn.DOMAIN_REF
from domain dmn
WHERE dmn.domain_id LIKE p_domain
)
AND ime.state > 1
AND ime.process_id LIKE p_process_name
AND ime.revision_tag LIKE p_revision
AND ime.receive_date < TRUNC(sysdate)-p_older_than;
--
DBMS_OUTPUT.PUT_LINE ('-> #invoke msg '||SQL%ROWCOUNT);
--
-- delete callback calls
--
DELETE FROM dlv_message dme
WHERE dme.domain_ref IN
(
SELECT dmn.DOMAIN_REF
from domain dmn
WHERE dmn.domain_id LIKE p_domain
)
AND dme.state > 1
AND dme.process_id LIKE p_process_name
AND dme.revision_tag LIKE p_revision
AND dme.receive_date < TRUNC(sysdate)-p_older_than;
--
DBMS_OUTPUT.PUT_LINE ('-> #callback msg '||SQL%ROWCOUNT);
END;
/

SHOW ERRORS

Purging ESB instances

Here is a script that can be used to purge Oracle ESB instances from the ORAESB schema. I created the script that it can be used by calling a Pl/SQL procedure, to delete instances older than a 'x' days:

REM
REM Name:
REM
REM purge_esb_instances.sql
REM
REM Description:
REM
REM This script purges data from the BPEL dehydration store
REM
REM Usage:
REM
REM purge_esb_instances
REM
REM p_older_than DEFAULT 999 - delete instances older than # days
REM
REM Example:
REM
REM sqlplus oraesb/pwd@database
REM exec purge_esb_instances(10);
REM commit;
REM

CREATE OR REPLACE
PROCEDURE purge_esb_instances
(
p_older_than IN NUMBER DEFAULT '999'
)
IS
diff NUMBER;
BEGIN
diff := (
(TRUNC(SYSDATE)-p_older_than) - TO_DATE('01/01/1970','MM/DD/YYYY')
)*24*60*60*1000;

DELETE ESB_ACTIVITY
WHERE ID IN
(
SELECT ID
FROM ESB_ACTIVITY A
WHERE EXISTS
(
SELECT FLOW_ID
FROM ESB_ACTIVITY B
where TIMESTAMP < diff
AND A.FLOW_ID = B.FLOW_ID
)
);
--
DELETE ESB_TRACKING_FIELD_VALUE
WHERE ACTIVITY_ID IN
(
SELECT ID
FROM ESB_ACTIVITY A
WHERE EXISTS
(
SELECT FLOW_ID
from ESB_ACTIVITY B
WHERE TIMESTAMP < diff
AND A.FLOW_ID = B.FLOW_ID
)
);
--
DELETE FROM ESB_FAULTED_INSTANCE
WHERE ACTIVITY_ID IN
(
SELECT ID
FROM ESB_ACTIVITY A
WHERE EXISTS
(
SELECT FLOW_ID
FROM ESB_ACTIVITY B
WHERE TIMESTAMP < diff
AND A.FLOW_ID = B.FLOW_ID
)
);
--
DELETE FROM ESB_TRANSACTION_STATUS
WHERE TIMESTAMP < diff;
--
DELETE FROM ESB_INSTANCE_RELATION_XML
WHERE FLOW_ID IN
(
SELECT FLOW_ID
FROM ESB_ACTIVITY A
WHERE EXISTS
(
SELECT FLOW_ID
FROM ESB_ACTIVITY B
WHERE TIMESTAMP < diff
AND A.FLOW_ID = B.FLOW_ID
)
);
--
IF(SQL%ROWCOUNT >0)
THEN
DELETE FROM ESB_RELATION_XML A
WHERE NOT EXISTS
(
SELECT RELATION_XML_ID
FROM ESB_INSTANCE_RELATION_XML
);
--
DELETE ESB_SERVICE_RELATION
WHERE RELATION_XML_ID IN
(
SELECT ID
FROM ESB_RELATION_XML
WHERE NOT EXISTS
(
SELECT RELATION_XML_ID
FROM ESB_INSTANCE_RELATION_XML
WHERE ID = RELATION_XML_ID
)
);
END IF;
END;
/

SHOW ERRORS

Wednesday, March 12, 2008

Getting started with Oracle SOA, BPEL, ESB, AIA

As I do SOA projects for a few years, many users are just entering this service oriented architecture world. For me the technology is common sense and I expect often that SOA/BPEL/ESB is well known. But off-course this is not the case. When I started working with SOA/BPEL/ESB it was, and still is, a steep learning curve. The Oracle developers world comes from a traditional approach; thinking in functions and entities and since Java came to us, we are even thinking about objects and inherited them.

With SOA it is different, you have to think about processes; functional and technical. The gap between the functional people and technical people is reduced. Processes are part of the business, otherwise they do not have any reason for existence. While processes are running, the can fail, wait on other process, they finish, started, run in parallel etc. So SOA is a mind shift from the traditional development approach point of view.

Last year Oracle announce the Oracle Application Integration Architecture. This is an add-on on the Oracle SOA Suite, which defines an abstract message layer that can be used to interface various systems; Siebel / SAP / Protal BRM / Cordys via a centralized system (Oracle SOA + AIA). This messages layers are created specific for different industries, for example the telecommunication market.

To start with Oracle SOA here are some of useful links:

Oracle SOA Suite general

Oracle SOA Suite software

Oracle Application Integration Architecture

Documentation

Installation

Performance

API

Tutorials

Oracle Blogs

Oracle Forum

Oracle General

Useful tools

Webservice standards

Monday, March 10, 2008

BPEL Fault Policies; best practise

Introduction
Apart of the fault handling within the BPEL processes itself, a new feature is available in BPEL. This is called the fault handling. Previous the fault must all be handled within each process. An article on this is written in:

OraBPEL Technote #7

The new feature is documented in the release note of Oracle BPEL 10.1.3.3, in the chapter "Fault Management Framework".

BPEL 10.1.3.3 Release notes

Another nice article on BPEL fault handling with an example is written here.

Fault Business Rules
The fault policy is implemented for all the BPEL processes based on the following rules:

Existing exception handling in the BPEL processes will be implemented as defined in the standards for that application.
A distinction will be made in fault policies for synchronous and a-synchronous policies.
The policies will be implemented at Oracle BPEL Process level.
The default fault policy, valid for all BPEL processes, will be implemented as if there was no fault policy.

Fault Policies

DefaultFaultPolicy
The default policy will catch all faults message and pass it back to the process that raises the fault. This is defined as follows:

  DefaultPolicy.xml
=============
<?xml version="1.0" encoding="UTF-8"?>
<faultPolicy
    version="2.0.1"
    id="DefaultPolicy"
    xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns="http://schemas.oracle.com/bpel/faultpolicy"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<!--
  This section describes default conditions.
  Build more conditions with faultName, test and action
-->
<!--
  CONDITIONS
-->
<Conditions>
  <faultName>
   <condition>
      <action ref="ora-rethrow-fault"/>
    </condition>
  </faultName>
</Conditions>
<!--
  ACTIONS
-->
<Actions>
<!--
  This is an action will bubble up the fault
-->
  <Action id="ora-rethrow-fault">
    <rethrowFault/>
  </Action>
</Actions>
</faultPolicy>

The DefaultPolicy.xml file should be created in the following directory

  $ORACLE_HOME/domain/<domain-name>/config/fault-policies

For example

  $ORACLE_HOME/domain/default/config/fault-policies

The bind the DefaultPolicy.xml file as default fault policy handler. The following file must be created and filled with the following content:

  $ORACLE_HOME/domain/<domain-name>/config/fault-bindings.xml

For example


$ORACLE_HOME/domain/default/config/fault-bindings.xml
===================================================
<?xml version="1.0" encoding="UTF-8"?>
<faultPolicyBindings
    version="2.0.1"
    xmlns="http://schemas.oracle.com/bpel/faultpolicy"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<!--
    DefaultPolicy is defined
    in ./fault-policies/DefaultPolicy.xml
-->
  <process faultPolicy="DefaultPolicy"/>
  <partnerLink faultPolicy="DefaultPolicy"/>
</faultPolicyBindings>

FaultPolicySynchronous
The fault policy for synchronous process is as follows. When a binding or remote fault occurs, it will retry calling this invoke/partnerlink again. It will retry this 5 times with an interval of 4 seconds. This means that if after 20 seconds the retry was still failing, it executes a re-thrown. Meaning that the fault is passed back to the process. The process van now handle the fault in his normal exception handling.


FaultPolicySynchronous.xml
==========================
<?xml version="1.0" encoding="UTF-8"?>
<faultPolicy
    version="2.0.1"
    id="FaultPolicySynchronous"
    xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns="http://schemas.oracle.com/bpel/faultpolicy"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<!--
  This section describes fault conditions.
  Build more conditions with faultName, test and action
-->
<!--
  CONDITIONS
-->
<Conditions>
<!--
  Fault if wsdlRuntimeLocation is not reachable
-->
  <faultName
      xmlns:bpelx="http://schemas.oracle.com/bpel/extension"
      name="bpelx:remoteFault">
    <condition>
      <action ref="ora-retry"/>
    </condition>
  </faultName>
<!--
  Fault if location port is not reachable
-->
  <faultName
    xmlns:bpelx="http://schemas.oracle.com/bpel/extension"
    name="bpelx:bindingFault">
    <condition>
      <action ref="ora-retry"/>
    </condition>
  </faultName>
</Conditions>
<!--
  ACTIONS
-->
<Actions>
<!--
  This action will attempt 5 retries with intervals of 4 seconds
  retry after 4, 8, 12, 16, 20 seconds
-->
  <Action id="ora-retry">
    <retry>
      <retryCount>5</retryCount>
      <retryInterval>4</retryInterval>
      <retryFailureAction ref="ora-rethrow-fault"/>
    </retry>
  </Action>
<!--
  This is an action will cause a replay scope fault
-->
  <Action id="ora-replay-scope">
    <replayScope/>
  </Action>
<!--
  This is an action will bubble up the fault
-->
  <Action id="ora-rethrow-fault">
    <rethrowFault/>
  </Action>
<!--
  This is an action will mark the work item
  to be "pending recovery from console"
-->
  <Action id="ora-human-intervention">
    <humanIntervention/>
  </Action>
<!--
  This action will cause the instance to terminate
-->
  <Action id="ora-terminate">
    <abort/>
  </Action>
</Actions>
</faultPolicy>

Copy the FaultPolicySynchronous.xml into the $ORACLE_HOME/bpel/domains/<domain-name>/config/fault-policies directory.

FaultPolicyASynchronous
The fault policy for a-synchronous process is as follows. When a binding or remote fault occurs, it will retry calling this invoke/partner link again. It will retry this 10 times with an interval of 2 seconds, growing exceptional. This means that 9 attempts with increased intervals of 2 seconds, it retries after 2, 4, 8, 16, 32, 64, 128, 256, 512, 1024 seconds, this will result in a duration of 2046 seconds; approx 34 minutes.

If after 34 minutes the retry was still failing, it executes a human-intervention. Meaning that the fault is waiting for human input. In the Oracle BPEL Console, on the activation tab, the processes is waiting on an action of the administrator.

  FaultPolicyASynchronous.xml
==========================
<?xml version="1.0" encoding="UTF-8"?>
<faultPolicy
    version="2.0.1"
    id="FaultPolicyASynchronous"
    xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns="http://schemas.oracle.com/bpel/faultpolicy"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<!--
  This section describes fault conditions.
  Build more conditions with faultName, test and action
-->
<!--
  CONDITIONS
-->
<Conditions>
<!--
  Fault if wsdlRuntimeLocation is not reachable
-->
  <faultName
    xmlns:bpelx="http://schemas.oracle.com/bpel/extension"
    name="bpelx:remoteFault">
    <condition>
      <action ref="ora-retry"/>
    </condition>
  </faultName>
<!--
  Fault if location port is not reachable
-->
  <faultName
    xmlns:bpelx="http://schemas.oracle.com/bpel/extension"
    name="bpelx:bindingFault">
    <condition>
      <action ref="ora-retry"/>
    </condition>
  </faultName>
</Conditions>
<!--
  ACTIONS
-->
<Actions>
<!--
  This action will attempt 9 retries with increased intervals of 2 seconds
  retry after 2, 4, 8, 16, 32, 64, 128, 256, 512 seconds,
  this will result in a duration of 1022 seconds; approx 17 minutes
-->
  <Action id="ora-retry">
    <retry>
      <retryCount>9</retryCount>
      <retryInterval>2</retryInterval>
      <exponentialBackoff/>
      <retryFailureAction ref="ora-human-intervention"/>
    </retry>
  </Action>
<!--
  This is an action will cause a replay scope fault
-->
  <Action id="ora-replay-scope">
    <replayScope/>
  </Action>
<!--
  This is an action will bubble up the fault
-->
  <Action id="ora-rethrow-fault">
    <rethrowFault/>
  </Action>
<!--
  This is an action will mark the work item to be "pending recovery from console"
-->
  <Action id="ora-human-intervention">
    <humanIntervention/>
  </Action>
<!--
  This action will cause the instance to terminate
-->
  <Action id="ora-terminate">
    <abort/>
  </Action>
</Actions>
</faultPolicy>

Copy the FaultPolicyASynchronous.xml into the $ORACLE_HOME/bpel/domains/<domain-name>/config/fault-policies directory.

Implementation
The fault policies are read during startup of the Oracle BPEL PM. This means that new fault policies or policies that are changed can only be made available by restarting the BPEL PM.

All processes should be aware of the new fault policy mechanism. This means that synchronous process should use the "FaultPolicySynchronous" policy. A-Synchronous process should use the "FaultPolicyASynchronous" policy.

To implement the fault policy in a BPEL process, add the following lines in the BPEL.XML file for each process, just before the "</BPELProcess>" tag:

Synchronous processes:

  <faultPolicyBindings>
  <process faultPolicy="FaultPolicySynchronous"/>
</faultPolicyBindings>

A-Synchronous processes:

  <faultPolicyBindings>
  <process faultPolicy="FaultPolicyASynchronous"/>
</faultPolicyBindings>

Saturday, March 01, 2008

Manual redeploy AIA environment.

Here are the steps to redeploy the various AIA components. Normally this is done via the runInstaller with a nice GUI. But sometimes you need to do this manually.

This can be done as follows. I assume that the Oracle SOA Suite and Oracle AIA were already deployed and configured, in $ORACLE_HOME and in $AIA_HOME

The steps to be takes is as follows:

  cd $AIA_HOME/bin
aiaenv.sh

cd $AIA_HOME/install/scripts

# If you need new common objects (database)
export DEPLOY_COMP=oracle.aia.common
ant -buildfile Deploy.xml

# Reinstall CAVS
cd $AIA_HOMEinstall/conf/cavs
ant

cd $AIA_HOME/install/scripts
# If you use billingcare
export DEPLOY_COMP=oracle.aia.billingcare
ant -buildfile Deploy.xml

# If you use revenue
export DEPLOY_COMP=oracle.aia.revenue
ant -buildfile Deploy.xml

# If you use order to billing
export DEPLOY_COMP=oracle.aia.order2bill
ant -buildfile Deploy.xml

Note 1: For Microsoft Windows replace the '/' by '\', the '$' with '%' and the '.sh' files with '.bat' extension

Note 2: The task 'oracle.aia.common' will recreate all the common objects including the database object. Verify if you need to execute this task.

Deploy AIA BPEL/ESB as non Oracle account

Some administrators of servers will not give the developer access to the application server as owner 'oracle'. They use this account for installation and configuration of the Oracle software. More often this account is shared along the servers, so the password is the same. In this case they will not 'give' the developer access.

If you have access to such a server with another account as 'oracle', you will notice that executing the build.xml for deployment will not work. The tasks in the build.xml is changing some files in the Oracle directory tree.

To deploy an AIA specific component, such ESB service or BPEL process, on the Linux machine, as a non oracle owner is possible. The ANT script along with the ESB service, does need specific read/write settings. Under the user you are logged on the application server, make that this non-oracle user has the right for read/write: $AIA_HOME/bin/bsracces.log

$AIA_HOME/install
$AIA_HOME/install/scripts/bsrwsdl.properties
$AIA_HOME/config/

$ORACLE_HOME/Apache/Apache/htdocs/AIAComponents/
$ORACLE_HOME/bpel/domains/default/config/fault-policies/

Now you are able to execute the 'build.xml' files supplied with the components of AIA.