jump to navigation

Removing Fatal NI connect error xxxx from your alert.log 18 January 2011

Posted by David Alejo Marcos in Oracle 11.2.
Tags:
trackback

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:

***********************************************************************

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

***********************************************************************

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=xx.xx.x.xx)(PORT=nnnn))
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.ora file 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

As always, comments are welcome.

Comments»

1. Stig Andersen - 9 November 2011

Ran into this problem today and you blog helped me. Thanks – keep blogging! 🙂

Best regards

Stig Andersen
Denmark

2. Vikas Thakur - 17 November 2011

Hi David,

Thanks a lot.

Warm Regards,
Vikas Thakur
India

3. Joan Marc Riera Duocastella - 29 November 2011

Thanks.

Marc

4. challa.v - 12 January 2012

Hi David,

thanks a lot man

warm regrads,
challa.
india

5. Nelson - 3 April 2012

Does it matter what case you use in the listener_name in DIAG_ADR_ENABLED_listener_name?

6. Nelson - 3 April 2012

Hi David,
Does it matter the case on the name of the listener in DIAG_ADR_ENABLED_listener_name=OFF?

Thanks

David Alejo Marcos - 18 May 2012

Hi Nelson,

It should not matter.

Regards,

David Marcos.

7. Meera - 18 June 2012

Hi David,
This helped me at the right time. Thanks a lot. Can you please share the alert log monitoring script for 11gr2 (linux)

Thanks,
Meera

David Alejo Marcos - 29 June 2012

Hello,

Thank you for your comments. Unfortunately I cannot provide the script but there are free monitoring tools like OEM that, in my opinion, do a great job for monitoring and alerting.

Thank you,

David.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: