ORA-02068: Following Severe Error from Database Link - Fix Distributed Failures

ORA-02068: Following Severe Error from Database Link

Error Overview

Error Text: ORA-02068: following severe error from string

The ORA-02068 error occurs when a database link operation encounters a severe error on the remote database. It is always accompanied by a secondary error that identifies the root cause — such as ORA-03113 (end-of-file on communication), ORA-12541 (no listener), or ORA-01033 (Oracle initialization in progress). This error is common in distributed environments relying on database links for cross-database queries, distributed transactions, and two-phase commit (2PC) coordination.

Common Causes

1. Network Interruption During Distributed Transaction

• TCP/IP connection dropped mid-transaction
• Firewall or load balancer timing out idle connections
• Network interface reset while waiting for remote response
• WAN latency or packet loss causing protocol timeouts

2. Remote Database Unavailability

• Remote Oracle instance shut down or restarted
• Remote listener not running or misconfigured
• Remote database in restricted session or mounted-not-open state
• Remote host OS-level reboot or crash

3. Two-Phase Commit (2PC) Failure

• Prepared but unresolved in-doubt transactions in DBA_2PC_PENDING
• Remote database crashed after PREPARE phase but before COMMIT
• Coordinator cannot contact the remote participant to resolve transaction state
• Orphaned distributed transactions consuming undo space

4. Database Link Configuration Issues

• DB link pointing to wrong service name or host
• TNS alias unresolvable from the source database server
• Authentication failure using fixed-user DB link credentials
• DB link created with outdated password after remote user password change

5. Resource Exhaustion on Remote Side

• Remote database has reached maximum sessions or processes
• Remote PGA or shared memory exhausted
• Remote TEMP tablespace full, causing query failure mid-execution

Diagnostic Queries

Identify the Root Cause Error

-- The secondary error will appear in the alert log or session error stack.
-- Query the session error for the current connection:
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.status,
  s.sql_id,
  s.event,
  s.program,
  s.machine
FROM v$session s
WHERE s.status = 'ACTIVE'
ORDER BY s.last_call_et DESC;

-- Check recent errors in the alert log via ADR
SELECT
  originating_timestamp,
  message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-02068%'
   OR message_text LIKE '%ORA-02019%'
ORDER BY originating_timestamp DESC
FETCH FIRST 20 ROWS ONLY;

Check Database Link Configuration

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

-- Test connectivity (will raise ORA-02068 if link is broken)
-- Run from SQL*Plus: SELECT 1 FROM dual@your_db_link;

-- Check if DB link uses a valid TNS alias
SELECT
  db_link,
  host
FROM dba_db_links
WHERE db_link = 'YOUR_LINK_NAME';

Inspect In-Doubt Distributed Transactions

-- Find all in-doubt (prepared but unresolved) transactions
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,
  parent_remote_tran_id
FROM dba_2pc_pending
ORDER BY fail_time;

-- Check distributed transaction neighbours
SELECT
  local_tran_id,
  in_out,
  database,
  dbuser,
  interface,
  dbid,
  sess#,
  branch
FROM dba_2pc_neighbors
ORDER BY local_tran_id;

Review RECO Background Process

-- Check if RECO process is active and resolving transactions
SELECT
  name,
  description,
  elapsed_time,
  error_number
FROM v$bgprocess
WHERE name = 'RECO';

-- Monitor distributed transaction retry activity
SELECT
  TO_CHAR(retry_time, 'DD-MON-YYYY HH24:MI:SS') as last_retry,
  TO_CHAR(fail_time,  'DD-MON-YYYY HH24:MI:SS') as fail_time,
  state,
  local_tran_id,
  global_tran_id
FROM dba_2pc_pending
WHERE state = 'prepared'
ORDER BY fail_time;

Check Remote Database Availability

-- Verify listener services registered at the remote host
-- (Run this from the remote DB or via OS)
-- lsnrctl status

-- Check sessions connected via DB links
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.machine,
  s.osuser,
  s.logon_time,
  s.status
FROM v$session s
WHERE s.program LIKE '%Oracle%'
  AND s.username IS NOT NULL
ORDER BY s.logon_time DESC;

Step-by-Step Resolution

1. Identify and Fix the Underlying Error

ORA-02068 is always a wrapper. Locate the secondary error in the session trace or alert log first.

-- Enable tracing for a session experiencing the error
ALTER SESSION SET EVENTS '02068 trace name errorstack level 3';

-- After reproducing the error, locate the trace file
SELECT
  value
FROM v$diag_info
WHERE name = 'Default Trace File';

Common secondary errors and their fixes:

• ORA-03113 / ORA-03114: Network lost — check firewalls and keepalive settings.
• ORA-12541: Listener not running on remote — start the listener.
• ORA-01033 / ORA-01034: Remote DB not open — open the remote database.
• ORA-01017: Bad credentials on fixed-user DB link — recreate the link.

2. Verify and Recreate the Database Link

-- Drop and recreate a public database link
DROP PUBLIC DATABASE LINK your_link_name;

CREATE PUBLIC DATABASE LINK your_link_name
  CONNECT TO remote_user IDENTIFIED BY "password"
  USING 'remote_service_name';

-- Test the link
SELECT * FROM dual@your_link_name;

