Slow listener response & slow TNSPING response

PROBLEM: Slow TNSPING  response and slow LSNRCTL response

OS: Microsoft Windows Server 2008 R2 Enterprise

System Model: VMware Virtual Platform
System Type: x64-based PC

Recently I came across a problem where in application team complained about high connection time when trying to connect to one our their QA database. I tried to check various things from my end after logging into the db server. Listener response was very slow. When I tried to check the status of the services registered with the db, it failed with error TNS-01153 (Like the one given below)

C:\Users\_meidba>lsnrctl

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 – Production on 24-SEP-2013 04:58:27

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=CHDQADB3.jdadelivers.com)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 – Production
Start Date 24-SEP-2013 04:58:10
Uptime 0 days 0 hr. 4 min. 10 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\app\oracle\product\11.2.0\db_1\network\admin\listener.ora
Listener Log File d:\app\oracle\diag\tnslsnr\CHDQADB3\listener\alert\log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=CHDQADB3.jdadelivers.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary…
Service “CLRExtProc” has 1 instance(s).
Instance “CLRExtProc”, status UNKNOWN, has 3 handler(s) for this service…
Service “VMIDEV.jdadelivers.com” has 1 instance(s).
Instance “vmidev”, status READY, has 1 handler(s) for this service…
Service “VMIDEVXDB.jdadelivers.com” has 1 instance(s).
Instance “vmidev”, status READY, has 1 handler(s) for this service…
The command completed successfully
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=CHDQADB3.jdadelivers.com)(PORT=1521)))
TNS-01153: Failed to process string: X☺≡♥

Then I tried to TNSPING the connection from my local laptop and saw high response time:

C:\Users\j1009744>tnsping SCPOQA

TNS Ping Utility for 64-bit Windows: Version 11.2.0.2.0 – Production on 24-SEP-2013 14:27:52

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:
D:\APP\oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = CHDQADB3.jdadelivers.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SCPOQA.jdadeliv
ers.com)))
OK (403200 msec)

Also, referred the metalink note “11gR2 Listener With RATE_LIMIT Set: Slow Connect Time and Tnsping Response Time High with TNS-1158 Error (Doc ID 1568591.1)” but this too was not the cause of the problem.

Spoke to my network team and they did not see any packet drop across the network for traffic to db server. Then I tried to verify the listener.log file for any errors reported and thats where the problem was.

LISTENER.LOG file size was greater then 4GB and every time we did any listener operation. OS & their underlying file system used 32 bit to represent file sizes & positions. So, no file could be larger that 2^32-1 bytes i.e 4GB. So, if file size reaches size of 4GB on 32 bit operating system, it created problem.

SOLUTION:

Renamed listener.log file to listener_old.log and then new listener.log file was created. Henceforth, response time for listener was back to normal. So fix this permanently, we scheduled a batch script to archive listener.log file every week.