"FatalNIconnecterror12170" error in AlertLog

Regular inspection of database alertlog information is an important means of routine database maintenance, inspection, and troubleshooting. Database System ldquo; run rdquo with illness ;,

Regular inspection of Database alert log information is an important means of routine database maintenance, inspection and troubleshooting. Database System ldquo; run rdquo with illness ;,

Regular inspection of Database alert log information is an important means of routine database maintenance, inspection and troubleshooting. Database Systems "Run with illness" and "Run with injury" are often the main killer of "minor illness and death. The so-called "preventing problems before they happen" requires the database administrator to learn about the system running situation and handle the problem as soon as possible.

This article mainly introduces the problems in log inspection during Oracle 11gR2, although it is not solved successfully. Record it and keep it for future reference.

1. Problem Description

The author uses a development environment where the database version is 11gR2 and the minor version is 11.2.0.4.

SQL> select * from v $ version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

PL/SQL Release 11.2.0.4.0-Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0-Production

NLSRTL Version 11.2.0.4.0-Production

Some error messages are found during the alert log inspection of the database.

Tue May 19 23:04:55 2015

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

Fatal NI connect error 12170.

Version information:

TNS for Linux: Version 11.2.0.4.0-Production

Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0-Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0-Production

Time: 19-MAY-2015 23:04:55

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 = 172. xx) (PORT = 50741 ))

Errors of the same type occur repeatedly in logs. About 10 errors occur every day. The difference is that the IP addresses of each Host are different.

2. Problem Analysis

This type of error occurs frequently in 11gR2. This problem often occurs in some of my previous production systems. Currently, the system architecture for development is traditional and is a typical CS architecture. The client desktop application is a rich client software, and all business logic is on the client. The client directly connects to the database.

This architecture method is a traditional one, and the defects of this method have been discussed in the industry for many years. From the database perspective, this architecture means more database connections and more frequent access structures.

By using the IP address, we can locate the IP address connection from the listener. log in the listener log.

[Oracle @ localhost trace] $ pwd

/U01/app/diag/tnslsnr/localhost/listener/trace

[Oracle @ localhost trace] $ cat listener. log | grep 172. xx

19-MAY-2015 13:51:10 * (CONNECT_DATA = (CID = (PROGRAM = JDBC Thin Client) (HOST =__ jdbc _) (USER = visvim) (SERVICE_NAME = sicsdb )) * (ADDRESS = (PROTOCOL = tcp) (HOST = 172. xx. xx. xx) (PORT = 50741) * establish * sicsdb * 0

According to the information feedback on MOS, this type of error prompt is a normal Oracle operating mechanism. After the Client Process is connected to the Server Process, the two processes form a "same Life and Death" relationship (VPC connection mode ). Unless the client initiates an interruption or the Server Process is killed abnormally.

In the actual running environment, this ideal state is often broken. If the Client Process only keeps the connection and does not execute the Statement, the session is in the idle state. Such connections are easily cut off by network devices such as firewalls.

In Oracle11gR2, if the Server Process that has not been connected for a long time is cut off by external force, Oracle will automatically write the information as a prompt error to the alert log as a prompt. In 11R1, this information is written to sqlnet. log.

3. Solution

To sum up the methods in MOS and the network, there are generally two policies: Using DCD and disabling ADR.

The full name of DCD is Dead Connection Detection. It is a policy that checks the Client Process of the Oracle zombie Client based on the active probing method. The key to configuring DCD is to set the sqlnet. expire_time parameter. In the SQL Net system, Oracle will send network communication packets to all Client processes based on the time interval to determine whether the Client is alive.

It is through this packet communication that the firewall can think that the network connection is still in the active State and will not be forcibly disconnected. Similar mechanisms include the tcp keep live mechanism on Linux, and similar policies are used for inspection.

[Oracle @ localhost trace] $ cd/u01/app/oracle/network/admin/

[Oracle @ localhost admin] $ ls-l

Total 16

-Rw-r --. 1 oracle oinstall 343 Sep 2 2014 listener. ora

Drwxr-xr-x. 2 oracle oinstall 4096 Jun 16 2014 samples

-Rw-r --. 1 oracle oinstall 381 Dec 17 2012 shrept. lst

-Rw-r --. 1 oracle oinstall 0 Sep 2 2014 sqlnet. ora

-Rw-r -----. 1 oracle oinstall 308 Sep 5 2014 tnsnames. ora

[Oracle @ localhost admin] $ cat sqlnet. ora

[Oracle @ localhost admin] $ cat sqlnet. ora

Sqlnet. expire_time = 10

Another method recommended by Oracle is to disable the 11g ADR mechanism. ADR (Automatic Diagnostic Repository) is a tool component for Oracle Automatic diagnosis and Automatic reminder. Oracle considers that if you do not need to apply ADR to the SQL Net component, you can disable the configuration in sqlnet. ora.

[Oracle @ localhost admin] $ cat sqlnet. ora

Sqlnet. expire_time = 10

DIAG_ADR_ENABLED = OFF

DIAG_ADR_ENABLED_LISTENER = OFF

Then, reload the Listener Configuration, or restart the listener.

[Oracle @ localhost admin] $ lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.4.0-Production on 21-MAY-2015 10:13:34

Copyright (c) 1991,201 3, Oracle. All rights reserved.

Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521 )))

4. Conclusion