connecting to ORACLE

[Kapil_2]

Hi everyone

When I try to connect to the ORACLE 7.3 database through SQLPLUS or through a PRO*C program it takes a long time, approx 15-20 seconds.
The OS is HPUX 11.0.
What could be the possible reasons?

Kapil

[harry d brown jr]

Take a look at this thread, and within it Paula has another thread listed. As stated in the thread it could be the way your system resolves network names. I recently had a similar problem, and it was the way the system was resolving names (I switched it to files first, ie /etc/hosts).

http://forums.itrc.hp.com/cm/QuestionAnswer/1,1150,0x40865220af9bd5118ff10090279cd0f9,00.html

[A. Clay Stephenson]

Hi,

This is almost certainly due to hostname resolution problems. The server must be able to resolve the client and vice versa. I would try to ping the server from the client by hostname and then repeat the process from the server to the client. I'll bet that there is a DNS lookup failure on one end or the other and it is then failing over to the hosts file or NIS.

Clay

[Sridhar Bhaskarla]

I second that. What is the time it is taking for your server and client to resolve a hostname?. If it is immediate, try to resolve the server name to the IP Address on the client and the client name to IP on the server.

-Sri

[Wodisch]

Hello Kapil,

this could be name resolution problem on one or even
both boxes: client and/or server.
So, check the "tnsnames.ora" on the client, and the
"listener.ora" on the server, and "sqlnet.ora" on both
of them. If possible, you could change all hostnames
in there to plain ip-addresses, getting rid of name
resolution...
Be careful: if the files mentioned do exist in "/etc" and
in "$ORACLE_HOME/network/admin/" then only those
in "/etc" will be used!

HTH,
Wodisch

[Volker Borowski]

Hello,

did you try to set up

..../network/admin/protocol.ora

with

tcp.nodelay=true

????

Volker

[Kapil_2]

Hi harry, A.Clay, Sridhar
My client & server are running on the same UNIX m/c.
ie the PRO*C program which is trying to connect to ORACLE is running on the same UNIX m/c where ORACLE is running.
When I try to do a telnet connection from my local machine, there is no problem.
Also on the UNIX server /etc/hosts is getting used to find out the IP address from the hostname and it happens fast.
Any other checks I need to perform?

Kapil

[Kapil_2]

Hi Volker

There is no such file as "protocol.ora" at the moment in $ORACLE_HOME/network/admin.
How exactly do we set it up?
What is its purpose?

Kapil

[Kapil_2]

Hi Wodisch,
I changed all hostnames to IP addresses but the situation is still the same.
What other checks do you recommend?

Kapil

[Kapil_2]

Hi harry, A.Clay, Sridhar
I think my wordings were technically incorrect.
Basically I am not using the Oracle client package to connect to the Oracle server.
I am already logged on to UNIX M/C through telnet and then I try to connect to the database through sqlplus or a PRO*C program.
What other checks do you recommend?

Kapil

[Wodisch]

Hello Kapil,

well, first, how long does the name resolution of the
client's hostname into an ip-addr (and vice versa) last
on the server?
You could run a loop to this for about a hundred times
to get some average value... Is this slow? Then check
"/etc/nsswitch.conf" and "/etc/reolv.conf" on that server.

Second, could you do that kind of benchmarking from
the client to the server using "tnsping"? Again for a
couple of times? How fast is "tnsping" on reaching the
server?

What is the ouput of that "tnsping" showing: which
config files does it use, those in "/etc/" or those in
"$ORACLE_HOME/network/admin/"? Are they the ones
you changed?

Third, have a look into your "listeners'" log files (in
"$ORACLE_HOME/network/log/") if there are any
errors in there.
Check the output of your "lsnrctl start" and of your
"lsrnctl status", does this refer to the "/etc/" or the
other directory?

As far as I know, you do not get any patches for Oracle7
from Oracle corp. any longer, but do you have a current
state of patches? Check against Oracle's patch server
and Oracle Technet, perhaps.

Did you relink the Oracle codefiles after patching HP-UX?
If not, do it (well, after a backup, of course ;-).

HTH,
Wodisch

[makoto uchino]

Hello Kapil,

PROTOCOL.ORA is protocol specific configuration file.

The following protocols require you to configure additional parameters, syntax is
LOCAL_LOOKUP=alias (The LOCAL_LOOKUP parameter points to a non-global address in a PROTOCOL.ORA file) in TNSNAMES.ORA or LISTENER.ORA

APPC/LU6.2
ASYNC
X.25
OSI4

To configure persistent buffer flushing, add the following parameter in your PROTOCOL.ORA file:

tcp.nodelay = yes

Please refer to Net8 Administrator's Guide.