This discussion is archived
10 Replies Latest reply: Apr 11, 2013 7:43 AM by clcarter RSS

sqlplus hangs

1002050 Newbie
Currently Being Moderated
Newbie here, hoping someone can help. I have an Oracle 10g Express Edition installed locally on my Windows 7 64-bit machine. I have been using it for years no problem. Today, however, my application has stopped connecting with my local database. I can connect using SQLPlus and run queries as follows:

C:\Windows\System32>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 9 12:05:33 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: system
Enter password:

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> select count(*) from <username>.<tablename>;

COUNT(*)

--------------------------------------------------------------------------------
8425

Yet when I start my application, itcannot connect. I also cannot connect through Toad, SQLDeveloper, etc. Furthermore, when I run commands like the following…

C:\Windows\System32>sqlplus -L <user>/<password>@XE

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 9 12:50:56 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

…it appears to login but then just hangs. I never get a command prompt. I have restarted OracleXE and Oracle TNS services several times and even restarted machine to no avail. Not sure what is going on. Please forgive me my hopelessly naive description of the problem and suggest a way for me to fix this.

Regards
  • 1. Re: sqlplus hangs
    clcarter Expert
    Currently Being Moderated
    Doesn't sound like a sqlplus hang if sqlplus ... connect system works OK, might be something wonky with the listener when it tries to pass off a connection to the database. The connect ...@<tnsalias> should pass through the listener, if listener is running and the tnsalias is configured correctly.

    Try a tnsping to check if the client is set up, verify all the bits (like HOST= is your hostname, hostname resolves after checking your lsnrctl status and services output(s):
    lsnrctl stat
    ...
    ...
    lsnrctl serv
    tnsping xe
    ... used parameter file c:...network\admin\sqlnet.ora ...
    ... attempting to contact ...HOST= ... PORT= ... CONNECT_DATA ... SID= ...
    # could be SERVICE_NAME=  instead of SID=
    OK (<n> msec) # or could be a TNS-n error ...
    hostname
    ... <hostname>
    nslookup <hostname>
    ... <hostname> ... <IP address>
    Ensure the hostname and IP values are correct ...
  • 2. Re: sqlplus hangs
    1002050 Newbie
    Currently Being Moderated
    All that looks good. Here is the ouput (names changed to protect the innocent)

    C:\dw\server\trunk\server\source>lsnrctl stat

    LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 09-APR-2013 15:19:13

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
    Start Date 09-APR-2013 14:02:15
    Uptime 0 days 1 hr. 16 min. 58 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Default Service XE
    Listener Parameter File C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\listener.ora
    Listener Log File C:\oraclexe\app\oracle\product\10.2.0\server\network\log\listener.log
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXXXX)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXXXX)(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_XPT" 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

    C:\dw\server\trunk\server\source>lsnrctl serv

    LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 09-APR-2013 15:19:52

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
    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:1002 state:ready
    DISPATCHER <machine: DW-CMILLEY5, pid: 3580>
    (ADDRESS=(PROTOCOL=tcp)(HOST=XXXXX)(PORT=49162))
    Service "XE_XPT" has 1 instance(s).
    Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:8 refused:0 state:ready
    LOCAL SERVER
    Service "xe" has 1 instance(s).
    Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:8 refused:0 state:ready
    LOCAL SERVER
    The command completed successfully

    C:\dw\server\trunk\server\source>tnsping xe

    TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 09-APR-2013 15:21:08

    Copyright (c) 1997, 2005, Oracle. All rights reserved.

    Used parameter files:
    C:\dw\server\trunk\server\source\..\..\appserverinstance\sharedata\config\oracle\sqlnet.ora


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = XXXXX)(PORT = 1521)) (CONNECT_DATA = (SID =
    XE)))
    OK (0 msec)

    C:\dw\server\trunk\server\source>nslookup XXXXX
    Server: XXXXXX
    Address: 10.200.20.50

    Name: XXXXX
    Address: 10.200.12.206
  • 3. Re: sqlplus hangs
    clcarter Expert
    Currently Being Moderated
    C:\dw\server\trunk\server\source\..\..\appserverinstance\sharedata\config\oracle\sqlnet.ora
    Doesn't look like an XE install, perhaps another oracle install (client? rdbms server? something else?) has been installed since XE? Although lsnrctl appears to be the 10g version. Been a while since I've looked at a 10g XE, I guess 10.2.0.1 is the right patch level.

    Anyways, a client trace might reveal something, but going through the detail can be a bit frustrating unless you know what to look for and what to ignore.

    In your ...sqlnet.ora file try adding these lines, of course adjust the c:\temp to a valid directory:
    # logging stuff
    TRACE_LEVEL_CLIENT=user
    #TRACE_LEVEL_CLIENT=admin
    #TRACE_LEVEL_CLIENT=support
    TRACE_FILE_CLIENT=sqlnet.trc
    TRACE_DIRECTORY_CLIENT=c:\temp
    Try the trace ...=user first, and in the trace directory after trying the sqlplus ... conn ...@xe and see what you get, =user trace won't be too much verbage, but there should be something helpful.

    Using XE for years? Win7 ... x64?
  • 4. Re: sqlplus hangs
    1002050 Newbie
    Currently Being Moderated
    I reinstalled Oracle 10g and still see the same problem.

    Works:

    sqlplus
    username: <username>
    password: <password>

    Doesnt work:

    sqlplus <username>/<password>@XE

    Still cannot bring up database home page http://127.0.0.1:8080/apex
  • 5. Re: sqlplus hangs
    1002050 Newbie
    Currently Being Moderated
    What happens differently with these two syntaxes for connecting? Why does one work and the other doesnt? I feel quite helpless trying to debug this.
  • 6. Re: sqlplus hangs
    Bas de Klerk Pro
    Currently Being Moderated
    Hi,

    Are you sure your connections are not being blocked by a firewall ? Check your (windows) fiewall settings and/or disable it to see if this solves it.


    Regards
    Bas
  • 7. Re: sqlplus hangs
    1002050 Newbie
    Currently Being Moderated
    Windows Firewall not enabled. When I do "netstat -ab", I see

    TCP 0.0.0.0:1521 DW-CMILLEY5:0 LISTENING
    [tnslsnr.exe]

    so it seems like listener is connected to the socket properly.
  • 8. Re: sqlplus hangs
    clcarter Expert
    Currently Being Moderated
    Doesnt work: sqlplus <username>/<password>@XE
    Still cannot bring up database home page http://127.0.0.1:8080/apex
    That's two different problems. And firewall is not relevant for a localhost 127.0.0.1 connection. Getting apex to work depends on the correct network client setup, as well as having the database up and listener running.
    netstat -ab ... TCP 0.0.0.0:1521 DW-CMILLEY5:0 LISTENING ... tnslsnr ... seems like listener is connected
    That verifies there is a listener using port 1521, is it the correct listener? And hostname, "DW-CMILLEY5" ? That can prevent the apex service from working with the database. And could be its not the XE listener. From earlier, the lsnrctl status output:
    Listener Parameter File C:\oraclexe\app\...\NETWORK\ADMIN\listener.ora
    Your tnsping was hitting a different path, must be other oracle installs on the host. On linux for multiple installs, its not too tricky, but you do have to set up the environment to the correct $ORACLE_HOME, adjust $PATH, and $ORACLE_SID as well before doing anything with the database and listener.

    For Windows, its a bit trickier. There are registry entries, and what ever was installed last might be messing up the setup, at least for XE. Double check your lsnrctl status output, and the port=8080...http... endpoint, then try setting up %PATH% to force the XE %ORACLE_HOME% to the front, see if there is any difference. That may fix the sqlplus ...@xe connection problem.
    lsnrctl stat
     ... Parameter File ...
     ... 
    set ORACLE_HOME=c:\oraclexe\...
    set PATH=%ORACLE_HOME%\bin:%PATH%
    lsnrctl stat
      ...
    sqlplus /nolog
    connect system 
      ... password ...
    connect ... @xe
      ... ???
    There's nothing saying you can't run more than one listener on a host. But there is a rule that you can't have two (or more) listeners running using the same port, and the default 1521 port is what you get if the port is not specified in the listener.ora configuration.
  • 9. Re: sqlplus hangs
    1002050 Newbie
    Currently Being Moderated
    I dont have multiplpe Oracle installs. I do have multipe tnsnames.ora files because our appllication dynamically generates one.

    This problem is really bewildering. I even reinstalled Oracle XE and saw the same behavior! Must be some kind of problem with the port or network.
  • 10. Re: sqlplus hangs
    clcarter Expert
    Currently Being Moderated
    some kind of problem with the port or network
    Could be. How many NICs on the host? An ethernet port, or two? Plus maybe a wireless card as well? A command box ipconfig /all might show some clues on the NIC setups. A `netstat -an|findstr /i tcp` should show all the open tcp connections, `netstat -an|findstr /i listen` should show any ports with any type of service or program using the port. 1521 should be in there, will be a few others, like 139 (netbios?).

    Back to the lsnrctl stat output, if it shows at least two endpoints, one for the 1521 listener port, and the 8080 http presentation one, apex should work on 127.0.0.1, regardless of the host firewall settings. If you want to access apex remotely, for 10g the remote connections must specifically be enabled, see http://docs.oracle.com/cd/B25329_01/doc/admin.102/b25107/network.htm#sthref133

    May also have to have an exception for port 1521 in the firewall to get a successful ...@xe connection, although firewall shouldn't block local requests on the host IP. But since that's one of the problem symptoms, its worth looking into.

    The ...@xe connection hang is abnormal- should get a tns timeout (eventually) or some other tns error depending on what's wrong with the client setup.
    multipe tnsnames.ora files
    Really? There's only one per client setup, and every client should also have a sqlnet.ora. And the application client(s) are pointed to the different tnsnames files how? With seting TNS_ADMIN environment variable?

    Try adding an xe1 tns alias in the tnsnames.ora file (the one in the same folder where a `tnsping xe` shows its "Used parameter files:" location) and using your host IP address for host= and see if a sqlplus ...@xe1 hangs too:
    xe1 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = n.n.n.n )(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = XE)
        )
      )

Legend

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