connect not log in, DB hanging?

[a128]

I have a DB server (Oracle 8.1.7.3 on Win2k SP2) with one listener and two DBs. Both DBs uses the same listener. One of the DBs periodically does not accept connections anymore. Here are the findings:

- The alert.log and listener log show nothing no errors/warning/failures. Listener.log file shows the connection as "* establish *"

- Both DBs are in archived log mode. The archiving has been OK. I do have 3 ARCH processes doing the archiving though.

- It has plenty of spaces on all the drives.

- When I connect from both remotely and locally with SQLPlus, they both shows the banner but the SQL> prompt never comes up

- The CPU usage varies; one time it stays around 50% while the other time it's close to 0

- I've waited for at least 1/2 hours before giving up

- There are plenty of physical memory left

- The connections are in Dedicated Server mode

- Nomorally WebLogic creates a connection pool of 10 connections to connect to the troubled DB

- I have to use USE_SHARED_SOCKET because it's Win2k and behind a firewall

- It's been working OK for some time

- Can not do "connect internal" because it exhibits the same sympton as Sqlplus log in as regular user: never get a SQL prompt.

I don't think it's performance problem because no matter how slow it is, it should not take this long to process a connection, correct? Can anybody shed some light on this?

Thanks,

[abhaysk]

Originally posted by a128
I have a DB server (Oracle 8.1.7.3 on Win2k SP2) with one listener and two DBs. Both DBs uses the same listener. One of the DBs periodically does not accept connections anymore. Here are the findings:

- When I connect from both remotely and locally with SQLPlus, they both shows the banner but the SQL> prompt never comes up




Thanks,

As far as SQL prompt is conserned...try checking Glogin.sql...

- I've waited for at least 1/2 hours before giving up

Do u mean ... u were waiting for the clinet to connect to server for such a long time...i blv it shud timeout by this time..

Ne ways try restating ur listener, and check if it connects within few mins...and also check the CPU usage & Memory usage at the time when u connect....

Abhay.

[ssekhar]

Try using the following parameters in SQLNET.ora to output any errors to trace file during dB connection. This trace would give you more details to solve the problem.

TRACE_LEVEL_CLIENT = 16
NAMES.DIRECTORY_PATH = (TNSNAMES)
TRACE_DIRECTORY_CLIENT =
TRACE_FILE_CLIENT = client

Did you try to ping from Client to the dB using tns, I mean to verify the local service_name is valid or not??
----------------------------------------------------------------------
C:\>tnsping wmsd

TNS Ping Utility for 32-bit Windows: Version 9.0.1.1.1 - Production on 24-MAR-20
03 15:59:56

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:
D:\Oracle\network\admin\sqlnet.ora
D:\Oracle\network\admin\tnsnames.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = y3_uatdb1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = wmsd)))
OK (90 msec)
----------------------------------------------------------------------

Regards,