Setting Oracle 11g Session Timeout

Question

After rebooting the server  the oracle connection from the Tomcat server times out every night  Prior to the reboot  the connection didn t timeout  Now  in the morning  the application throws a JDBC connection error while accessing the DB  Restarting Tomcat corrects the issue  I m assuming that s due to the connections being re-established  I think  this is due to the Oracle DB timing out the session  How can the session timeout be disabled in Oracle 11g  Thanks  Steve  Config groovy with dev and test omitted   dataSource     pooled   true    hibernate      cache use second level cache   true    cache use query cache   true    cache provider class    net sf ehcache hibernate EhCacheProvider        environment specific settings environments   production     dataSource       driverClassName    oracle jdbc driver OracleDriver      username    XXXXX      password    XXXXXX      dialect    org hibernate dialect Oracle10gDialect      dbCreate    update     one of  create    create-drop   update      url    jdbc oracle thin  XXXXXX 1521 xxxx

User · Answer

That s generally controlled by the profile associated with the user Tomcat is connecting as     SQL gt  SELECT PROFILE  LIMIT FROM DBA PROFILES WHERE RESOURCE NAME    IDLE TIME    PROFILE                        LIMIT ------------------------------ ---------------------------------------- DEFAULT                        UNLIMITED  SQL gt  SELECT PROFILE FROM DBA USERS WHERE USERNAME   USER   PROFILE ------------------------------ DEFAULT   So the user I m connected to has unlimited idle time - no time out

User · Answer

Does the DB know the connection has dropped  or is the session still listed in v session  That would indicate  I think  that it s being dropped by the network  Do you know how long it can stay idle before encountering the problem  and if that bears any resemblance to the TCP idle values  net ipv4 tcp keepalive time  tcp keepalive probes and tcp keepalive interval from sysctl if I recall correctly   Can t remember whether sysctl changes persist by default  but that might be something that was modified and then reset by the reboot   Also you might be able to reset your JDBC connections without bouncing the whole server  certainly can in WebLogic  which I realise doesn t help much  but I m not familiar with the Tomcat equivalents

User · Answer

I came to this question looking for a way to enable oracle session pool expiration based on total session lifetime instead of idle time  Another goal is to avoid force closes unexpected to application   It seems it s possible by setting pool validation query to  select 1 from V SESSION  where AUDSID   userenv  SESSIONID   and sysdate-LOGON TIME  lt  30 24 60   This would close sessions aging over 30 minutes in predictable manner that doesn t affect application

User · Answer

This is likely caused by your application s connection pool   not an Oracle DBMS issue   Most connection pools have a validate statement that can execute before giving you the connection   In oracle you would want  Select 1 from dual    The reason it started occurring after you restarted the server is that the connection pool was probably added without a restart and you are just now experiencing the use of the connection pool for the first time   What is the modification dates on your resource files that deal with database connections   Validate Query example     lt Resource name  jdbc EmployeeDB  auth  Container               validationQuery  Select 1 from dual  type  javax sql DataSource  username  dbusername  password  dbpassword              driverClassName  org hsql jdbcDriver  url  jdbc HypersonicSQL database              maxActive  8  maxIdle  4   gt    EDIT  In the case of Grails  there are similar configuration options for the grails pool   Example for Grails 1 2  see release notes for Grails 1 2   dataSource       pooled   true     dbCreate    update      url    jdbc mysql   localhost yourDB      driverClassName    com mysql jdbc Driver      username    yourUser      password    yourPassword      properties           maxActive   50         maxIdle   25         minIdle   5         initialSize   5         minEvictableIdleTimeMillis   60000         timeBetweenEvictionRunsMillis   60000         maxWait   10000

User · Answer

Check applications connection Pool settings  rather than altering any session timout settings on the oracle db  It s normal that they time out   Have a look here  http   grails org doc 1 0 x guide 3  20Configuration html 3 3 20The 20DataSource  Are you sure that you have set the  pooled  parameter correctly   Greetings  Lars    EDIT  Your config seems ok on first glimpse  I came across this issue today  Maybe it is related to your pain   Infinite loop of exceptions if the application is started when the database is down for maintenance

User · Answer

Adam has already suggested database profiles    You could check the SQLNET ORA file  There s an EXPIRE TIME parameter but this is for detecting lost connections  rather than terminating existing ones   Given it happens overnight  it sounds more like an idle timeout  which could be down to a firewall between the app server and database server  Setting the EXPIRE TIME may stop that happening  as there ll be check every 10 minutes to check the client is alive    Or possibly the database is being shutdown and restarted and that is killing the connections    Alternatively  you should be able to configure tomcat with a validationQuery so that it will automatically restart the connection without a tomcat restart

[oracle] Setting Oracle 11g Session Timeout

Examples related to oracle

Examples related to oracle11g