XAER_RMERR during XA transaction recovery for Oracle database in JBoss EAP

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.

NOTE While the below may be appropriate in many cases - Oracle documentation or Support should be consulted for specific recommendations.

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:

• Oracle 12 Documentation
• Oracle 11 Documentation

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:

• Refer to XA Recovery for multiple datasources against the same database identified by host and port.

• Also, ensure that the JDBC driver used by JBoss EAP corresponds to the version of Oracle in use.  It is possible to set up the correct permissions in Oracle 11g but still receive this error because an older driver is still attempting to use sys.dbms_system.
  The supported database/driver versions with EAP 4 and EAP 5 , with EAP 6 and with EAP 7 can be found under Red Hat JBoss Supported Configurations.

• See How Do I Configure XA Recovery with JBossTS in JBoss EAP 4 & 5 for a complete discussion of XA transaction recovery in JBoss EAP 4 & 5.

• Fix for bug 5945463 is included from 10.2.0.4 onwards.
  If there are multiple XA data sources referencing the same server for example:
  jdbc:oracle:thin:@remoteHost:1521:SID-1
  and
  jdbc:oracle:thin:@remoteHost:1521:SID-2
  it is necessary to run the above GRANT statements for each SID.