Slow tnsping Response and Slow Connections to the Database

We can use TNSPING utility to check connection with the database, for the client side. If you observe that TNSPING is returning very late/slow, and application connection is also very slow, there are a couple of things that you can check. First thing is to use network speed. You can use OS command “ping” to check if network is stable and response is fast enough. You can also seek help of your network team to verify if network is working as expected, and no latency or packet loss is being observed.

Another reason of slow connection to the database could be size of listener log file, and this was exactly what I experienced recently. I observed a slow TNSPING response, and slow application connection to the instance/database, and even some times tnsping and application connections returned ORA-12170. Upon investigation, I found that listener logs on my all 3 RAC nodes had become quite huge. Node 1 listener log had size more than 2.5G.

Following was the tnsping response I observed before purging/rotating listener logs.  

C:\Users\salman>tnsping prodinst1 TNS Ping Utility for 64-bit Windows: Version 11.2.0.3.0 - Production on 04-AUG-2018 22:21:26 Copyright (c) 1997, 2011, Oracle.  All rights reserved. Used parameter files: D:\oracle\11203\DB\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.4)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = proddb) (INSTANCE_NAME = prodinst1))) OK (2420 msec)

As soon as I renamed (so that a new listener log could get created automatically) existing listener log file of node 1(I tested above tnsping for instance 1 only), tnsping response and application connections to the instance 1 became normal as can be seen bellow.

C:\Users\salman>tnsping prodinst1 TNS Ping Utility for 64-bit Windows: Version 11.2.0.3.0 - Production on 04-AUG-2018 22:29:17 Copyright (c) 1997, 2011, Oracle.  All rights reserved. Used parameter files: D:\oracle\11203\DB\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.4)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = proddb) (INSTANCE_NAME = prodinst1))) OK (120 msec)

Eventually, I rotated listener logs on other 2 nodes as well.