SCAN LISTENER TRACING

SCAN LISTENER TRACING

Posted by Srikrishna Murthy Annam on May 12, 2011

This article describes enabling and disabling the SCAN listener tracing. Before capturing the scan listener tracing,please make sure your scan listener is configured as per the following Oracle Support IDs.
1070607.1 
975457.1

Some where you should find some clue for your SCAN connectivity issues. If you are still unable to find the exact problem with the scan connectivity, you need to enable the tracing and analyze the trace files or upload the trace files to oracle support.

To enable level 16 tracing for SCAN listeners, add the following parameters to listener.ora and  try to reproduce the connectivity issue.

1 2 3 4

TRACE_LEVEL_ = 16 TRACE_DIRECTORY_ =<dir location> TRACE_TIMESTAMP_ = TRUE DIAG_ADR_ENABLED_=off

Then try to reload all the listeners after these parameters are in place using lsnrctl reload .

Reproduce the issue and verify that the trace and log files are created in the directory mentioned in the parameter TRACE_DIRECTORY_.

To disable scan listener tracing:

1 2 3 4

$lsnrctl LSNRCTL>set current_listener LSNRCTL>set trc_level OFF LSNRCTL>save_config

In the above tracing we disabled diagnostic repository with the parameter DIAG_ADR_ENABLED_ and collected trace files. Now if you want to re-enable DIAG_ADR, just comment out DIAG_ADR_ENABLED_ in listener.ora and reload all the listeners.

NOTE: ADR is the new concept introduced from 11g and it is a file system repository to store all diagnostic data. Please review the article  Automatic Diagnostic Repository  to know more on ADR concepts.

Session Log to show enabling SCAN Tracing:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59

[grid@b600e6tr01 admin]$ cp listener.ora listener.ora_bak [grid@b600e6tr01 admin]$ vi listener.ora   ==> Here add trace parameter [grid@b600e6tr01 admin]$ clear [grid@b600e6tr01 admin]$ ps -ef | grep -i tns grid      5943  4623  0 07:12 pts/1    00:00:00 grep -i tns grid     18139     1  0 May03 ?        00:01:01 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER -inherit grid     18142     1  0 May03 ?        00:01:14 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER_SCAN2 -inherit grid     18164     1  0 May03 ?        00:01:18 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER_SCAN3 -inherit [grid@b600e6tr01 admin]$ lsnrctl reload listener   LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-MAY-2011 07:12:38   Copyright (c) 1991, 2009, Oracle.  All rights reserved.   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) The command completed successfully   [grid@b600e6tr01 admin]$ lsnrctl reload LISTENER_SCAN2   LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-MAY-2011 07:12:55   Copyright (c) 1991, 2009, Oracle.  All rights reserved.   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))) The command completed successfully [grid@b600e6tr01 admin]$ lsnrctl reload LISTENER_SCAN3   LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-MAY-2011 07:13:02   Copyright (c) 1991, 2009, Oracle.  All rights reserved.   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))) The command completed successfully [grid@b600e6tr01 admin]$   [grid@b600e6tr02 admin]$ cp listener.ora listener.ora_bak [grid@b600e6tr02 admin]$ vi listener.ora  ==> Here add trace parameters [grid@b600e6tr02 ~]$ ps -ef | grep -i tns grid      1042     1  0 May03 ?        00:01:20 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER_SCAN1 -inherit grid      1171     1  0 May03 ?        00:01:04 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER -inherit grid      8231  7935  0 07:06 pts/1    00:00:00 grep -i tns [grid@b600e6tr02 ~] [grid@b600e6tr02 admin]$ lsnrctl reload LISTENER   LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-MAY-2011 07:13:26   Copyright (c) 1991, 2009, Oracle.  All rights reserved.   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) The command completed successfully [grid@b600e6tr02 admin]$ lsnrctl reload LISTENER_SCAN1   LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-MAY-2011 07:13:35   Copyright (c) 1991, 2009, Oracle.  All rights reserved.   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))) The command completed successfully [grid@b600e6tr02 admin]$

Session Log to show disabling SCAN Tracing:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88

