ORA-02055: Distributed Update Failed - Rollback Required

ORA-02055: Distributed Update Operation Failed; Rollback Required

Error Overview

Error Text: ORA-02055: distributed update operation failed; rollback required

The ORA-02055 error occurs during a distributed transaction when a remote database operation fails partway through a two-phase commit (2PC) sequence. Oracle marks the local transaction as requiring rollback to maintain consistency across all participating databases. The error signals that the distributed transaction cannot proceed and that the entire transaction must be explicitly rolled back before any further work can be done in the session.

This error commonly appears in environments that use database links to perform DML across multiple Oracle databases. Once raised, the current session is “poisoned” — no further SQL will succeed until a ROLLBACK is issued.

Common Causes

1. Network Interruption During Two-Phase Commit

• The network connection to the remote database dropped between the PREPARE and COMMIT phases
• A firewall timeout silently killed the database link connection mid-transaction
• Packet loss caused an incomplete acknowledgement from the remote instance

2. Remote Database Unavailable

• The target database was shut down or restarted during the distributed transaction
• The remote listener stopped responding after the local prepare phase completed
• An RAC instance on the remote side failed between the prepare and commit phases

3. Remote Rollback Segment or Undo Issues

• The remote database ran out of undo space and could not maintain transaction state
• ORA-01555 on the remote side caused the remote portion of the transaction to fail
• Undo tablespace on the remote database was taken offline

4. Two-Phase Commit Protocol Failure

• The global coordinator lost contact with one or more participants after PREPARE
• An in-doubt transaction on the remote side was manually forced to rollback by a DBA
• The COMMIT_POINT_STRENGTH parameter caused an unexpected coordinator selection

5. Resource Constraints on the Remote Database

• Maximum sessions or processes exceeded on the remote database
• The remote database reached its maximum number of open transactions
• Insufficient shared pool on the remote database prevented 2PC state storage

6. Database Link Misconfiguration

• The database link user lacked the required privileges on the remote side
• The link used a fixed user account whose password was recently changed
• The remote service name changed without the link being recreated

Diagnostic Queries

Check Current In-Doubt Transactions

-- View all in-doubt transactions on the local database
SELECT
  local_tran_id,
  global_tran_id,
  state,
  mixed,
  advice,
  tran_comment,
  fail_time,
  force_time,
  retry_time,
  os_user,
  os_terminal,
  host,
  db_user,
  commit#
FROM dba_2pc_pending
ORDER BY fail_time DESC;

Check Pending Distributed Transactions Across All Nodes

-- Show pending neighbors (remote participants)
SELECT
  local_tran_id,
  in_out,
  database,
  dbuser,
  interface,
  dbid,
  sess#,
  branch
FROM dba_2pc_neighbors
ORDER BY local_tran_id;

Identify Sessions Waiting on Distributed Transactions

-- Sessions blocked waiting for distributed transaction resolution
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.status,
  s.event,
  s.wait_class,
  s.seconds_in_wait,
  s.sql_id,
  s.program,
  s.machine
FROM v$session s
WHERE s.event LIKE '%distributed%'
   OR s.event LIKE '%db link%'
   OR s.wait_class = 'Other'
   AND s.state = 'WAITING'
ORDER BY s.seconds_in_wait DESC;

Check Database Link Status

-- View all database links accessible to current user
SELECT
  owner,
  db_link,
  username,
  host,
  created
FROM dba_db_links
ORDER BY owner, db_link;

-- Test connectivity via a database link
SELECT * FROM dual@remote_db_link;

Review Alert Log for 2PC Errors

-- Check recent errors from the alert log via ADR
SELECT
  originating_timestamp,
  message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-02055%'
   OR message_text LIKE '%2PC%'
   OR message_text LIKE '%in-doubt%'
ORDER BY originating_timestamp DESC
FETCH FIRST 50 ROWS ONLY;

Assess RECO Background Process Activity

-- Check if RECO is actively resolving in-doubt transactions
SELECT
  name,
  description,
  value
FROM v$bgprocess
WHERE name = 'RECO';

-- View RECO trace for resolution activity
SELECT
  originating_timestamp,
  message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%RECO%'
ORDER BY originating_timestamp DESC
FETCH FIRST 30 ROWS ONLY;

Step-by-Step Resolution

1. Immediate Action — Roll Back the Failed Transaction

The first and most important step after receiving ORA-02055 is to issue an explicit rollback in the affected session. No further DML will succeed until this is done.

-- In the session that received ORA-02055
ROLLBACK;

After the rollback, retry the entire distributed transaction from scratch.

2. Allow RECO to Automatically Resolve In-Doubt Transactions

Oracle’s RECO (Recoverer) background process automatically attempts to resolve in-doubt transactions. Allow time for RECO to contact the remote database and complete resolution. Monitor its progress:

-- Check if in-doubt transactions are being resolved
SELECT local_tran_id, state, fail_time, retry_time
FROM dba_2pc_pending
ORDER BY retry_time DESC;

If retry_time is updating regularly, RECO is actively working. Wait for the state to change from prepared to resolved (rows will disappear from the view).

3. Manually Force Commit or Rollback an In-Doubt Transaction

If RECO cannot reach the remote database and you must resolve manually (after confirming the remote database’s state with the remote DBA):

-- Force a commit of an in-doubt transaction (use only when remote side committed)
COMMIT FORCE 'local_tran_id';

-- Force a rollback of an in-doubt transaction (use only when remote side rolled back)
ROLLBACK FORCE 'local_tran_id';

Warning: COMMIT FORCE and ROLLBACK FORCE override normal 2PC safety. Only use them after verifying the transaction state on all remote participants.

4. Delete Resolved Pending Transaction Records

After forcing resolution, clean up the DBA_2PC_PENDING entries:

