TNS-12535: TNS: operation timed out and TNS-00505: Operation timed out processing, tns-12535tns-00505

TNS-12535: TNS: operation timed out and TNS-00505: Operation timed out processing, tns-12535tns-00505


Preface: View alert logs and find many error messages:
[Oracle @ localhost trace] $ more alert_powerdes.log


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


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


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


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
Time: 28-JAN-2015 15:14:16
Time: 28-JAN-2015 15:14:16
Tracing not turned on.
Tracing not turned on.
Tns error struct:
Tns error struct:
Ns main err code: 12535
Ns main err code: 12535

TNS-12535: TNS: operation timed out
TNS-12535: TNS: operation timed out
Ns secondary err code: 12560
Ns secondary err code: 12560
Nt main err code: 505
Nt main err code: 505

TNS-00505: Operation timed out.
TNS-00505: Operation timed out.
Nt secondary err code: 110
Nt secondary err code: 110
Nt OS err code: 0
Nt OS err code: 0
Client address: (ADDRESS = (PROTOCOL = tcp) (HOST = xxx. xxx.170.220) (PORT = 54418 ))
Client address: (ADDRESS = (PROTOCOL = tcp) (HOST = xxx. xxx.170.220) (PORT = 54420 ))
Wed Jan 28 15:19:00 2015
LGWR: Standby redo logfile selected to archive thread 1 sequence 29226
LGWR: Standby redo logfile selected for thread 1 sequence 29226 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 29226 (LGWR switch)
Current log #3 seq #29226 mem #0:/home/oradata/powerdes/redo03.log
Wed Jan 28 15:19:01 2015
Archived Log entry 57344 added for thread 1 sequence 29225 ID 0xca2ab4eb dest 1:
Wed Jan 28 15:34:28 2015
Wed Jan 28 15:34:28 2015


......


I. For analysis, refer to the official instructions on the warning:
Note: 465043.1

The "WARING: inbound connection timed out (ORA-3136)" in the alert log indicates that the client was not able to complete it's authentication within the period of time specified by parameter SQLNET. INBOUND_CONNECT_TIMEOUT.

You may also witness ORA-12170 without timeout error on the database sqlnet. log file. this entry wowould also have the client address which failed to get authenticated. some applications or JDBC thin driver applications may not have these details.

1. network attacks, such as semi-open connection attacks

Server gets a connection request from a malcious client which is not supposed to connect to the database, in which case the error thrown is the correct behavior. you can get the client address for which the error was thrown via sqlnet log file.

This oracle dba is in the LAN and may be excluded from network attacks.


2. The Client fails to complete authentication within 60 seconds of default.

The server has es a valid client connection request but the client tabkes a long time to authenticate more than the default 60 seconds.


Check whether the default 60 s:
[Oracle @ localhost ~] $ Lsnrctl


LSNRCTL for Linux: Version 11.2.0.1.0-Production on 28-JAN-2015 16:26:25

Copyright (c) 1991,200 9, Oracle. All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> show inbound_connect_timeout
Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost. localdomain) (PORT = 1521 )))
LISTENER parameter "inbound_connect_timeout" set to 60
The command completed successfully
LSNRCTL>

The inbound_connect_timeout value is 60 seconds, which may be caused by the failure of the Client to complete authentication within 60 seconds by default.


3. High DB Load

The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified.

WANGING: inbound connection timed out (ORA-3136)
[Oracle @ localhost admin] $ w
18:24:09 up 88 days, 6 users, load average: 0.60, 0.88, 1.21
User tty from login @ IDLE JCPU PCPU WHAT
Root pts/3 xxx.1xx. 120.238 Tue11 1: 55 m 0.29 s 0.04 s-bash
Root pts/4 xxx.1xx. 120.238 Tue11 0.00 s 0.18 s 0.00 s w
Root pts/7 xxx.1xx. 120.238 Tue14 6: 51 m 0.28 s 0.20 s rlwrap sqlplus/as sysdba
Root pts/6 xxx.1xx. 120.238 2: 34 m 0.00 s 0.00 s-bash
[Oracle @ localhost admin] $
The online db load is very low and less than 1 w, so this situation is ruled out.


