ORA-02051: another session or branch in same transaction failed or finalized

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.

 

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.

Observe the following message in the alert.log file:

Following on-commit snapshots not refreshed :
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:

If you specify this clause, then you cannot subsequently execute a distributed transaction on any master table of this materialized view. For example, you cannot insert into the master by selecting from a remote table. The ON DEMAND clause does not impose this restriction on subsequent distributed transactions on master tables.

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 )

 

 96 views last month,  4 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply