0 Replies Latest reply on Sep 11, 2014 3:28 AM by Faisal Basra

    Jboss XA Datasource with Oracle Can't Open Connection

    Faisal Basra Newbie

      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.

      Datasource:

      <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>

       

       

      Exception:

      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]

       

      OS Version: Linux version 2.6.32-431.20.3.el6.x86_64 (mockbuild@c6b9.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-4) (GCC) ) #1 SMP Thu Jun 19 21:14:45 UTC 2014

       

      Java Version: jdk1.7.0_65

       

      Any expert opinion regarding above issue?