ORA-02067: Transaction Rollback Required - Fix DB Link Errors

ORA-02067: Transaction or Savepoint Rollback Required

Error Overview

Error Text: ORA-02067: transaction or savepoint rollback required

The ORA-02067 error is raised when Oracle determines that a distributed transaction has entered an inconsistent or unrecoverable state and the only valid action is to roll back — either the entire transaction or back to a savepoint established before the distributed work began. The error is closely related to ORA-02055, but specifically highlights that savepoint-level recovery may be possible if the application established a savepoint before the distributed operation.

This error most commonly appears in multi-database environments using database links when a remote DML operation fails after the local side has already made changes. The local Oracle engine refuses to continue until the transaction boundary is cleaned up, preventing partial commits across distributed participants.

Common Causes

1. Partial Failure During Distributed DML

• Local INSERT or UPDATE succeeded, but the remote DML over a database link subsequently failed
• The remote database returned an error (deadlock, constraint violation, ORA-01555) after local changes were already made
• Oracle cannot proceed with mixed success/failure state across distributed participants

2. Savepoint Established Before Distributed Operation

• A SAVEPOINT sp1 was set before a remote DML was issued
• The remote DML failed, leaving the transaction in a state where only rollback to sp1 or a full ROLLBACK is valid
• Application code attempted to continue after the remote failure without rolling back

3. Nested Distributed Transactions with Cascading Failures

• A chain of database links (A → B → C) where link B→C fails after A→B succeeded
• The failure cascades back as ORA-02063 wrapping the remote error, followed by ORA-02067
• The intermediate database B has prepared its portion but C has failed

4. Network Timeout Mid-Transaction

• A database link timeout occurred after the remote prepare phase had begun
• Oracle detected the link failure during execution, not during commit, leaving state inconsistent
• The SQLNET.EXPIRE_TIME or OS TCP timeout killed the connection between operations

5. Remote Constraint Violation or Trigger Failure

• A foreign key, unique constraint, or check constraint on the remote table rejected the DML
• A remote trigger raised an application exception that propagated back over the link
• The remote DML executed partially (e.g., only some rows of a multi-row INSERT) before failing

6. Autonomous Transaction Interaction with Distributed Transactions

• PL/SQL code using PRAGMA AUTONOMOUS_TRANSACTION inside a distributed transaction
• The autonomous transaction committed or rolled back independently, leaving the outer distributed transaction in an ambiguous state

Diagnostic Queries

Confirm the Transaction State

-- Check if there is an active transaction in the current session
SELECT
  s.sid,
  s.serial#,
  s.username,
  t.xid,
  t.status,
  t.start_time,
  t.used_ublk,
  t.used_urec
FROM v$session s
JOIN v$transaction t ON s.taddr = t.addr
WHERE s.sid = SYS_CONTEXT('USERENV', 'SID');

Check for In-Doubt Distributed Transactions

-- View in-doubt transactions resulting from the partial failure
SELECT
  local_tran_id,
  global_tran_id,
  state,
  mixed,
  advice,
  fail_time,
  retry_time,
  host,
  db_user
FROM dba_2pc_pending
ORDER BY fail_time DESC;

View Pending Neighbors for Each In-Doubt Transaction

-- Identify which remote databases are involved
SELECT
  p.local_tran_id,
  p.state,
  n.database,
  n.in_out,
  n.interface,
  n.dbuser
FROM dba_2pc_pending p
JOIN dba_2pc_neighbors n ON p.local_tran_id = n.local_tran_id
ORDER BY p.local_tran_id, n.in_out;

Identify Sessions Holding Distributed Locks

-- Sessions waiting on or holding distributed transaction resources
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.status,
  s.event,
  s.seconds_in_wait,
  s.sql_id,
  l.type,
  l.lmode,
  l.request
FROM v$session s
LEFT JOIN v$lock l ON s.sid = l.sid
WHERE l.type IN ('TX', 'TM')
  OR s.event LIKE '%distributed%'
ORDER BY s.seconds_in_wait DESC;

Review SQL Causing the Error

-- Find recent distributed SQL in the shared pool
SELECT
  sql_id,
  SUBSTR(sql_text, 1, 300) AS sql_text,
  executions,
  parse_calls,
  last_active_time,
  parsing_schema_name
FROM v$sql
WHERE sql_text LIKE '%@%'
  AND last_active_time > SYSDATE - 1/24
ORDER BY last_active_time DESC
FETCH FIRST 20 ROWS ONLY;

Check Remote Database for Corresponding Transaction State

