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

喜欢ヅ旅行 2022-08-06 14:05 90阅读 0赞

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][XA transactions 1]. 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][Weblogic Server].

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

![Weblogic XA transaction][]

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/

[XA transactions]: http://download.oracle.com/docs/cd/E13222_01/wls/docs100/jta/jtatxexp.html
[JDBC datasource]: http://download.oracle.com/docs/cd/E12840_01/wls/docs103/jdbc_admin/config.html
[Weblogic Server]: http://www.oracle.com/technetwork/middleware/weblogic/overview/index.html
[XA transactions 1]: http://docs.sun.com/app/docs/doc/820-5892/ref_xatrans?l=en&a=view
[details.]: http://download.oracle.com/docs/cd/E13222_01/wls/docs100/jta/thirdpartytx.html
[Weblogic XA transaction]: http://tomecode.com/img/weblogic/10g/weblogicXaDatasourceTransaction.PNG
[here]: http://download.oracle.com/docs/cd/E13222_01/wls/docs100/jta/jtatxexp.html#wp1053782