Connections which go bad
in [Oracle]
|
From: Troels Arvin on 12 Jan 2010 03:41 Hello, I'm having trouble with JDBC transactions to an Oracle which are dropped after a while of inactivity. The situation can be reproduced using sqlplus: E.g. at time t1, I connect with sqlplus and run a simple query like =================================================== SQL> SELECT banner FROM v$version; BANNER --------------------------------------------------- Oracle Database 11g Release 11.1.0.7.0 - 64bit Production PL/SQL Release 11.1.0.7.0 - Production CORE 11.1.0.7.0 Production TNS for Linux: Version 11.1.0.7.0 - Production NLSRTL Version 11.1.0.7.0 - Production =================================================== All fine. Now, I leave the terminal and return an hour or two later, and perform a "SELECT banner FROM v$version;" again. The sqlplus session is silent for around five minutes, and then responds: =================================================== SELECT banner FROM v$version * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 5103 Session ID: 218 Serial number: 22182 =================================================== The sqlplus client in this case is v. 11.2 64 bit on Linux. Can some parameter be set so that a keep-alive like method is employed on the wire to keep connections alive? -- Regards, Troels Arvin
From: ddf on 12 Jan 2010 09:59 On Jan 12, 3:41 am, Troels Arvin <tro...(a)arvin.dk> wrote: > Hello, > > I'm having trouble with JDBC transactions to an Oracle which are dropped > after a while of inactivity. > > The situation can be reproduced using sqlplus: > > E.g. at time t1, I connect with sqlplus and run a simple query like > =================================================== > SQL> SELECT banner FROM v$version; > > BANNER > --------------------------------------------------- > Oracle Database 11g Release 11.1.0.7.0 - 64bit Production > PL/SQL Release 11.1.0.7.0 - Production > CORE 11.1.0.7.0 Production > TNS for Linux: Version 11.1.0.7.0 - Production > NLSRTL Version 11.1.0.7.0 - Production > =================================================== > > All fine. Now, I leave the terminal and return an hour or two later, and > perform a "SELECT banner FROM v$version;" again. The sqlplus session is > silent for around five minutes, and then responds: > =================================================== > SELECT banner FROM v$version > * > ERROR at line 1: > ORA-03113: end-of-file on communication channel > Process ID: 5103 > Session ID: 218 Serial number: 22182 > =================================================== > > The sqlplus client in this case is v. 11.2 64 bit on Linux. > > Can some parameter be set so that a keep-alive like method is employed on > the wire to keep connections alive? > > -- > Regards, > Troels Arvin I expect one is already set to disconnect inactive sessions; look in your sqlnet.ora file for sqlnet.expire_time and note its setting as it governs when the connection is considered 'dead' and disconnected. Comment this entry to disable this functionality. David Fitzjarrell
From: Mark D Powell on 12 Jan 2010 10:20 On Jan 12, 9:59 am, ddf <orat...(a)msn.com> wrote: > On Jan 12, 3:41 am, Troels Arvin <tro...(a)arvin.dk> wrote: > > > > > > > Hello, > > > I'm having trouble with JDBC transactions to an Oracle which are dropped > > after a while of inactivity. > > > The situation can be reproduced using sqlplus: > > > E.g. at time t1, I connect with sqlplus and run a simple query like > > =================================================== > > SQL> SELECT banner FROM v$version; > > > BANNER > > --------------------------------------------------- > > Oracle Database 11g Release 11.1.0.7.0 - 64bit Production > > PL/SQL Release 11.1.0.7.0 - Production > > CORE 11.1.0.7.0 Production > > TNS for Linux: Version 11.1.0.7.0 - Production > > NLSRTL Version 11.1.0.7.0 - Production > > =================================================== > > > All fine. Now, I leave the terminal and return an hour or two later, and > > perform a "SELECT banner FROM v$version;" again. The sqlplus session is > > silent for around five minutes, and then responds: > > =================================================== > > SELECT banner FROM v$version > > * > > ERROR at line 1: > > ORA-03113: end-of-file on communication channel > > Process ID: 5103 > > Session ID: 218 Serial number: 22182 > > =================================================== > > > The sqlplus client in this case is v. 11.2 64 bit on Linux. > > > Can some parameter be set so that a keep-alive like method is employed on > > the wire to keep connections alive? > > > -- > > Regards, > > Troels Arvin > > I expect one is already set to disconnect inactive sessions; look in > your sqlnet.ora file for sqlnet.expire_time and note its setting as it > governs when the connection is considered 'dead' and disconnected. > Comment this entry to disable this functionality. > > David Fitzjarrell- Hide quoted text - > > - Show quoted text - I would say disabling unused connected sessions may well be a good thing. If someone is going to be gone 2 hours then he or she should close their session and open a new one upon returning. However, the sqlnet parameters are all connections or none and if you have applications that connect then can go silent for hours at a time this may be undesirable. Sessions can also be terminated via the user profile which has an idle time setting and also through the use of the database resource manager so if you do not find any sqlnet settings that seem to apply check these. HTH -- Mark D Powell --
From: Mladen Gogala on 12 Jan 2010 12:51 On Tue, 12 Jan 2010 08:41:37 +0000, Troels Arvin wrote: > Can some parameter be set so that a keep-alive like method is employed > on the wire to keep connections alive? Well, there is this thing called "the philosopher's stone". Last time I read about it, it was in possession of a guy named Harry Potter. He lives in a place called "Hogwarts School of Witchcraft and Wizardry", also known as the Oak Table. Short of having a piece of rock that can make your sessions live forever, you may also feel inclined to look into the alert log and see if there are any completely benign errors like ORA-0600 or ORA-07445. That can be a sign of a serious trouble. -- http://mgogala.byethost5.com
From: vsevolod afanassiev on 13 Jan 2010 00:23 It could be firewall killing connections. Is application server software running on the same host as database? Or apps server is on different host? Is there firewall between them? If Apps server is on different host then you can try running SQL*Plus on both hosts. Most likely SQL*Plus on the same host as database will stay connected while SQL*Plus on different host will be disconnected. If the problem is caused by firewall killing idle connections then it can prevented: - Adjust firewall settings, disable killing of idle connections - JDBC should have parameter that forces all connections to be tested (typically by executing 'select * from dual'). - If Oracle Dead Connection Detection is enabled ( sqlnet.expire_time ) then Oracle sends probe packets
|
Next
|
Last
Pages: 1 2 Prev: Problem renaming a Tablespace Datafile in 10.2.0.4 Next: Materialized Views |