Oracle database error: fatal Ni connect error 12170

Fatal NI connect error 12170

VERSION INFORMATION:
TNS for Linux: VERSION 11.2.0.1.0 – Production
Oracle Bequeath NT Protocol Adapter for Linux: VERSION 11.2.0.1.0 – Production
TCP/IP NT Protocol Adapter for Linux: VERSION 11.2.0.1.0 – Production
TCP/IP NT Adapter for Linux:
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=xxx.xxx.xxx.xxx)(PORT=33358))
The reason:
According to the online search, the session is in idle state because the client has not executed a statement for a period of time after connecting to Oracle, which is easy to be cut off by the network layer device. In Oracle 11GR2, due to the opening of ADR (Automatic Diagnostic Repository), this error is written into the alert log.
Solutions:
1. Close the ADR

在sqlnet.ora Added parameters.
DIAG_ADR_ENABLED=OFF

在listener.oraAdd an argument to the following (where <listenername> is replaced with your own listener name).
 DIAG_ADR_ENABLED_<listenername>=OFF

2. Oracle actively sends the probe package to the client

在sqlnet.ora Added parameters.
sqlnet.expire_time=10  ## Probe packets are sent to clients every 10 minutes, with a slight impact on network performance

3. Restart listening after adding parameters

lsnrctl stop
lsnrctl start
or
lsnrctl reload

 

Read More: