Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
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"
That's generally controlled by the profile associated with the user Tomcat is connecting as.
SQL> SELECT PROFILE, LIMIT FROM DBA_PROFILES WHERE RESOURCE_NAME = 'IDLE_TIME';
PROFILE LIMIT
------------------------------ ----------------------------------------
DEFAULT UNLIMITED
SQL> SELECT PROFILE FROM DBA_USERS WHERE USERNAME = USER;
PROFILE
------------------------------
DEFAULT
So the user I'm connected to has unlimited idle time - no time out.
–
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
–
–
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:
<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"/>
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
–
–
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 < 30/24/60
This would close sessions aging over 30 minutes in predictable manner that doesn't affect application.
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.
–
–
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,
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"
–
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
