Thursday, November 18, 2010

Connecting to the Oracle Database Listener is extremely slow

This note really applies to any version of the Oracle Database on Linux.

Problem:

Any connection through the database listener is extremely slow. It works fine, but it is not unusual for connection to take over a minute.

I try to start the listener as follows:
oracle@soa11gdev:/u01/app/oracle/product/11.2.0/db_1> lsnrctl start
It starts up after several minutes, instead of a few seconds.

Once it's up, I try to ping the listener as follows:
oracle@soa11gdev:/u01/app/oracle/product/11.2.0/db_1> tnsping orcl
The response is over 60 seconds.

When I try to sqlplus into the database through the listener, it takes over 60 to 70 seconds:
oracle@soa11gdev:/u01/app/oracle/product/11.2.0/db_1> sqlplus system/manager@orcl
Trying to sqlplus to the database directly (i.e., bypassing the listener) is fine:
oracle@soa11gdev:/u01/app/oracle/product/11.2.0/db_1> sqlplus system/manager
The thing is, the listener works and is not misconfigured. But connecting is exceptionally slow.

Investigation:

Check your $ORACLE_HOME/network/admin/sqlnet.ora configuration file and ensure that the TNSNAMES is specified first as follows:
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
In my case, the database was installed on Oracle Enterprise Linux 5.5.

My hostname was set to the fully qualified name of "oradev.ipnweb.com" and my /etc/hosts had the following entry:
192.168.1.55    oradev.ipnweb.com    oradev
Solution:

The problem is that /etc/resolv.conf had an incorrectly configured search domain.

Original /etc/resolv.conf:
nameserver 192.168.1.2
search localdomain
Corrected /etc/resolv.conf:
nameserver 192.168.1.2
Once this was corrected, problem was solved.

Applicable Versions:
  • Oracle Database 11g (11.2.0) 32-bit and 64-bit
  • Oracle Enterprise Linux 5.5 32-bit and 64-bit

No comments: