This discussion is archived
12 Replies Latest reply: Jan 11, 2013 11:37 PM by 984438 RSS

Connection hangs when connecting to XE from an IDE

TheNomad Newbie
Currently Being Moderated
Hello,

I installed XE, Weblogic and Zend and everything worked fine until a few days ago. I tried everything I found online until finally deleting and reinstalling everything all over, but to no avail.
The problem is I cannot connect to the database from an\any IDE. I can connect from sqlplus. Also, the apex link on 8080 is not working. It should not be a firewall issue since tnsping works (and I turned off the FW service). The problem is, it used to work fine. I tried JDeveloper and TOAD and same thing as the page: it hangs and doesn't connect. This problem happened "over night" but I am not sure when. Today when I wanted to create a few tables and write a program, I was amazed to see the connection attempt to end up in a freeze (and was forced to bring up the task manager)

(the setup is on my Windows XP SP 3 desktop, locally so it is not a network issue)

I use sys as sysdba on sqlplus and it works (select returns rows, for example), but I can't login from an IDE with any other user including sys.


All previous settings are identical to the ones here after the re-installation:

listener
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = F:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = F:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.4)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)
tns
XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.4)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

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

ORACLR_CONNECTION_DATA = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 
    ) 
    (CONNECT_DATA = 
      (SID = CLRExtProc) 
      (PRESENTATION = RO) 
    ) 
  ) 
status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production
Start Date                05-AUG-2012 16:42:57
Uptime                    0 days 0 hr. 5 min. 16 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   F:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Listener Log File         F:\oraclexe\app\oracle\diag\tnslsnr\eastfrontiers04\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.50.4)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=eastfrontiers04)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully
service
LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 05-AUG-2012 16:49:06

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: EASTFRONTIERS04, pid: 588>
         (ADDRESS=(PROTOCOL=tcp)(HOST=eastfrontiers04)(PORT=1636))
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
SGA:
SQL> show sga

Total System Global Area  644468736 bytes
Fixed Size                  1385488 bytes
Variable Size             192941040 bytes
Database Buffers          444596224 bytes
Redo Buffers                5545984 bytes
SQL>
Output in the listener log:

Trying to login from TOAD without auth-ing just hangs, I am forced to end the process:
Sun Aug 05 16:52:47 2012
05-AUG-2012 16:52:47 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)(CID=(PROGRAM=C:\Program?Files\Quest?Software\Toad?for?Oracle?10.6\Toad.exe)(HOST=192.168.50.4)(USER=User))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.50.4)(PORT=1653)) * establish * XE * 0
Sun Aug 05 16:54:14 2012
05-AUG-2012 16:54:14 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)(CID=(PROGRAM=C:\Program?Files\Quest?Software\Toad?for?Oracle?10.6\Toad.exe)(HOST=192.168.50.4)(USER=User))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.50.4)(PORT=1656)) * establish * XE * 0
Logging from sqlplus with auth:
Sun Aug 05 16:54:26 2012
05-AUG-2012 16:54:26 * service_update * xe * 0
Sun Aug 05 16:55:11 2012
05-AUG-2012 16:55:11 * service_update * xe * 0
Any ideas ? This is really annoying...
I am a relative beginner to the whole DBA scene, but this is too weird for me ... Apparently any "outside" application trying to connect seems to hang, but not get denied with an error message.
Curious if anyone else had this before.

