The application works all day. Then the first request after a quiet spell fails: the first one in the morning, the first one after lunch, the first one on a rarely used screen. Retry it, and it works.

ORA-03113: end-of-file on communication channel

or

ORA-03135: connection lost contact

The database is fine. No other session noticed anything. The cause is a device between the application and the database: a firewall, a NAT gateway, a cloud load balancer. It tracks every TCP connection that passes through it, and it forgets the ones that stay idle longer than its timeout. A connection pool is designed to keep connections open and idle. The two meet, and the next query on a forgotten connection goes nowhere.

This post covers which error you get and why, what the database is left holding, the one setting that fixes it, and the settings that look like fixes and aren’t. It ends with a lab that puts a firewall between a client and Oracle Database Free 26ai and proves each case.

What the device forgets

A stateful firewall, a NAT gateway or a load balancer keeps a table of the TCP connections it lets through. An entry stays as long as packets keep flowing. When a connection sends nothing for longer than the device’s idle timeout, the entry is removed. Nothing is sent to either end. The client and the database both still think the connection is open.

The defaults are short compared with how long a pooled connection can sit idle:

Device	Default idle timeout	What happens to the next packet
Azure Load Balancer	4 minutes (configurable 4 to 100)	dropped silently, unless “TCP reset on idle” is turned on, which resets both ends
Azure NAT Gateway	4 minutes (configurable 4 to 120)	answered with a reset, toward the side that sent it
AWS Network Load Balancer	350 seconds (configurable 60 to 6,000 for TCP listeners)	answered with a reset to the client

On-premises firewalls have their own timeouts, often an hour or more, and they’re usually set by a network team you’ll have to ask. Check each provider’s current documentation for your setup; these are the documented defaults at the time of writing.

Which error, and why

What the application sees depends on what the device does with the first packet of a forgotten connection.

The device sends a reset. The client’s next query gets a TCP reset straight back, and fails at once. In the lab, on a 26ai client:

ORA-03135: connection lost contact
Process ID: 333
Session ID: 51 Serial number: 3605

The device drops the packet silently. The client sends the query and gets no answer. Its TCP stack retransmits, waiting longer each time, until it gives up. On Linux that’s controlled by net.ipv4.tcp_retries2, 15 by default. In the lab, with the default, the query hung for 951 seconds, almost 16 minutes, and then failed:

ORA-03113: end-of-file on communication channel
Process ID: 377
Session ID: 213 Serial number: 13151

A request that hangs for 16 minutes is often worse than one that fails. Threads and pool connections pile up behind it, and the application’s own timeouts may fire first, so the application log can show a generic timeout and no ORA- error at all.

So: an immediate ORA-03135 after a quiet period points to a device that resets. A long hang, then ORA-03113, points to one that drops silently. Either way the connection was idle longer than something on the path allows.

⤢ Enlarge

What the database is left holding

The database often never finds out. In the lab, in both the reset case and the silent case, the client’s session stayed in V$SESSION as INACTIVE after the client had given up on it. The reset went only to the client, and in the silent case nothing reached the database at all. (An Azure Load Balancer with TCP reset turned on resets both ends, so there the database does find out.)

Those orphan sessions are real sessions. They count against SESSIONS and PROCESSES, and if the application had an open transaction, they hold its locks. A pool that replaces each broken connection with a new one leaves one orphan behind per failure.

The same happens when the client host itself disappears: a VM that’s deleted, a network that’s cut. In the lab, with no keepalive configured, the session of a client whose network was disconnected was still there after 240 seconds, when the drill stopped checking, and orphans from earlier tests stayed for more than 30 minutes. Nothing was ever going to remove them.

To look for them, find sessions idle for longer than the device’s timeout, from the application’s hosts:

SELECT sid, serial#, username, machine, program, status,
       ROUND(last_call_et / 60) AS idle_minutes
FROM   v$session
WHERE  type = 'USER'
AND    status = 'INACTIVE'
AND    last_call_et > 240          -- the idle timeout on the path, in seconds
ORDER  BY last_call_et DESC;

A pool’s healthy idle connections show up here too, so this is a list to compare, not a list to kill. The signal is many more sessions from an app server than its pool can hold, or sessions from a host that no longer exists.

The alert log doesn’t help with the client’s errors: in the lab they left nothing there. The only network entries it had were two Fatal NI connect error 12170 entries with TNS-12535: TNS:operation timed out. Those are the database’s own keepalive giving up on dead connections, which happens once SQLNET.EXPIRE_TIME is set.

The fix: SQLNET.EXPIRE_TIME on the server

SQLNET.EXPIRE_TIME goes in the database server’s sqlnet.ora, and its value is in minutes:

