ORA-12571: TNS:packet writer failure with ASP.NET

Question

My development team is experiencing numerous ORA-12571: TNS:packet writer failure errors using ASP.NET 3.5 and 4.0 against Oracle 11g. These errors are inconsistent as to when they occur, and are generated by numerous applications. This exception happens while calling random stored procedures, packets, and inline 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 come up with the same error.

There are other non .NET applications that run on a different web server, but use the same database server. The apps do not have any such issues. My initial thinking is that there is something configured incorrectly on the web server with the Oracle client.

Has anyone else received this error? What did you do to fix it?

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 thinks your connection is dead and closes it underneath you. You will only find out when you try to execute a query and get the ORA-12571 error.

This is caused by having connections open for a long time with no activity.

The solution is to add the SQLNET.EXPIRE_TIME to the sqlnet.ora file on the server and set it to some interval (10). This will cause the connections to be pinged every 10 minutes to ensure they are still alive.

The result of this is that your firewall will see network activity and not close the connection.

SQLNET.EXPIRE_TIME=10

ORA-12571: TNS:packet writer failure - One of the hardest problems I've had to resolve