Oracle SOA Suite 11g and 12c: Determining composite dependencies to the level of individual operations

In large companies, often there are many services and dependencies between services. It is important to track service dependencies in order to for example estimate the impact of changes. Design documents or architecture views can be used for this but as everybody knows, there is often a gap between theory and practice (design and implementation).

In this blog post I provide code to determine dependencies between composites to the level of operation calls. In order to achieve this, I'll parse the composite.xml files, JCA files (used by adapters) and also the BPEL and BPMN files in order to determine the operations. The script can be used for SOA Suite 11g and 12c composites.

The above picture shows different parts of which a composite is composed and how they are linked. The script first determines references. The references specify which external services are called. Then by using wires, the relevant components are determined. Based on the component type, specific logic is used to extract the operation. Not shown in this picture is how database dependencies can also be determined by the script by parsing the JCA files specified in the reference. If you're in a hurry, you can go to the 'Executing the script' part directly and skip the explanation.

DbAdapter, connection factories, connection pools and datasources

Going from a BPMN,BPEL or Mediator process instance to the database is not a short road. On this road, several abstraction layers need to be passed. If the configuration for a layer is not in accordance with the configuration of the other layers, problems can arise. This becomes apparent when the database load is high.

In this post I will describe some problems. First I cause a problem  and then I describe how it can be avoided.

The Oracle SOA Suite DbAdapter has connection factories. These connection factories have connection pools.

The connection factories also refer to datasources

These datasources also have connection pools

Setup

What happens when the connection pool of the adapter is insufficient and what happens if the connectionpool of the datasource is insufficient?

In order to test the situations, a large number of open database connections is needed. I needed a procedure which would not reply immediately making the calling process wait for a response so I could instantiate a large number of calling processes and keep the connections open for a while. I created a small database procedure to wait a while;

create or replace
procedure waitawhile as
 stime TIMESTAMP(9);
 etime TIMESTAMP(9);
begin
  stime := SYSTIMESTAMP;
  dbms_lock.sleep( 60 );
  etime := SYSTIMESTAMP;
  dbms_output.put_line(etime-stime);
end;

I created a BPEL process which did nothing but call this procedure. The default settings were;

Database adapter connection factory connection pool settings
Initial capacity: 50
Max capacity: 200

Datasource connection pool setting
Initial capacity: 1
Max capacaty: 15

Results

When starting 500 processes (SOAP UI) which all use the connection pool, the following errors occur;

No resources currently available


Connection factory;

Datasource;

Errors in log file;

BINDING.JCA-11812
Interaction processing error.
Error while processing the execution of the WAITAWHILE API interaction.
An error occurred while processing the interaction for invoking the WAITAWHILE API. Cause: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.1.v20111018-r10243): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Internal error: Cannot obtain XAConnection weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool testuser to allocate to applications, please increase the size of the pool and retry..
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResourceInternal(ResourcePoolImpl.java:591)
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:343)
at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:471)
at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:363)

As you can see, the datasource is overloaded. The connection factory of the DbAdapter doesn't show problems. Increasing the datasource might help solve this issue. Do keep in might that you might encounter the bottlenecks described below.

java.sql.SQLException: Connection closed

When increasing the datasource connection pool size to 300, the following occurred;

Connection factory;

Datasource;

The log file when performing the test without resetting the datasource and updating the database adapter

