RSS

Slow listener response & slow TNSPING response

24 Sep

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.

Advertisements
 
24 Comments

Posted by on September 24, 2013 in DB Issues

 

Tags: , ,

24 responses to “Slow listener response & slow TNSPING response

  1. Ericson

    March 4, 2014 at 1:19 pm

    Very helpful. I’ve been suffering for 1 week until I found this. Thanks

     
    • VJOracleDBA

      March 18, 2014 at 5:37 pm

      Hello Ericson,

      I am glad this helped.

      Thanks
      -Vijayendra.

       
  2. John McCullough

    April 1, 2014 at 9:36 pm

    Wow! Thanks for posting this. Helped us get out of a huge jam.

     
    • VJOracleDBA

      April 1, 2014 at 11:00 pm

      Hello John,

      I am glad this helped.

      -Vijayendra.

       
  3. suresh

    April 28, 2014 at 3:28 pm

    Hi vijayendra,

    Thanks a lot. It helped me.
    Thank god, I found this blog immediately without stuck in other readings.

     
    • VJOracleDBA

      May 23, 2014 at 3:31 am

      I am glad Suresh this helped 🙂

       
  4. bokongmu

    May 26, 2014 at 2:03 pm

    thanks a lot man.. this blog really helpful. I love you from deep inside my hearth

     
    • VJOracleDBA

      May 26, 2014 at 6:30 pm

      Im glad man! Even I struggled for long before I found a solution.

      All the best!

       
  5. Harmandeep Singh

    September 9, 2014 at 11:44 am

    Great.. right on point. it helped

     
    • VJOracleDBA

      September 9, 2014 at 4:29 pm

      Im glad and you are welcome!

       
  6. ETamas

    September 23, 2014 at 5:16 pm

    Thank you for the hint, it helped a lot.

     
  7. Phutb

    November 5, 2014 at 8:14 am

    Thanks so much

     
  8. Kevin James

    November 6, 2014 at 11:56 pm

    I’ve had been dealing with this issue for a while, this was the exact fix. Thanks!!

     
  9. Nassou

    May 13, 2015 at 5:36 pm

    Excellent tricks, the Oracle Network back to the normal with this

     
  10. rakesh

    July 20, 2015 at 7:32 pm

    I faced the same issue. By disabling the Listerner Log the ping speed has increased drastically .

    Thanks ,
    Rakesh Sayini

     
  11. Lekcy

    August 15, 2015 at 3:44 pm

    Thanks!!!
    It took me lots of time before reading this post

     
  12. Andreas

    September 1, 2015 at 3:06 pm

    Thank’s a lot! Problem solved.

     
  13. sebastian

    January 27, 2016 at 9:17 pm

    excellent!!!, I had the same problem, and this article resolve my problem. thank you

     
  14. Katie

    April 14, 2016 at 6:55 pm

    Thanks so much… was completely stumped on what was wrong! This sorted it straight away!!

     
  15. eshabe

    July 11, 2016 at 8:18 pm

    Thank you very much!
    I spent couple hours to day for this problem.

    I’m work in Windows env. The TNSListener Service should be stoped before rename the log file, then start it after.

    Thank you!

     
  16. Alyz

    February 3, 2017 at 3:09 pm

    Thank you very much. This guide spared us a lot of work.

     
    • VJOracleDBA

      February 3, 2017 at 5:19 pm

      Hello Alyz,

      I am glad it helped you.

      Thanks
      -Vijayendra.

       

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
vjdba

Oracle 9i, 10g & 11g certified DBA

%d bloggers like this: