Login/Register
Jboss XA Datasource with Oracle Can't Open Connection
Asked Answered
oracle-databasejbossoracle11gjboss7.x
A

2

5

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?

Ard answered 11/9, 2014 at 7:16 Comment(0)
L
7

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

Lacking answered 11/11, 2014 at 7:47 Comment(1)
I solved too by replacing the hostname of one of the DBs with its IP address (in datasources definition of JBoss EAP 6). – Cleptomania
D
0

This works for me for Oracle 11.2.0.1.0 and WildFly 15

            <xa-datasource jndi-name="java:jboss/datasources/OracleDS" pool-name="OracleDS" enabled="true" statistics-enabled="true">
                <xa-datasource-property name="URL">jdbc:oracle:thin:@127.0.0.1:1521:ORADB</xa-datasource-property>
                <xa-datasource-property name="User">xxxx</xa-datasource-property>
                <xa-datasource-property name="Password">xxxx</xa-datasource-property> 
                <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
                <driver>oracle</driver>
                <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
                <driver>oracle</driver>
                <xa-pool>
                    <min-pool-size>10</min-pool-size>
                    <max-pool-size>50</max-pool-size>
                    <prefill>false</prefill>
                    <use-strict-min>false</use-strict-min>
                    <flush-strategy>FailingConnectionOnly</flush-strategy>
                    <pad-xid>false</pad-xid>
                    <wrap-xa-resource>true</wrap-xa-resource>
                    <is-same-rm-override>false</is-same-rm-override>
                </xa-pool>                    
            </xa-datasource>

Here is a description of the flags for your reference:

https://access.redhat.com/documentation/en-us/red_hat_jboss_operations_network/3.1/html/dev_complete_resource_reference/jbossas7-jbossas7_host_controller-profile-datasources_-profile--xadatasource_-profile-

Dosi answered 16/6, 2019 at 0:16 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.