[grid@b600e6tr01 ~]$ ps -ef | grep -i tns grid      5309  4623  0 07:06 pts/1    00:00:00 grep -i tns grid     18139     1  0 May03 ?        00:01:01 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER -inherit grid     18142     1  0 May03 ?        00:01:14 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER_SCAN2 -inherit grid     18164     1  0 May03 ?        00:01:18 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER_SCAN3 -inherit [grid@b600e6tr01 ~]$ lsnrctl   LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-MAY-2011 07:07:02   Copyright (c) 1991, 2009, Oracle.  All rights reserved.   Welcome to LSNRCTL, type "help" for information.   LSNRCTL> set current_listener LISTENER Current Listener is LISTENER LSNRCTL> set trc_level OFF Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) LISTENER parameter "trc_level" set to off The command completed successfully LSNRCTL> save_config Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) Saved LISTENER configuration parameters. Listener Parameter File   /u01/home/11.2.0/grid/network/admin/listener.ora Old Parameter File   /u01/home/11.2.0/grid/network/admin/listener.bak The command completed successfully LSNRCTL> set current_listener LISTENER_SCAN2 Current Listener is LISTENER_SCAN2 LSNRCTL> set trc_level OFF Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))) LISTENER_SCAN2 parameter "trc_level" set to off The command completed successfully LSNRCTL> save_config Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))) Saved LISTENER_SCAN2 configuration parameters. Listener Parameter File   /u01/home/11.2.0/grid/network/admin/listener.ora Old Parameter File   /u01/home/11.2.0/grid/network/admin/listener.bak The command completed successfully LSNRCTL> set current_listener LISTENER_SCAN3 Current Listener is LISTENER_SCAN3 LSNRCTL> set trc_level OFF Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))) LISTENER_SCAN3 parameter "trc_level" set to off The command completed successfully LSNRCTL> save_config Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))) Saved LISTENER_SCAN3 configuration parameters. Listener Parameter File   /u01/home/11.2.0/grid/network/admin/listener.ora Old Parameter File   /u01/home/11.2.0/grid/network/admin/listener.bak The command completed successfully LSNRCTL>   [grid@b600e6tr02 ~]$ ps -ef | grep -i tns grid      1042     1  0 May03 ?        00:01:20 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER_SCAN1 -inherit grid      1171     1  0 May03 ?        00:01:04 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER -inherit grid      8231  7935  0 07:06 pts/1    00:00:00 grep -i tns [grid@b600e6tr02 ~]$ lsnrctl   LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-MAY-2011 07:10:37   Copyright (c) 1991, 2009, Oracle.  All rights reserved.   Welcome to LSNRCTL, type "help" for information.   LSNRCTL> set current_listener LISTENER Current Listener is LISTENER LSNRCTL> set trc_level OFF Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) LISTENER parameter "trc_level" set to off The command completed successfully LSNRCTL> save_config Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) Saved LISTENER configuration parameters. Listener Parameter File   /u01/home/11.2.0/grid/network/admin/listener.ora Old Parameter File   /u01/home/11.2.0/grid/network/admin/listener.bak The command completed successfully LSNRCTL> set current_listener LISTENER_SCAN1 Current Listener is LISTENER_SCAN1 LSNRCTL> set trc_level OFF Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))) LISTENER_SCAN1 parameter "trc_level" set to off The command completed successfully LSNRCTL> save_config Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))) Saved LISTENER_SCAN1 configuration parameters. Listener Parameter File   /u01/home/11.2.0/grid/network/admin/listener.ora Old Parameter File   /u01/home/11.2.0/grid/network/admin/listener.bak The command completed successfully LSNRCTL>

TNS-12535: TNS:operation timed out with firewall

Nice doc : https://jvortega.wordpress.com/2012/11/24/solving-elusive-problems-oracle-timeout/
httpv://www.youtube.com/watch?v=dS9uUXXTTko

Error :
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: 18-JAN-2015 11:40:51
  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=192.168.1.99)(PORT=54125))

solution :

http://hemora.blogspot.com/2013/05/how-to-resolve-tns-12535-or-tns-00505.htmlrce

How To Resolve TNS-12535 or TNS-00505 Operation Timed Out Errors

While examining logs I have found lots of “TNS Operation Timeout Errors” in sqlnet.log file

Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 10.2.0.4.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.4.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.4.0 - Production
  Time: 06-MAY-2009 18:54:41
  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=*.*.*.*)(PORT=3448))


