2 Replies Latest reply on Apr 20, 2015 11:12 AM by ctomc

    Wildfly 8.0 XA Transaction Recovery with Oracle: ORA-01031: insufficient privileges

    alex_k

      Hello,

       

      I am trying to setup distributed transactions containing a HornetQ resource and a Oracle resource.

      The setup uses Wildfly 8.0, Oracle 11g Release 2 and ojdbc6 11.2.0.4.

       

      The datasource was configured as described here:

      https://access.redhat.com/documentation/en-US/JBoss_Enterprise_Application_Platform/6.1/html/Administration_and_Configuration_Guide/Example_Oracle_XA_Datsource1.html

       

                     <xa-datasource jndi-name="java:jboss/datasources/dbDs" pool-name="dbDS" enabled="true" use-java-context="true">

                          <security>

                              <user-name>***</user-name>

                              <password>***</password>

                          </security>

                          <xa-datasource-property name="URL">

                              jdbc:oracle:thin:@db

                          </xa-datasource-property>

                          <driver>ojdbc6</driver>

                          <xa-pool>

                              <min-pool-size>1</min-pool-size>

                              <max-pool-size>100</max-pool-size>

                              <prefill>true</prefill>

                              <is-same-rm-override>false</is-same-rm-override>

                          </xa-pool>

                          <validation>

                              <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>

                              <stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker"/>

                              <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>

                          </validation>

                      </xa-datasource>

       

      Following privileges where granted to the user:

      GRANT SELECT ON sys.dba_pending_transactions TO ***;

      GRANT SELECT ON sys.pending_trans$ TO ***;

      GRANT SELECT ON sys.dba_2pc_pending TO ***;

      GRANT EXECUTE ON sys.dbms_xa TO ***;

       

      It seems that the setup works as expected. The application is able to connect to the database. But the recovery manager seems to have a problem to do a rollback of an old transaction:

       

      2014-07-23 13:31:44,894 DEBUG [com.arjuna.ats.jta] (Periodic Recovery) Have 1 Xids to recover on this pass.

      2014-07-23 13:31:44,894 DEBUG [com.arjuna.ats.jta] (Periodic Recovery) Checking whether Xid < formatId=131077, gtrid_length=29, bqual_length=36, tx_uid=0:ffffc0a81451:2986a129:52dc58f9:8815, node_name=1, branch_uid=0:ffffc0a81451:2986a129:52dc58f9:881e, subordinatenodename=null, eis_name=java:jboss/datasources/dbDs > exists in ObjectStore.

      2014-07-23 13:31:44,894 DEBUG [com.arjuna.ats.jta] (Periodic Recovery) Looking for 0:ffffc0a81451:2986a129:52dc58f9:8815 and /StateManager/BasicAction/TwoPhaseCoordinator/AtomicAction

      2014-07-23 13:31:44,895 DEBUG [com.arjuna.ats.jta] (Periodic Recovery) No record found for < formatId=131077, gtrid_length=29, bqual_length=36, tx_uid=0:ffffc0a81451:2986a129:52dc58f9:8815, node_name=1, branch_uid=0:ffffc0a81451:2986a129:52dc58f9:881e, subordinatenodename=null, eis_name=java:jboss/datasources/dbDs >

      2014-07-23 13:31:44,895 DEBUG [com.arjuna.ats.jta] (Periodic Recovery) XAResourceOrphanFilter com.arjuna.ats.internal.jta.recovery.arjunacore.JTATransactionLogXAResourceOrphanFilter voted ABSTAIN

      2014-07-23 13:31:44,896 DEBUG [com.arjuna.ats.jta] (Periodic Recovery) node name of < formatId=131077, gtrid_length=29, bqual_length=36, tx_uid=0:ffffc0a81451:2986a129:52dc58f9:8815, node_name=1, branch_uid=0:ffffc0a81451:2986a129:52dc58f9:881e, subordinatenodename=null, eis_name=java:jboss/datasources/dbDs > is 1

      2014-07-23 13:31:44,896 DEBUG [com.arjuna.ats.jta] (Periodic Recovery) XAResourceOrphanFilter com.arjuna.ats.internal.jta.recovery.arjunacore.JTANodeNameXAResourceOrphanFilter voted ROLLBACK

      2014-07-23 13:31:44,896 DEBUG [com.arjuna.ats.jta] (Periodic Recovery) subordinate node name of < formatId=131077, gtrid_length=29, bqual_length=36, tx_uid=0:ffffc0a81451:2986a129:52dc58f9:8815, node_name=1, branch_uid=0:ffffc0a81451:2986a129:52dc58f9:881e, subordinatenodename=null, eis_name=java:jboss/datasources/dbDs > is null AXAResourceOrphanFilter voted ABSTAIN

      2014-07-23 13:31:44,897 INFO  [com.arjuna.ats.jta] (Periodic Recovery) ARJUNA016013: Rolling back < formatId=131077, gtrid_length=29, bqual_length=36, tx_uid=0:ffffc0a81451:2986a129:52dc58f9:8815, node_name=1, branch_uid=0:ffffc0a81451:2986a129:52dc58f9:881e, subordinatenodename=null, eis_name=java:jboss/datasources/dbDs >

      FAIL: oracle.jdbc.xa.OracleXAException

              at oracle.jdbc.xa.OracleXAResource.checkError(OracleXAResource.java:1135)

              at oracle.jdbc.xa.client.OracleXAResource.rollback(OracleXAResource.java:941)

              at org.jboss.jca.adapters.jdbc.xa.XAManagedConnection.rollback(XAManagedConnection.java:346)

              at org.jboss.jca.core.tx.jbossts.XAResourceWrapperImpl.rollback(XAResourceWrapperImpl.java:186)

              at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.handleOrphan(XARecoveryModule.java:786) [narayana-jts-jacorb-5.0.0.Final.jar:5.0.0.Final (revision: 9aa71)]

              at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.xaRecoverySecondPass(XARecoveryModule.java:692) [narayana-jts-jacorb-5.0.0.Final.jar:5.0.0.Final (revision: 9aa71)]

              at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.bottomUpRecovery(XARecoveryModule.java:431) [narayana-jts-jacorb-5.0.0.Final.jar:5.0.0.Final (revision: 9aa71)]

              at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.periodicWorkSecondPass(XARecoveryModule.java:212) [narayana-jts-jacorb-5.0.0.Final.jar:5.0.0.Final (revision: 9aa71)]

              at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.doWorkInternal(PeriodicRecovery.java:789) [narayana-jts-jacorb-5.0.0.Final.jar:5.0.0.Final (revision: 9aa71)]

              at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.run(PeriodicRecovery.java:371) [narayana-jts-jacorb-5.0.0.Final.jar:5.0.0.Final (revision: 9aa71)]

      Caused by: java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges

              at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)

              at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)

              at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:382)

              at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:675)

              at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)

              at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)

              at oracle.jdbc.driver.T4CTTIOtxen.doOTXEN(T4CTTIOtxen.java:168)

              at oracle.jdbc.driver.T4CXAResource.doTransaction(T4CXAResource.java:746)

              at oracle.jdbc.driver.T4CXAResource.kputxrec(T4CXAResource.java:809)

              at oracle.jdbc.driver.T4CXAResource.doRollback(T4CXAResource.java:645)

              at oracle.jdbc.xa.client.OracleXAResource.rollback(OracleXAResource.java:936)

              ... 8 more

       

      It seems to find an old transaction in the sys.dba_2pc_pending table (see the attached "select * from sys.dba_2pc_pending;" output), and tries to do a rollback on this transaction.

       

      The tranaction is there since ~6 Months, and does not belong to the user configured in the XA-Datasource (it wasn't even done from the same host). I assume that this is also the reason I get this "insufficient privileges" exception. So my question is:

       

      Why does Wildfly try to rollback this transaction? The transaction does not belong to the database user used in this setup. Shouldn't this transaction be left back for it's owner?

       

      BR,

      Alex