Re: Dead Clients with SQL*Net and TCP/IP

In article <423nfq$nln_at_ujf.ujf-grenoble.fr>, Gilles.Bruno_at_ujf-grenoble.fr (Gilles BRUNO) writes:
>> dischner_at_med.uni-muenchen.de (Anton Dischner) wrote:
>>
>> >Hi Guenter,
>> >enter tcpctl -?
>> >the standard timeout is set to 0 seconds (off).
>> >Try to set it to a reasonable time.
>> >Please drop me a note if this (and what value) solves your problem.
 

>> >In article <41rnmr$2t60_at_news-s01.ny.us.ibm.net>, brorsg_at_ibm.net (Guenter
>> >Brors) wrote:
 

>> >> When an MS-Windows client crashes or the PC is turned off without
>> >> properly ending the application, the session on the Oracle server
>> >> remains forever open. This is a waste of ressources, apart from DBMS
>> >> locks that may also be held forever and block other users.
>> >>
>> >> It seems that the underlying TCP/IP is used by SQL*Net without the
>> >> KEEPALIVE option, which could help in detecting dead clients. Is there
>> >> any way to turn on this option ?
>> >>
>> >> I do not want a DBA to regularly check on the server side and manually
>> >> kill dead sessions. I want an application that can turn 24 hours a day
>> >> without administration.
>> >>
>> >> I'm using Oracle 7.1.16 on RS/6000 and SQL*Net 2.0 with Pro*Cobol and
>> >> ODBC on MS-Windows.
>> >>
>> >> Please let me know how you solved this problem.
>> >>
>>
>> An other solution Oracle customer support people gave me
>> was on AIX to configure the Unix kernel so as to make it
>> check and validate the Tcp/Ip connexions more frequently
>> Normally it has not to be donne while communicating with
>> regular unix boxes, but the PC's tcp/ip kernel does not
>> behave as well as the other do (in the case off a GPF or
>> a power down for example).
>>
>> On AIX you could use the 'no' (network option) command so
>> as the decrease the 'tcp_keepidle' time : by default it
>> checks the tcp connexions every 2 hours (in that case the
>> Rdbms really knows your client is 'dead' 2 hours after it
>> crashes - and so PMON close it's sql*net shadow process
>> and the corresponding Oracle session is automatically
>> closed).
>>
>> You can decrease this parameter to make it nears 15 minutes
>> but BEWARE that the lower it will be, the higher would be
>> the network traffic (it generates many brodcasts for each
>> connected client)...
>>
>> Hope this helps.
>>
>> -------------------------------------------------------------
>> ('< Gilles BRUNO. Universite Joseph Fourier.
>> ,',) Tel: +33 76 63 56 68 Fax: +33 76 51 42 74
>> ''<< E-mail: Gilles.Bruno_at_ujf-grenoble.fr
>> ----""-------------------------------------------------------
>>

Try upgrading to SqlNet 2.1 with the 'Dead Connection Detection' feature. Does exactly what you want.

Also, helps on Unix side if power goes out , reboot , etc.

-- 
*****************************************************
* Burt Peltier Shell Offshore    ep94bvps_at_shell.com *
* (504) 588-0179 701 Poydras #1660 New Orleans 70139*
*****************************************************