The same error message was repeating during whole day almost for every application server.

I’ve finally found what was causing the problem. Our database is behind a firewall. Firewall has an “idle session timeout” value. If a connection remains idle for more than the “idle session timeout” value it drops the connections. 

Application developers usually configure their connection pools to remain alive for a long time, which is more than the “idle session timeout” value of the firewall. Not all the connections in the pool are used. Some stays idle. After sometime the firewall drops them and I get those operation timed out problems.

Solution:

Add the following line to the sqlnet.ora file on the server.
SQLNET.EXPIRE_TIME=10


In this configuration database will probe the application servers every 10 minutes to see if they are up. In fact this is a mechanism to determine stale connections by database. Because it sends packets to application servers every ten minutes, the connections are detected as active by firewalls and they are not broken.

Oracle SQLNET Parameter

source : http://docs.oracle.com/cd/B28359_01/network.111/b28317/sqlnet.htm#NETRF210

SQLNET.INBOUND_CONNECT_TIMEOUT

Purpose

Use the SQLNET.INBOUND_CONNECT_TIMEOUT parameter to specify the time, in seconds, for a client to connect with the database server and provide the necessary authentication information.

If the client fails to establish a connection and complete authentication in the time specified, then the database server terminates the connection. In addition, the database server logs the IP address of the client and an ORA-12170: TNS:Connect timeout occurred error message to thesqlnet.log file. The client receives either an ORA-12547: TNS:lost contact or an ORA-12637: Packet receive failed error message.

The default value of this parameter is appropriate for typical usage scenarios. However, if you need to explicitly set a different value, Oracle recommends setting this parameter in combination with the INBOUND_CONNECT_TIMEOUT_listener_name parameter in the listener.ora file. When specifying the values for these parameters, note the following recommendations:

• Set both parameters to an initial low value.
• Set the value of the INBOUND_CONNECT_TIMEOUT_listener_name parameter to a lower value than the SQLNET.INBOUND_CONNECT_TIMEOUTparameter.

For example, you can set INBOUND_CONNECT_TIMEOUT_listener_name to 2 seconds and SQLNET.INBOUND_CONNECT_TIMEOUT parameter to 3 seconds. If clients are unable to complete connections within the specified time due to system or network delays that are normal for the particular environment, then increment the time as needed.

SQLNET.RECV_TIMEOUT

Purpose

Use the parameter SQLNET.RECV_TIMEOUT to specify the time, in seconds, for a database server to wait for client data after connection establishment. A client must send some data within the time interval.

For environments in which clients shut down on occasion or abnormally, setting this parameter is recommended. If a client does not send any data in time specified, then the database server logs an ORA-12535: TNS:operation timed out and ORA-12609: TNS: Receive timeout occurred to thesqlnet.log file. Without this parameter, the database server may continue to wait for data from clients that may be down or are experiencing difficulties.

You can also set this parameter on the client-side to specify the time, in seconds, for a client to wait for response data from the database server after connection establishment. Without this parameter, the client may wait for a long period of time for a response from a database server saturated with requests.

Set the value for this parameter to an initial low value and adjust according to system and network capacity. If necessary, use this parameter in conjunction with the SQLNET.SEND_TIMEOUT parameter.

See Also:

Oracle Database Net Services Administrator's Guide for information about configuring these parameters

Default

None

Example

SQLNET.RECV_TIMEOUT=3

SQLNET.SEND_TIMEOUT

Purpose

Use to specify the time, in seconds, for a database server to complete a send operation to clients after connection establishment.

For environments in which clients shut down on occasion or abnormally, setting this parameter is recommended. If the database server is unable to complete a send operation in the time specified, then it logs an ORA-12535: TNS:operation timed out andORA-12608: TNS: Send timeout occurred to the sqlnet.log file. Without this parameter, the database server may continue to send responses to clients that are unable to receive data due to a downed computer or a busy state.

You can also set this parameter on the client-side to specify the time, in seconds, for a client to complete send operations to the database server after connection establishment. Without this parameter, the client may continue to send requests to a database server already saturated with requests.

Set the value for this parameter to an initial low value and adjust according to system and network capacity. If necessary, use this parameter in conjunction with the SQLNET.RECV_TIMEOUT parameter.