Ora 12571 ошибка

ORA-12571: TNS:packet writer failure

i have oracle database 11g running on windows server 2008 release-2. Today morning our developer face ORA-12571 TNS: packet writer failure error while compile large store procedure in Toad and same error get while execute same store procedure through sql*plus. developers are not get this error while compile small store procedure and create table. this error only get when store procedure is large about more than 1000 lines.

As error in TNS Packet so i guess there is a problem in Network side or in sqlnet or tnsname file.

we try below step.

1. so first try to re-create the listener. delete and create the listener.

2. shutdown and startup the database. (if you get downtime from client) do not try this step on production.

3. check network cable connection. This error has been observed when there was a loose cable connection to the SCSI disk   Related errors were also subsequently noted in the NT event log. try all cable jack in jack out and check problem still persist.

4. this error may occur if ip conflict on windows server (but in our case there is no ip conflict).

5.edit the sqlnet.ora in path D:/app/oracle/network/admin/sqlnet.ora and change below line.

SQLNET.AUTHENTICATION_SERVICES= (NTS)
into
SQLNET.AUTHENTICATION_SERVICES= (NONE).
6. Try to de install antivirus if you have installed.

7. you may solve this error by modify below parameter using sysdba privileges.

alter system set shared_server_sessions=200 scope=spfile; (if it is null than set this value)
alter system set processes=300 scope=spfile;
alter system set sessions=500 scope=spfile;
restart the database.


If you resolve your error using above one of step than you are lucky but we not get solution and we still get ora-12571 error. so we log bug to oracle support.

oracle gives below solution.

1.1. Add the following parameters in the sqlnet.ora file on the CLIENT workstation:

TRACE_LEVEL_CLIENT=16
TRACE_DIRECTORY_CLIENT=<some_known_directory>
TRACE_FILE_CLIENT=client
TRACE_UNIQUE_CLIENT=ON
TRACE_TIMESTAMP_CLIENT=ON
DIAG_ADR_ENABLED =OFF          — add this in case of 11g client

The default directory for sqlnet.ora file is $ORACLE_HOME/network/admin , if not specified differently by TNS_ADMIN environment variable.

1.4. Disable tracing by removing the TRACE lines from sqlnet.ora. 

We collect the sqlnet trace file and send it to oracle support but oracle support didnot find any error in this sqlnet log files. so oracle support recommend to check below things.

we get solution by below steps

Oracle Support ask below question to us.
>> do you have any firewall in the setup?? if yes, please try making a connection from inside the firewall to the sb server??

we reconfigure the firewall in data centre and tell to data centre to some issue in our 1521 listener port. after the data-centre network reconfiguration we solved our issue. so at last you have to check your network configuration and firewall configuration to solve ora-12571 error.

—please give your expert comments if any others.

May 8, 2020

ORA-12571: TNS:packet writer failure