-- Query the remote database's pending transactions (run through the link)
SELECT
  local_tran_id,
  global_tran_id,
  state,
  fail_time
FROM dba_2pc_pending@remote_db_link
ORDER BY fail_time DESC;

Examine Active Savepoints

-- Oracle does not expose savepoint names directly in V$ views,
-- but you can review the transaction's undo records for context
SELECT
  xid,
  ubafil,
  ubablk,
  ubasqn,
  ubarec,
  flag
FROM v$transaction
WHERE xid IN (
  SELECT xid FROM v$session s
  JOIN v$transaction t ON s.taddr = t.addr
  WHERE s.sid = SYS_CONTEXT('USERENV', 'SID')
);

Step-by-Step Resolution

1. Roll Back to a Savepoint (If One Was Established)

If your application established a savepoint before the distributed DML, rolling back to that savepoint may be sufficient:

-- Roll back to the savepoint established before the distributed operation
ROLLBACK TO SAVEPOINT pre_distributed_work;

-- The session is now clean from that savepoint forward
-- Local changes made before the savepoint are still intact
-- Now retry or skip the distributed operation

2. Full Transaction Rollback

If no savepoint was established, or if rolling back to a savepoint is not appropriate, roll back the entire transaction:

-- Full rollback clears all local and distributed state
ROLLBACK;

-- Verify no transaction is active
SELECT COUNT(*) FROM v$transaction
WHERE addr = (
  SELECT taddr FROM v$session
  WHERE sid = SYS_CONTEXT('USERENV', 'SID')
);
-- Result should be 0

3. Allow RECO to Resolve In-Doubt Transactions

For any in-doubt transactions that appear in DBA_2PC_PENDING as a result of the failure, wait for Oracle’s RECO process to automatically resolve them:

-- Monitor RECO resolution progress
SELECT
  local_tran_id,
  state,
  fail_time,
  retry_time,
  ROUND((SYSDATE - fail_time) * 1440, 1) AS minutes_pending
FROM dba_2pc_pending
ORDER BY fail_time;

If retry_time continues to update, RECO is working. Once rows disappear from DBA_2PC_PENDING, resolution is complete.

4. Force-Resolve Stuck In-Doubt Transactions

If RECO cannot reach the remote database and the in-doubt transaction has been pending for an extended period, coordinate with the remote DBA to determine the remote transaction state, then force resolution:

-- Force commit (only if remote side committed)
COMMIT FORCE 'local_tran_id';

-- Force rollback (only if remote side rolled back)
ROLLBACK FORCE 'local_tran_id';

-- Purge the entry after forcing resolution
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

5. Restructure Application Code to Handle Distributed Failures

After rolling back, address the root cause by refactoring the application transaction flow:

CREATE OR REPLACE PROCEDURE safe_distributed_update(
  p_local_id   IN NUMBER,
  p_remote_id  IN NUMBER,
  p_value      IN VARCHAR2
) AS
  v_savepoint_set BOOLEAN := FALSE;
BEGIN
  -- Establish savepoint before any distributed work
  SAVEPOINT before_distributed;
  v_savepoint_set := TRUE;

  -- Local DML first
  UPDATE local_table SET col1 = p_value WHERE id = p_local_id;

  -- Remote DML through database link
  UPDATE remote_table@remote_db_link SET col1 = p_value WHERE id = p_remote_id;

  -- All succeeded: commit
  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    -- Roll back to savepoint if it was set, otherwise full rollback
    IF v_savepoint_set THEN
      ROLLBACK TO SAVEPOINT before_distributed;
    ELSE
      ROLLBACK;
    END IF;

    -- Log the error for investigation
    INSERT INTO error_log (error_time, error_code, error_msg, program_unit)
    VALUES (SYSDATE, SQLCODE, SUBSTR(SQLERRM, 1, 500), 'safe_distributed_update');
    COMMIT;

    RAISE;
END;
/

6. Verify Remote Database and Link Health After Resolution

-- Test the database link is fully operational
SELECT SYSDATE AS remote_time FROM dual@remote_db_link;

-- Confirm the remote database has no pending transactions from the incident
SELECT COUNT(*) AS pending_count FROM dba_2pc_pending@remote_db_link;

-- Verify the remote table is consistent
SELECT COUNT(*) FROM remote_table@remote_db_link WHERE last_updated > SYSDATE - 1/24;

Prevention Strategies

1. Always Use Savepoints Before Distributed DML

Establish a savepoint immediately before any distributed operation. This gives the exception handler a precise rollback target and avoids discarding all local work on failure:

