When WebSphere Application Server attempts to recover Oracle database transactions, the following exception is issued:
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
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.
This problem can occur with all versions of Oracle: 8i, 9i, 10g, and 11g.
Resolving the problem
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.
This problem is mentioned under Oracle bug: 3979190. Running the preceding commands solve the problem.
Update: Since the SYS.DBMS_SYSTEM package contains many procedures, granting execute privileges to that package gives access to all of them. In Oracle patch 5892995 (JDBC driver) and 5945463 (server), Oracle has moved the DIST_TXN_SYNC procedure to a new package called DBMS_XA. These patches are available in 10.2.0.4 and higher. By using the new DBMS_XA package, it will reduce the security risk which DBMS_SYSTEM might cause.
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, 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:
(1) Stop the server.
(2) Delete the '
\transaction' folder and all subdirectories.
(3) Restart the server.
The above exceptions will no longer occur.
NOTE: Transaction logs should NOT be deleted if running in a WebSphere Process Server environment.
The WebSphere Process Server system can get into an inconsistent state because the logs are tightly coupled with information in other places like BPEDB, WPSDB, SIB messages and MQ, if it is configured.
If WebSphere Process Server is being used, follow the information in this document:
How to resolve Transaction- and Partnerlog recovery issues in WebSphere Process Server
|Application Servers||WebSphere Application Server for z/OS||DB Connections/Connection Pooling||Multi-Platform||6.0|
|Application Servers||Runtimes for Java Technology||Java SDK||z/OS||6.0, 6.0.1|
|Application Servers||Runtimes for Java Technology||Java SDK|