Oracle.ManagedDataAccess connection remains active after close/dispose

User_XSI6X

I'm using Powershell to connect to our Oracle database.

Ooriginally I was using 4.122.1.0 from 2018 but have since upgraded to 18.3.0 all the way up to the current release 19.3.0, using the package from https://www.nuget.org/packages/Oracle.ManagedDataAccess/

The issue I have is that since upgrading to 18.3.0 closing and/or disposing the connection object no longer truly disconnects the user.

You can view this by doing a `netstat -an` before connecting, after connecting and after closing and disposing on the windows machine on which I run my code. Even when the connection was closed/disposed hours ago, but with the shell not closed, the connection remains active.

The biggest issue I have with this is that the database on our various environments does a frequent rollback, which fails because the user is still connected. I could off course try to fix this in the rollback script but thats tricky and also not the way it should be.

Steps to reproduce:

1. Download the latest from https://www.nuget.org/packages/Oracle.ManagedDataAccess/
2. Extract 'Oracle.ManagedDataAccess.dll' to c:\test
3. Start powershell
4. enter 'netstat -an' and verify no connection exists from your machine to your database
5. run the following powershell code, after editing it to suite your environment. My DataSource contains a failover which I left here because it might be relevant...

$dataSource = '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=YOURDATABASEHOST1)(PORT=1522))(ADDRESS=(PROTOCOL=TCP)(HOST=YOURDATABASEHOST2)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=YOURDBSERVICE)))'

$userID= 'YOURDBUSER'

$securePassword = Read-Host -AsSecureString -Prompt 'Password'

$securePassword.MakeReadOnly()

Add-Type -Path "C:\test\Oracle.ManagedDataAccess.dll"

$connBuilder = New-Object Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder

$connBuilder["Data Source"] = $dataSource

$oracleCredential = New-Object Oracle.ManagedDataAccess.Client.OracleCredential $userID, $securePassword

$dbConn = New-Object Oracle.ManagedDataAccess.Client.OracleConnection $connBuilder, $oracleCredential

$dbConn.open()

netstat -an | findstr "DBIPRANGE"

$dbConn.close()

netstat -an | findstr "DBIPRANGE"

$dbConn.dispose()

netstat -an | findstr "DBIPRANGE"

Start-Sleep -Seconds 1800

netstat -an | findstr "DBIPRANGE"

Since powershell basicaly just runs .NET the same issue should exist there, even with the using statements. The connection only get's closed when the application in which the Oracle.ManagedDataAccess was loaded closes.

Question1: Is there some way to get this connection to close that I've missed?

Question2: I've not found an official place to put bugs other than file a ticket perhaps, is there some place to post bugs other than here?

Yves_D

I don't see any place in your code where you set connection pooling to false. As the default is true (see https://docs.oracle.com/en/database/oracle/oracle-data-access-components/18.3/odpnt/featConnecting.html#GUID-0CFEB161-68… ) you actually closing and disposing the connection does nothing more than giving the connection back to the pool where it remains open and available until the pool closes it. Which is after 3 minutes of idle time if I'm not mistaken.

So if you really want the connection closed, don't use connection pooling.

Yves_D

I don't see any place in your code where you set connection pooling to false. As the default is true (see https://docs.oracle.com/en/database/oracle/oracle-data-access-components/18.3/odpnt/featConnecting.html#GUID-0CFEB161-68… ) you actually closing and disposing the connection does nothing more than giving the connection back to the pool where it remains open and available until the pool closes it. Which is after 3 minutes of idle time if I'm not mistaken.

So if you really want the connection closed, don't use connection pooling.

AndrewSayer

Yves_D wrote:I don't see any place in your code where you set connection pooling to false. As the default is true (see https://docs.oracle.com/en/database/oracle/oracle-data-access-components/18.3/odpnt/featConnecting.html#GUID-0CFEB161-68… ) you actually closing and disposing the connection does nothing more than giving the connection back to the pool where it remains open and available until the pool closes it. Which is after 3 minutes of idle time if I'm not mistaken.So if you really want the connection closed, don't use connection pooling.

And remember that connection pooling is generally a good idea. It means you avoid the relatively large overhead of establishing that connection. I’ve seen applications grind to a halt due to poor connection pooling going on.

User_XSI6X

Thanks for pointing that out!

I'll look into that as a work around. Perhaps setting '$connBuilder["Min Pool Size"] = 0' will also help as I see from the documentation you posted that it defaults to 1

If the pool should close after a certain amount of idle time than I think this is still a bug because even when I wait wait for hours a session remains active and I'm sure its idle. I'm wondering if there is some keep alive going on that breaks the idle check...

Also, I've only found the 'Connection Timeout' which is set to the default of 15 seconds, is there some way to lower that pool timeout?

And lastly, shouldn't there be some way to tell the pool to completely disconnect?

Edit:Looks like the following helped:

$dataSource = '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=YOURDATABASEHOST1)(PORT=1522))(ADDRESS=(PROTOCOL=TCP)(HOST=YOURDATABASEHOST2)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=YOURDBSERVICE)))'

$userID= 'YOURDBUSER'

$securePassword = Read-Host -AsSecureString -Prompt 'Password'

$securePassword.MakeReadOnly()

Add-Type -Path "C:\test\Oracle.ManagedDataAccess.dll"

$connBuilder = New-Object Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder

$connBuilder["Data Source"] = $dataSource

$connBuilder["Min Pool Size"] = 0

$connBuilder["Max Pool Size"] = 2

$connBuilder["Decr Pool Size"] = 2

$connBuilder["Connection Lifetime"] = 1

$oracleCredential = New-Object Oracle.ManagedDataAccess.Client.OracleCredential $userID, $securePassword

$dbConn = New-Object Oracle.ManagedDataAccess.Client.OracleConnection $connBuilder, $oracleCredential

$dbConn.open()

netstat -an | findstr "DBIPRANGE"

[Oracle.ManagedDataAccess.Client.OracleConnection]::ClearPool($dbConn)

$dbConn.close()

netstat -an | findstr "DBIPRANGE"

$dbConn.dispose()

netstat -an | findstr "DBIPRANGE"

Start-Sleep -Seconds 1800

netstat -an | findstr "DBIPRANGE"

Any thoughts on my solution? Also, connection pooling, I'm guessing thats mostly for applications that have to do multiple queries in parallel right? So I'm guessing for Powershell pooling is generally not needed as its not usually doing anything in parallel.

Now that I've been educated I don't think I should still call this a bug, I'm guessing this is expected behavior from the default 'Min Pool Size' = 1 and the default 'Connection Lifetime' = 10000 (almost 8 hours)

Alex Keh-Oracle

If you're going to set up your connection pool with a min size of 0, clean up each connection returning to the pool with a very low Connection Lifetime, and clear the pool anyway, why bother using a pool in the first place? Just set Pooling=false if that's the behavior you are really after.

User_XSI6X

Yeah, In fact, I already switched to that as it didn't make much sense for my use case

What I added was the maximum effort I could think of to get the pool to really disconnect as soon as possible while still having a pool that could be used for parallel running queries.