Websphere errors regarding Oracle transaction recovery
When restarting Websphere there are occasions when you get errors related to Oracle transaction recovery.
This happens because Websphere is set to recover the transactions that were not handled when the server went down and it cannot do so because Oracle requires special permissions to attempt to perform the recovery.
This problem can occur with all versions of Oracle: 8i, 9i, 10g, 11g.
To resolve the problem, the following commands are needed to be executed as 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>>;
where <<user>> is the username configured in the Websphere datasource, for that specific database.
If you don’t care to recover those transactions and you just hate seeing that exception in the logs over and over again (it does not go away if you attempt yet another restart), you can go ahead and delete the transactions manually.
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
You’d have to stop the Websphere server, delete the content of ‘\transaction’ folder and all subdirectories and restart the Websphere server.
