VERIFIED SOLUTION i

Resolve error "WTRN0037W: The transaction service encountered an error on an xa_recover operation. Error code is: XAER_RMERR" in EngageOne

Product Feature: Installation / Upgrade


 

Issue

The following errors is encountered in the SystemOut.log following an upgrade of the EngageOne environment using Websphere and Oracle:

[3/26/14 0:50:24:065 GST] 00000006 XARecoveryDat A WTRN0151I: Preparing to call xa recover on XAResource: wpdbDS_jcr [3/26/14 0:50:24:333 GST] 00000006 WSRdbXaResour E DSRA0304E: XAException occurred. XAException contents and details are: The cause is : null. [3/26/14 0:50:24:334 GST] 00000006 WSRdbXaResour E DSRA0302E: XAException occurred. Error code is: XAER_RMERR (-3). Exception is: <null> [3/26/14 0:50:24:335 GST] 00000006 XARminst E WTRN0037W: The transaction service encountered an error on an xa_recover operation. The resource was com.ibm.ws.rsadapter.spi.WSRdbXaResourceImpl@276e276e. The error code was XAER_RMERR. The exception stack trace follows: javax.transaction.xa.XAException at oracle.jdbc.xa.OracleXAResource.recover(OracleXAResource.java:709) at com.ibm.ws.rsadapter.spi.WSRdbXaResourceImpl.recover(WSRdbXaResourceImpl.java:1088) at com.ibm.ws.Transaction.JTA.XARminst.recover(XARminst.java:141) at com.ibm.ws.Transaction.JTA.XARecoveryData.recover(XARecoveryData.java:626)

Cause

Oracle requires that you have special permissions to attempt to perform the recovery. If there is a system crash during a distributed transaction, the WebSphere Application Server transaction manager will attempt to clean up any failed transactions which it finds in the transaction logs. When the recovery involves an Oracle XAResource, the Oracle JDBC driver oracle.jdbc.xa.OracleXAResource.recover function will make a call to DIST_TXN_SYNC. The above error occurs when the user trying to execute the .recover method does not have sufficient privileges to make a call to DIST_TXN_SYNC.

Resolution

UPDATED: August 3, 2017
1. Create a file “grant_jcrdb.sql” in “/home/oracle” directory:
grant select on pending_trans$ to JCRDB; grant select on dba_2pc_pending to JCRDB; grant select on dba_pending_transactions to JCRDB;
grant execute on dbms_system to JCRDB; grant execute on dbms_xa to JCRDB;
Note: Change the JCRDB user/schema name as per your environment.

2. Run the script:
ORACLE_SID=WP_INST sqlplus “/as sysdba” @/home/oracle/grant_jcrdb.sql

3. If the recovery of the inflight transactions is not important, then one possible workaround to this problem is to remove the transaction logs. (Does NOT apply to WebSphere Process Server and IBM Business Process Manager, please see note below for details) The transaction logs are stored in the following folder: <WebSphere Application Server_install_root>\profiles\<PROFILE_NAME>\tranlog\<CELL_NAME>\<NODE_NAME>\<SERVER_NAME>\transaction
To remove the transaction logs, do the following: 3.1 Stop the server. 3.2 Delete the ‘\transaction‘ folder and all subdirectories. 3.3 Restart the server. The above exceptions will no longer occur.