xa_recover operation XAER_RMERR

xa_recover operation XAER_RMERR error shows up in logs frequently in Websphere Portal Server v8.0 when configured with Oracle database. In Portal server system out logs, below exception is seen frequently. Customer was repeatedly asking me whats the issue and why its throwing very often.

Error occurred on,

Portal server v8.0.0.1

Oracle Database v12c

AIX 7 Operating system

IBM Support link explains clearly on the issue and also a resolution here, http://www-01.ibm.com/support/docview.wss?uid=swg21196663

Exception details:

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)

Resolution

1. Run the following commands as user SYS:

grant select on pending_trans$ to <user>;
grant select on dba_2pc_pending to <user>;
grant select on dba_pending_transactions to <user>;
grant execute on dbms_system to <user>;  (If using Oracle 10.2.0.3 or lower JDBC driver)
grant execute on dbms_xa to <user>; (If using Oracle 10.2.0.4 or higher JDBC driver)

Where <user> is the username configured in the Oracle datasource’s authentication alias.

2. If the recovery of the inflight transactions is not important, then one possible workaround to this problem is to remove the transaction logs.

<PROFILE_NAME>\tranlog\<CELL_NAME>\<NODE_NAME>\<SERVER_NAME>\transaction

To remove the transaction logs, do the following:
(1) Stop the server.
(2) Delete the ‘\transaction‘ folder and all subdirectories.
(3) Restart the server.
The above exceptions will no longer occur.

Though one of the solution is to update the Oracle database privileges, but in my case, customer was not ready to do (infact the oracle database team didn’t agree to do). So as this doesn’t affect much, during any restarts they normally clean up the transactions logs (after taking a backup of portal server partition).