Clear pending XA transactions (Oracle Thin/XA Driver) on Weblogic

By one of our customer, I met the following errors regularly on WLS production environment;

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

 Unexpected exception while enlisting XAConnection java.sql.SQLException: XA error: XAResource.XAER_RMERR start() failed on resource '=XXXJDBCDataSource': 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 'XXXJDBCDataSource': XAER_RMERR : A resource manager error has occured in the transaction oracle.jdbc.xa.OracleXAException

To Fix this problem we need to follow the following steps:

First of all I tried to execute the following command;

grant select on v$xatrans$ to public;but I got the following error;

SQL> desc v$xatrans$
ERROR:
ORA-04043: object v$xatrans$ does not exist.

so we will need to execute the following script;

•  Log on to database as system user
• Execute sql script xaview.sql in /ORACLE_HOME/rdbms/admin
• SQL>

select object_name ,object_type from dba_objects where object_name like '%XATRANS%'

 

OBJECT_NAME                                                                                                                      OBJECT_TYPE

------------------

D$PENDING_XATRANS$                                                                                                               VIEW

V$PENDING_XATRANS$                                                                                                               SYNONYM

D$XATRANS$                                                                                                                       VIEW

V$XATRANS$                                                                                                                       SYNONYM

• Execute the following command 
• grant select on v$xatrans$ to public (or );
• Execute sql:
• grant select on pending_trans$ to public;
• Execute sql:
• grant select on dba_2pc_pending to public;

 

Any database account performing distributed transactions must have the following privileges:

 

• Execute sql:

  grant select on dba_pending_transactions to public;

• Execute sql:

  grant force any transaction to public; 

you need to enable cleaning unfinished/pending transactions in Weblogic Server 

• Login to AS console
• Home >Summary of Servers >Summary of JDBC Data Sources > XX DataSource> Transcaction
• Enable Recover Only Once for the JDBC data source.
• Enable XA End Only Once for the JDBC data source

 

 

Reason to 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.

For more details please visit the following URL;

http://docs.oracle.com/cd/E13222_01/wls/docs100/jta/jtatxexp.html#wp1053782