Use SQLNET. EXPIRE_TIME to clear dead connections

After the client connected to the database is abnormally disconnected, its possession is not released. For example, from the v $ session view, the corresponding session is still inactive and

After the client connected to the database is abnormally disconnected, its possession is not released. For example, from the v $ session view, the corresponding session is still inactive and

After the client connected to the database is abnormally disconnected, its possession is not released. For example, from the v $ session view, the corresponding session is still inactive, the corresponding server process has not been released, causing resources to be occupied for a long time. What should we do in this case? SQLNET. EXPIRE_TIME provides a solution for this problem. It is used to clear abnormal disconnections, such as network interruptions, client power loss, and abnormal restart. This article describes how to set the SQLNET. EXPIRE_TIME parameter and demonstrate the situation of dead connections and the release of resources.

1. Understand the SQLNET. EXPIRE_TIME Parameter
Use parameter SQLNET. EXPIRE_TIME to specify a time interval, in minutes, to send a probe to verify that client/server
Connections are active.
Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination.
If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing
Server process to exit.
This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.

Set the parameter to a non-zero value (minute) to send the test package to check whether the client is disconnected abnormally. Once an abnormal connection is found in the probe package, an error is returned and the corresponding server process is cleared.
The following are some restrictions on the use of parameters. (The default value is 0 and the minimum value is 0. The recommended value is 10. SQLNET. EXPIRE_TIME = 10)
Limitations on using this terminated connection detection feature are:

It is not allowed on bequeathed connections.
Though very small, a probe packet generates additional traffic that may downgrade network performance.
Depending on which operating system is in use, the server may need to perform additional processing to distinguish
The connection probing event from other events that occur. This can also result in degraded network performance.

2. Dead Connection Detection (DCD) and Inactive Sessions

Dead connections:
These are previusly valid connections with the database but the connection between the client and server processes has
Terminated abnormally.
Examples of a dead connection:
-A user reboots/turns-off their machine without logging off or disconnecting from the database.
-A network problem prevents communication between the client and the server.

In these cases, the shadow process running on the server and the session in the database may not terminate.

Implemented
* Adding SQLNET. EXPIRE_TIME = To the sqlnet. ora file

With DCD is enabled, the Server-side process sends a small 10-byte packet to the client process after the duration
The time interval specified in minutes by the SQLNET. EXPIRE_TIME parameter.

If the client side connection is still connected and responsive, the client sends a response packet back to the database
Server, resetting the timer... and another packet will be sent when next interval expires (assuming no other activity on
The connection ).

If the client fails to respond to the DCD probe packet
* The Server side process is marked as a dead connection and
* PMON performs the clean up of the database processes/resources
* The client OS processes are terminated

NOTE: SQLNET. RECV_TIMEOUT can be set on the SERVER side sqlnet. ora file. This will set a timeout for the server process
To wait for data from the client process.

Inactive Sessions:
These are sessions that remain connected to the database with a status in v $ session of INACTIVE.
Example of an INACTIVE session:
-A user starts a program/session, then leaves it running and idle for an extended period of time.

3. Configure SQLNET. EXPIRE_TIME

# For SQLNET. EXPIRE_TIME configuration, You need to modify sqlnet. ora and then add the SQLNET. EXPIRE_TIME item
[Oracle @ orasrv admin] $ more sqlnet. ora
Sqlnet. expire_time = 1 # You only need to configure this item. The following items are only used to generate trace logs, which can be omitted.
TRACE_LEVEL_SERVER = 16
TRACE_FILE_SERVER = SERVER
TRACE_DIRECTORY_SERVER =/u01/app/oracle/network/trace
TRACE_TIMESTAMP _ SERVER = ON
TRACE_UNIQUE_SERVER = ON
DIAG_ADR_ENABLED = OFF