ORA-02050: Transaction Rolled Back - Remote DBs May Be In-Doubt

ORA-02050: Transaction ID Rolled Back, Some Remote DBs May Be In-Doubt

Error Overview

Error Text: ORA-02050: transaction TRAN_ID rolled back, some remote DBs may be in-doubt

ORA-02050 indicates that a distributed (two-phase commit) transaction failed during the commit phase, leaving the local database rolled back but with uncertainty about the state of one or more remote databases. The remote databases may have committed, rolled back, or remain in a “prepared” (in-doubt) state requiring DBA intervention.

Understanding Two-Phase Commit (2PC)

In a 2PC distributed transaction:

Prepare phase: Coordinator asks all nodes to prepare to commit
Commit phase: If all prepare successfully, coordinator instructs all to commit
In-doubt state: Network failure between phases leaves remote DBs uncertain

ORA-02050 typically occurs when the connection to a remote database is lost between prepare and commit.

Common Causes

Network Failures

Network outage during commit phase
Database link timeout (DISTRIBUTED_LOCK_TIMEOUT exceeded)
Firewall dropping idle connections
Listener restart on remote database

Remote Database Issues

Remote DB shutdown during transaction
Remote DB instance crash
Tablespace offline on remote during prepare
Resource exhaustion on remote (sessions, memory)

Configuration Problems

Mismatched commit point strength settings
GLOBAL_NAMES=TRUE mismatch
Distributed transaction recovery (RECO) not running

Diagnostic Steps

Identify In-Doubt Transactions

-- View pending distributed transactions
SELECT local_tran_id, global_tran_id, state, mixed,
       advice, tran_comment, fail_time, force_time
FROM dba_2pc_pending;

-- Possible states:
--   collecting: Still preparing
--   prepared: Ready to commit, waiting for instruction
--   committed: Local committed, remote unknown
--   forced commit: Manually committed
--   forced rollback: Manually rolled back

Check Transaction Neighbors

-- Show coordinator and remote DB info
SELECT local_tran_id, in_out, database, dbuser_owner,
       interface, dbid, sess#, branch
FROM dba_2pc_neighbors;

Verify RECO Process Status

-- RECO automatically resolves in-doubt transactions
SELECT name, status FROM v$bgprocess WHERE name = 'RECO';

-- Check parameter
SHOW PARAMETER distributed_lock_timeout;
SHOW PARAMETER distributed_recovery_connection_hold_time;

Check Alert Log

# Look for distributed transaction messages
tail -500 $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log \
    | grep -iE "DISTRIBUTED|RECO|2PC|in-doubt"

Resolution Steps

1. Wait for Automatic RECO Recovery

In most cases, the RECO process resolves in-doubt transactions automatically once network connectivity is restored:

-- Verify RECO running
SELECT * FROM v$bgprocess WHERE name = 'RECO';

-- Force RECO scan now
ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;

-- Check resolution progress
SELECT local_tran_id, state FROM dba_2pc_pending;

2. Manually Force Resolution

If RECO cannot resolve and the remote state is verified:

-- Step 1: Check actual state on remote DB
SELECT local_tran_id, state, fail_time
FROM dba_2pc_pending@remote_db
WHERE global_tran_id = 'GLOBAL_TRAN_ID_HERE';

-- Step 2a: If remote committed, force local commit
COMMIT FORCE 'local_tran_id_here';

-- Step 2b: If remote rolled back, force local rollback
ROLLBACK FORCE 'local_tran_id_here';

-- Step 3: Purge entry after resolution
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id_here');

3. Cleanup Mixed Transactions

If different nodes have inconsistent outcomes:

-- Mixed transaction (some committed, some rolled back)
SELECT local_tran_id, state, mixed FROM dba_2pc_pending WHERE mixed = 'yes';

-- Application-level reconciliation may be needed
-- Document the discrepancy and inform stakeholders

4. Increase Tolerance for Slow Networks

