JDBC from the Oracle Service Bus

There are different ways to do database calls from the Oracle Service Bus. In this blog post I look at several methods.

The methods looked at;
1 using an external webservice (without OSB)
2 using an external webservice proxied by the OSB

3 using the fn-bea:execute-sql function from the OSB

4 using the JCA DbAdapter from the OSB provided by Oracle as part of Oracle SOA Suite

Monitoring DataSources on Weblogic

As an Oracle SOA developer, I've often heard the phrase; 'BPEL doesn't work!'. Almost always the cause can be found in backend systems which do not function as expected. This error becomes visible when executing a service which uses a specific resource. When people start complaining about BPEL, usually this is an indication you should work on process feedback and error handling so the responsible party can quickly be identified. A trial and error mechanism is however often not what you want. A dashboard or script to monitor backend databases can help prevent such issues.

Often development and system test databases are not monitored as thoroughly as acceptance test or production environments. To be able to quickly identify for example a database which is malfunctioning (for example put down for maintenance without informing the developers) it is useful to have some tools and scripts available which you can run for the occasion. Usually this is quicker then using the Enterprise Manager. This is especially useful in complex environments where multiple systems are linked. In these scripts/tools, it is not a good idea to have the databases/users/passwords hardcoded, because that would require maintenance of the scripts in case of changes and as a lazy developer you of course don't want that.

In this article I will describe two possible options for monitoring DataSources on Weblogic servers.

- The first option is a servlet which uses JNDI to obtain JDBC DataSources. This has the drawback that if the DataSource is not loaded correctly or has been disabled, it cannot be looked up using JNDI and is not visible. It can however also be used when the Db/Aq adapter is not used. A servlet can be accessed by anyone, reducing the amount of technical knowledge required to monitor the databases.

- The second option is by using WLST to obtain DataSources defined in the DbAdapter/AqAdapter and provide statistics. This is specific to the Db/Aq adapter and it's a WLST script, so a Middleware installation and login credentials to the server are required in order to execute it.

Both methods query for available DataSources. The DataSource is used so no usernames/passwords/hostnames/sids etc are required.

Implementation

Java

The below servlet does a JNDI lookup of JDBC DataSources and does a 'select sysdate from dual' on them. If the DataSource is not available (can not be looked up via JNDI), it will not appear in the list. If for example a tablespace is full or an account is locked, you will however see it in the list as NOK (short for Not OK). It has not been extensively tested in error situations!

Output of the servlet can be for example;

 

When I lock the testuseraccount and reset the connectionpool;

Below is the servlet code. It can of course easily be improved (some people like colors and nice layouts while I tend to focus on functionality).

package ms.testapp;

import java.io.IOException;
import java.io.PrintWriter;
import java.io.StringWriter;

import java.sql.Connection;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.Hashtable;

import javax.naming.Binding;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingEnumeration;
import javax.naming.NamingException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import javax.sql.DataSource;

public class CheckDb extends HttpServlet {

