Switching to an XA JDBC driver explodes the console output

[Lasse Koskela]

Hi,

I recently swapped in the Oracle XA JDBC driver to replace the non-XA driver I had used before. Now, suddenly I get a *lot* of the following in the console output:

<Nov 12, 2004 10:55:18 AM EET> <Error> <JDBC> <BEA-001112> <Test "select count(*) from FOO" set up for pool "myConnectionPool" failed with exception: "javax.transaction.xa.XAException".>
<Nov 12, 2004 10:55:18 AM EET> <Info> <JDBC> <BEA-001128> <Connection for pool "myConnectionPool" closed.>
<Nov 12, 2004 10:55:18 AM EET> <Info> <JDBC> <BEA-001073> <Connection for XA pool "myConnectionPool" refreshed.>

So, this raises two questions:

(1) Why am I getting an XAException from the connection test? What can I do to stop it? What am I doing wrong?

(2) Is there any way to filter out those "connection for ... closed" and "connection for ... refreshed" messages from the log? They would be alright if I'd put the console output to /dev/null but since I have to pipe it into a log file (nohup.out), I'm worrying that the size of those log files will grow too fast (I've already seen one nohup.out file of 2 gigs which got all sorts of red lights flashing but that was due to a WebLogic bug...).

And thanks for your effort reading this far

[Billy Tsai]

can you provide some more information about what versions of Oracle database,WebLogic Server, oracle driver(JDBC 2.0?) you are using and the setting of ur connection pool and datasource?
maybe u didnt select honor global transactions for your datasource and u changed the driver of ur connection pool, or u should delete the existing datasource and create a new one with honor global transactions to use the connection pool.
It seems that the failure occurred at javax.transaction.xa.XAResource.prepare() in ur situation.
By the way don't create two Tx Data Sources that point to the same connection pool. If a transaction uses two different Tx Data Sources which are both pointed to the same connection pool, you will get an XA_PROTO error when you try to access the second connection.

[Lasse Koskela]

Thanks, Billy.

I'm pretty sure I enabled global transactions but I'll post that and other configuration details tomorrow when I'm at the office again.

This is what I remember about the product versions:
- WebLogic 8.1 sp3
- Oracle 9.2.x
- Oracle 10.x driver, the one that comes with WLS 8.1 SP3

[Lasse Koskela]

Originally posted by Billy Tsai:
can you provide some more information about what versions of Oracle database,WebLogic Server, oracle driver(JDBC 2.0?) you are using and the setting of ur connection pool and datasource?

Ok. Here's the DataSource/ConnectionPool configs from my config.xml:

    <JDBCConnectionPool Name="epmSapConnectionPool"
        CapacityIncrement="1" SupportsLocalTransaction="true"
        DriverName="oracle.jdbc.xa.client.OracleXADataSource"
        InitialCapacity="1" MaxCapacity="2"
        Properties="user=sapimport;dll=ocijdbc8;protocol=thin"
        Targets="wlsName"
        TestConnectionsOnRelease="true" TestConnectionsOnReserve="true"
        TestTableName="DUAL"
        URL="jdbc:oracle:thin:@servername:1521:DBNAME" Password="{3DES}T4LZMd07vTeJos9t5u0LnQ=="/>
 
    <JDBCTxDataSource EnableTwoPhaseCommit="true"
        JNDIName="epmSapDataSource" Name="epmSapDataSource"
        PoolName="epmSapConnectionPool" Targets="epmServer1,epmServer2,epmServer"/>

[Valentin Crettaz]

Why do you have a DataSource class specified as the driver name of your connection pool?
DriverName="oracle.jdbc.xa.client.OracleXADataSource"

You can just use the normal oracle.jdbc.driver.OracleDriver and Weblogic will take care of using the correct data source depending on whether it is TX enabled or not...

Try that and let me know...

[Mark Spritzler]

Here is a clip form my config.xml

 
<JDBCConnectionPool
        DriverName="oracle.jdbc.xa.client.OracleXADataSource"
        Name="ConnectionPool1" Password="{3DES}xYsVqKioya4g6UdZvGWkAg=="
        Properties="user=usernamehere;url=jdbc :-o	 racle:thin:@(description=(address_list=(address=(protocol=tcp)(host=168.25.16.37)(port=1630))(address=(protocol=tcp)(host=168.192.101.137)(port=1521)))(connect_data=(service_name=accsdev))(source_route=yes));dll=ocijdbc9;dataSource=connectionPool1;protocol=thin"
        Targets="accsserver" URL="jdbc :-o	 racle:thin:@(description=(address_list=(address=(protocol=tcp)(168.25.16.37)(port=1630))(address=(protocol=tcp)(host=168.192.101.137)(port=1521)))(connect_data=(service_name=accsdev))(source_route=yes))"/>
    <JDBCTxDataSource EnableTwoPhaseCommit="false"
        JNDIName="TRSDataSourceXA" Name="DataSource1"
        PoolName="ConnectionPool1" Targets="accsserver"/>
 

The only thing you should ignore from my version is the "@(description..."
part because we have to go around a firewall to access out database.

I hope that helps

Mark

[Lasse Koskela]

Thanks guys.

Val, that didn't work. The problem was that someone somewhere uses local transactions with the pool and when someone else creates a global transaction, it all comes to pieces...

Mark, may I ask why you use the verbose (foo=bar) format? I've only seen people use that with the OCI driver (tnsnames.ora).

Anyway, I think I figured out a solution. I switched to using WebLogic's own Oracle JDBC XA driver (weblogic.jdbcx.oracle.OracleDataSource) and set the RollbackLocalTxUponConnClose property for the connection pool to "true" (effectively causing all those local transactions to finish along with the connection being returned back to the pool.

I'll let you know whether I broke anything with that...

[Mark Spritzler]

Mark, may I ask why you use the verbose (foo=bar) format?

What do you actually mean by that all the properties of the tag are name=value. Is there a specific one you are talking about. Are you talking about the part to get past our internal Firewalls?

Mark

[Lasse Koskela]

Originally posted by Mark Spritzler:
What do you actually mean by that all the properties of the tag are name=value. Is there a specific one you are talking about. Are you talking about the part to get past our internal Firewalls?

Yes. the URL parameter:

 @(description=
    (address_list=
      (address=
        (protocol=tcp)
        (168.25.16.37)
        (port=1630)
      )
      (address=
        (protocol=tcp)
        (host=168.192.101.137)
        (port=1521)
      )
    )
    (connect_data=
      (service_name=accsdev)
    )
    (source_route=yes)
  )

How does that help you get through a firewall?