ODP.NET Oracle.ManagedDataAcess random ORA-12570 errors

Edgar Carvalho picture Edgar Carvalho · Feb 12, 2016 · Viewed 20k times · Source

I'm trying to migrate to Oracle.ManagedDataAcess from unmanaged version and receiving randoms ORA-12570 TNS:packet reader failure.

I don't know why this error starts, but once it starts, every subsequent request gives the same error for about 10-30 minutes, then it works again for another 10-30 minutes and so on.

So it is a random of subsequent failures for some time then subsequent success

Already tried a lot of things, to resume:

The environment:

  • Oracle.ManagedDataAcess version 12.1.2400 (4.121.2.20150926) (nuget) (no gac reference installed on server that could override the bin version)
  • Oracle Server Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  • Windows 2012 (Windows Update ok)

Checked:

  • Firewall: It is not a firewall problem
  • Machine error: The same problem happens on my machine, Azure WebApp and an AWS EC2 Instance
  • Interference: There is no sniffer running, transparent proxy etc.
  • Encryption: I don't use any kind of encryption (unless there is something enabled by default that I don't know)
  • Connections string: The same connection string is working perfectly with the unmanaged version

Aditional information:

  • This is a production database, it is very stable
  • The application is compiled to anycpu, the IIS app pool is restricted to 64bits
  • Im testing exactly the same request every time (just a refresh on a get url of a rest ws, webapi), so it is not related to data format

Configuration:

Server sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Application Web.config

<connectionStrings>
<add name="XXXX" connectionString="Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.com)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xxx)));User Id=xxxxx;Password=xxxxx;" />
</connectionStrings>

<configSections>
    <section name="oracle.manageddataaccess.client" type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</configSections>

<oracle.manageddataaccess.client>
    <version number="*">
      <dataSources>
        <!--<dataSource alias="SampleDataSource" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL))) " />-->
      </dataSources>
      <settings>
        <setting name="SQLNET.AUTHENTICATION_SERVICES" value="NONE"/> <!--NTS-->
        <setting name="sqlnet.crypto_checksum_server" value="rejected"/>
        <setting name="sqlnet.crypto_checksum_client" value="rejected"/>
        <setting name="SQLNET.ENCRYPTION_SERVER" value="rejected"/>
      </settings>
    </version>
</oracle.manageddataaccess.client>

Some references:

https://community.oracle.com/thread/3634263?start=0&tstart=0

ODP.net managed driver throws ORA-12570: Network Session: Unexpected packet read error

Managed Oracle Client with Oracle Advanced Security Options

ODP.NET error in IIS: ORA-12357 Network Session End of file

UPDATE 1

After pooling changed (as I described as an answer here), I decided to publish a version to do some real test. After 1 day and users complaining about performance I got another error: Value cannot be null. Parameter name: byteArray

I changed the reference back to the unmanaged version and everything was fine again, faster, without bytearray error, better pooling management.

So I'm just giving up of the managed version for now, maybe I will try again on Oracle next release.

Here some references about this new error, as you can see, looks like another bug (still without any answer).

https://community.oracle.com/thread/3676588?start=0&tstart=0

EF + ODP.NET + CLOB = Value Cannot be Null - Parameter name: byteArray?

So far, reasons to not use:

  • Pooling management bug
  • CLOB null/not null bytearray errors
  • Performance degradation probably related to pooling bug

Answer

Edgar Carvalho picture Edgar Carvalho · Feb 12, 2016

After disabling pooling (Pooling=False), as @bdn02 suggested, I could confirm that it worked. However I think it should affect the performance and I was concerned about publishing this code into production without any pooling (I thought the standard values were ok).

So I tried many configurations and looks like somehow (it is not very clear) the pool management of oracle was raising an ORA-12570 error and, after a period of time, the sessions are closed and the application worked again.

To find the best configuration with pooling enabled I created a test application to start 50 threads (each one doing 1 test each 50ms), and decreased the default pool values until the error stoped. This way I was able to get an optimal configuration, stable, without any errors.

Obviously it does not applies to every server, but this is my final connection string configuration:

Pooling=true;Min Pool Size=1;Connection Lifetime=180;Max Pool Size=50;Incr Pool Size=5