    @SuppressWarnings("compatibility:-5693855291723951046")
    private static final long serialVersionUID = 1L;
    public CheckDb() {
        super();
    }
    public void doGet(HttpServletRequest request,
        HttpServletResponse response) throws ServletException,
            IOException {
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
        out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0 " +
                   "Transitional//EN\">\n" +
                "<HTML>\n" +
                "<HEAD><TITLE>Datasource status</TITLE></HEAD>\n" +
                "<BODY>\n" +
                listJDBCContextTable
                () + "</BODY></HTML>");
    }
    private Context getContext() throws NamingException {
        Hashtable myCtx = new Hashtable();
        myCtx.put(Context.INITIAL_CONTEXT_FACTORY,
                 "weblogic.jndi.WLInitialContextFactory");
        Context ctx = new InitialContext(myCtx);
        return ctx;
    }
    private String checkDataSource(DataSource ds) {
        try {
            Connection conn = ds.getConnection();
            Statement st = conn.createStatement();
            st.execute("select sysdate mydate from dual");
            st.getResultSet().next();
            Date mydate = st.getResultSet().getDate("mydate");
            conn.close();
            String date = mydate.toString();
            if (date.length() == 10 && date.indexOf("-") == 4 && date.
                lastIndexOf("-") == 7) {
                return "OK";
            } else {
                return "NOK";
            }
        } catch (Exception e) {
            return "NOK"; //getStackTrace(e);
        }
    }
    private static String getStackTrace(Throwable e) {
        StringWriter sw = new StringWriter();
        PrintWriter pw = new PrintWriter(sw);
        e.printStackTrace(pw);
        return sw.toString();
    }
    private String listJDBCContextTable() {
        String output = "<table>";
        ArrayList<String> tab = new ArrayList<String>();
        String line = "";
        try {

           tab = listContext((Context)getContext().lookup("jdbc"), "", tab);
            Collections.sort(tab);

           for (int i = 0; i < tab.size(); i++) {
                output += tab.get(i);
            }
            output += "</table>";
            return output;
        } catch (NamingException e) {
            return getStackTrace(e);
        }
    }
    private ArrayList<String> listContext(Context ctx, String indent,
        ArrayList<String> output) throws NamingException {
        String name = "";
        try {
            NamingEnumeration list = ctx.listBindings("");
            while (list.hasMore()) {
                Binding item = (Binding)list.next();
                String className = item.getClassName();
                name = item.getName();
                if (!(item.getObject() instanceof DataSource)) {
                    //output = output+indent + className + " " + name+"\n";
                } else {
                    output.add("<tr><td>" + name + "</td><td>" +
                              checkDataSource((DataSource)item.getObject()) +
                              "</td></tr>");
                }
                Object o = item.getObject();
                if (o instanceof javax.naming.Context) {
                    listContext((Context)o, indent + " ", output);
                }
            }
        } catch (NamingException ex) {
            output.add("<tr><td>" + name + "</td><td>" + getStackTrace(ex) +
                      "</td></tr>");
        }
        return output;
    }
}

You can download the JDev 11.1.1.6 project here; https://dl.dropbox.com/u/6693935/blog/DbUtils.zip 

Also I found that not all DataSources allow remote JDBC calls such as the MDS DataSource. When using a servlet, this is not a problem since the Java code runs on the server. When running a piece of Java code locally (from your laptop for example) however, it will not work and will throw; java.lang.UnsupportedOperationException: Remote JDBC disabled.

WLST

The below script is based on http://albinoraclesoa.blogspot.nl/2012/06/monitoring-jca-adapters-through-wlst.html and http://davidmichaelkarr.blogspot.nl/2008/10/make-wlst-scripts-more-flexible-with.html and was created by Marcel Bellinga. It contains an example on how to pass arguments to a WLST script and how to query / test DataSources from a WLST script. The DataSources for which statistics are printed, are determined by querying the DbAdapter and AqAdapter connectionpools.

import sys
import os
from java.lang import System

import getopt

user        = ''
credential  = ''
host        = ''
port        = ''
targetServerName  = ''

def usage():
    print "Usage:"
    print "ResourceAdapterMonitor -u user -c credential -h host -p port -s serverName"
   

def monitorDBAdapter(serverName):
    cd("ServerRuntimes/"+str(serverName)+"/ApplicationRuntimes/DbAdapter/ComponentRuntimes/DbAdapter/ConnectionPools")
    connectionPools = ls(returnMap='true')
    print '--------------------------------------------------------------------------------'
    print 'DBAdapter Runtime details for '+ serverName
    print '--------------------------------------------------------------------------------'

    print '%10s %13s %15s %18s' % ('Connection Pool', 'State', 'Current', 'Created')
    print '%10s %10s %24s %21s' % ('', '', 'Capacity', 'Connections')
    print '--------------------------------------------------------------------------------'
    for connectionPool in connectionPools:
       if connectionPool!='eis/DB/SOADemo':
          cd('/')
          cd("ServerRuntimes/"+str(serverName)+"/ApplicationRuntimes/DbAdapter/ComponentRuntimes/DbAdapter/ConnectionPools/"+str(connectionPool))
          print '%15s %15s %10s %20s' % (cmo.getName(), cmo.getState(), cmo.getCurrentCapacity(), cmo.getConnectionsCreatedTotalCount())

    print '--------------------------------------------------------------------------------'


