ORA-12571: TNS: write failure in packet with ASP.NET

Question

My development team is experiencing numerous ORA-12571: TNS:packet writer failure using ASP.NET 3.5 and 4.0 against Oracle 11g. These errors are incompatible as to when they occur, and are generated by numerous applications. This exception occurs when calling random stored procedures, packages, and built-in SQL statements. The Oracle 11 client is installed on the web server. Some applications use Microsoft System.Data.OracleClient to connect to Oracle, and some use the .NET components provided by oracle (ODP.NET). Both data access objects have the same error.

There are other non-.NET applications that run on a different web server but use the same database server. Applications have no such problems. My initial thinking is that there is something incorrectly configured on the web server with the Oracle client.

Has anyone else got this error? What have you done to fix this?

 ORA-12571: TNS:packet writer failure 

Stack trace:

 at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc) at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals) at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals) at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) 

Answer 1

Another possible solution is that the firewall between you and the Oracle database believes that your connection is dead and closes it under you. You will find out when you try to execute the request and get ORA-12571 error.

This is because the connections are open for a long time without activity.

The solution is to add SQLNET.EXPIRE_TIME to the sqlnet.ora file on the server and set it to a certain interval (10). This will cause the connections to ping every 10 minutes to make sure they are still alive.

As a result, your firewall will see network activity and will not close the connection.

 SQLNET.EXPIRE_TIME=10 

ORA-12571: TNS: failure to write a package is one of the most difficult problems that I had to solve

Answer 2

I think this is a mistake in Oracle. I ran into many problems using the DBDataAdapter.Fill method, where Oracle Client suffocates from a memory error. This was resolved for me using client 11.2.0.2 with fix 6.

If you are looking for an Oracle support site, you will see many such problems.

Also check for problems reading protected memory with 11g1 / 11g2 clients.

Answer 3

After I installed the elmah module and was able to parse the exceptions, I tried:

• Change the connection configuration.
• Delete and / or update server firewall rules.
• Update the Oracle client on the server machine.

Any of the above options resolved the issue, but I forgot the outdated provider (System.Data.OracleClient) that we used. After I replaced it with the latest version of ODP.NET (Oracle.DataAccess), everything began to work flawlessly.

Discuss: Based on your description of exceptions, you are currently using an outdated provider.