XAException occurred. Error code is: XAER_RMERR (-3). Exception is:

I'm tinkering with WebSphere Application Server, underlying IBM BPM Advanced, in the context of transaction/compensation/partner log recovery.

In this scenario, I'm placing these logs into a database, Oracle 12c.

It's been a useful - and interesting - learning curve, and I've pretty much proved/tested the concept.

I did, however, see one issue today; specifically, these messages: -

[16/07/17 13:34:06:748 BST] 00000074 XARecoveryDat A   WTRN0151I: Preparing to call xa recover on XAResource: dave
[16/07/17 13:34:06:866 BST] 00000074 WSRdbXaResour E   DSRA0304E:  XAException occurred. XAException contents and details are: "".
[16/07/17 13:34:06:866 BST] 00000074 WSRdbXaResour E   DSRA0302E:  XAException occurred.  Error code is: XAER_RMERR (-3).  Exception is: <null>
[16/07/17 13:34:06:866 BST] 00000074 XARminst      E   WTRN0037W: The transaction service encountered an error on an xa_recover operation. The resource was com.ibm.ws.rsadapter.spi.WSRdbXaResourceImpl@35d8b386. The error code was XAER_RMERR. The exception stack trace follows: javax.transaction.xa.XAException
at oracle.jdbc.xa.OracleXAResource.recover(OracleXAResource.java:727)
at com.ibm.ws.rsadapter.spi.WSRdbXaResourceImpl.recover(WSRdbXaResourceImpl.java:1111)
at com.ibm.ws.Transaction.JTA.XARminst.recover(XARminst.java:141)
at com.ibm.ws.Transaction.JTA.XARecoveryData.recover(XARecoveryData.java:723)
at com.ibm.tx.jta.impl.PartnerLogTable.recover(PartnerLogTable.java:432)
at com.ibm.tx.jta.impl.RecoveryManager.resync(RecoveryManager.java:1544)
at com.ibm.tx.jta.impl.RecoveryManager.performResync(RecoveryManager.java:2286)
at com.ibm.ws.tx.jta.RecoveryManager.performResync(RecoveryManager.java:119)
at com.ibm.tx.jta.impl.RecoveryManager.run(RecoveryManager.java:2239)
at java.lang.Thread.run(Thread.java:809)

appearing in the SystemOut.log for one of my cluster members - AppCluster.

This IBM Knowledge Center article: -

Data access problems for Oracle data sources

<snip>

Problem

When WebSphere Application Server attempts to recover Oracle database transactions, the transaction service issues the following exception:

WTRN0037W: The transaction service encountered an error on an xa_recover operation. 

The resource was com.ibm.ws.rsadapter.spi.WSRdbXaResourceImpl@1114a62. 

The error code was XAER_RMERR. The exception stack trace follows: 

javax.transaction.xa.XAException

at oracle.jdbc.xa.OracleXAResource.recover(OracleXAResource.java:726)

at com.ibm.ws.rsadapter.spi.WSRdbXaResourceImpl.recover(WSRdbXaResourceImpl.java:954)

at com.ibm.ws.Transaction.JTA.XARminst.recover(XARminst.java:137)

at com.ibm.ws.Transaction.JTA.XARecoveryData.recover(XARecoveryData.java:609)

at com.ibm.ws.Transaction.JTA.PartnerLogTable.recover(PartnerLogTable.java:511)

at com.ibm.ws.Transaction.JTA.RecoveryManager.resync(RecoveryManager.java:1784)

at com.ibm.ws.Transaction.JTA.RecoveryManager.run(RecoveryManager.java:2241)

Cause

Oracle requires services such as the WebSphere Application Server transaction service to have special permissions for performing transaction recoveries.

Solution

As user SYS, run the following commands on your Oracle server:

grant select on pending_trans$ to public;

grant select on dba_2pc_pending to public;

grant select on dba_pending_transactions to public;

grant execute on dbms_system to <user>;

User is a user ID in the application server that is authorized to perform transaction recovery for the XA data source. If you have not authorized any user IDs to perform transaction recovery, the application server uses the login alias for the data source as the user ID.
This problem is mentioned under Oracle bug: 3979190. Running the preceding commands solves the problem.

</snip>

Following the KC article, I updated the Oracle DB permissions: -

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jul 16 13:20:39 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

grant select on pending_trans$ to public;

Grant succeeded.

grant select on dba_2pc_pending to public;

Grant succeeded.

grant select on dba_pending_transactions to public;

Grant succeeded.

grant execute on dbms_system to cmnuser;

Grant succeeded.

and then restarted the AppCluster.

Let's see how it goes, but it's looking good so far.