-- Execute as SYS or user with DBA privilege
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

-- Or delete directly from the system table (requires extreme care)
-- DELETE FROM sys.pending_trans$ WHERE local_tran_id = 'local_tran_id';
-- COMMIT;

5. Verify Database Link Connectivity

After resolving in-doubt transactions, confirm the database link is operational before retrying:

-- Test the link
SELECT SYSDATE FROM dual@remote_db_link;

-- Check link definition is correct
SELECT db_link, username, host FROM dba_db_links WHERE db_link = 'REMOTE_DB_LINK';

-- If the link is broken, drop and recreate it
DROP DATABASE LINK remote_db_link;

CREATE DATABASE LINK remote_db_link
  CONNECT TO remote_user IDENTIFIED BY password
  USING 'remote_service';

6. Retry the Original Transaction

Once connectivity is confirmed and in-doubt transactions are resolved, retry the full distributed operation:

BEGIN
  -- Distributed DML
  UPDATE local_table SET col1 = :val1 WHERE id = :id;
  UPDATE remote_table@remote_db_link SET col1 = :val1 WHERE id = :id;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

Prevention Strategies

1. Minimize Distributed Transaction Scope

Keep distributed transactions as short as possible. Reduce the number of remote operations inside a single commit boundary:

-- Avoid mixing many remote operations in one transaction
-- BAD: Multiple remote databases in one transaction
UPDATE tableA@db_link_1 SET col = val WHERE id = :id;
UPDATE tableB@db_link_2 SET col = val WHERE id = :id;
COMMIT;

-- BETTER: Process one remote database at a time when possible
UPDATE tableA@db_link_1 SET col = val WHERE id = :id;
COMMIT;

UPDATE tableB@db_link_2 SET col = val WHERE id = :id;
COMMIT;

2. Implement Retry Logic in Application Code

CREATE OR REPLACE PROCEDURE distributed_update_with_retry(
  p_id       IN NUMBER,
  p_val      IN VARCHAR2,
  p_max_retry IN NUMBER DEFAULT 3
) AS
  v_retry NUMBER := 0;
  v_success BOOLEAN := FALSE;
BEGIN
  WHILE NOT v_success AND v_retry < p_max_retry LOOP
    BEGIN
      UPDATE local_table SET col1 = p_val WHERE id = p_id;
      UPDATE remote_table@remote_db_link SET col1 = p_val WHERE id = p_id;
      COMMIT;
      v_success := TRUE;
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK;
        v_retry := v_retry + 1;
        IF v_retry >= p_max_retry THEN
          RAISE;
        END IF;
        -- Wait before retry
        DBMS_SESSION.SLEEP(5 * v_retry);
    END;
  END LOOP;
END;
/

3. Configure COMMIT_POINT_STRENGTH Appropriately

-- On the most reliable database (usually the primary coordinator)
-- Set a higher commit point strength
ALTER SYSTEM SET commit_point_strength = 200 SCOPE=BOTH;

-- On remote/less reliable databases, use a lower value
ALTER SYSTEM SET commit_point_strength = 50 SCOPE=BOTH;

4. Monitor In-Doubt Transactions Proactively

-- Create a scheduled job to alert on lingering in-doubt transactions
CREATE OR REPLACE PROCEDURE check_indoubt_transactions AS
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count
  FROM dba_2pc_pending
  WHERE fail_time < SYSDATE - (30/1440);  -- Older than 30 minutes

  IF v_count > 0 THEN
    -- Send alert via DBMS_ALERT or external notification
    DBMS_OUTPUT.PUT_LINE('WARNING: ' || v_count || ' in-doubt transactions older than 30 minutes.');
  END IF;
END;
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'MONITOR_2PC_PENDING',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'check_indoubt_transactions',
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
    enabled         => TRUE,
    comments        => 'Alert on lingering in-doubt distributed transactions'
  );
END;
/

5. Network and Infrastructure Hardening

• Configure TCP keepalives on the database server OS to detect dead connections earlier
• Set SQLNET.EXPIRE_TIME in sqlnet.ora to probe connections and detect failures
• Ensure firewalls do not have silent session timeouts shorter than the longest expected transaction
• Use dedicated network interfaces for database link traffic in critical environments

Diagnostic Scripts

These Oracle Day by Day utility scripts can assist with distributed transaction diagnostics:

• gvsess.sql — Identify sessions involved in distributed operations
• health.sql — Overall database health including pending transactions

Related Errors

• ORA-02063 - Preceding line from database link (wraps remote errors)
• ORA-02064 - Distributed operation not supported
• ORA-02067 - Transaction or savepoint rollback required
• ORA-02069 - GLOBAL_NAMES parameter must be TRUE
• ORA-02070 - Database does not support operation in this context
• ORA-02049 - Timeout: distributed transaction waiting for lock

Emergency Response

Quick Fixes

1. Roll back immediately in the affected session

   ROLLBACK;

2. Force-resolve a stuck in-doubt transaction (after remote DBA confirmation)

   -- If remote side committed:
   COMMIT FORCE 'local_tran_id';
   
   -- If remote side rolled back:
   ROLLBACK FORCE 'local_tran_id';

3. Kill the affected session if it is blocking others

   ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Post-Resolution Cleanup

-- Purge resolved pending transaction entries
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

-- Verify DBA_2PC_PENDING is clear
SELECT COUNT(*) FROM dba_2pc_pending;

-- Re-test database link
SELECT SYSDATE FROM dual@remote_db_link;

-- Review AWR for distributed transaction wait events
SELECT event, total_waits, time_waited
FROM dba_hist_system_event
WHERE event LIKE '%distributed%'
  AND snap_id = (SELECT MAX(snap_id) FROM dba_hist_snapshot)
ORDER BY time_waited DESC;