def monitorAQAdapter(serverName):
    cd("ServerRuntimes/"+str(serverName)+"/ApplicationRuntimes/AqAdapter/ComponentRuntimes/AqAdapter/ConnectionPools")
    connectionPools = ls(returnMap='true')
    print '--------------------------------------------------------------------------------'
    print 'AqAdapter Runtime details for '+ serverName
    print '--------------------------------------------------------------------------------'

    print '%10s %13s %15s %18s' % ('Connection Pool', 'State', 'Current', 'Created')
    print '%10s %10s %24s %21s' % ('', '', 'Capacity', 'Connections')
    print '--------------------------------------------------------------------------------'
    for connectionPool in connectionPools:
       if connectionPool!='eis/DB/SOADemo':
          cd('/')
          cd("ServerRuntimes/"+str(serverName)+"/ApplicationRuntimes/AqAdapter/ComponentRuntimes/AqAdapter/ConnectionPools/"+str(connectionPool))
          print '%15s %15s %10s %20s' % (cmo.getName(), cmo.getState(), cmo.getCurrentCapacity(), cmo.getConnectionsCreatedTotalCount())

    print '--------------------------------------------------------------------------------'   

def parameters():
      global user
      global credential
      global host
      global port
      global targetServerName
      try:
        opts, args    = getopt.getopt(sys.argv[1:], "u:c:h:p:s:",
                                  ["user=", "credential=", "host=", "port=",
                                   "targetServerName="])
      except getopt.GetoptError, err:
        print str(err)
        usage()
        sys.exit(2)

      for opt, arg in opts:
        if opt == "-n":
            reallyDoIt  = false
        elif opt == "-u":
            user        = arg
        elif opt == "-c":
            credential  = arg
        elif opt == "-h":
            host        = arg
        elif opt == "-p":
            port        = arg
        elif opt == "-s":
            targetServerName  = arg       
      if user == "":
        print "Missing \"-u user\" parameter."
        usage()
        sys.exit(2)
      if credential == "":
        print "Missing \"-c credential\" parameter."
        usage()
        sys.exit(2)
      if host == "":
        print "Missing \"-h host\" parameter."
        usage()
        sys.exit(2)
      if port == "":
        print "Missing \"-p port\" parameter."
        usage()
        sys.exit(2)
      if targetServerName == "":
        print "Missing \"-s targetServerName\" parameter."
        usage()
        sys.exit(2)           

   
def main():
      parameters()
      #connect(username, password, admurl)
      connect(user,credential,'t3://'+host+':'+port)
      servers = cmo.getServers()
      domainRuntime()
      cd("/ServerLifeCycleRuntimes/" + targetServerName)
      if cmo.getState() == 'RUNNING': 
        monitorAQAdapter(targetServerName)
        monitorDBAdapter(targetServerName)               
      disconnect()
   
main()


Conclusion

There are various ways to monitor backend systems and databases . It is useful to create your own dashboards, especially when there are a lot of systems involved and you don't want to (or can't) login to the Enterprise Manager on every one of them. Make sure though such unsecured dashboards don't end up on production systems. Depending on the problem with a database, a JNDI lookup might or might not work. The DbAdapter and AqAdapter have JDBC DataSources configured. It is useful to create a script which determines the DataSources based on the DbAdapter/AqAdapter configuration since that listing contains all DataSources used by the adapter, even if they are not loaded succesfully. That is the list of DataSources that should be tested. This can be done with WLST as shown in this post. Using a servlet however is more convenient then using WLST scripts since the URL of the servlet can be mailed to for example testers so they can monitor the databases. WLST requires a usuable Middleware installation and connection properties, which are not always available. I might create a Java servlet which provides the functionality of the WLST script mentioned in this post in the near future.

Things to mind in a clustered SOA Suite 11g environment

Introduction

When working in a clustered environment, there are several challenges which need to be taken into account. I've encountered and documented several of them in this post.

It is always wise to use the latest version of the software and keep your version up to date with regular patches. Oracle SOA Suite 11g is the first SOA Suite on Weblogic server and Oracle has done a great job on migrating several engines from OC4J to Weblogic. Also they have provided many new and useful features such as the EDN and the MDS. There is still some work to be done though but it will only get better!

Issues

Callbacks do not arrive

When working with SOA Suite 11.1.1.4 in a clustered environment, it is possible asynchronous callbacks do not arrive. The parent process is waiting for a callback, however the child has already send the callback.

