设置 Oracle 11g 会话超时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3363907/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Setting Oracle 11g Session Timeout
提问by ptsw
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
重启服务器后，从Tomcat服务器的oracle连接每晚都会超时。在重新启动之前，连接没有超时。现在，在早上，应用程序在访问数据库时抛出 JDBC 连接错误。重新启动 Tomcat 可以解决此问题。我假设这是由于重新建立了连接。我认为，这是由于 Oracle DB 会话超时所致。如何在 Oracle 11g 中禁用会话超时？
谢谢！
史蒂夫
Config.groovy with dev and test omitted.
省略了 dev 和 test 的 Config.groovy。
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"
}
} }
回答by Adam Musch
That's generally controlled by the profile associated with the user Tomcat is connecting as.
这通常由与 Tomcat 连接的用户关联的配置文件控制。
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.
所以我连接的用户有无限的空闲时间 - 没有超时。
回答by Gary Myers
Adam has already suggested database profiles.
Adam 已经建议了数据库配置文件。
You could check the SQLNET.ORA file. There's an EXPIRE_TIMEparameter but this is for detecting lost connections, rather than terminating existing ones.
您可以检查 SQLNET.ORA 文件。有一个EXPIRE_TIME参数，但这是用于检测丢失的连接，而不是终止现有的连接。
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 maystop that happening (as there'll be check every 10 minutes to check the client is alive).
鉴于它发生在一夜之间，听起来更像是空闲超时，这可能是由于应用程序服务器和数据库服务器之间的防火墙造成的。设置 EXPIRE_TIME可能会阻止这种情况发生（因为每 10 分钟会检查一次客户端是否还活着）。
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 validationQueryso that it will automatically restart the connection without a tomcat restart
或者，您应该能够使用validationQuery配置 tomcat，以便它会自动重新启动连接，而无需重新启动 tomcat
回答by Brian
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".
这很可能是由您的应用程序的连接池引起的；不是 Oracle DBMS 问题。大多数连接池都有一个验证语句，可以在为您提供连接之前执行该语句。在 oracle 中，您需要“从双重中选择 1”。
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)
编辑：在 Grails 的情况下，grails 池有类似的配置选项。例如Grails的1.2（参见发布说明的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
}
}
回答by Vadzim
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.
我来到这个问题寻找一种方法来启用基于总会话生命周期而不是空闲时间的 oracle 会话池到期。另一个目标是避免强制关闭应用程序意外。
It seems it's possible by setting pool validation queryto
似乎可以通过将池验证查询设置为
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.
这将以不影响应用程序的可预测方式关闭超过 30 分钟的会话。
回答by Lars
Check applications connection Pool settings, rather than altering any session timout settings on the oracle db. It's normal that they time out.
检查应用程序连接池设置，而不是更改 oracle 数据库上的任何会话超时设置。他们超时是正常的。
Have a look here: http://grails.org/doc/1.0.x/guide/3.%20Configuration.html#3.3%20The%20DataSource
看看这里：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?
您确定正确设置了“pooled”参数吗？
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"
编辑：
您的配置乍一看似乎没问题。我今天遇到了这个问题。也许这与您的痛苦有关：
“如果在数据库停机维护时启动应用程序，则会出现无限循环的异常”
回答by Alex Poole
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.
DB 是否知道连接已断开，或者会话是否仍列在 v$session 中？我认为，这表明它正在被网络丢弃。您是否知道在遇到问题之前它可以保持空闲状态多长时间，并且这是否与 TCP 空闲值有任何相似之处（如果我没记错的话，来自 sysctl 的 net.ipv4.tcp_keepalive_time、tcp_keepalive_probes 和 tcp_keepalive_interval）？不记得默认情况下 sysctl 更改是否持续存在，但这可能是修改过的内容，然后通过重新启动重置。
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.
此外，您还可以重置 JDBC 连接，而无需跳动整个服务器；在 WebLogic 中当然可以，我意识到这没有多大帮助，但我不熟悉 Tomcat 等价物。