3. Resolve In-Doubt Distributed Transactions

When the RECO background process cannot automatically resolve a 2PC transaction, manual intervention is required.

-- Option A: Force commit an in-doubt transaction
-- Use when the remote side has committed (verify with remote DBA first)
COMMIT FORCE 'local_tran_id';

-- Option B: Force rollback an in-doubt transaction
-- Use when the remote side has rolled back
ROLLBACK FORCE 'local_tran_id';

-- Example using actual tran_id from DBA_2PC_PENDING
COMMIT FORCE '1.2.3456';   -- replace with actual local_tran_id

-- Verify the transaction has been resolved
SELECT COUNT(*) FROM dba_2pc_pending WHERE state = 'prepared';

4. Clean Up Resolved In-Doubt Transactions

-- After forcing commit or rollback, purge the entry
-- Oracle removes it automatically after resolution, but if it lingers:
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

-- Confirm cleanup
SELECT local_tran_id, state FROM dba_2pc_pending;

5. Fix Network Keepalive to Prevent Future Timeouts

Configure TCP keepalive at the Oracle Net layer in sqlnet.ora (on both client and server):

# sqlnet.ora entries to maintain idle connections
SQLNET.EXPIRE_TIME = 10          # Send probe every 10 minutes
TCP.KEEPALIVE = TRUE
TCP.CONNECT_TIMEOUT = 60
SQLNET.RECV_TIMEOUT = 30

6. Handle Firewall-Killed Connections

-- Check current Oracle Net profile parameters
SELECT name, value FROM v$parameter
WHERE name IN (
  'sqlnet.expire_time',
  'tcp_keepalive',
  'distributed_transactions',
  'commit_point_strength'
)
ORDER BY name;

-- Increase commit point strength on the most critical database
-- (makes it the coordinator for 2PC, reducing orphan risk)
ALTER SYSTEM SET commit_point_strength = 200 SCOPE=BOTH;

Prevention Strategies

1. Monitor In-Doubt Transactions Proactively

-- Create an alert procedure for 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 state = 'prepared'
    AND fail_time < SYSDATE - 1/24;  -- Older than 1 hour

  IF v_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('ALERT: ' || v_count ||
      ' in-doubt distributed transaction(s) require manual resolution.');
  END IF;
END;
/

-- Schedule to run every 30 minutes
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'CHECK_INDOUBT_TXNS',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'check_indoubt_transactions',
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=30',
    enabled         => TRUE
  );
END;
/

2. Limit Distributed Transaction Exposure

-- Reduce maximum concurrent distributed transactions if needed
-- (0 = disable distributed transactions entirely)
ALTER SYSTEM SET distributed_transactions = 100 SCOPE=BOTH;

-- Use autonomous transactions for non-critical DB link reads
-- to avoid 2PC overhead on simple queries
CREATE OR REPLACE FUNCTION get_remote_count RETURN NUMBER AS
  PRAGMA AUTONOMOUS_TRANSACTION;
  v_cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_cnt FROM some_table@remote_link;
  RETURN v_cnt;
END;
/

3. Configuration Best Practices

• Set SQLNET.EXPIRE_TIME in sqlnet.ora on both sides to detect dead connections early
• Configure OS-level TCP keepalive (tcp_keepalives_idle, tcp_keepalives_intvl)
• Avoid long-lived cursors open across database links
• Prefer read-only DB link queries over distributed DML where possible
• Test all DB links after listener restarts or database failovers
• Document all DB links with their purpose and owner in a CMDB

Diagnostic Scripts

These Oracle Day by Day scripts can assist with distributed environment diagnostics:

• gvsess.sql — Identify active sessions and their status
• health.sql — Overall database health check including background processes

Related Errors

• ORA-02063 - Preceding line from database link (companion error)
• ORA-03113 - End-of-file on communication channel
• ORA-12154 - TNS could not resolve the connect identifier
• ORA-12541 - TNS no listener

Emergency Response

Quick Fixes

1. Identify all in-doubt transactions immediately

   SELECT local_tran_id, global_tran_id, state, fail_time
   FROM dba_2pc_pending
   ORDER BY fail_time;

2. Force-resolve a prepared transaction (confirm with remote DBA first)

   COMMIT FORCE 'local_tran_id_here';
   -- or --
   ROLLBACK FORCE 'local_tran_id_here';

3. Bounce RECO to retry automatic resolution

   -- RECO cannot be killed directly; bouncing the instance restarts it.
   -- As a softer option, verify RECO is not stuck:
   SELECT name, error_number FROM v$bgprocess WHERE name = 'RECO';

Post-Resolution Cleanup

-- Confirm no in-doubt transactions remain
SELECT COUNT(*) as pending_count FROM dba_2pc_pending WHERE state = 'prepared';

-- Review DB link health after network event
SELECT db_link, username, host FROM dba_db_links ORDER BY owner;

-- Run AWR report to review distributed transaction wait events
-- Key events to look for: 'SQL*Net message from dblink'
SELECT
  event,
  total_waits,
  time_waited_micro / 1e6 as time_waited_sec
FROM v$system_event
WHERE event LIKE '%dblink%'
   OR event LIKE '%SQL*Net%'
ORDER BY time_waited_micro DESC;