Why Are SNIPED Sessions Not Getting Cleared in Oracle?

🔹 Why Are SNIPED Sessions Not Getting Cleared in Oracle?

SNIPED sessions in Oracle are inactive sessions that the database marked for termination but are not fully cleared. These sessions typically remain in the V$SESSION view with a SNIPED status.

✅ 1️⃣ What is a SNIPED Session?

A SNIPED session is a session that:

Exceeded the idle timeout (e.g., via IDLE_TIME in a profile).
Was disconnected by Oracle but the client did not acknowledge it.
Still appears in V$SESSION but does not hold resources.

🔹 Check SNIPED Sessions

SELECT sid, serial#, username, status, program
FROM v$session
WHERE status = 'SNIPED';

🔹 If a session is SNIPED, it means Oracle tried to kill it but the client (application) has not acknowledged it.

✅ 2️⃣ Why SNIPED Sessions Are Not Clearing?

Cause	Explanation	Fix
Client Did Not Close Connection	The session is marked as SNIPED but remains in `V$SESSION` because the client has not acknowledged the termination.	Kill the session manually.
OS-Level TCP Timeout Not Configured	Oracle waits for the OS to close idle TCP connections, but it is not properly configured.	Adjust TCP timeout settings at OS level.
Resource Limit Handling in `IDLE_TIME` Profile	If `IDLE_TIME` is set, Oracle marks sessions as SNIPED instead of killing them.	Use `DISCONNECT SESSION` instead.
Shared Server Connections	SNIPED sessions using Shared Server Mode (MTS) may persist.	Use `ALTER SYSTEM KILL SESSION` to remove them.

✅ 3️⃣ How to Forcefully Clear SNIPED Sessions

🔹 Step 1: Kill the Session in Oracle

ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

🔹 Replace SID and SERIAL# from the SNIPED session list.

🔹 Step 2: Kill the Session at OS Level (If Needed)

If the session is not clearing, find its OS process ID (SPID):

SELECT s.sid, s.serial#, p.spid, s.username, s.status
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.status = 'SNIPED';

Then, kill the process at the OS level:

🔹 On Linux/Unix:

kill -9 <SPID>

🔹 On Windows:

taskkill /PID <SPID> /F

🔹 Step 3: Set Oracle to Fully Disconnect Idle Sessions

Instead of marking sessions as SNIPED, configure Oracle to fully disconnect them:

ALTER PROFILE DEFAULT LIMIT IDLE_TIME UNLIMITED;

OR (To automatically disconnect idle sessions):

ALTER PROFILE DEFAULT LIMIT IDLE_TIME 30;  -- 30 minutes

🔹 Step 4: Configure OS-Level TCP KeepAlive (Prevents Hanging SNIPED Sessions)

If SNIPED sessions remain due to TCP settings, configure the TCP timeout in the OS.

echo 600 > /proc/sys/net/ipv4/tcp_keepalive_time

OR in /etc/sysctl.conf:

net.ipv4.tcp_keepalive_time = 600

Then apply the changes:

sysctl -p

🔹 Windows

Run this in Command Prompt (Admin):

netsh int tcp set global keepaliveinterval=60

✅ 4️⃣ How to Prevent SNIPED Sessions in Future

🔹 Enable Automatic Session Termination (`DISCONNECT SESSION`)

Instead of SNIPING, configure Oracle to fully disconnect idle sessions:

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;
ALTER PROFILE DEFAULT LIMIT IDLE_TIME 30;

🔹 This ensures sessions are properly killed.

🔹 Summary

Issue	Fix
SNIPED sessions not clearing	Kill sessions manually (`ALTER SYSTEM KILL SESSION`).
Sessions persist at OS level	Kill process using `kill -9 <SPID>` (Linux) or `taskkill /PID <SPID>` (Windows).
Idle sessions remain due to `IDLE_TIME`	Use `DISCONNECT SESSION` instead of `SNIPED`.
TCP connections not closing	Adjust TCP keep-alive settings in OS.

✅ Best Fix: Set IDLE_TIME to automatically disconnect sessions instead of marking them SNIPED.

The net.ipv4.tcp_keepalive_time parameter in Linux controls how often TCP keepalive packets are sent to verify that an idle connection is still active.

✅ 1️⃣ What Does `net.ipv4.tcp_keepalive_time` Do?

Determines the idle time (in seconds) before sending the first TCP keepalive probe.
Prevents stale connections from remaining open indefinitely.
Useful in Oracle Databases, SSH, and other network applications to detect inactive clients.

🔹 Default Value

cat /proc/sys/net/ipv4/tcp_keepalive_time

🔹 Default: 7200 seconds (2 hours) (which is often too high).

✅ 2️⃣ How to Change `net.ipv4.tcp_keepalive_time`

You can lower the value to 600 seconds (10 minutes) to ensure inactive sessions are detected earlier.

🔹 Temporarily Change (Until Reboot)

echo 600 > /proc/sys/net/ipv4/tcp_keepalive_time

🔹 Permanently Change (Survives Reboot)

1️⃣ Open the sysctl.conf file:

sudo nano /etc/sysctl.conf

2️⃣ Add this line:

net.ipv4.tcp_keepalive_time = 600

3️⃣ Apply the changes:

sudo sysctl -p

✅ 3️⃣ Related TCP Keepalive Settings

Parameter	Description	Default	Recommended
`net.ipv4.tcp_keepalive_time`	Time before first keepalive packet is sent	7200 sec (2 hours)	600 sec (10 min)
`net.ipv4.tcp_keepalive_intvl`	Interval between keepalive probes	75 sec	30 sec
`net.ipv4.tcp_keepalive_probes`	Number of failed probes before closing connection	9	5

🔹 Set All Three Parameters for Better Performance

sudo nano /etc/sysctl.conf

Add:

net.ipv4.tcp_keepalive_time = 600
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_keepalive_probes = 5

Apply:

sudo sysctl -p

✅ 4️⃣ Why Is This Important for Oracle?

Prevents SNIPED or idle DB sessions from hanging indefinitely.
Ensures faster detection of dead clients.
Helps load balancers and firewalls detect stale connections.

Why Are SNIPED Sessions Not Getting Cleared in Oracle?