[2012-11-16T04:17:11.257-08:00] [AdminServer] [ERROR] [] [oracle.soa.bpel.engine] [tid: orabpel.invoke.pool-4.thread-18] [userId: <anonymous>] [ecid: 11d1def534ea1be0:4e20b7b7:13b089a2423:-8000-0000000000002eec,1:31310] [APP: soa-infra] Attempt (1/2): caught GLOBAL_RETRY exception. Retry after 3 seconds.
[2012-11-16T04:17:11.264-08:00] [AdminServer] [ERROR] [] [oracle.soa.bpel.engine.dispatch] [tid: orabpel.invoke.pool-4.thread-5] [userId: <anonymous>] [ecid: 11d1def534ea1be0:4e20b7b7:13b089a2423:-8000-0000000000002eeb,1:31308] [APP: soa-infra] database communication failure[[
java.sql.SQLException: Connection closed
    at weblogic.jdbc.wrapper.JTAConnection.getXAConn(JTAConnection.java:213)
    at weblogic.jdbc.wrapper.JTAConnection.checkConnection(JTAConnection.java:84)
    at weblogic.jdbc.wrapper.JTAConnection.checkConnection(JTAConnection.java:74)
    at weblogic.jdbc.wrapper.Connection.preInvocationHandler(Connection.java:100)
    at weblogic.jdbc.wrapper.Connection.prepareStatement(Connection.java:545)

The most likely cause here is the sessions parameter of the database. See; https://forums.oracle.com/forums/thread.jspa?threadID=1025872 on how to set this. It happens often that this parameter is not set high enough. Especially on SOA Infra databases.

JCA Binding Component is unable to create an outbound JCA (CCI) connection.

The log file when performing the same test as above after resetting the datasource and updating the database adapter

[2012-11-16T04:34:18.471-08:00] [AdminServer] [ERROR] [] [oracle.soa.bpel.engine.dispatch] [tid: orabpel.invoke.pool-4.thread-20] [userId: <anonymous>] [ecid: 11d1def534ea1be0:4e20b7b7:13b089a2423:-8000-00000000000034f7,1:32093] [APP: soa-infra] failed to handle message[[
com.oracle.bpel.client.BPELFault: faultName: {{http://schemas.oracle.com/bpel/extension}bindingFault}
messageType: {{http://schemas.oracle.com/bpel/extension}RuntimeFaultMessage}
parts: {{
summary=<summary>Exception occured when binding was invoked.
Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'WaitAWhileDB' failed due to: JCA Binding Component connection issue.
JCA Binding Component is unable to create an outbound JCA (CCI) connection.
CallWaitAWhile:WaitAWhileDB [ WaitAWhileDB_ptt::WaitAWhileDB(InputParameters) ] : The JCA Binding Component was unable to establish an outbound JCA CCI connection due to the following issue: BINDING.JCA-12510
JCA Resource Adapter location error.
Unable to locate the JCA Resource Adapter via .jca binding file element &lt;connection-factory/>
The JCA Binding Component is unable to startup the Resource Adapter specified in the &lt;connection-factory/> element:  location='eis/DB/testuser'.
The reason for this is most likely that either
 1) the Resource Adapters RAR file has not been deployed successfully to the WebLogic Application server or
 2) the '&lt;jndi-name>' element in weblogic-ra.xml has not been set to eis/DB/testuser. In the last case you will have to add a new WebLogic JCA connection factory (deploy a RAR).
Please correct this and then restart the Application Server

Please make sure that the JCA connection factory and any dependent connection factories have been configured with a sufficient limit for max connections. Please also make sure that the physical connection to the backend EIS is available and the backend itself is accepting connections.
".
The invoked JCA adapter raised a resource exception.
Please examine the above error message carefully to determine a resolution.
</summary>

In this case the connection factory is the limiting factor. Increasing the number connections in the connection pool of the connection factory might help with this issue.

Distributed transaction error and binding exception

ORA-00164: distributed autonomous transaction disallowed within migratable distributed transaction / ORA-24777: use of non-migratable database link not allowed

Situation

A BPEL process used an XA datasource with Global transaction support to call a PL/SQL function. This function used a private database link to insert data in a local table. The following exceptions occurred;

ORA-24777: use of non-migratable database link not allowed

when using pragma autonomous transaction in the function;

ORA-00164: distributed autonomous transaction disallowed within migratable distributed transaction

Splitting the fetching of data from the view and the inserting in the local table by using collections, did not help. Also adding commit statements made no difference.

Solution

The best solution is not to use an XA datasource. Other solutions include using a shared public database link or setting the database server to MTS (this is not advisable!).

Below illustrates how to configure a datasource not to support distributed transactions.

In the JDBC datasource configuration, Configuration, Connection pool, use the following driver for Oracle databases; oracle.jdbc.OracleDriver.

Under Configuration, transactions; disable Supports Global Transactions.

Under the DbAdapter configuration, Outbound Connection Pools, (specific) connection factory; make sure under properties that the dataSourceName is filled and not xADataSourceName.

Under transaction support specify; No transaction

I had to restart the domain for the changes to become effective.

Exception occured when binding was invoked

Situation

After adding a ConnectionFactory to the DbAdapter, I notived the JNDI name was incorrect. I changed the JNDI name. I made sure a DataSource was created and specified by the ConnectionFactory. After using a BPEL process to connect to the database (using the ConnectionFactory specified in the DbAdapter), I got the following error.

Error

<bindingFault>
<part  name="summary">
<summary>Exception occured when binding was invoked. Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'censored_DB' failed due to: Could not create/access the TopLink Session. This session is used to connect to the datastore. Caused by javax.resource.spi.InvalidPropertyException: Missing Property Exception. Missing Property: [ConnectionFactory> xADataSourceName or dataSourceName]. You may have set a property (in _db.jca) which requires another property to be set also. Make sure the property is set in the interaction (activation) spec by editing its definition in _db.jca. . You may need to configure the connection settings in the deployment descriptor (i.e. DbAdapter.rar#META-INF/weblogic-ra.xml) and restart the server. This exception is considered not retriable, likely due to a modelling mistake. ". The invoked JCA adapter raised a resource exception. Please examine the above error message carefully to determine a resolution. </summary>
</part>
<part  name="detail">
<detail>Missing Property Exception. Missing Property: [ConnectionFactory> xADataSourceName or dataSourceName]. You may have set a property (in _db.jca) which requires another property to be set also. Make sure the property is set in the interaction (activation) spec by editing its definition in _db.jca. </detail>
</part>
<part  name="code">
<code>null</code>
</part>
</bindingFault>

Solution

I had not specified any unusual properties in the .jca files. At first I of course checked I had correctly specified the DataSource in the connectionfactory. I restarted the JDBC connection pool and updated the DbAdapter. This did not help. Recreating the ConnectionFactory however, solved my issue.