2. Set inbound_connect_timeout.


1. view the value of inbound_connect_timeout.

[Oracle @ localhost ~] $ Lsnrctl


LSNRCTL for Linux: Version 11.2.0.1.0-Production on 28-JAN-2015 16:26:25


Copyright (c) 1991,200 9, Oracle. All rights reserved.


Welcome to LSNRCTL, type "help" for information.


LSNRCTL> show inbound_connect_timeout
Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost. localdomain) (PORT = 1521 )))
LISTENER parameter "inbound_connect_timeout" set to 60
The command completed successfully
LSNRCTL>


There are three methods to operate:
1) set the sqlnet. ora file: SQLNET. INBOUND_CONNECT_TIMEOUT = 0;
2) set the listener. ora file: INBOUND_CONNECT_TIMEOUT_listenername = 0;
3), reload or restart the listener.


2. Temporarily reset the value online.
LSNRCTL> show inbound_connect_timeout
Connecting to (ADDRESS = (PROTOCOL = tcp) (HOST =) (PORT = 1521 ))
LISTENER parameter "inbound_connect_timeout" set to 60
The command completed successfully
LSNRCTL>
LSNRCTL>
LSNRCTL> set inbound_connect_timeout 0
Connecting to (ADDRESS = (PROTOCOL = tcp) (HOST =) (PORT = 1521 ))
LISTENER parameter "inbound_connect_timeout" set to 0
The command completed successfully
LSNRCTL>


3. permanently set in listener. ora
[Oracle @ powerlong4 admin] $ vim listener. ora
INBOUND_CONNECT_TIMEOUT_listener = 0
[Oracle @ powerlong4 admin] $
[Oracle @ powerlong4 admin] $
[Oracle @ powerlong4 admin] $ lsnrctl stop


LSNRCTL for Linux: Version 11.2.0.1.0-Production on 28-JAN-2015 16:40:33


Copyright (c) 1991,200 9, Oracle. All rights reserved.


Connecting to (ADDRESS = (PROTOCOL = tcp) (HOST =) (PORT = 1521 ))
The command completed successfully
[Oracle @ powerlong4 admin] $ lsnrctl start


LSNRCTL for Linux: Version 11.2.0.1.0-Production on 28-JAN-2015 16:40:37


Copyright (c) 1991,200 9, Oracle. All rights reserved.


Starting/oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...


TNSLSNR for Linux: Version 11.2.0.1.0-Production
System parameter file is/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener. ora
Log messages written to/oracle/app/oracle/diag/tnslsnr/powerlong4/listener/alert/log. xml
Listening on: (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = powerlong4) (PORT = 1521 )))


Connecting to (ADDRESS = (PROTOCOL = tcp) (HOST =) (PORT = 1521 ))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0-Production
Start Date 28-JAN-2015 16:40:37
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener. ora
Listener Log File/oracle/app/oracle/diag/tnslsnr/powerlong4/listener/alert/log. xml
Listening Endpoints Summary...
(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = powerlong4) (PORT = 1521 )))
The listener supports no services
The command completed successfully
[Oracle @ powerlong4 admin] $ lsnrctl


LSNRCTL for Linux: Version 11.2.0.1.0-Production on 28-JAN-2015 16:40:41


Copyright (c) 1991,200 9, Oracle. All rights reserved.


Welcome to LSNRCTL, type "help" for information.


