Fatal NI connect error 12514, connecting to:

We have a script to monitor the alert.log file for any ORA-xxxx errors.

With 11g we found out many entries in regards of:

Fatal NI connect error

The problem:

alert.log file has many entries for Fatal NI connect error.

Examples:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

***********************************************************************   Fatal NI connect error 12514, connecting to:  (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=nnn))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=sss)(CID=(PROGRAM=oracle)(HOST=xxx)(USER=oracle))))    VERSION INFORMATION:  TNS for Linux: Version 11.2.0.2.0 - Production  TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production  Time: 17-JAN-2011 10:20:45  Tracing not turned on.  Tns error struct:  ns main err code: 12564   TNS-12564: TNS:connection refused  ns secondary err code: 0  nt main err code: 0  nt secondary err code: 0  nt OS err code: 0 Error 12514 received logging on to the standby Mon Jan 17 10:21:45 2011     ***********************************************************************

or

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

***********************************************************************   Fatal NI connect error 12170.    VERSION INFORMATION:  TNS for Linux: Version 11.2.0.2.0 - Production  Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production  TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production  Time: 18-JAN-2011 15:13:08  Tracing not turned on.  Tns error struct:  ns main err code: 12535   TNS-12535: TNS:operation timed out  ns secondary err code: 12560  nt main err code: 505   TNS-00505: Operation timed out  nt secondary err code: 110  nt OS err code: 0  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=<span class="skimlinks-unlinked">xx.xx.x.xx)(PORT=nnnn</span>)) Tue Jan 18 15:40:20 2011

The solution:

We will have an entry on the alert.log file every time a client connection of any kind fails to complete or is aborted.

One way to minimize the impact is by using the parameter SQLNET.INBOUND_CONNECT_TIMEOUT (default to 60 seconds on 10gR2 and 11g) but, sometimes, this value is not adequate.

Oracle also mentions the occurrence of this error if you use DB Console or Enterprise Manager to monitor your databases and the emagent will try to connect to the target database repeatedly and, statistically, some will fail (frequency will depend on how busy your system is).

Most of the time (certainly for DB Console and Enterprise Manager Agent) the application will try to connect again and it will succeed.

To fix the problem you could increase the value of SQLNET.INBOUND_CONNECT_TIMEOUT on the listener.ora and the sqlnet.orafile located on the server side.

If you already have a value you have considered adequate, you might want to add the following line on your listener.ora file:

DIAG_ADR_ENABLED_listener_name=OFF

This line will hide the error on the alert.log file and the error will be posted on the $ORACLE_HOME/network/log/sqlnet.log file