-- Allow longer wait for distributed locks
ALTER SYSTEM SET distributed_lock_timeout = 300 SCOPE=SPFILE;

-- Restart required for this parameter

Common Scenarios

Scenario 1: WAN Link Outage

Application updates local + remote DB; WAN drops mid-commit.
ORA-02050: transaction 12.34.5678 rolled back, some remote DBs may be in-doubt

Fix: Wait 5 minutes for RECO; verify dba_2pc_pending empty afterward.

Scenario 2: Remote DB Restart

Maintenance window restarted target DB; in-flight transactions left in-doubt.

Fix: After remote DB restart, both RECOs reconcile automatically. Check dba_2pc_pending on both sides.

Scenario 3: Application Holds Long Transaction

ETL holds distributed transaction for 2 hours; idle TCP connection killed.

Fix: Shorten transaction scope; use staging tables instead of cross-DB updates.

Sample Output

SQL> COMMIT;
COMMIT
*
ERROR at line 1:
ORA-02054: transaction 12.34.5678 in-doubt
ORA-02050: transaction 12.34.5678 rolled back, some remote DBs may be in-doubt
ORA-02063: preceding line from REMOTE_DB

SQL> SELECT local_tran_id, global_tran_id, state, fail_time, advice
  2  FROM dba_2pc_pending;

LOCAL_TRAN_ID  GLOBAL_TRAN_ID                STATE      FAIL_TIME    ADVICE
-------------- ----------------------------- ---------- ------------ ------
12.34.5678     PROD.WORLD.1234.12.34.5678    prepared   05-MAY-26    R

-- ADVICE='R' means rollback
-- Verify remote state first
SQL> SELECT state FROM dba_2pc_pending@remote_db
  2  WHERE global_tran_id = 'PROD.WORLD.1234.12.34.5678';
STATE
----------
prepared

-- Both sides prepared, neither committed; safe to rollback both
SQL> ROLLBACK FORCE '12.34.5678';
Rollback complete.

-- Then on remote DB
REMOTE> ROLLBACK FORCE '...';
REMOTE> EXEC DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('...');

-- Then locally
SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('12.34.5678');

Prevention Strategies

Network Reliability

Use redundant network paths
Configure TCP keepalive on database listeners
Set SQLNET.EXPIRE_TIME in sqlnet.ora

SQLNET.EXPIRE_TIME = 10

Transaction Design

Keep distributed transactions short
Avoid holding transactions across user-think time
Use compensation patterns instead of true 2PC where possible

Monitoring

-- Daily check for stuck transactions
SELECT COUNT(*) AS stuck_count,
       MIN(fail_time) AS oldest_failure
FROM dba_2pc_pending
WHERE fail_time < SYSDATE - 1;

#!/bin/bash
# Alert on persistent in-doubt transactions
COUNT=$(sqlplus -s / as sysdba <<EOF
SET PAGESIZE 0 FEEDBACK OFF
SELECT COUNT(*) FROM dba_2pc_pending WHERE fail_time < SYSDATE - 1/24;
EOF
)

if [ "$COUNT" -gt 0 ]; then
    echo "$COUNT in-doubt transactions older than 1 hour" | mail [email protected]
fi

Force RECO Aggressive Recovery

-- Increase RECO scan frequency
ALTER SYSTEM SET distributed_recovery_connection_hold_time = 60;
ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;

ORA-02049: Distributed lock timeout
ORA-02054: Transaction in-doubt
ORA-02055: Distributed update operation failed; rollback required
ORA-02063: Preceding line from database link
ORA-02068: Following severe error from database link

Troubleshooting Checklist

Check dba_2pc_pending for in-doubt transactions
Verify RECO process is running
Inspect dba_2pc_neighbors for remote DB info
Confirm remote DB state before forcing commit/rollback
Run DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY after resolution
Check alert log for network/2PC errors
Consider keepalive and timeout tuning