LSNRCTL> show inbound_connect_timeout
Connecting to (ADDRESS = (PROTOCOL = tcp) (HOST =) (PORT = 1521 ))
LISTENER parameter "inbound_connect_timeout" set to 0
The command completed successfully
LSNRCTL> exit
[Oracle @ powerlong4 admin] $ vim listener. ora
[Oracle @ powerlong4 admin] $ vim listener. ora
[Oracle @ powerlong4 admin] $
[Oracle @ powerlong4 admin] $ lsnrctl stop


LSNRCTL for Linux: Version 11.2.0.1.0-Production on 28-JAN-2015 16:41:38


Copyright (c) 1991,200 9, Oracle. All rights reserved.


Connecting to (ADDRESS = (PROTOCOL = tcp) (HOST =) (PORT = 1521 ))
^ [[AThe command completed successfully
[Oracle @ powerlong4 admin] $ lsnrctl start


LSNRCTL for Linux: Version 11.2.0.1.0-Production on 28-JAN-2015 16:41:46


Copyright (c) 1991,200 9, Oracle. All rights reserved.


Starting/oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...


TNSLSNR for Linux: Version 11.2.0.1.0-Production
System parameter file is/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener. ora
Log messages written to/oracle/app/oracle/diag/tnslsnr/powerlong4/listener/alert/log. xml
Listening on: (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = powerlong4) (PORT = 1521 )))


Connecting to (ADDRESS = (PROTOCOL = tcp) (HOST =) (PORT = 1521 ))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0-Production
Start Date 28-JAN-2015 16:41:46
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener. ora
Listener Log File/oracle/app/oracle/diag/tnslsnr/powerlong4/listener/alert/log. xml
Listening Endpoints Summary...
(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = powerlong4) (PORT = 1521 )))
The listener supports no services
The command completed successfully
[Oracle @ powerlong4 admin] $ lsnrctl


LSNRCTL for Linux: Version 11.2.0.1.0-Production on 28-JAN-2015 16:41:49


Copyright (c) 1991,200 9, Oracle. All rights reserved.


Welcome to LSNRCTL, type "help" for information.


LSNRCTL> show inbound_connect_timeout
Connecting to (ADDRESS = (PROTOCOL = tcp) (HOST =) (PORT = 1521 ))
LISTENER parameter "inbound_connect_timeout" set to 0
The command completed successfully
LSNRCTL>
LSNRCTL> exit
[Oracle @ powerlong4 admin] $

PS: Here, lsnrctl stop | start will disconnect all the client connections in the current oracle, or you can use lsnrctl reload to load

Later: no similar alarm information is displayed after it is set to 0. However, setting inbound_connect_timeout to 0 poses a risk. Because this parameter is introduced from 9i, it specifies the timeout time for the client to connect to the server and provide authentication information. If the client does not provide the correct authentication information after this time, the server will automatically stop the connection request, the IP address and ORA-12170 that you are attempting to Connect to: TNS: Connect timeout occurred errors are also logged.

This parameter is mainly used to prevent DoS attacks. Malicious attackers can enable a large number of connection requests without stopping, occupying server connection resources, and thus making the server unable to provide effective services. From 10.2.0.1, this parameter is set to 60 seconds by default. However, the introduction of this parameter also leads to some related bugs. For example:


Bug 5594769-remote session dropped when local session shared and INBOUND_CONNECT_TIMEOUT SET
Bug 5249163-connects refused by tnslsnr every 49 days for INBOUND_CONNEC_TIMEOUT SECONDS
Therefore, setting it to 0 is also a risk of being attacked. Setting it to 60 seconds is too long, so the final trade-off is that I set inbound_connect_timeout to 8 seconds.


Reference Article address: http://www.cnblogs.com/future2012lg/p/3739752.html

Bytes ----------------------------------------------------------------------------------------------------------------
<All Rights Reserved. This document can be reprinted, but the source address must be indicated by link. Otherwise, we will be held legally responsible.>
Original blog address: http://blog.itpub.net/26230597/viewspace-1418586/
Original Author: Huang Shan (mchdba)
Bytes ----------------------------------------------------------------------------------------------------------------