Oracle 到 .Net 的连接 - 连接池
更具体地说，这是针对 SQL Server Reporting Services 的，但我也将其视为典型的 .Net 应用程序问题。
问题就在这里。我们使用 SSRS 报告来自 Oracle 数据库的数据。从第一天起，我们的用户就抱怨他们遇到了诸如 ORA-2396：超出最大空闲时间、ORA-01012-未登录等错误。
我们的成员包括 DBA、应用程序开发人员、网络工程师，但到目前为止，我们找不到更好的解决方案线索。
最近，在进行一些研究时，我发现许多人记录了 .Net 与 Oracle 的连接的一个“已知”问题。正如他们提到的，如果我们使用连接池，则即使是无用的连接也可能在池中可用。在下一个请求时，可能会使用此连接，此时将引发错误。正如我们的 DBA 所确认的，我们的 oracle 实例设置为在空闲 X 分钟时终止连接。
以下是上述提及的链接 http://msdn.microsoft.com/en- us/library/8xx3tyca(v=VS.90).aspx
http://www.codeproject.com/KB/dotnet/ADONET_ConnectionPooling.aspx
还有更多。然而，我没有在任何地方得到非常确认的声明，表明是的，这是当前（最新版本的.Net 和 oracle 等）的问题。
在解决方案部分，他们提到我们将使用“Validate Connection=true”属性作为连接字符串的一部分。但如果我尝试使用它，它会显示“不支持关键字 - 验证连接”。 （我也尝试过 Validcon）
我的问题是这些 1、是否确认这个连接池“脏”读问题存在？ 2. 如果有，解决办法是什么？ 3. 如果不是，可能是什么原因导致我们出现问题。
如果您需要更多信息，请随时发表评论。
This is more specifically with SQL Server Reporting Services, however I also see this as typical .Net application issue as well.
Here is the problem. We are using SSRS to report data from Oracle database. From day 1 our users are complaining that they get errors like ORA-2396: Exceeded maximum idle time, ORA-01012-Not logged on etc.
We have included DBAs, app developers, network engineer, but so far we could not find any better clue to that.
Recently while perform some research, I have observed that many people have documented one "known" issue with .Net connectivity to oracle. As they mention, if we are using connection pooling, there is a possibility that even un-useful connection might be available in the pool. On next request, this connection might get used and at that point the error will be thrown. As confirmed by our DBA, our oracle instance is set to kill the connection if it's idle for X minutes.
Here are the links which has the aforesaid mention
http://msdn.microsoft.com/en-us/library/8xx3tyca(v=VS.90).aspx
http://www.codeproject.com/KB/dotnet/ADONET_ConnectionPooling.aspx
There are couple of more as well. However I do not get a very confirmed statement anywhere stating that yes, this is an issue with current (latest versions of .Net and oracle etc).
On solution part, they mention that we shall be using "Validate Connection=true" attribute as part of connection string. But if I try to use that, it says "Key word not supported - validate connection". ( I have also tried Validcon)
Questions I have are these
1. Is it confirmed that this "dirty" read from connection pool problem exists?
2. If so, what are the solutions?
3. If not, what could be causing the issues at our end.
If you need more information, feel free to comment.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这里的根本问题在于您的 DBA。当我使用一个帐户（即我的个人 Oracle 帐户）时，我会看到同样的错误，该帐户容易受到我们的 DBA 运行的相同空闲连接清理的影响。最好的解决办法是让他们为您分配一个“服务”帐户，该帐户在空闲时间后不会终止。
也就是说，我猜您会收到“不支持关键字 - 验证连接”，因为您使用的是 microsoft 提供商而不是 oracle 提供商。我对 SSRS 连接不太熟悉，所以我不确定您如何选择其中之一。然而在.net 中，System.Data.OracleClient 和Oracle.DataAccess 之间存在差异。
如果解决了这个问题，验证连接选项会在使用连接之前预先对连接进行 ping 操作，从而增加开销，这比简单地禁用池要好一些。您可以尝试的另一件事是设置最小池大小= 0，这可以让池变成零，从而减少连接闲置时间过长的可能性。不幸的是，这不是一个完美的修复，因为提供商仅在使用后检查过时的连接。
如果您还没有 Oracle 数据提供程序的连接字符串参数，请参考以下链接：
http://download.oracle.com/docs/html/E10927_01/featConnecting .htm#i1006393
The root issue here belongs your DBAs. I see the same error when I use an account (ie my personal oracle account) that is succeptible to the same idle connection cleanup that our DBAs have running. The best fix would be to have them assign an "service" account to you that is not terminated after an idle time.
That said, I would guess that you are getting "Key word not supported - validate connection" because you are using the microsoft provider and not the oracle provider. I'm not that familiar with SSRS connections so I'm not sure how you select one over the other. In .net however it's the difference between System.Data.OracleClient and Oracle.DataAccess.
If you get that resolved, the validate connection option adds overhead by pre-pinging a connection before using it, making it marginally better than simply disabling pooling. Another thing you can try is setting min pool size = 0 which lets the pool go down to nothing, leaving a smaller chance that a connection sits idle for too long. Unfortunately it's not a perfect fix as the provider only checks for stale connections AFTER they are used.
Here's the reference to the connection string parameters for oracle's data provider if you didn't already have it:
http://download.oracle.com/docs/html/E10927_01/featConnecting.htm#i1006393
这是一个已知问题，无论您在哪里使用连接池，它都会发生，并且取决于几个因素 - 其中之一是服务器上的设置...有时在使用 Oracle RAC 时甚至很复杂...
一些 Oracle .NET 提供商拥有内置的池化解决方案，可无缝解决此问题... Devart 出售的就是这样的提供商... http://www.devart.com/dotconnect/oracle/
我不隶属于 Devart，只是一个满意的客户...
This is a known problem and it occurs wherever you use connection pooling and depends on several things - one of them being the settings on the server... sometimes even complicated when using an Oracle RAC...
Some Oracle .NET providers have built-in solutions for pooling taking care of this problem seamlessly... one such provider is the one sold by Devart... http://www.devart.com/dotconnect/oracle/
I am not affiliated with Devart, just a happy customer...