Oracle ODP.Net and Pooling

Question

These are really two questions in one, I think.

We developed a .NET application that accesses the Oracle database, and noticed that after changing the password of the Oracle user, the application continues to work for a short time with the old password in the connection string. Presumably this is due to how the existing compounds are combined?

When we first examined this, we tried to disable the union in the connection string, but the application would not have worked, throwing the error “Failed to secure a distributed transaction” at the point where it is trying to open the connection. Although we probably would not want to disable pooling in a production application, I am curious why MSDTC seems to need it?

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

Thanks in advance

Andy

Answer 1

See below for more details.

For the first question: (the application is still connected to the old DB password)

If we connect the database with the pooling option, the connection pool manager will create and maintain the number of connection sessions the first time the open or close object is OracleConnection object. (the number of connection sessions depends on the size of the pool "min" and "max" in the connection string). In Oracle, I think you can check the active session, for example:

 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 an Oracle document, this connection pooling service will close connection sessions after 3 minutes of active state. [ http://docs.oracle.com/html/E10927_01/featConnecting.htm ]

• Thus, the most likely reason may be that your application is connected to the database using this pool and is still connected for a short time, even after you have changed the database password.
• Perhaps there is one possibility of "Oracle Client Cache" in ODP.net. But not quite sure, you can check, [ http://www.oracle.com/technetwork/issue-archive/2008/08-jul/o48odpnet-098170.html ]

For the second question: (why do I need MSDTC)

If you use a nested database connection in your code, it will be transferred to DTC. [ http://petermeinl.wordpress.com/2011/03/13/avoiding-unwanted-escalation-to-distributed-transactions/ ] Actually, ODP.net, DTC and Oracle Database operated Oracle Service for Microsoft Transaction Server ( OraMTS).

But you did not have this problem (MSDTC) before disabling pooling. It seems that your code is reusing the same connection from the remote connection pool, and this may eliminate the need for DTC promotion. There was a similar question about StaffOverflow. [ Why is my transaction not rising to DTC?