BEGIN
  -- ... prior local work ...

  SAVEPOINT before_remote_ops;

  -- Distributed operations
  INSERT INTO remote_audit@remote_db_link (event, ts) VALUES ('PROCESS', SYSDATE);
  UPDATE remote_inventory@remote_db_link SET qty = qty - :order_qty WHERE sku = :sku;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO SAVEPOINT before_remote_ops;
    -- Handle error without losing prior local work
    RAISE;
END;

2. Implement Idempotent Distributed Operations

Design distributed DML to be safely re-executable (idempotent) so that retry after rollback does not cause duplicates or constraint errors:

-- Use MERGE instead of INSERT to handle retry safely
MERGE INTO remote_table@remote_db_link t
USING (SELECT :id AS id, :val AS col1 FROM dual) s
ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.col1 = s.col1, t.updated_at = SYSDATE
WHEN NOT MATCHED THEN
  INSERT (id, col1, created_at) VALUES (s.id, s.col1, SYSDATE);

3. Keep Distributed Transactions Short

Minimize the window during which both local and remote databases hold uncommitted changes:

-- BAD: Long local work before the distributed commit
BEGIN
  UPDATE large_local_table SET status = 'PROCESSING' WHERE batch_id = :batch; -- slow
  -- ... minutes of processing ...
  UPDATE remote_table@remote_db_link SET status = 'DONE' WHERE batch_id = :batch;
  COMMIT; -- Remote link may have timed out by now
END;

-- BETTER: Do local work first, commit, then do remote work
BEGIN
  UPDATE large_local_table SET status = 'PROCESSING' WHERE batch_id = :batch;
  COMMIT; -- Commit local work independently

  UPDATE remote_table@remote_db_link SET status = 'DONE' WHERE batch_id = :batch;
  COMMIT; -- Separate remote commit
END;

4. Validate Network Stability Before Long Distributed Transactions

-- Quick connectivity check before starting a critical distributed transaction
DECLARE
  v_remote_time DATE;
BEGIN
  SELECT SYSDATE INTO v_remote_time FROM dual@remote_db_link;
  -- Only proceed if the above succeeds
  -- ... main distributed transaction ...
END;
/

5. Monitor and Alert on In-Doubt Transactions

-- Proactive monitoring job for ORA-02067 pattern
CREATE OR REPLACE PROCEDURE alert_on_indoubt AS
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count
  FROM dba_2pc_pending
  WHERE state = 'prepared'
    AND fail_time < SYSDATE - (5/1440); -- Pending > 5 minutes

  IF v_count > 0 THEN
    -- Raise alert
    DBMS_OUTPUT.PUT_LINE('ALERT: ' || v_count || ' transactions stuck in prepared state');
  END IF;
END;
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'ALERT_INDOUBT_TXN',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'alert_on_indoubt',
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=5',
    enabled         => TRUE,
    comments        => 'Alert DBAs when 2PC transactions remain in prepared state too long'
  );
END;
/

Diagnostic Scripts

These Oracle Day by Day utility scripts help with distributed transaction investigation:

• gvsess.sql — Identify sessions involved in distributed operations and their wait events
• health.sql — Overall database health check including pending transaction state

Related Errors

• ORA-02055 - Distributed update failed; rollback required
• ORA-02063 - Preceding line from database link
• ORA-02064 - Distributed operation not supported
• 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 to savepoint if available

   ROLLBACK TO SAVEPOINT pre_distributed_work;

2. Full rollback if no savepoint

   ROLLBACK;

3. Kill a session that cannot roll back on its own

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

4. Force resolve a stuck in-doubt transaction

   -- Confirm remote state first, then:
   ROLLBACK FORCE 'local_tran_id';
   -- or:
   COMMIT FORCE 'local_tran_id';

Post-Resolution Cleanup

-- Verify the session has no active transaction
SELECT COUNT(*) FROM v$transaction
WHERE addr = (SELECT taddr FROM v$session WHERE sid = SYS_CONTEXT('USERENV','SID'));

-- Confirm DBA_2PC_PENDING is clear
SELECT local_tran_id, state FROM dba_2pc_pending;

-- Purge any orphaned pending entries
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

-- Re-validate the database link
SELECT SYSDATE FROM dual@remote_db_link;

-- Review AWR for distributed wait events
SELECT event, total_waits, time_waited_micro/1000000 AS time_waited_sec
FROM dba_hist_system_event
WHERE event LIKE '%distributed%'
ORDER BY time_waited_micro DESC
FETCH FIRST 10 ROWS ONLY;