ORA-03114: Not Connected to Oracle - Diagnose Lost Connections

ORA-03114: Not Connected to Oracle

Error Overview

Error Text: ORA-03114: not connected to ORACLE

The ORA-03114 error indicates that the client process has lost its connection to the Oracle server process. Unlike ORA-03113 (end-of-file on communication channel), which signals the network-level disconnect, ORA-03114 is raised when the client attempts an Oracle call after the connection is already gone. Any work in the current transaction that had not been committed is lost. This error is common in long-running batch jobs, connection pool environments, reporting tools, and applications that hold idle connections for extended periods.

Common Causes

1. Oracle Instance Shutdown or Crash

DBA issued SHUTDOWN IMMEDIATE or SHUTDOWN ABORT while sessions were active
Oracle instance crashed due to ORA-00600, ORA-07445, or OS-level OOM kill
RAC node eviction disconnecting all sessions on that node
Standby switchover or failover terminating primary sessions

2. Network Interruption

TCP connection silently dropped by firewall, load balancer, or NAT device
WAN link failure between application server and database server
Network interface reset or IP address change
VPN disconnection for remote sessions

3. Session Timeout or Resource Limit

IDLE_TIME profile limit exceeded — Oracle kills idle sessions
CONNECT_TIME profile limit exceeded — session forcibly terminated after max duration
CPU_PER_SESSION or LOGICAL_READS_PER_SESSION exceeded
DBA manually killed the session with ALTER SYSTEM KILL SESSION

4. Connection Pool Stale Connections

Connection pool returning a dead connection from its pool
Pool validation query disabled or infrequent
Application holding connections open across database maintenance windows
Pool exhaustion causing connection reuse errors

5. Listener or Server Process Failure

Oracle listener process terminated while session was active
Dedicated server process killed by OS (SIGKILL)
Shared server dispatcher failure
Memory corruption in server process

Diagnostic Queries

Check Session and Connection Status

-- Find active sessions and time since last activity
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.status,
  s.osuser,
  s.machine,
  s.program,
  s.last_call_et                        as idle_seconds,
  ROUND(s.last_call_et / 60, 1)         as idle_minutes,
  TO_CHAR(s.logon_time, 'DD-MON HH24:MI:SS') as logon_time,
  s.event
FROM v$session s
WHERE s.username IS NOT NULL
ORDER BY s.last_call_et DESC;

-- Find sessions killed or marked for kill
SELECT
  sid,
  serial#,
  username,
  status,
  last_call_et,
  machine,
  program
FROM v$session
WHERE status IN ('KILLED', 'SNIPED')
ORDER BY last_call_et DESC;

Check Profile Limits

-- Review idle_time and connect_time limits for user profiles
SELECT
  p.profile,
  p.resource_name,
  p.limit
FROM dba_profiles p
WHERE p.resource_name IN (
    'IDLE_TIME',
    'CONNECT_TIME',
    'CPU_PER_SESSION',
    'LOGICAL_READS_PER_SESSION',
    'SESSIONS_PER_USER'
  )
ORDER BY p.profile, p.resource_name;

-- Check which profile each user is assigned
SELECT
  username,
  profile,
  account_status
FROM dba_users
WHERE account_status = 'OPEN'
ORDER BY profile, username;

Review Recent Instance Activity

-- Check for recent instance restarts
SELECT
  startup_time,
  ROUND((SYSDATE - startup_time) * 24, 2) as hours_up
FROM v$instance;

-- Check alert log for shutdown/abort events
SELECT
  originating_timestamp,
  message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%shutdown%'
   OR message_text LIKE '%abort%'
   OR message_text LIKE '%ORA-03114%'
   OR message_text LIKE '%instance terminated%'
ORDER BY originating_timestamp DESC
FETCH FIRST 30 ROWS ONLY;

Identify Firewall-Killed Connections

-- Check for Oracle Net dead connection detection settings
SELECT name, value
FROM v$parameter
WHERE name IN (
  'sqlnet.expire_time',
  'tcp_keepalive',
  'connection_brokers'
)
ORDER BY name;

-- Sessions waiting on SQL*Net events (may indicate network issues)
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.event,
  s.seconds_in_wait,
  s.wait_class,
  s.state
FROM v$session s
WHERE s.wait_class = 'Network'
  AND s.username IS NOT NULL
ORDER BY s.seconds_in_wait DESC;

Check Connection Pool Health (Application Server Side)

-- Count connections per machine/program to detect pool behaviour
SELECT
  machine,
  program,
  COUNT(*) as session_count,
  SUM(CASE WHEN status = 'ACTIVE'  THEN 1 ELSE 0 END) as active_count,
  SUM(CASE WHEN status = 'INACTIVE' THEN 1 ELSE 0 END) as idle_count,
  MAX(last_call_et) as max_idle_seconds
FROM v$session
WHERE username IS NOT NULL
GROUP BY machine, program
ORDER BY session_count DESC;

Step-by-Step Resolution

1. Verify the Instance Is Running

-- From SQL*Plus or OS, confirm the instance is up
SELECT instance_name, status, database_status FROM v$instance;

-- If the instance is down, start it:
-- STARTUP;

-- Check that required services are registered
SELECT name, network_name, creation_date, enabled
FROM dba_services
ORDER BY name;

2. Verify Network Connectivity

Run from the application server OS:

# Test basic TCP connectivity to the listener port (usually 1521)
telnet db-server-hostname 1521

# Test TNS resolution
tnsping service_name

# Check Oracle listener status on the DB server
lsnrctl status
lsnrctl services

3. Configure Oracle Net Dead Connection Detection

Edit sqlnet.ora on the server side to enable probing of idle connections:

SQLNET.EXPIRE_TIME = 10          # Probe every 10 minutes
SQLNET.RECV_TIMEOUT = 30         # Abort if no response in 30 seconds
SQLNET.SEND_TIMEOUT = 30

No restart is required for SQLNET.EXPIRE_TIME — it takes effect for new connections.

4. Adjust Profile Limits to Avoid Premature Disconnection

-- Check if IDLE_TIME is too aggressive for your application
SELECT profile, limit FROM dba_profiles
WHERE resource_name = 'IDLE_TIME'
ORDER BY profile;

-- Increase IDLE_TIME for an application profile (value in minutes)
ALTER PROFILE app_profile LIMIT IDLE_TIME 60;    -- 60 minutes
ALTER PROFILE app_profile LIMIT CONNECT_TIME UNLIMITED;

-- If no dedicated profile exists, create one
CREATE PROFILE app_profile LIMIT
  IDLE_TIME    60
  CONNECT_TIME UNLIMITED
  SESSIONS_PER_USER UNLIMITED;

ALTER USER app_user PROFILE app_profile;

5. Handle Stale Connections in Application Code

For JDBC-based applications, enable connection validation:

-- Oracle JDBC validation query (use in connection pool config)
-- Set 'testOnBorrow=true' and 'validationQuery' in pool config:
-- validationQuery = SELECT 1 FROM dual
-- testWhileIdle  = true
-- timeBetweenEvictionRunsMillis = 60000
-- minEvictableIdleTimeMillis    = 300000

For PL/SQL or SQL*Plus scripts, implement reconnect logic:

-- Test connection before using it (wrap in exception handler)
DECLARE
  v_test NUMBER;
  v_connected BOOLEAN := TRUE;
BEGIN
  BEGIN
    SELECT 1 INTO v_test FROM dual;
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE IN (-3114, -3113, -1012) THEN
        v_connected := FALSE;
        DBMS_OUTPUT.PUT_LINE('Connection lost — SQLCODE: ' || SQLCODE);
      ELSE
        RAISE;
      END IF;
  END;

  IF v_connected THEN
    -- Proceed with main logic
    NULL;
  END IF;
END;
/

6. Investigate and Stabilize Intermittent Drops

-- Enable SQL Trace on a suspect session to capture the disconnect point
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 42, serial_num => 1234, waits => TRUE);

-- After issue occurs, find the trace file
SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

-- Disable tracing
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 42, serial_num => 1234);

Prevention Strategies

1. Implement Connection Health Monitoring

-- Create a procedure to detect and log stale sessions
CREATE OR REPLACE PROCEDURE log_stale_sessions AS
  v_threshold NUMBER := 3600;  -- 1 hour idle threshold
BEGIN
  INSERT INTO dba_session_log (sid, serial#, username, machine, idle_seconds, log_time)
  SELECT sid, serial#, username, machine, last_call_et, SYSDATE
  FROM v$session
  WHERE username IS NOT NULL
    AND status = 'INACTIVE'
    AND last_call_et > v_threshold;
  COMMIT;
END;
/

-- Schedule every 15 minutes
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'LOG_STALE_SESSIONS_JOB',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'log_stale_sessions',
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
    enabled         => TRUE
  );
END;
/

2. Configure OS-Level TCP Keepalive

On Linux database servers, tune TCP keepalive to detect dead connections faster:

net.ipv4.tcp_keepalive_time    = 300     # Start keepalive after 5 min idle
net.ipv4.tcp_keepalive_intvl   = 60      # Probe every 60 seconds
net.ipv4.tcp_keepalive_probes  = 5       # Drop after 5 failed probes

Apply without reboot: sysctl -p

3. Best Practices for Application Design

Always handle ORA-03114 and ORA-03113 in application exception handlers and attempt reconnect
Use connection pool validation (test-on-borrow) to detect stale connections before use
Set connection pool maximum idle time lower than Oracle’s IDLE_TIME profile limit
For long-running jobs, periodically issue a lightweight heartbeat query (SELECT 1 FROM dual)
Avoid holding connections open during long inter-step pauses in batch processes
In RAC environments, configure TAF (Transparent Application Failover) or FAN events to handle node loss automatically

Diagnostic Scripts

These Oracle Day by Day scripts help investigate connectivity and session issues:

gvsess.sql — Active session analysis across RAC nodes
health.sql — Database health check including session and process counts

ORA-03113 - End-of-file on communication channel
ORA-01012 - Not logged on
ORA-00028 - Your session has been killed
ORA-12541 - TNS no listener
ORA-12560 - TNS protocol adapter error

Emergency Response

Quick Fixes

Verify the instance is accessible

SELECT instance_name, status FROM v$instance;

Check if the session was killed

SELECT sid, serial#, username, status FROM v$session
WHERE status IN ('KILLED', 'SNIPED');

Re-establish the connection — simply reconnect from your client tool or application. Any uncommitted work from the lost session is automatically rolled back by Oracle’s PMON process.

Check if profile killed the session due to idle time

SELECT profile, resource_name, limit FROM dba_profiles
WHERE resource_name = 'IDLE_TIME' AND limit != 'UNLIMITED';

Post-Resolution Cleanup

-- Clean up any KILLED sessions still visible in v$session (PMON handles this automatically)
SELECT sid, serial#, username, status, last_call_et
FROM v$session
WHERE status = 'KILLED';

-- Verify no orphaned locks remain from the dropped session
SELECT
  lo.session_id,
  lo.oracle_username,
  lo.os_user_name,
  lo.locked_mode,
  do.object_name,
  do.object_type
FROM v$locked_object lo
JOIN dba_objects do ON lo.object_id = do.object_id
ORDER BY lo.session_id;

-- If locks remain from a dead session, kill the session explicitly
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;