Oracle ODP.Net和连接池 [英] Oracle ODP.Net and connection pooling

问题描述

我猜这真的是两个问题.

我们开发了一个可访问Oracle数据库的.Net应用程序，并注意到在更改用户的Oracle密码后，该应用程序将在连接字符串中使用旧密码的情况下继续短暂工作.大概这与现有连接池化方式有关吗?

在首次调查此问题时，我们尝试关闭连接字符串中的池，但是该应用程序无法正常工作，并在尝试打开连接时抛出错误无法加入分布式事务".虽然我们可能不想在生产应用程序中关闭连接池，但我很好奇为什么MSDTC似乎需要它?

我们正在使用Oracle 11g(11.1.2)和最新的ODP.Net(我认为是11.2).

预先感谢

安迪

解决方案

请查看以下一些结果:

对于问题一:(应用程序仍使用旧的数据库密码连接)

如果使用连接池选项连接数据库，则连接池管理器将在首次调用OracleConnection对象的open或close时创建并维护连接会话数. (此连接会话的数量取决于连接字符串中最小"和最大"池的大小).在Oracle中，我认为您可以像这样检查活动会话:

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND';

根据Oracle文档，此连接池服务将在非活动状态3分钟后关闭连接会话. [ http://docs.oracle.com/html/E10927_01/featConnecting.htm]

1. 所以最可能的原因是，您的应用程序仍然 通过使用此池连接到数据库，并且仍然连接到 即使您更改了数据库密码也很短时间.
2. "Oracle客户端缓存"也可能存在一种可能性 ODP.net中的功能.但不太确定，您可以在[ http://www.oracle.com/technetwork/issue-archive/2008/08-jul/o48odpnet-098170.html ]

对于第二个问题:(为什么需要MSDTC)

如果在代码中使用嵌套的数据库连接，它将升级为DTC. [ http://petermeinl.wordpress. com/2011/03/13/avoiding-unwanted-escalation-to-distributed-transactions/]实际上，在ODP.net，DTC和Oracle数据库中，确实有用于Microsoft事务处理服务器(OraMTS)的Oracle服务.

但是在禁用连接池之前，您没有发生此问题(MSDTC).看起来您的代码正在重用未定义的连接池中的同一连接，并且可能消除了升级DTC的需要.关于StaffOverflow也有类似的问题. [为什么我的交易没有升级到DTC?]

this is really two questions in one I guess.

We've developed a .Net app that accesses an Oracle database, and have noticed that after changing the user's Oracle password, the app continues to work for a short time with the old password in the connection string. Presumably this is something to do with the way existing connections are pooled?

When first investigating this we tried turning off pooling in the connection string, however the app wouldn't work, throwing the error "Unable to enlist in a distributed transaction" at the point it tries to open a connection. While we probably wouldn't want to turn off connection pooling in a production app, I'm curious as to why MSDTC seems to need it?

We are using Oracle 11g (11.1.2) and latest ODP.Net (11.2 I think).

Thanks in advance

Andy

解决方案

Please see some of the finding below:

For Question One: (application still connected with old DB password)

If we connect the database with connection pooling option, connection pool manager would create and maintain the number of connection sessions when first calling the open or close of OracleConnection object. (number of this connection sessions depend on "min" & "max" pool size in connection string). In Oracle, I think you could check active session like:

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND';

And according to Oracle doc, this connection pooling service will close the connection sessions after 3 minutes of in-active state. [ http://docs.oracle.com/html/E10927_01/featConnecting.htm ]

1. So the most possible reason could be, your application still connected to the database by using this Pool and still connected for a short time, even after you changed the database password.
2. There could be also one possibility of "Oracle Client Cache" feature in ODP.net. But not quite sure, you can check at, [ http://www.oracle.com/technetwork/issue-archive/2008/08-jul/o48odpnet-098170.html ]

For Question Two: (why MSDTC needed)

If you are using nested Database connection in your code, it will be promoted to DTC. [ http://petermeinl.wordpress.com/2011/03/13/avoiding-unwanted-escalation-to-distributed-transactions/ ] Actually there was Oracle Service for Microsoft Transaction Server (OraMTS) act as among ODP.net, DTC, and Oracle Database.

But you didn't happend this problem (MSDTC) before disabled the connection pooling. It seems like your code is reusing the same connection out of undelining connection pool, and it might eliminate the need to promote DTC. There was similar question on StaffOverflow. [ Why isn't my transaction escalating to DTC? ]

这篇关于Oracle ODP.Net和连接池的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！