Oracle SQLPlus connection take 2-3 minutes

[Paul Skerry]

Over the weekend, our HP9000-based Oracle system has developed problem with SQLPlus connections. It now takes 2-3 minutes to connect to the database whereas before it would take maybe 1-2 seconds.

We are using Oracle version 8.1.7 - we've tried closing and re-opening the database but the same problem. Database performance, once connected, is about the same. Anyone have an idea what the problem might be?

Thanks for reading,
- Paul

[Shibin_2]

>> we've tried closing and re-opening the database but the same problem

How did you do that? From remote machine or from the system itself ?

[Paul Skerry]

I login via telnet to the UNIX server to close the database.

One other thing I found this evening: If I use sqlplus from the DOS prompt on my Windows PC to connect to the Oracle database, it connects almost immediately. This is bizarre!

[Wim Rombauts]

This looks like a TCP timeout problem.
Are all database servers that are in the connect definitions of your tnsnames.ora available ? Is there maybe a type in one of the servernames ?

If you lookup the database servername from the system where you run sqlplus, how long does it take to resolve ? IS the DNS server correctly reachable ? Any recent change on /etc/nsswitch.conf ?

[Paul Skerry]

Server is defined in tnsnames.ora - the server I'm connecting to is called "advp". Here is the tnsnames.ora file:

---
INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.240.128.12)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = advt)
(PRESENTATION = http://admin)
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = advc)
(PRESENTATION = http://admin)
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = advp)
(PRESENTATION = http://admin)
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = advtst)
(PRESENTATION = http://admin)
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = rcat)
(PRESENTATION = http://admin)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

advt =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.240.128.12)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = advt)
(ENVS = 'memwindw=TRUE')
)
)
advc =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.240.128.12)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = advc)
)
)
advp =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.240.128.12)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = advp)
(ENVS = 'memwindw=TRUE')
)
)

advtst =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.240.128.12)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = advtst)
(ENVS = 'memwindw=TRUE')
)
)
rcat =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.240.128.12)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = rcat)
)
)
fst =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 202.1.2.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = fst)
)
)
---

If I try a tnsping from the server it takes a couple of minutes - just like sqlplus - then responds: OK (50ms).

There have been no changes to /etc/nsswitch.conf since 2006. Here is what the file contains:

---
#
# /etc/nsswitch.files:
#
# @(#)B.11.11_LR
#
# An example file that could be copied over to /etc/nsswitch.conf; it
# does not use any name services.
#
passwd: files
group: files
hosts: files [NOTFOUND=continue UNAVAIL=continue TRYAGAIN=continue] dns
services: files
networks: files
protocols: files
rpc: files
publickey: files
netgroup: files
automount: files
aliases: files
---

[Paul Skerry]

One other thing: If I try to tnsping a non-existent server, it still pauses for a couple of minutes before finally returning the message "TNS-03505: Failed to resolve name"

It's crazy that any machine can TNSping and open a SQLPlus session on this database with no problems, but the server cannot connect to its own database without this delay.

[Matti_Kurkela]

Check your /etc/hosts.

If the server's own IP/hostname is configured wrong in /etc/hosts, it might cause problems in accepting local connections.

If the localhost line is missing from /etc/hosts, it can also cause strange problems.

The standard localhost line for HP-UX (at least 11.23 and older):
127.0.0.1 localhost loopback

MK

[Paul Skerry]

Thanks for writing MK,

Checked /etc/hosts and it does contain the following:

127.0.0.1 localhost loopback

The server is known as proda01. nslookup resolves "proda01" correctly as 10.240.128.12, and with no delay.

[Jean-Luc Oudart]

Hi Paul

not sure I got it right but if you run sqlplus from the server itself there is the delay (2-3mns) but if you run sqlplus from your own PC thereis no delay. Please confirm
I understand you run :
sqlplus user/pwd@advp

what if you run
export ORACLE_SID=advp
sqlplus user/pwd

You said the pb started after the w.e.
Have you checked for any change being made on the server (configuration ,...) ?

is this affecting only sqlplus (or any oracle client application) from the server itself ?

Regards
Jean-Luc

[Paul Skerry]

Hi Jean-Luc,

> not sure I got it right but if you run
> sqlplus from the server itself there is the
> delay (2-3mns) but if you run sqlplus from
> your own PC thereis no delay. Please confirm

That is correct.

> I understand you run :
> sqlplus user/pwd@advp
> what if you run
> export ORACLE_SID=advp
> sqlplus user/pwd

ORACLE_SID is set to "advp" by the UNIX user login script. I just tried the following:

$ORACLE_HOME/bin/sqlplus user/pass

And the SQL prompt came up right away, which was surprising! Appending @advp on to the end of that still results in the delay.

> You said the pb started after the w.e.
> Have you checked for any change being made on
> the server (configuration ,...) ?

We're checking into that. Towards the end of the week some websites were blocked on our internet connection, but other than that nothing else has changed.

> is this affecting only sqlplus (or any
> oracle client application) from the server itself ?

It's affecting Data Protector, which we use for backups, and our business system (which uses Oracle for data storage, so not surprising).

Regards,
- Paul

[Jean-Luc Oudart · Accepted solution]

Ok

when you use ORACLE_SID (and not the @connectionstring) you bypass sqlnet. Also, if DP is affected (is this to the same extent ie 2-3 mns more for each session ?) sounds like a network issue.
Anything in the log ? have you trace enabled so you can analyze with netfmt ?

if you try ftp on the localhost what's happening ?

regards
Jean-Luc

[Paul Skerry]

Hi Jean-Luc,

On a hunch, I commented out the name servers in /etc/resolv.conf and tried SQLPlus again. It came up immediately!

It seems that the network engineers probably did a little too much blocking at the weekend! I've turned it over to them.

Thank you so much for your help. You got me on the right track when bypassing SQL*Net made the problem go away.

Best regards,
- Paul

[Paul Skerry]

Problem attributed to changes to DNS settings causing disruption to Oracle connectivity.