Resolving XAER_NOTA : The XID is not valid
The below Error occurs if you are using two different JDBCConnection Pools for the same Oracle Database or two DataSources for the same pool. This post will help you understand and resolve the below issue.
ERROR:
Cause: java.sql.SQLException: Unexpected exception while enlisting XAConnection java.sql.SQLException: XA error: XAResource.XAER_NOTA start() failed on resour
ce ‘jdbc/TestDB’: XAER_NOTA : The XID is not valid
oracle.jdbc.xa.OracleXAException
at oracle.jdbc.xa.OracleXAResource.checkError(OracleXAResource.java:938)
As defined XA protocol requires all SQL statements for one and the same transaction branch be issued between an xa_start() and an xa_end() call. In a distributed transaction, an individual database represents one XAResource that will have one transaction branch in one distributed transaction. The WebLogic Server transaction manager considers every JDBC connection pool as a separate XAResource, i.e., it cannot evaluate if different connection pools or data sources point to the same database.
WebLogic Server makes sure that every call to one XAResource has the correct sequence of calls: xa_start, sql statements, and xa_end. Also WebLogic Server represents a multi-threading system, where different operations occur in a highly parallel environment. Consequently, calls to different XAResources like JDBC connection pools will happen concurrently. If two XA-Resources are defined for the same database, nested xa_start – xa_end pairs may result.
For example if there are two different ConnectionPools in one transaction, the calls may happen as follows:
— xa_start(NOFLAGS) to ConnectionPool-1
— sql statement for ConnectionPool-1
— xa_start(JOIN) to ConnectionPool-2
— sql statement for ConnectionPool-1
— sql statement for ConnectionPool-2
— xa_end() for ConnectionPool-2
— xa_end() for ConnectionPool-1
This scenario will work perfectly fine as long as ConnectionPool-1 and ConnectionPool-2 are pointing to different databases and therefore different resource managers which will complete Transaction smoothly. However, if ConnectionPool-1 and ConnectionPool-2 point to the same Oracle database, an Oracle issue will lead to the above exception.
SOLUTION:
If it is not possible to define only one connection pool for one database, using the Oracle OCI driver can be a solution for this problem, as it synchronizes xa_start calls.
Work Around
If you are facing this issue even after having one DataSource this work around shall work.
1. If you have not already done so, in the Change Center of the Administration Console, click “Lock & Edit”
2. In the left pane, click on the Data Sources under Services > JDBC > Data Sources.
3. In the right pane, Click on your configured DataSource. In my case it is JDBC Data Source-1
4. In the Settings for JDBC Data Source-1 page, select Configuration > Transaction tab.
5. Click the Set XA Transaction Timeout check box flag and set the XA Transaction Timeout to 0.
6. Click Save and in the Change Center of the Administration Console, click “Activate Changes”.
7. Restart all the WLS nodes targeted to this DataSource.
8. By completing steps1-6, the DataSource will now make of JTA Timeout specified under base_domain > Configuration > JTA
— In the left pane, click on the domain name.
— In the right pane, Select Configuration > JTA tab. specify timeout in second as 600.