Oracle WebLogic Server: How to clear pending XA transactions(Oracle Thin/XA Driver)?

If you are using XA transactions in your JDBC datasource and you are using Oracle Thin/XA Driver, and you have errors, for example:

java.sql.SQLException: Internal error: Cannot obtain XAConnection weblogic.common.resourcepool.ResourceDeadException: Pool testJDBCDataSource is disabled, cannot allocate resources to applications.

Unexpected exception while enlisting XAConnection java.sql.SQLException: XA error: XAResource.XAER_RMERR start() failed on resource 'testJDBCDataSource': XAER_RMERR : A resource manager error has occured in the transaction branch  oracle.jdbc.xa.OracleXAException

Unexpected exception while enlisting XAConnection java.sql.SQLException: XA error: XAResource.XAER_RMERR start() failed on resource 'testJDBCDataSource': XAER_RMERR : A resource manager error has occured in the transaction branchoracle.jdbc.xa.OracleXAException

When the Weblogic Server unclean pending XA transactions. How It fix?

First you need to enable XA in the database:

1. Log on to database as system user
2. Execute sql script xaview.sql in ORACLE_HOME/rdbms/admin
3. Execute sql:

   grant select on v$xatrans$ to public (or <user>);

4.  Execute sql:

   grant select on pending_trans$ to public;

5. Execute sql:

   grant select on dba_2pc_pending to public;

Here are the details.
Any database account performing distributed transactions must have the following privileges:

1. Execute sql:

   grant select on dba_pending_transactions to public;

2. Execute sql:

   grant force any transaction to public (or <user>);

Second, you need to enable cleaning unfinished/pending transactions in WebLogic Server.

1. Enable Recover Only Once for the JDBC data source.
2. Enable XA End Only Once for the JDBC data source.

Why enable “Recover Only Once”?

The WebLogic Server transaction manager retries the commit call every minute, until a valid XAResource instance is registered with the WebLogic Server transaction manager, if the setting “Recover only once” is allowed and the commit call failure then the Weblogic Server transaction manager calls recover on the resource only once and not every minute.

More information about the recovery are here.

How to select pending XA transaction in DB?

select count(*) , min(fail_time),max(fail_time) from dba_2pc_pending;

转载自：http://www.tomecode.com/2010/10/30/oracle-weblogic-server-how-to-clear-pending-xa-transactionsoracle-thinxa-driver/