SQLNET.EXPIRE_TIME = 1

Where the operating system supports it, Oracle Net uses it to set TCP keepalive on the server side of each connection: after that many minutes of idle time, the server sends a keepalive probe. The probe is traffic, so the firewall, the NAT gateway and the load balancer all see the connection as active and keep their entry. If the client really is gone, the probes go unanswered and the database closes the session itself.

In the lab, with a firewall that forgot connections after 90 seconds:

• SQLNET.EXPIRE_TIME = 1 (60 seconds): a connection idle for 110 seconds ran its next query normally.
• A client whose network was cut: the database removed its session after about 115 seconds (112 to 117 across runs), instead of never.

Three rules for using it:

1. It must be below the smallest idle timeout on the path. Behind an Azure Load Balancer or NAT gateway at the 4-minute default, use 1 to 3. Behind an AWS NLB at 350 seconds, up to 5. If you don’t know every device on the path, 1 or 2 is a safe choice; the probes are tiny.
2. It applies to new connections. Server processes read sqlnet.ora when they start. Connections that are already open keep what they had, so recycle the pool after the change.
3. It needs no listener restart, for the same reason: each new connection reads the file.

Fixes from the client side

When you can’t change the database server’s sqlnet.ora, there are two client-side options. Both worked in the lab; both have a catch.

(ENABLE=BROKEN) in the connect descriptor turns on TCP keepalive on the client’s socket:

(DESCRIPTION=(ENABLE=BROKEN)(ADDRESS=(PROTOCOL=TCP)(HOST=db)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=app)))

On its own, it didn’t help in the lab. It switches keepalive on, but the operating system decides when the first probe goes out, and the Linux default (net.ipv4.tcp_keepalive_time) is 7,200 seconds: two hours. With the client host’s keepalive time set to 60 seconds, the connection survived. That’s a kernel setting for the whole host, so it’s a conversation with whoever owns the app servers.

SQLNET.EXPIRE_TIME in the client’s sqlnet.ora also worked in the lab, where the client is 26ai. The 26ai Net Services Reference says the parameter is meant primarily for the server but can be used on clients too. I tested only a 26ai client, so check an older one before relying on it.

The application’s pool can help too. Most pools can retire connections that have been idle for longer than a set time, or validate a connection before handing it out. A maximum idle time below the device’s timeout avoids the problem. Validation on borrow doesn’t avoid it: it finds the broken connection and replaces it, which is better than failing a request, but against a device that drops silently the validation itself can hang, and each replaced connection can leave an orphan session behind. I didn’t test pool settings in the lab.

What doesn’t fix it

SQLNET.EXPIRE_TIME higher than the timeout. It’s the most common one. Oracle’s own reference gives 10 as the recommended value, and 10 does nothing behind a 4-minute load balancer. In the lab, EXPIRE_TIME = 2 (120 seconds) behind the 90-second firewall failed exactly like no setting at all: the first probe came after the firewall had already forgotten the connection.

(ENABLE=BROKEN) alone. See above: with the OS default, the first keepalive comes after two hours.

SQLNET.RECV_TIMEOUT on the client. It limits how long the client waits for an answer. With it set to 30 seconds, the silent drop in the lab ended in a different error after about 60 seconds (60 to 61 across runs, about two timeouts) instead of 16 minutes:

ORA-12609: TNS: Receive timeout occurred

That’s better than a 16-minute hang, and worth having, but the connection is still broken and the request still fails. It shortens the failure; it doesn’t prevent it.

Raising the device’s idle timeout. It works until a connection sits idle a little longer than the new value. It also costs something: Microsoft’s NAT gateway documentation warns that longer idle timeouts hold SNAT ports longer and raise the risk of running out of them. And it only fixes the device you changed, not the next one someone adds to the path.

Prove it

The lab runs Oracle Database Free in a container. There’s no firewall box: the database container’s network namespace gets connection tracking that forgets an idle TCP connection after 90 seconds, and one iptables rule that drops, or answers with a reset, any packet of a connection it has forgotten. Each client runs in its own container, runs a query, sits idle for 110 seconds, then runs a second query.

drill     case                                                   second query
reset     firewall sends a reset                                 ORA-03135 at once; the session stays INACTIVE
drop      firewall drops silently, client TCP retries cut to 5   ORA-03113 after ~14 s; the session stays INACTIVE
          same, SQLNET.RECV_TIMEOUT = 30 in the client           ORA-12609 after ~60 s
fix       SQLNET.EXPIRE_TIME = 1 on the server                   runs
toolate   SQLNET.EXPIRE_TIME = 2 on the server                   ORA-03113
client    (ENABLE=BROKEN), OS keepalive time 7,200 s             ORA-03113
          (ENABLE=BROKEN), OS keepalive time 60 s                runs
          SQLNET.EXPIRE_TIME = 1 in the 26ai client's sqlnet.ora runs
