ODP.NET：通过连接池避免连接超时
在一个站点，我可以使用 SQL Developer 连接到 Oracle 数据库，让它闲置很长时间（例如，> 60 分钟），然后返回，一切都很好。在第二个站点，如果它保持空闲状态超过 5-10 分钟（我没有精确计算），它会使 SQL Developer 处于新操作将超时的状态，我需要手动“断开连接”，然后按顺序重新连接做任何有用的事情。这似乎是第二个站点的连接超时，我不知道是什么原因导致的（我想知道如何将其关闭，尽管这不是我的主要问题）。
我的程序使用 ODP.NET 并处理突然出现的数据。每 30 分钟（为了讨论起见）它将获得一堆数据进行处理，这将涉及大量重复连接。它还使用连接池。我已将连接池设置为使用 5 分钟的生命周期。
我在第二个站点（而不是第一个站点）看到的是我的程序将在每次数据突发开始时出现连接超时异常（例如，ORA-03113）。我相信正在发生的事情是，在数据激增期间，连接池按设计使用。在突发结束时，检查“连接生命周期”，并且连接不是太旧，因此它留在连接池中。然后，30 分钟后，当新数据到达时，连接将从池中取出（并且不检查生命周期或超时）并使用，并且超时，就像我在 SQL Developer 中看到的那样。
如何避免连接超时，但在突发期间仍然利用连接池？从文档（和我的经验）看来，只有在连接进入池时才检查连接的生命周期，而不是在连接出来时检查连接的生命周期。
At one site I can connect to the Oracle Database with SQL Developer, leave it idle for a long time (e.g., > 60 minutes), and return, and it's fine. At a second site, if it stays idle for more than 5-10 minutes (I haven't counted exactly), it leaves SQL Developer in a state where new operations will timeout and I need to manually "Disconnect" and then reconnect in order to do anything useful. This seems to be a connection timeout at the second site, and I don't know what causes it (and I'd like to know how to turn it off, although this is not my main question).
My program uses ODP.NET and processes data that comes in spurts. Every 30 minutes (for the sake of discussion) it will get a bunch of data to process which will involve a number of repeated connections. It also uses Connection Pooling. I've set the Connection Pool to use a Lifetime of 5 minutes.
What I'm seeing at the second site (and not at the first) is my program will get connection timeout exceptions (e.g., ORA-03113) at the beginning of each spurt of data. What I believe is happening is that during the spurt of data, the connection pool is used as designed. At the end of the spurt the "Connection Lifetime" is checked, and the connection is not too old, so it is left in the connection pool. Then, 30 minutes later when new data arrives, the connection is taken out of the pool (and not checked for a lifetime or timeout) and used, and is timeing out, just as I see in SQL Developer.
How can I avoid the connection timeout but still take advantage of the Connection Pool during the spurts? It seems from the documentation (and my experience) that the connection is only checked for Lifetime when it goes into the pool, and not when it comes out.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
这是一个非常老的问题，但我在应用程序中遇到了一些类似的问题，所以我认为一些信息可能会对遇到这个问题的其他人有所帮助。
TL;DR 总结是 ODP.NET 驱动程序和 .NET 实现不能很好地相互配合，因此您正常运行的工厂连接池设置似乎并不完全按照您的预期工作。
IDLE_TIME设置为一个值（而不是UNLIMITED），那么最终这些长时间运行的空闲参数将被SNIPED数据库。这最终可能会导致 .NET 端出现问题，因为除非您明确检查连接是否仍然打开，否则 .NET 将提供这些SNIPED连接，就好像它们仍然可用一样（从而抛出上述超时ORA错误）。Data Validation=True;。这可确保 .NET 在为下一个服务调用提供连接之前检查会话连接。当此验证发现SNIPED会话时，会将其从 .NET 连接池中删除。鉴于此信息，OP 的原始问题很可能仅出现在一个站点中，这是由不同的数据库设置和/或 .NET 调用数据库的频率组合而成的。他可能在两种环境中都遇到了问题，但如果一种环境中的用户进行调用的频率足以让
Connection Lifetime完成其工作，那么他将永远不会在该数据库中看到这些超时。现在我仍然没有弄清楚如何在任何 Oracle IDLE_TIME 狙击发生之前终止 .NET 中的空闲连接，但只要您使用 Data Validation = True 参数，您应该就可以工作围绕这个问题。
This is a really old question but I've been experiencing some similar issues with an application and so I think some of the information might help anyone else who trips across this question.
The TL;DR summary is that ODP.NET drivers and the .NET implementation don't play nicely with each other and so your normal run of the mill connection pooling settings don't seem to work exactly how you would expect.
IDLE_TIMEset to a value in your Oracle user profile (and notUNLIMITED) then eventually these long running idle parameters will beSNIPEDby the database. This can end up causing problems on the .NET side because unless you are explicitly checking that your connections are still open, .NET is going to serve up theseSNIPEDconnections as if they are still available (thus throwing the above timeout ORA error).Data Validation=True;in your connection string. This ensures that .NET will check for session connectivity before it serves the connection up to the next service call. When this validation sees aSNIPEDsession it removes it from the .NET connection pool.Given this information, it is most likely that the OP's original problem was only appearing in the one site from a combination of different database settings and/or the frequency of the .NET calls to the database. He might have had the problem in both environments but if users in one environment were making calls frequently enough for
Connection Lifetimeto do it's job then he would never see these timeouts in that database.Now I still haven't figured out how to kill an idle connection in .NET before any Oracle IDLE_TIME sniping takes place but as long as you use that
Data Validation = Trueparameter you should hopefully be able to work around this problem.如果 5 分钟生命周期设置在第一个站点中表现良好，那么我认为这可能是由某人在 Oracle 服务器端的配置文件中设置空闲会话超时引起的。
然而，使用 5 分钟的生命周期设置，当您的突发变得更大时，您仍然可能会遇到超时，因为当您在下一次突发中将连接返回到池时，它们将被销毁。然后，池将忙于创建和删除连接，并且当负载非常大时可能会导致连接超时。
If the 5 minutes Lifetime setting is doing well in first site, then I think that this might be caused by someone setting the idle session timeout in a Profile in the Oracle server side.
Yet with the 5 min Lifetime setting you might still hit timeout when your spurt becomes bigger, because when you return connections to the pool in next spurt they'll get destroyed. The pool will then be busy creating and deleting connections and might lead to connection timeout when the load is really big.
默认情况下，ODP.NET 在连接池中始终创建并保持 1 个打开的连接。 Oracle Server 可以配置服务器端连接超时，在一段时间后关闭空闲连接。如果您的应用程序长时间空闲，这可能是问题所在，因为当服务器自行关闭连接时，ODP.NET 客户端不知道连接已关闭。客户端在池中仍然拥有该连接，并且会尝试使用它，然后您将收到错误消息。
有两种解决方案。
ODP.NET by default creates and holds 1 connection open in the connection pool at all times. Oracle Server can be configured with a server side connection timeout that will close idle connections after a period of time. If your application is idle for a long time this might be the problem, because when the Server closes a connection on its own the ODP.NET client doesn't know that connection is closed. The client still has that connection in the pool and will try to use it and then you will get an error.
There are two solutions.
您可以通过将 OracleCommand.ConnectionTimeout 属性设置为 0 来指定无限超时。
在这种情况下，不会有超时（至少在客户端）。
ConnectionPool也在这种情况下使用。
You may specify infinite timeout by setting
OracleCommand.ConnectionTimeoutproperty to 0.In this case there will be no timeout (at least on client-side).
ConnectionPool is used in this case too.