Oracle DCD (Dead Connection Detection) 설정


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

 


Comments

comments

haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다