Applies to:

Oracle Server - Enterprise Edition - Version: 7.3.0.0 to 11.1.0.6 - Release: 7.3.0 to 11.1
Information in this document applies to any platform.

Symptoms

ORA-2049 IN ANY DISTRIBUTED ENVIRONMENT

Changes

{CHANGE}

Cause

You receive the following error in any distributed environment: multi-master
replication, snapshots, distributed database or even a remote
update and receive the following error:

Solution

Problem Explanation:
====================
This errors occurs because the replicated transaction could not acquire the 
proper DML lock at the remote site to execute the deferred remote procedure 
call. 


Search Words:
=============
ORA-02049 dbms_defer_sys.execute dbms_defer_sys.push

Solution Description: 
===================== 

The following lists covers some possible causes for this and steps 
to take to resolve: 
 
1) The remote site may be down. 
   Do a 'ping' test to verify that the remote site(s) 
   are available to send the TXN to. 
   At the OS prompt: 
 
   Ex.  ping remotehostname   
 
   The remote site may have been down while the attempt to 
   push the TXN occurred.  Check the sys.dba_jobs view for  
   any broken jobs, BROKEN=Y and re-execute the job manually.
 
   Ex.  sqlplus repadmin/repadmin 
        sql> exec dbms_job.run(1);
 
 
2) Increase the DISTRIBUTED_LOCK_TIMEOUT in init.ora databases involved in   
   the environment.  The default value is 60 (seconds). 
 
   Increase the value to 300 (seconds) and restart all of 
   the replicated instances.   
 
   300 seconds is a common value in a distributed environment. 
   Depending on the network speed the value for DISTRIBUTED_LOCK_TIMEOUT  
   may need to be increased higher. 
 


3) Check the remote databases for any EXCLUSIVE locks held on the table(s)       
  involved.  Query the V$LOCK view. 
 
 
   Is the customer doing an ANALYZE TABLE TABLENAME COMPUTE STATISTICS  
   or SELECT FOR UPDATE on the table? 
 
   In 7.3 ANALYZE will put an exclusive lock on the object and prevent 
   the transaction from completing.  Check the object for TM locks 
   from querying V$LOCK.  Make sure there are no exclusive locks 
   and then attempt to re-execute the transaction from the remote database
   as shown in step 1. 

   In 10.2 an 'analyze table compute statistics' does not lock the table 
   in exclusive mode. All it does is take out a row level exclusive lock, 
   and this doesn't block distributed updates, inserts, or deletes. 
   There are no TM mode 6 locks for this operation.
 


Solution Explanation:
=====================
It is not always enough to just increase the DISTRIBUTED_LOCK_TIMEOUT
parameter.  If increasing it is not helping, then you need to resolve
issues at the remote site.  There may be some locks on the
tables Oracle needs to update.



Reference:
==========
DISTRIBUTED_LOCK_TIMEOUT in Oracle� Database Reference 10g Release 2 (10.2) Part Number B14237-02
DISTRIBUTED_LOCK_TIMEOUT in Oracle� Database Reference 11g Release 1 (11.1) Part Number B28320-01


Still have questions ?

To discuss this information further with Oracle experts and industry peers, we encourage you to review, 
join or start a discussion in the My Oracle Support Streams and Distributed Database Community