IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
I got ” ORA-02051: another session in same transaction failed ” error in Oracle database.
ORA-02051 another session or branch in same transaction failed or finalized
Details of error are as follows.
ORA-02051 another session or branch in same transaction failed or finalized Cause: A session at the same site with the same global transaction ID failed. The failure could also be caused by an application error if an attempt was made to update the database while another tightly coupled transaction branch with the same global transaction ID was finalized or aborted. Action: No action necessary as the transaction will be automatically recovered. In the case of an application error, make sure there are no more updates to the database once the transaction manager has started two-phase commit for tightly coupled distributed transaction.
When invoking xa_commit from a Transactional Monitor like Tuxedo or Websphere, you can get errors ORA-2054 and ORA-2051 in the TM itself and alert.log file. In addition to see you may see prepared transactions on dba_2pc_pending views as a consequence of this error. On TM log we can see: [4/21/10 10:25:12:425 CEST] 000019ba WSRdbXaResour E DSRA0304E: XAException occurred. XAException contents and details are: The XA Error is : -3 The XA Error message is : A resource manager error has occured in the transaction branch. The Oracle Error code is : 2054 The Oracle Error message is: Internal XA Error The cause is : java.sql.SQLException: ORA-02054: transaction 10.10.55727 in-doubt ORA-02051: another session in same transaction failed . [4/21/10 10:25:12:432 CEST] 000019be WSRdbXaResour E DSRA0304E: XAException occurred. XAException contents and details are: The XA Error is : -3 The XA Error message is : A resource manager error has occured in the transaction branch. The Oracle Error code is : 2054 The Oracle Error message is: Internal XA Error The cause is : java.sql.SQLException: ORA-02054: transaction 9.13.56980 in-doubt ORA-02051: another session in same transaction failed In alert.log file we can see : Following on-commit snapshots not refreshed : MYTEST.MY_MVIEW Error 2051 trapped in 2PC on transaction 9.13.56980. Cleaning up. Error stack returned to user: ORA-02054: transaction 9.13.56980 in-doubt ORA-02051: another session in same transaction failed Wed Apr 21 10:25:12 2010 In dba_2pc_pending and dba_2pc_neighbors we can see pending transactions.
another session or branch in same transaction failed or finalized
This ORA-02051 error is related to the session at the same site with the same global transaction ID failed. The failure
could also be caused by an application error if an attempt was made to update the database while another tightly coupled transaction branch with the same global transaction ID was finalized or aborted.
Observe the following message in the alert.log file:
MYTEST.MY_MVIEW
MYTEST.MY_MVIEW is an ON_COMMIT refreshable materialized view.
When xa_commit call is invoked by TM, it fails with :
Return Code is -7 : XAER_RMFAIL – resource manager unavailable
because the transaction has done a DML operation on a base table where materialized view MYTEST.MY_MVIEW is based on.
This error is raised because a restriction of ON COMMIT materialized views.
At Oracle Database SQL Reference guide we can find the following restrictions for ON COMMIT mviews:
As XA operations are managed as Distributed transactions this error is expected.
To solve this error, Do not create ON COMMIT materialized views on top of tables that are going to be modified by XA operations. there is no way to skip this restriction.
Workaround
Use ON DEMAND materialized views, which refresh operation does not suffer this restriction.
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
