XAER_RMERR during XA transaction recovery for Oracle database in JBoss EAP

Solution Verified - Updated -

Environment

  • Red Hat JBoss Enterprise Application Platform (EAP)
    • 4
    • 5
    • 6
    • 7
  • Oracle Database

Issue

  • The following message appears in the log intermittently (with the frequency configured for recovery) when XA recovery has been configured for an Oracle XA datasource:
14:55:40,069 WARN  [com.arjuna.ats.jta] (Periodic Recovery) ARJUNA016027: Local XARecoveryModule.xaRecovery got XA exception XAException.XAER_RMERR: javax.transaction.xa.XAException
at oracle.jdbc.xa.OracleXAResource.recover(OracleXAResource.java:703)
at org.jboss.jca.adapters.jdbc.xa.XAManagedConnection.recover(XAManagedConnection.java:362)
at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.xaRecoverySecondPass(XARecoveryModule.java:709) [jbossjts-jacorb-4.17.15.Final-redhat-4.jar:4.17.15.Final-redhat-4]
at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.bottomUpRecovery(XARecoveryModule.java:431) [jbossjts-jacorb-4.17.15.Final-redhat-4.jar:4.17.15.Final-redhat-4]
at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.periodicWorkSecondPass(XARecoveryModule.java:212) [jbossjts-jacorb-4.17.15.Final-redhat-4.jar:4.17.15.Final-redhat-4]
at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.doWorkInternal(PeriodicRecovery.java:789) [jbossjts-jacorb-4.17.15.Final-redhat-4.jar:4.17.15.Final-redhat-4]
at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.run(PeriodicRecovery.java:371) [jbossjts-jacorb-4.17.15.Final-redhat-4.jar:4.17.15.Final-redhat-4]

Resolution

Disclaimer: Links contained herein to external website(s) are provided for convenience only. Red Hat has not reviewed the links and is not responsible for the content or its availability. The inclusion of any link to an external website does not imply endorsement by Red Hat of the website or their entities, products or services. You agree that Red Hat is not responsible or liable for any loss or expenses that may result due to your use of (or reliance on) the external site or content.

Configure Oracle to allow the user access to the tables necessary for XA recovery.  If Oracle 10g R2 with patch for bug 5945463 or 11g (or later) are in use then grant these permissions:

  GRANT SELECT ON sys.dba_pending_transactions TO <user>;
  GRANT SELECT ON sys.pending_trans$ TO <user>;
  GRANT SELECT ON sys.dba_2pc_pending TO <user>;
  GRANT EXECUTE ON sys.dbms_xa TO <user>;
  GRANT FORCE ANY TRANSACTION TO <user>;

Where <user> is the user defined to connect from JBoss to Oracle.

If an unpatched version prior to 11g is used, grant these permissions (the difference is the GRANT EXECUTE query):

  GRANT SELECT ON sys.dba_pending_transactions TO <user>;
  GRANT SELECT ON sys.pending_trans$ TO <user>;
  GRANT SELECT ON sys.dba_2pc_pending TO <user>;
  GRANT EXECUTE ON sys.dbms_system TO <user>;
  GRANT FORCE ANY TRANSACTION TO <user>;

In some cases it may also be necessary to manually run the Oracle script xaview.sql as noted in:

For definitive requirements on what is needed for Oracle please consult the Oracle documentation or contact Oracle Support. Note that the above requirements do not originate with JBoss EAP. These are requirements specific to the Oracle implementation of database server side functionality which supports the javax.transaction.xa.XAResource API which is required to perform recovery of in-doubt transactions from Java based applications.

Important notes:

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.

4 Comments

Table Names are incorrect the correct table names are below.
GRANT SELECT ON sys.DBA_PENDING_TRANSACTIONS TO USERS;
GRANT SELECT ON sys.PENDING_TRANS$ TO USERS;
GRANT SELECT ON sys.DBA_2PC_PENDING TO USERS;
GRANT EXECUTE ON sys.DBMS_SYSTEM TO USERS;

Note that you MUST configure the following settings on jboss side, for only ONE xa-datasource


...


my_user

my_passwd

Note that you MUST configure the following settings on jboss side, for only ONE xa-datasource

<xa-datasource jndi-name="java:/jdbc/MyDS" pool-name="MyDS" enabled="true" use-ccm="false">
...
  <recovery>
    <recover-credential>
      <user-name>my_user</user-name>
      <password>my_passwd</password>
    </recover-credential>
  </recovery>
</xa-datasource>

It should be noted that use of <recovery><recover-credential/></recovery> is only applicable to EAP 6.