DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, April 9, 2015

                           Listener Slow Response


Environment Details :
—————————————
Database Version : 11.2.0.1
Server : Linux 5 X64
Database : Single Instance Production
Application : Ebs

—————————————

Today i faced one issue in which listener was very working very slow.

Symptoms :

(1) TNSPING was very slow from client i.e. from my machine as well as from server. It was taking lot of time to give output.
(2) lsnrctl status command was also very slow ( almost in hang state ) both from server as well as from client .
(3) Users were complaining slow query performance but were able to connect quickly though SAP ( Application ).

Diagnosis :

(1) Checked TNSPING response from both client as well as from server :

Server :
—————————————————————–
bash-3.2$ tnsping bip

TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 28-MAR-2014 10:04:39

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

Used parameter files:
/oracle/BIP/112_64/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ******(PORT = ****))) (CONNECT_DATA = (SID = BIP) (GLOBAL_NAME = BIP.WORLD)))
OK (254410 msec)
——————————————————————

Client :
——————————————————————
C:\Users\rahul.adeshra>tnsping bip

TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 28-MAR-2014 10:04:39

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

Used parameter files:
/oracle/BIP/112_64/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =*******)(PORT = ****))) (CONNECT_DATA = (SID = BIP) (GLOBAL_NAME = BIP.WORLD)))
OK (661770 msec)

——————————————————————-

(2) Checked lsnrctl status from server :

——————————————————————-
bash-3.2$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 28-MAR-2014 10:09:19

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=*******)(PORT=****)))
STATUS of the LISTENER
——————————–
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 – Production
Start Date 28-MAR-2014 09:09:32
Uptime 0 days 0 hr. 59 min. 47 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/BIP/112_64/network/admin/listener.ora
Listener Log File /oracle/diag/tnslsnr/…/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=*********)(PORT=****)))
Services Summary…
Service “BIP” has 1 instance(s).
Instance “BIP”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

—————————————————————-

(3) Check sqlnet.ora for any irrelevant entry :

————————————————————
################
# Filename……: sqlnet.ora
# Created…….: created by SAP AG, R/3 Rel. >= 6.10
# Name……….:
# Date……….:
# @(#) $Id: //bas/720_REL/src/krn/tpls/ora/SQLNET.ORA#1 $
################
#AUTOMATIC_IPC = ON
#TRACE_LEVEL_CLIENT = OFF
NAMES.DEFAULT_DOMAIN = WORLD
# 05.01.06 unsorported parameter now
#NAME.DEFAULT_ZONE = WORLD
# 05.01.06 set the default to 10
#SQLNET.EXPIRE_TIME = 10
# 05.01.06 set to default
#TCP.NODELAY=YES
# 05.01.06 set to 32768
#DEFAULT_SDU_SIZE=32768
————————————————————-

(4) Checked listener.ora
—————————————————

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = BIP)
(ORACLE_HOME = /oracle/BIP/112_64)
(SID_NAME = BIP)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = **** )(PORT = ****))
)

ADR_BASE_LISTENER = /oracle

—————————————————

(5) Checked tnsnames.ora
—————————————————
BIP.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =****)(PORT = ****))
)
(CONNECT_DATA =
(SID = BIP)
(GLOBAL_NAME = BIP.WORLD)
)
)

—————————————————

(6) Checked for errors in listener :

—————————————————
Error Message :
TNS-12525: TNS:listener has not received client’s request in time allowed
TNS-12535: TNS:operation timed out
TNS-12606: TNS: Application timeout occurred

TNS-12571: TNS:packet writer failure
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
Linux Error: 104: Connection reset by peer
—————————————————

(7) Check alert.log file for any other error :

– there was no error in alert.log file .

(8) Checked for any network delay or timeout

– It was all good. i was able to ping the IP & Hostname both from server as well as client without any delay or RTO.’

(9) Checked for /etc/hosts file whether the hostname was resolved in /etc/hosts file.

– It was resolved in host file.

(10) Check for Size of listener.ora file

– If the file exceeds to GB in linux X64. The listener performance will be slow. In my case it was not so as the size was in MB’s.

Solution :

After analyzing all the above information, it was clear that the issue was not related to oracle as the listener worked fine prior to today and there was no configuration changes made recently. There was no issue related to network.
I found that from oracle11g, oracle first resolves hostname using DNS entry & reads /etc/resolv.conf file first instead of /etc/hosts.

Output of /etc/resolve.conf
————————————
; generated by /sbin/dhclient-script
search ****
nameserver ******
————————————

I found that there was a wrong DNS IP mentioned in /etc/resolve.conf file which we were not using. After commenting the same, it worked fine and the response time was dropped from couple of mins to 0sec on server.

Modified file :
————————————
; generated by /sbin/dhclient-script
#search *****
#nameserver *****
————————————

NOTE : In the above discription “****” is used instead of hostname/IP/Port no. for security reasons.

No comments: