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

问题:

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)

回答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

回答2:

I think this is a bug in Oracle. I came across many issues with the DBDataAdapter.Fill method where the Oracle Client would choke on a memory error. This was resolved for me by using the 11.2.0.2 client with patch 6 applied.

If you search Oracle's Support site you will see many issues like this.

Check also on "Read Protected Memory" issues with the 11g1 / 11g2 clients.

回答3:

After I installed the elmah module and could analyze the exceptions, I tried to:

1. Change the connection configuration.
2. Remove and/or update the server firewall rules.
3. Update the Oracle client on the server machine.

Any of the options above resolved the problem, but I was forgetting the obsolete provider (System.Data.OracleClient) the we were using. After I replaced it with the last version of the ODP.NET (Oracle.DataAccess), everthing started to work flawlessly.

Obs: Based on your exception description you are currently using the obsolete provider.