connect not log in, DB hanging?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: connect not log in, DB hanging?

  1. #1
    Join Date
    Apr 2001
    Posts
    257

    connect not log in, DB hanging?

    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,

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: connect not log in, DB hanging?

    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.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Aug 2002
    Posts
    19
    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,
    Soma

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width