Jboss XA Datasource with Oracle Can't Open Connection

Question

We have strange problem with Jboss Wildfly 8.1 with XA Datasource using Oracle database.

After some time we get "Cannot open connection" exception, while we can see 100+ inactive connection in pool but Jboss Datasource is not using any of them from pool but giving exception.

Following are details.

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options.

<subsystem xmlns="urn:jboss:domain:datasources:2.0">
            <datasources>
                <xa-datasource jndi-name="java:/XAOracleDS" pool-name="XAOracleDS" enabled="true" use-java-context="true" use-ccm="true">

                    <xa-datasource-property name="URL">
                        jdbc:oracle:thin:@10.0.1.55:1521:sid
                    </xa-datasource-property>

                    <driver>oracle</driver>

                    <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

                   <xa-pool>
                        <min-pool-size>10</min-pool-size>
                        <max-pool-size>50</max-pool-size>
                        <prefill>true</prefill>
                        <use-strict-min>false</use-strict-min>
                        <flush-strategy>FailingConnectionOnly</flush-strategy>
                        <pad-xid>true</pad-xid>
                        <wrap-xa-resource>true</wrap-xa-resource>
                    </xa-pool>

                   <security>
                        <user-name>scott</user-name>
                        <password>tigger</password>
                    </security>

                    <validation>
                        <check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>
                        <background-validation>true</background-validation>
                        <use-fast-fail>false</use-fast-fail>
                        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>
                    </validation>

                    <timeout>
                        <blocking-timeout-millis>120000</blocking-timeout-millis>
                        <idle-timeout-minutes>5</idle-timeout-minutes>
                        <allocation-retry>3</allocation-retry>
                        <allocation-retry-wait-millis>1000</allocation-retry-wait-millis>
                    </timeout>

                    <statement>
                        <prepared-statement-cache-size>50</prepared-statement-cache-size>
                    </statement>

                </xa-datasource>

                <drivers>
                    <driver name="oracle" module="com.oracle.ojdbc6">
                        <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
                    </driver>
                </drivers>

            </datasources>
        </subsystem>

Linux version 2.6.32-431.20.3.el6.x86_64 ([email protected]) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-4) (GCC) ) #1 SMP Thu Jun 19 21:14:45 UTC 2014

JDK Version: jdk1.7.0_65

