DCD(Dead Connection Detection)설정
1. Client의 user process가 비정상적으로 종료되었을 경우, Oracle Server Process는 dead connection을 detection하지 않기 때문에, 세션이 그대로 남아있을 수 있다.
2. Dead Connection Detection을 설정하여, 10분(권장값)내에 probe packet에 대한 응답을 받지 않을 경우, Dead Connection으로 판단하고 pmon process가 session cleanup을
할 수 있도록 한다.
3. 조치방법
sqlnet.ora 파일에 아래를 설정 한 이후에 리스너를 재기동 한다.
SQLNET.EXPIRE_TIME=10
리스너는 재기동을 하더라도 접속된 세션에는 영향을 주지 않는다. 단, 재기동 중에 새롭게 접속하는 세션은 접속이 불가능하다. 값은 10이 권장값이며 10분 을 뜻한다.
단, 테스트 시스템 적용 후에 모든 어플리케이션에서 문제가 없다라고 확인되었을 때에만, 실시스템에 적용해야 한다.
*. 관련노트
Note 1018160.6 Common Questions About Dead Connection Detection (DCD)
Q: What is Dead Connection Detection?
A: Dead Connection Detection (DCD) allows SQL*Net/Net8 to identify connections
that have been left hanging by the abnormal termination of a client. This
feature minimizes the waste of resources by connections that are no longer
valid. It also automatically forces a rollback of uncommitted transactions
Q: How does Dead Connection Work?
A: On a connection with DCD enabled, a small probe packet is sent from server
to client at a user defined interval (usually several minutes). If the
connection is invalid (usually due to the client process or machine being
unreachable), the connection is closed when an error is generated by the send
operation, and the server process terminates the connection.
Q: How do you set the Dead Connection Detection feature?
A: DCD is enabled on the server side of the connection by defining a parameter
in the sqlnet.ora file in $ORACLE_HOME/network/admin called
sqlnet.expire_time. This parameter determines the time period between
successive probe packets across a connection between client and server.
SQLNET.EXPIRE_TIME= <# of minutes>
The sqlnet.expire_time parameter is defined in minutes and can have any value
between 1 and an infinite number. If it is not defined in the sqlnet.ora
file, DCD is not used. A time of 10 minutes is probably optimum for most
applications.
DCD probe packets originate on the server side, so it must be enabled on the server side. If you define sqlnet.expire_time on the client side, it will be ignored.
*. SQLNET.EXPIRE_TIME 설명
Purpose
Use parameter SQLNET.EXPIRE_TIME to specify a the 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 the server process to exit. This parameter is primarily intended for the database server,
which typically handles multiple connections at any one time.
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.
Default
0
Minimum Value
0
Recommended Value
10