dead      client network cut, no EXPIRE_TIME                     session still there after 240 s
          client network cut, EXPIRE_TIME = 1                    session removed after ~115 s

The drills cut the client’s TCP retries from 15 to 5 so a silent drop fails in seconds instead of 16 minutes. That changes how long the hang lasts, not which error you get. The 951-second hang with the Linux default was measured once by hand.

Don’t take my word for it — run it. The idle-connection-drop lab asserts every row: the error for each firewall mode, and that the database still has the session afterwards; ORA-12609 with RECV_TIMEOUT; the second query running with EXPIRE_TIME = 1 and failing with 2; ENABLE=BROKEN failing on its own and working with a 60-second keepalive time; client-side EXPIRE_TIME working; and a vanished client’s session kept without EXPIRE_TIME and removed with it. If any of that doesn’t reproduce, the run fails. Proven on every CI push.

What teams get wrong

• Treating ORA-03113 as a database crash. It can be one, but when it comes after idle time, from one application, with nothing in the alert log, it’s the network path.
• Setting SQLNET.EXPIRE_TIME without knowing the timeout. It’s in minutes and it has to be below the smallest idle timeout on the path. 10 doesn’t work behind a 4-minute load balancer.
• Expecting the change to fix open connections. It applies to new ones. Recycle the pool.
• Trusting (ENABLE=BROKEN) on its own. Without a shorter OS keepalive time, the first probe goes out after two hours.
• Calling RECV_TIMEOUT the fix. It makes the failure faster, not less likely.
• Ignoring the orphans. Every failed connection can leave a session on the database. Without EXPIRE_TIME, nothing removes it.

Frequently asked questions

What causes ORA-03113 end-of-file on communication channel after a connection has been idle?

Usually a stateful device between the application and the database, such as a firewall, NAT gateway or cloud load balancer, that removed the idle connection from its table. When the application uses the connection again, the device drops the packets silently, the client's TCP stack retransmits until it gives up, and the client reports ORA-03113. In the companion lab, with Linux's default TCP retries, that took 951 seconds. The database itself is fine.

What is the difference between ORA-03113 and ORA-03135?

Both mean the client lost its connection to the database. In the companion lab on a 26ai client, a device that answered the forgotten connection with a TCP reset produced ORA-03135 connection lost contact at once, and a device that dropped the packets silently produced a long hang followed by ORA-03113 end-of-file on communication channel. So an immediate ORA-03135 points to a reset, and a long hang followed by ORA-03113 points to a silent drop.

What should SQLNET.EXPIRE_TIME be set to?

A number of minutes below the smallest idle timeout of any firewall, NAT gateway or load balancer between the application and the database. Azure Load Balancer and Azure NAT Gateway default to 4 minutes, so use 1 to 3; an AWS Network Load Balancer defaults to 350 seconds, so up to 5. In the companion lab, behind a 90-second timeout, EXPIRE_TIME = 1 kept idle connections working and EXPIRE_TIME = 2 did not.

Where do I set SQLNET.EXPIRE_TIME, and does it need a restart?

In the database server's sqlnet.ora. It applies to connections made after the change, because each server process reads the file when it starts; connections that are already open are not affected, so recycle the application's connection pool. No listener or database restart is needed.

Does ENABLE=BROKEN fix idle connection timeouts?

Not on its own. (ENABLE=BROKEN) in the connect descriptor turns on TCP keepalive on the client's socket, but the operating system decides when the first keepalive is sent, and the Linux default is 7,200 seconds. In the companion lab it failed on its own and worked when the client host's net.ipv4.tcp_keepalive_time was set to 60 seconds.

Why are there INACTIVE sessions left on the database after ORA-03113 or ORA-03135?

Because the database never learned that the client was gone. In the companion lab, the reset went only to the client and the silent drop reached neither end, so the session stayed in V$SESSION as INACTIVE. It holds a process and any locks of an open transaction. With SQLNET.EXPIRE_TIME set, the database's keepalive probes go unanswered and the database removes such a session itself; in the lab that took about 115 seconds with EXPIRE_TIME = 1.

ORA-03113 after idle time isn’t a database fault. It’s a connection that sat quiet longer than something on the network allows. Find the smallest idle timeout on the path, set SQLNET.EXPIRE_TIME on the database server below it, and recycle the pool. If clients can’t reach the database at all, rather than losing it after a while, that’s a different problem: start with ORA-12514 and the listener. Then prove it with the idle-connection-drop lab, where a 90-second firewall breaks every idle connection and one line in sqlnet.ora keeps them alive.