Exception Stack trace:

 ERROR [org.springframework.scheduling.quartz.SchedulerFactoryBean#1_Worker-1] | Hibernate operation: Cannot open connection; uncategorized SQLException for SQL [???]; SQL state [null]; error code [0]; javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@506f65ce[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@46c87b4b connection handles=0 lastUse=1410399087408 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@6b8913c mcp=SemaphoreArrayListManagedConnectionPool@984da7b[pool=XAOracleDS] xaResource=XAResourceWrapperImpl@10cab9b8[xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@46c87b4b pad=true overrideRmValue=null productName=Oracle productVersion=Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options jndiName=java:/XAOracleDS] txSync=null]; nested exception is java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@506f65ce[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@46c87b4b connection handles=0 lastUse=1410399087408 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@6b8913c mcp=SemaphoreArrayListManagedConnectionPool@984da7b[pool=XAOracleDS] xaResource=XAResourceWrapperImpl@10cab9b8[xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@46c87b4b pad=true overrideRmValue=null productName=Oracle productVersion=Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options jndiName=java:/XAOracleDS] txSync=null]
 org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: Cannot open connection; uncategorized SQLException for SQL [???]; SQL state [null]; error code [0]; javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@506f65ce[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@46c87b4b connection handles=0 lastUse=1410399087408 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@6b8913c mcp=SemaphoreArrayListManagedConnectionPool@984da7b[pool=XAOracleDS] xaResource=XAResourceWrapperImpl@10cab9b8[xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@46c87b4b pad=true overrideRmValue=null productName=Oracle productVersion=Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options jndiName=java:/XAOracleDS] txSync=null]; nested exception is java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@506f65ce[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@46c87b4b connection handles=0 lastUse=1410399087408 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@6b8913c mcp=SemaphoreArrayListManagedConnectionPool@984da7b[pool=XAOracleDS] xaResource=XAResourceWrapperImpl@10cab9b8[xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@46c87b4b pad=true overrideRmValue=null productName=Oracle productVersion=Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options jndiName=java:/XAOracleDS] txSync=null]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:424)
    at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:410)
    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411)
    at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
    at org.springframework.orm.hibernate3.HibernateTemplate.saveOrUpdate(HibernateTemplate.java:737)
    at com.inov8.framework.server.dao.framework.hibernate.BaseHibernateDAO.saveOrUpdate(BaseHibernateDAO.java:590)
    at com.inov8.microbank.server.service.transactionmodule.TransactionManagerImpl.saveTransactionCodeModel(TransactionManagerImpl.java:144)
    at com.inov8.microbank.server.service.transactionmodule.TransactionManagerImpl.generateTransactionCodeRequiresNewTransaction(TransactionManagerImpl.java:99)
    at sun.reflect.GeneratedMethodAccessor1031.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    at com.sun.proxy.$Proxy59.generateTransactionCodeRequiresNewTransaction(Unknown Source)
    at com.inov8.microbank.server.service.bulkdisbursements.BulkDisbursementsManagerImpl.creditOLABulkDisbursementSundryAccount(BulkDisbursementsManagerImpl.java:555)
    at com.inov8.microbank.server.service.bulkdisbursements.BulkDisbursementsManagerImpl.makeDebitCreditAccount(BulkDisbursementsManagerImpl.java:143)
    at sun.reflect.GeneratedMethodAccessor1030.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    at com.sun.proxy.$Proxy136.makeDebitCreditAccount(Unknown Source)
    at com.inov8.microbank.server.facade.BulkDisbursementsFacadeImpl.makeDebitCreditAccount(BulkDisbursementsFacadeImpl.java:67)
    at com.inov8.microbank.server.service.dailyjob.BulkDisbursementsScheduler.activateDailyBulkDisbursements(BulkDisbursementsScheduler.java:178)
    at com.inov8.microbank.server.service.dailyjob.BulkDisbursementsScheduler.init(BulkDisbursementsScheduler.java:84)
    at com.inov8.microbank.server.service.dailyjob.BulkDisbursementsScheduler$$FastClassByCGLIB$$e344897e.invoke(<generated>)
    at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191)
    at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618)
    at com.inov8.microbank.server.service.dailyjob.BulkDisbursementsScheduler$$EnhancerByCGLIB$$a63ce042.init(<generated>)
    at sun.reflect.GeneratedMethodAccessor1029.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:273)
    at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:311)
    at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:113)
    at org.quartz.core.JobRunShell.run(JobRunShell.java:223)
    at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549)
 Caused by: java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@506f65ce[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@46c87b4b connection handles=0 lastUse=1410399087408 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@6b8913c mcp=SemaphoreArrayListManagedConnectionPool@984da7b[pool=XAOracleDS] xaResource=XAResourceWrapperImpl@10cab9b8[xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@46c87b4b pad=true overrideRmValue=null productName=Oracle productVersion=Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options jndiName=java:/XAOracleDS] txSync=null]
    at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:154)
    at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
    at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:423)
    at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144)
    at org.hibernate.jdbc.AbstractBatcher.prepareSelectStatement(AbstractBatcher.java:123)
    at org.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:73)
    at org.hibernate.id.SequenceHiLoGenerator.generate(SequenceHiLoGenerator.java:53)
    at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:99)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:187)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:172)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:94)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
    at org.hibernate.impl.SessionImpl.fireSaveOrUpdate(SessionImpl.java:507)
    at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:499)
    at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:495)
    at org.springframework.orm.hibernate3.HibernateTemplate$16.doInHibernate(HibernateTemplate.java:740)
    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
    ... 42 more
 Caused by: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@506f65ce[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@46c87b4b connection handles=0 lastUse=1410399087408 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@6b8913c mcp=SemaphoreArrayListManagedConnectionPool@984da7b[pool=XAOracleDS] xaResource=XAResourceWrapperImpl@10cab9b8[xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@46c87b4b pad=true overrideRmValue=null productName=Oracle productVersion=Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options jndiName=java:/XAOracleDS] txSync=null]
    at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.reconnectManagedConnection(AbstractConnectionManager.java:773)
    at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:516)
    at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:146)
    ... 58 more
 Caused by: javax.resource.ResourceException: IJ000461: Could not enlist in transaction on entering meta-aware object
    at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.managedConnectionReconnected(TxConnectionManagerImpl.java:546)
    at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.reconnectManagedConnection(AbstractConnectionManager.java:768)
    ... 60 more
 Caused by: javax.transaction.SystemException: IJ000356: Failed to enlist: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: 0:ffff7f000001:751c2dee:54107a6d:1db4a7 status: ActionStatus.ABORT_ONLY >
    at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener$TransactionSynchronization.checkEnlisted(TxConnectionListener.java:812)
    at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener.enlist(TxConnectionListener.java:362)
    at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.managedConnectionReconnected(TxConnectionManagerImpl.java:539)
    ... 61 more
ramework.scheduling.quartz.SchedulerFactoryBean#1_Worker-1) com.inov8.framework.common.exception.FrameworkCheckedException: Hibernate operation: Cannot open connection; uncategorized SQLException for SQL [???]; SQL state [null]; error code [0]; javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@506f65ce[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@46c87b4b connection handles=0 lastUse=1410399087408 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@6b8913c mcp=SemaphoreArrayListManagedConnectionPool@984da7b[pool=XAOracleDS] xaResource=XAResourceWrapperImpl@10cab9b8[xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@46c87b4b pad=true overrideRmValue=null productName=Oracle productVersion=Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
c=null]; nested exception is java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@506f65ce[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@46c87b4b connection handles=0 lastUse=1410399087408 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@6b8913c mcp=SemaphoreArrayListManagedConnectionPool@984da7b[pool=XAOracleDS] xaResource=XAResourceWrapperImpl@10cab9b8[xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@46c87b4b pad=true overrideRmValue=null productName=Oracle productVersion=Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
c=null]

Any expert opinion regarding this issue?

Answer 1

Try following: switch off pad-xid (according to https://issues.jboss.org/browse/JBTM-1249 it should not be used for Oracle 11g) turn on no-tx-separate-pools (it helped with similar issue noted in https://community.oracle.com/thread/3558157)

Finally, I ran into the similar issue when using two Oracle xa-datasources using the same URL. When I changed one of the URLs (changed server name to IP address), issue "disappeared".