ORA-02049: timeout: distributed transaction – Application team reported this error ORA-02049. Some of the jobs are getting failed which are fetching data from remote servers using the DBlink.
When we looked the issue we found the application team not used proper commit; within transactions and they are fetching the data in bulk from RAC environments.
Cause: The time to wait on a lock in a distributed transaction has been exceeded.
This time is specified in the initialization parameter DISTRIBUTED_LOCK_TIMEOUT.
Action: This situation is treated as a deadlock and the statement was rolled back
ORA-02049: timeout: distributed transaction
Sometime if we have set some restriction on OS level to control the usage of PGA in RAC environment i.e. if any remote job using log of PGA then OS job will kill that session and application job might be failed with same error.
Generally in the RAC environments where more than one node is available to process the transaction means executing distributed transaction are unable to acquire the lock on same resource across the instance till the time limit specified in PFILE parameter DISTRIBUTED_LOCK_TIMEOUT has reached. The default value of this parameter is 60 seconds.
Example:
column local_tran_id new_value TRANS_ID
select local_tran_id from dba_2pc_pending;
We can also check the OPEN_LINKS PARAMETER values and increase it.
Open_links restricts the number of concurrent db link executions per session. The default value is 4 which could be cause of this error and we can increase it as per your environments.
show parameter open_links
alter system set open_links=30 scope=spfile;
alter system set open_links_per_instaance=30 scope=spfile;
To apply the value in SPFILE we need to bounce the database.
shu immediate
startup
Check the User and the SQL text (DML) of an In-doubt Distributed Transaction found in dba_2pc_pending
Goal:
An in-doubt distributed transaction is present in the data dictionary view DBA_2PC_PENDING. How to find the user (schema) who initiated this transaction and the transaction SQL text ?
Solution:
Having the transaction_id of the in-doubt transaction and the corresponding COMMIT# from DBA_2PC_PENDING , use Logminer to examine the online redo log(s) or the archived redo log(s) where the transaction is in , to determine the user name and the SQL text.
select mixed, count(*) from DBA_2PC_PENDING group by mixed;
OCAL_TRAN_ID COMMIT# STATE
---------------------- ---------------- ----------------
1.16.940 1485850 prepared
With the COMMIT# , check whether the transaction is in Online Redo logs (query V$LOG and V$LOGFILE) or in Archived Red logs (query V$ARCHIVED_LOG)
Online Redo:
SQL> select a.thread#, a.sequence#, b.member
2 from v$log a, v$logfile b
3 where a.group# = b.group# and
4 1485850 between a.FIRST_CHANGE# and a.NEXT_CHANGE#;
THREAD# SEQUENCE#
---------- ----------
MEMBER
--------------------------------------------------------------------------------
1 50
/u01/app/oracle/oradata/TESTDB/redo01.log
Archived Redo:
SQL> select thread#, sequence#, name
2 from v$archived_log
3 where 1485850 between FIRST_CHANGE# and NEXT_CHANGE#;
THREAD# SEQUENCE#
---------- ----------
NAME
--------------------------------------------------------------------------------
1 50
/u01/app/oracle/TESTDB/arch1_50_2342213.dbf
Now user Logminer to examine the redo, for example:
SQL> show user
USER is "SYS"
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/TESTDB/arch1_50_2342213.dbf');
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR (OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
SQL> select OPERATION, TABLE_NAME, SQL_REDO, USERNAME
2 from V$LOGMNR_CONTENTS
3 where XIDUSN=1 and
4 XIDSLT = 16 and
5 XIDSQN=940 order by SCN asc;
OPERATION TABLE_NAME
-------------------------------- --------------------------------
SQL_REDO
--------------------------------------------------------------------------------
USERNAME
------------------------------
START
set transaction read write;
SCOTT
INSERT EMP
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('1041','MULDER',NULL,NULL,NULL,NULL,NULL,'10');
SCOTT
INTERNAL
SCOTT
INTERNAL
SCOTT
SQL>
As seen above, the in-doubt transaction 1.16.940 was executed by SCOTT and was an INSERT into SCOTT.DEPT
Please share this Blog with your colleagues or friends. Your suggestions and feedback are very helpful for everyone who come to this site and learn it from oracleocpworld.com.
Please comment here for your any query related to above post. You can email me on : oracleocpworld@gmail.com.