When looking at the diagnostics log, the following error is shown;

an unhandled exception has been thrown in the Collaxa Cube systemr; exception reported is: "javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.3.v20110304-r9073): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SOA_SOAINFRA.AT_PK) violated

Error Code: 1
Call: INSERT INTO AUDIT_TRAIL (CIKEY, COUNT_ID, NUM_OF_EVENTS, BLOCK_USIZE, CI_PARTITION_DATE, BLOCK_CSIZE, BLOCK, LOG) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
bind => [851450, 5, 0, 118110, 2012-01-12 16:08:04.063, 5048, 3, [B@69157b01]
Query: InsertObjectQuery(com.collaxa.cube.persistence.dto.AuditTrail@494ca627)
at org.eclipse.persistence.internal.jpa.EntityManagerImpl.flush(EntityManagerImpl.java:744)

The solution to this problem is described on;

There are 3 known workarounds;
- shutting down one managed server in the cluster
- disable audit trail logging for processes using asynchronous callbacks (http://albinoraclesoa.blogspot.com/2012/02/oracle-soa-11g-callback-not-reaching.html)
- set the AuditStorePolicy to "async". Howto: SOA Administration -> BPEL Properties -> More BPEL Configuration Properties. https://support.oracle.com/CSP/main/article?cmd=show&type=BUG&id=9964636

There is one fix;
- install patch; https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=PROBLEM&id=1338478.1


Depending on the specific situation at a customer such as
- development process
- application server maintenance process
- time available
- local legislation

The best option for a specific customer can differ.

Database adapter

For polling issues see: http://javaoraclesoa.blogspot.com/2012/04/polling-with-dbadapter-in-clustered.html

Oracle recommends putting the configuration plan for the JCA adapters (such as the DbAdapter) on a shared storage. If a customer has not done this, the Plan.xml needs to be copied manually to other managed servers in a cluster after every change. If this is not done, the managed servers can have different (mismatched) configurations on the managed servers which can cause unexpected results.

Scheduling using Quartz

Scheduling using a servlet is described in; http://www.oracle.com/technetwork/middleware/soasuite/learnmore/soascheduler-186798.pdf
This can cause issues in a clustered environment depending on the specific implementation

In a clustered environment it is better to use EDN business events and DBMS_SCHEDULER;
See: http://javaoraclesoa.blogspot.com/2012/04/scheduling-edn-business-events-using.html

Test console does not function in Enterprise Manager

In a clustered environment which is installed using Oracle's Enterprise Deployment Guide, when using the Enterprise Manager, the WSDL URL of the test console (available to test a webservice) which is generated can be a managed server specific URL. This URL is in certain setups not directly available. This can be fixed by manually replacing the URL of the managed server with the URL of the loadbalancer and then click the Parse button.

Environment specific issues

It is not known if the below issues are bugs or caused by misconfiguration of the server. They have been observed on one installation at a specific customer (11.1.1.4).

Deployment issues

Deployment (using Ant scripts) does not always (it works most of the time...) roll out a process to both managed servers in a cluster. I have not found the cause of this issue and I'm not sure if this is a known bug. It is however wise to check that a process is deployed to all managed servers in a cluster and if one branch does not contain the process, repeat the deployment till it does.

Partition creation

After creation of a partition, it was only present on one managed server in the cluster. Removing the partition and recreating it solved this issue.

Conclusion

There are some challenges when working on a clustered environment. I will expand this post when I find more issues or things to mind. For a predictable development process it is wise to use a clustered development environment when production is also clustered so these issues can be solved in development and not first encountered on production.

Polling with the DbAdapter in a clustered environment

Introduction

Most customers use a clustered production environment. The development environment is often not clustered. There are several things to consider for developers when the software developed will eventually run in a clustered environment. It would be a shame if the software has been developed, unit tested, system tested, accepted by the users and then breaks on the production system.

I will first discuss the DbAdapter and polling in this post. This is not a complete description of all the settings which can influence this behavior, just some things I've tried and problems I've encountered.

I've used the (active-active cluster) setup as described in;
http://javaoraclesoa.blogspot.com/2012/03/oracle-soa-suite-cluster-part-1.html
http://javaoraclesoa.blogspot.com/2012/03/oracle-soa-suite-cluster-part-2.html

This article is about the DbAdapter. An error which can occur when using the AqAdapter when dequeueing in a clustered environment is that a message is queued once and dequeued more then once. This can occur in 11.2 databases. Look at; http://www.oracle.com/technetwork/middleware/docs/aiasoarelnotesps5-1455925.html for a description on how to fix this. Below has been copied from the mentioned document;

Bug: 13729601
Added: 20-February-2012
Platform: All
The dequeuer returns the same message in multiple threads in high concurrency environments when Oracle database 11.2 is used. This means that some messages are dequeued more than once. For example, in Oracle SOA Suite, if Service 1 suddenly raises a large number of business events that are subscribed to by Service 2, duplicate instances of Service 2 triggered by the same event may be seen in an intermittent fashion. The same behavior is not observed with a 10.2.0.5 database or in an 11.2 database with event10852 level 16384 set to disable the 11.2 dequeue optimizations.

Workaround: Perform the following steps:

    Log in to the 11.2 database:
    CONNECT /AS SYSDBA

    Specify the following SQL command in SQL*Plus to disable the 11.2 dequeue optimizations:
    SQL> alter system set event='10852 trace name context forever,
    level 16384'scope=spfile;

Polling setup

In an active/active cluster configuration, a deployed process will have two instances of a process polling on the same table. In this case it is important to consider if it will be a problem if more then one instance picks up the same entry in the table.

I used the following database setup to simulate and log the test

The script contains three tables;
POLLING_TEST_CLUSTER
- this table will be used by the DbAdapter for polling
POLLING_TEST_LOG
- this table will log status changes in POLLING_TEST_CLUSTER (POLLING_TEST_CLUSTER has a before update trigger)
POLLING_TEST_OUTPUT
- a BPEL process will read from POLLING_TEST_CLUSTER and put entries in this table. this table has a unique constraint on the ID column. the same ID is used as in POLLING_TEST_CLUSTER thus if the same entry from the  POLLING_TEST_CLUSTER table is picked up twice by BPEL, it will cause a unique key constraint when it tries to insert the entry in POLLING_TEST_CLUSTER

I've used a 'pragma autonomous_transaction' in the logging procedure. This will fail (with an ORA-06519: active autonomous transaction detected and rolled back) if I don't end the procedure with an explicit commit.

Next configure a datasource and the database adapter in the Weblogic Console so you can use them in BPEL. Don't create an XA datasource! It will cause problems with autonomous transactions such as java.sql.SQLException: Cannot call rollback when using distributed transactions (XA datasources can also cause problems with database links; http://javaoraclesoa.blogspot.com/2012/02/exception-occured-when-binding-was.html)

When configuring the DbAdapter, keep in mind that you have to copy the Plan.xml file (deployment plan for the DbAdapter) to the other managed server if you have not configured a shared storage for this file (which is suggested in the Enterprise Deployment Guide, http://docs.oracle.com/cd/E17904_01/core.1111/e12036/extend_soa.htm, paragraph 5.21.1). If you don't do this, the connection factory will not be available in the other managed server.

Polling test

You can download the processes here

I created a small process to insert a record in the POLLING_TEST_CLUSTER table with a status NEW so it would directly be picked up. I used SOAPUI (http://www.soapui.org/) to do a stress test and call this process a large number of times.

I was able to produce the error (a small number of times at high loads) that two instances of the adapter, running on different servers in the cluster, picked up and processed a message at the same time. I have also seen this happening at a customer.

In my setup this situation would cause a unique constraint violation as shown below;

<bpelFault><faultType>0</faultType><bindingFault xmlns="http://schemas.oracle.com/bpel/extension"><part name="summary"><summary>Exception occured when binding was invoked. Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'insert' failed due to: DBWriteInteractionSpec Execute Failed Exception. insert failed. Descriptor name: [write_textline_DB.PollingTestOutput]. Caused by java.sql.BatchUpdateException: ORA-00001: unique constraint (TESTUSER.POLLING_TEST_OUTPUT_PK) violated . Please see the logs for the full DBAdapter logging output prior to this exception. This exception is considered not retriable, likely due to a modelling mistake. To classify it as retriable instead add property nonRetriableErrorCodes with value "-1" to your deployment descriptor (i.e. weblogic-ra.xml). To auto retry a retriable fault set these composite.xml properties for this invoke: jca.retry.interval, jca.retry.count, and jca.retry.backoff. All properties are integers. ". 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>ORA-00001: unique constraint (TESTUSER.POLLING_TEST_OUTPUT_PK) violated </detail></part><part name="code"><code>1</code></part></bindingFault></bpelFault>

This occurred even with the NumberOfThreads value set to 1 (this is the default);

Below I will describe two possible solutions for this issue and my experience with it. Distributed polling and using a Reserved Value.

Distributed Polling

This is also described more extensively in; http://www.oracle.com/technetwork/database/features/availability/maa-soa-assesment-194432.pdf

It is possible to set the DbAdapter property to do distributed polling;

Distributed Polling means that when a record is read, it is locked by the reading instance. Another instance which wants to pickup the record skips locked records. This can however cause problems with locks which could originate from different sources then the processes; records which would require processing, could be skipped.

Also, a BPEL process is by default invoked asynchronously by the DbAdapter;

This causes the lock to be released right after the DbAdapter is done with it and the BPEL process is started. This makes a case for using the logical delete provided in the DbAdapter if you want to use this mechanism and not update the field later in the BPEL process.

Using distributed polling in combination with logical delete is however not recommended by Oracle; from the manual (Help button in the JDeveloper wizard); A better alternative is to set either NumberOfThreads or MarkReservedValue for logical delete or delete strategies.

I tested the same process with distributed polling enabed. Still a small number of processes failed with a unique key constraint thus this mechanism is not 100% safe. I did get a bit better results however.

Unread and reserved value

Unread Value

Setting the Unread value causes the select query used for polling to contain a where clause matching the field to the Unread value. During my test I found that setting the Unread value in the DbAdapter configuration wizard caused my process not to pickup records with the set value. I have however seen at customers that this value was used succesfully to limit the records being picked up.


The help documentation says the following (which made me doubt the purpose of this field); Unread Value
(Optional) Enter an explicit value to indicate that the row does not need to be read. During polling, this row is skipped.

The below image showed a setting that did work.

Reserved value

In the release notes of SOA Suite 11.1.1.4 (https://supporthtml.oracle.com/epmos/faces/ui/km/SearchDocDisplay.jspx?_afrLoop=3308018508015000&type=DOCUMENT&id=1290512.1&displayIndex=3&_afrWindowMode=0&_adf.ctrl-state=y8cqyff7j_134), the following is documented;

18.1.5.1 Distributed Polling Using MarkReservedValue Disabled by Default
In this release, Oracle recommends that you use the new distributed polling approach based on skip locking. When editing an Oracle Database Adapter service which has a MarkReservedValue set, that value will be removed to enable the new best practice. To use the old distributed polling approach based on a reserved value, select the value from the drop down menu.

In the help in JDeveloper, the following is documented for distributed polling (skip locking as mentioned above); Distributed Polling. Select this checkbox if distributed polling is required. However, this implementation uses a SELECT FOR UPDATE command. A better alternative is to set either NumberOfThreads or MarkReservedValue for logical delete or delete strategies.


As you can see in the above screenshots, it is possible to set a reserved value.

This reserved value (MarkReservedValue in *_db.jca) causes an instance of the process to set an identifier. This identifier is skipped by the other polling instances in the cluster.

When I however tried to use this setting (Reserved Value) in 11.1.1.6 (of course setting the Unread value to ''), I noticed the DbAdapter did not pickup any messages. When changing the Read Value and redeploying (after emptying the reserved value), it did pickup messages again immediately. I'm not sure why it didn't work in this test. My guess is for this to work, an additional setting is required. If I've found this setting, I will update this post. Also notice the wizard empties the Reserved Value if you go through it again. For the time being, I'll use the skip locking setting (distributed polling).

Singleton DbAdapter

Based on a suggestion done in the comments of this post, there is also the option to configure the DbAdapter as a singleton with a JCA property.

See the documentation for more information on this;
http://docs.oracle.com/cd/E23943_01/integration.1111/e10231/life_cycle.htm#BABDAFBH

The behavior of this property is described in the following post; http://ayshaabbas.blogspot.nl/2012/11/db-adapter-singleton-behaviour-in-high.html

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.