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.