Minor note: before reinstalling, I even tried a shutdown immediate\startup mount and lsnrctl stop\start, but nothing changed...
  • 1. Re: Connection hangs when connecting to XE from an IDE
    orafad Oracle ACE
    Currently Being Moderated
    Likely the trouble lies outside of Oracle i.e. os networking setup lacking for server type apps.

    Try a simple troubleshooting measure - follow below, then post copy paste of entire session:

    (open a command window i.e. cmd)

    hostname

    ipconfig

    lsnrctl stat

    lsnrctl stop

    (rename listener.ora to switch to defaults, e.g.:)

    move listener.ora listener.ora_install

    lsnrctl start

    (wait at least 1 minute here)

    lsnrctl stat
  • 2. Re: Connection hangs when connecting to XE from an IDE
    TheNomad Newbie
    Currently Being Moderated
    I find it unlikely to be a networking issue since the DB is on my local PC. The only thing from your suggestions I haven't tried is renaming listener.
    I am pretty desperate so if this works, at least I'll calm down :)
  • 3. Re: Connection hangs when connecting to XE from an IDE
    orafad Oracle ACE
    Currently Being Moderated
    TheNomad wrote:
    I find it unlikely to be a networking issue since the DB is on my local PC.
    Where you are running the server and client parts of Oracle really does not change anything. The listener requires proper underlying networking (look e.g. at the endpoints section of lsnrctl status output, or lines with connection attempts from the toad) and Apex web interface requires a functioning listener in turn.

    If you want to connect "locally", you should use bequeath type connection. This is what you are using when you connect with sqlplus, without the connection string.
    There should be a way with JDev, Toad, etc. to use this style of connection. Please refer to documentation for each of the products or google e.g. 'bequeath connection toad'.
  • 4. Re: Connection hangs when connecting to XE from an IDE
    TheNomad Newbie
    Currently Being Moderated
    I get your point, but the problem was everything worked until a few days ago :(
    So I was hoping somebody else tackled this problem before. I really don't remember changing\installing anything that might displease Oracle or that might change settings in Windows that affect it.

    In the meantime, I'll check "bequeath connection toad". As I said, I am desperate :)
  • 5. Re: Connection hangs when connecting to XE from an IDE
    Udo Guru
    Currently Being Moderated
    Hmm, perhaps your local IP changed? Could you check whether it still is 192.168.50.4 (ipconfig) and this address is actually reachable locally (ping)?

    -Udo
  • 6. Re: Connection hangs when connecting to XE from an IDE
    TheNomad Newbie
    Currently Being Moderated
    I wish it were that easy :( It defauled to the PC hostname, but I decided to try with the IP just out of despeaation.
    In the earlier install between the "it worked" and "it doesn't work" days, it used the hostname (and I didn't change the PC name).

    Small update:

    cmd -> sqlplus -> sys as sysdba: works
    cmd -> sqlplus -> sys@xe as sysdba: hangs

    Do any of you experienced DBA know if there is a difference between the 2 login modes ? (sys and sys@instance)

    The DB is working (selects work all around), but it's just as if the DB shuts out any incoming contact. But not with a firewall type of error (or TNS no listner, in start up sequence, bad loging etc.), but it just "hangs" incoming connections. And because I restart my PC, the installation is fresh AND I don't have any jobs running, I doubt there are any sessions that clog the pipes. I even tried killing the ones running (those that weren't SYS related) and no change.

    My last resort would be to try to start a hanganalyze but I am not sure I can interpret the logs, but if someone were to help me, I'd give it a shot.
    This is seriously getting on my nerves and I am even ready to go the full mile and format.

    But if this weren't an XE DB and it was a production platform, that would have obviously not been an option :(
  • 7. Re: Connection hangs when connecting to XE from an IDE
    Udo Guru
    Currently Being Moderated
    Do any of you experienced DBA know if there is a difference between the 2 login modes ? (sys and sys@instance)
    Sure. The first one uses the local IPC (Inter Process Communications) connector to the default or set ORACLE_SID, while the other one resolves your TNS Name and uses whatever is configured for it. Note that this TNS name is really something different from the instance name, although you can use the same "value" for both.
    So basically, this doesn't get us much further than we still know it's got to be network related, as orafad pointed out before...

    Let's try one more thing... You say
    In the earlier install between the "it worked" and "it doesn't work" days, it used the hostname (and I didn't change the PC name).
    So it stopped working with your hostname some day and you changed the listener to use the IP instead, right? Could it be your hostname was mapped to a different IP or to the local loopback device?
    Could you start lsnrctl and start tracing?
    LSNRCTL> trace support xe
    This will generate much debug information into trace file, way more than I can actually understand. But I guess if something is hanging, you'll find a clue there what may cause this.
    The DB is working (selects work all around), but it's just as if the DB shuts out any incoming contact.
    Which probably is more a listener problem rather than a database problem. But I would expect you to get some tns-related error like ORA-12514 in such case.
    Are you sure there is no firewall blocking that access?
    Since you try to connect to the database as sysdba, your user has to be member of the group ORACLE_DBA . Did you check that?
    Could it be your machine moved into a domain structure? Or is there any other authentication base that might be hanging?
    You've already posted the contents of listener.ora and tnsnames.ora . Could you also share what's in your sqlnet.ora to check wheter you have anything in there that might cause this behaviour?

    -Udo
  • 8. Re: Connection hangs when connecting to XE from an IDE
    orafad Oracle ACE
    Currently Being Moderated
    Has the hosts file (system32\drivers\etc\hosts) been changed? Please post its content.

    Post output of path command.

    Also follow previous suggestion per Re: Connection hangs when connecting to XE from an IDE and post the result.


    Related to "hang" when connecting with connect identifier, log output in the first post showing connection from Toad is interesting.
    Try: sqlplus user@xe, and while it appear hanging, get the most recent lines of listener.log, that should include this connect attempt, and post back here.

    Edited by: orafad on Aug 9, 2012 10:05 PM
  • 9. Re: Connection hangs when connecting to XE from an IDE
    TheNomad Newbie
    Currently Being Moderated
    Sorry for the late reply. Been a bit busy at work :(
    So it stopped working with your hostname some day and you changed the listener to use the IP instead, right? Could it be your hostname was mapped to a different IP or to the local loopback device?
    Has the hosts file (system32\drivers\etc\hosts) been changed? Please post its content.
    As I said, I only did that after reinstalling it. I can assure you all it's not an IP\hostname problem. The DB is local and I hardcoded my DHCP to give my PC's net card a static local IP (since it's mapped on its mac address it should NEVER EVER change unless Windows wants to have some unwanted fun with my nervous system).

    eastfrontiers04 is my PC name which I didn't change, so it shouldn't be a problem with that. It all happened automagically literally overnight.
    Since you try to connect to the database as sysdba, your user has to be member of the group ORACLE_DBA . Did you check that?
    It is a member of ORACLE_DBA.
    Could it be your machine moved into a domain structure?
    Nope. It's on a very simple LAN: a modem (For external access), a router (for internal access) and 1 PC and 1 laptop (I admit I haven't tried connecting from the laptop, but it's a bit more complicated since it's not mine)
    Has the hosts file (system32\drivers\etc\hosts) been changed? Please post its content.
    No.
    # Copyright (c) 1993-1999 Microsoft Corp.
    #
    # This is a sample HOSTS file used by Microsoft TCP/IP for Windows.
    #
    # This file contains the mappings of IP addresses to host names. Each
    # entry should be kept on an individual line. The IP address should
    # be placed in the first column followed by the corresponding host name.
    # The IP address and the host name should be separated by at least one
    # space.
    #
    # Additionally, comments (such as these) may be inserted on individual
    # lines or following the machine name denoted by a '#' symbol.
    #
    # For example:
    #
    #      102.54.94.97     rhino.acme.com          # source server
    #       38.25.63.10     x.acme.com              # x client host
    
    127.0.0.1       localhost
    127.0.0.1       localhost
    You've already posted the contents of listener.ora and tnsnames.ora . Could you also share what's in your sqlnet.ora to check wheter you have anything in there that might cause this behaviour?
    Since I reinstalled it should be the default now. Same behavior, of course, but why not:
    # This file is actually generated by netca. But if customers choose to 
    # install "Software Only", this file wont exist and without the native 
    # authentication, they will not be able to connect to the database on NT.
    
    SQLNET.AUTHENTICATION_SERVICES = (NTS)
    Post output of path command.
    Path=F:\oraclexe\app\oracle\product\11.2.0\server\bin;;C:\Program Files\NVIDIA C
    orporation\PhysX\Common;C:\Program Files\Common Files\Microsoft Shared\Windows L
    ive;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\system32\
    WindowsPowerShell\v1.0;C:\Program Files\TortoiseSVN\bin;C:\Program Files\Java\jd
    k1.7.0_03\bin;C:\Program Files\Common Files\Microsoft Shared\Windows Live
    Note: I reinstalled Microsoft Security Essentials since after uninstalling it my Firewall service went haywire and I thought that was what messed up the DB access but nothing changed :(
    ATM the firewall is disabled.
    Also follow previous suggestion per Re: Connection hangs when connecting to XE from an IDE and post the result.
    I tried your suggestion some time ago, but I don't remember the result. I'll do it again a bit later and repost it. Sorry about that :(

    user@xe:
    Tue Sep 04 20:32:44 2012
    04-SEP-2012 20:32:44 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)(CID=(PROGRAM=F:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe)(HOST=EASTFRONTIERS04)(USER=User))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.50.4)(PORT=1433)) * establish * XE * 0
    And yeah, now it just hangs. Doesn't even care if the password is right or wrong.
    After closing cmd by force:
    Tue Sep 04 20:32:44 2012
    04-SEP-2012 20:32:44 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)(CID=(PROGRAM=F:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe)(HOST=EASTFRONTIERS04)(USER=User))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.50.4)(PORT=1433)) * establish * XE * 0
    Tue Sep 04 20:35:02 2012
    04-SEP-2012 20:35:02 * service_update * xe * 0
    Tue Sep 04 20:35:20 2012
    04-SEP-2012 20:35:20 * service_update * xe * 0
    Tue Sep 04 20:35:32 2012
    04-SEP-2012 20:35:32 * service_update * xe * 0
    Tue Sep 04 20:40:17 2012
    04-SEP-2012 20:40:17 * service_update * xe * 0
    Tue Sep 04 20:40:32 2012
    04-SEP-2012 20:40:32 * service_update * xe * 0
    trace support xe
    http://dl.transfer.ro/transfer_ro-04sep-c9895aa3554d7a.zip
    (the link should work for 10 more days)


    Thanks for your patience guys !
    Hope one way or another I manage to find the problem (preferably without formatting)

    Minor note: one pattern I found was using sys as sydba then putting the admin password worked from sqlplus. trying to use @ in the password to specify the database I want makes it hang.

    Also tried with archived log as I read it sometimes messes up with a similar behavior; no changes. Trying @ in a password running sqlplus /nolog also hangs.
  • 10. Re: Connection hangs when connecting to XE from an IDE
    orafad Oracle ACE
    Currently Being Moderated
    With two localhost lines in hosts file, it certainly looks altered. Remove one of the duplicates. Remove any "blank" lines just to rid potential errors in the file.


    As things might have changed since start of this thread, try this to give some starting ground again:

    c:\> lsnrctl stop

    move off listener.ora ( eg rename to listener.ora_moved).

    c:\> lsnrctl start

    wait at least 1 minute

    c:\> lsnrctl stat


    Copy paste full command line session from above.


    Also run and post a copy paste of the following:

    hostname

    ping -a <hostname from above>

    ping -a <ip adress from previous ping response>
  • 11. Re: Connection hangs when connecting to XE from an IDE
    Udo Guru
    Currently Being Moderated
    In addition to what orafad posted, you should also try to add an entry for your hostname to the hosts file to ensure it can be resolved properly. From what I can see, you currently only have an entry for localhost but use EASTFRONTIERS04 and IP +192.168.50.4+ for your database configuration. And please also check whether the firewall on the network interface with that IP allows connections for TCP port 1521.
    I'm also a bit surprised to find your connection aimed at port 1433, which usually belongs to MS SQL Server, not to Oracle Database. What did you configure in your IDE?

    -Udo
  • 12. Re: Connection hangs when connecting to XE from an IDE
    984438 Newbie
    Currently Being Moderated
    Hi - Were you able to solve the problem ?? I am also facing exactly the same issue ...can you please help - I have created this thread for my issue - "sqlplus system/pswd" OK - but FAILING "sqlplus system/pswd@xe" - Plz HELP

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points