Shulou(Shulou.com)06/01 Report--

Hardware description:

Operating system version: ORACLE LINUX 6.364 bit

Database version: 11.2.0.3 64-bit

Description of the problem:

When checking the alert log of the database, a large number of 12170 and TNS-12535 errors were found.

Fatal NI connect error 12170.

VERSION INFORMATION:

TNS for Linux: Version 11.2.0.3.0-Production

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

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

Time: 06-APR-2014 10:46:14

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=110.80.1.83) (PORT=50226))

Sun Apr 06 10:46:24 2014

Problem solving:

Look on the metalink platform and find that this symptom is exactly the same as that described in the document ID (1628949.1), organized according to the contents of the document as follows:

1. The version with the problem

Oracle Net Services-Version 11.2.0.3 to 12.1.0.1 [Release 11.2 to 12.1] Information in this document applies to any platform.

2. the symptoms of the error or the format of the error report are as follows:

Fatal NI connect error 12170.VERSION INFORMATION:TNS for 64-bit Windows: Version 11.2.0.3.0-ProductionOracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.3.0-ProductionWindows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.3.0-ProductionTime: 22-FEB-2014 12:45:09Tracing not turned on.Tns error struct:ns main err code: 12535TNS-12535: TNS:operation timed outns secondary err code: 12560nt main err code: 505TNS-00505 Operation timed outnt secondary err code: 60nt OS err code: 0***Client address: (ADDRESS= (PROTOCOL=tcp) (HOST=121.23.142.141) (PORT=45679))

The PORT field here is the ephemeral port assigned to the client for this connection. This does not correspond to the listener port.

3. The cause of the problem

The alert.log message indicates that a connection was terminated AFTER it was established to the instance. In this case, it was terminated 2 hours and 3 minutes after the listener handed the connection to the database.

This would indicate an issue with a firewall where a maximum idle time setting is in place.

The connection would not necessarily be "idle". This issue can arise during a long running query or when using JDBC Thin connection pooling. If there is no data'on the wire' for lengthy

Periods of time for any reason, the firewall might terminate the connection.

4. Solution:

The following parameter, set at the * * RDBMS_HOME/network/admin/sqlnet.ora, can resolve this kind of problem. DCD or SQLNET.EXPIRE_TIME can mimic data transmission between the server and the client during long periods of idle time. SQLNET.EXPIRE_TIME=n Where is a non-zero value set in minutes.

Go to the ORACLE_HOME/network/admin directory, add the sqlnet.ora file, and add a line of SQLNET.EXPIRE_TIME=10

5. Supplementary explanation SQLNET.EXPIRE_TIME

Purpose

To specify a time interval, in minutes, to send a check to verify that client/server connections are active. The following usage notes apply to this parameter:

Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination.If the probe finds a terminated connection, or a connection that is no longer in use, then it returns an error, causing the server process to exit.This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.Limitations on using this terminated connection detection feature are:It is not allowed on bequeathed connections.Though very small, a probe packet generates additional traffic that may downgrade network performance.Depending on which operating system is in use The server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performanc

6. After the above operations, restart the monitoring of the database

Detailed metalink link: https://support.oracle.com/epmos/faces/DocumentDisplay?id=1628949.1

-

ORACLE database operation and maintenance group 367875324

-

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.