This discussion is archived
7 Replies Latest reply: Jan 17, 2013 9:32 PM by 984438 RSS

"sqlplus system/pswd" OK - but FAILING "sqlplus system/pswd@xe" - Plz HELP

984438 Newbie
Currently Being Moderated
I have just installed Oracle 11g R2 on my Win 7 64 bit machine. Faced issue with the key_xe.reg file, and followed this post for it - http://blog.mclaughlinsoftware.com/2011/09/13/oracle-11g-xe-installer/.

However now, while I am able to connect as "sqlplus system/password", but not to XE as "sqlplus system/password@xe". The sqlplus just hangs and do not return.

tnsping is also ok. It is probably for this reason only, that I am not able to make new connection to xe in sqldeveloper.

I have spent whole day struggling with this installation, and now desperately needs HELP :(.



---------------------------------------------
Output and environment details below:
---------------------------------------------

C:\Windows\system32>
C:\Windows\system32>sqlplus system/system

SQL*Plus: Release 11.2.0.2.0 Production on Sat Jan 12 04:36:45 2013

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

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL>
SQL> quit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

C:\Windows\system32>
C:\Windows\system32>sqlplus system/system@xe

SQL*Plus: Release 11.2.0.2.0 Production on Sat Jan 12 04:37:01 2013

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

<------------- Hangs here, does not return ------------------------->


---------------------------------------------
lsnrctl stat
---------------------------------------------

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 12-JAN-2013 04:28:42

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

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 12-JAN-2013 04:27:28
Uptime 0 days 0 hr. 1 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\listener.ora
Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\SGH038PKB4\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SGH038PKB4)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SGH038PKB4)(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


---------------------------------------------
tnsping xe
---------------------------------------------

TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 12-JAN-2013 04:29:55

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

Used parameter files:
C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\sqlnet.ora


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



---------------------------------------------
listener.ora
---------------------------------------------

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

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

DEFAULT_SERVICE_LISTENER = (XE)

---------------------------------------------
tnsname.ora
----------------------------------------------

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(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)
)
)
  • 1. Re: "sqlplus system/pswd" OK - but FAILING "sqlplus system/pswd@xe" - Plz HELP
    Paul M. Oracle ACE
    Currently Being Moderated
    Change localhost with machine name within your configuration files, that is :

    - stop the listener

    - update files

    - start the listener

    - wait up to a minute and retry.
  • 2. Re: "sqlplus system/pswd" OK - but FAILING "sqlplus system/pswd@xe" - Plz HELP
    984438 Newbie
    Currently Being Moderated
    Hi -

    Actually, I changed it to 'localhost' when it didn't work fine with the hostname.

    Anyways, I tried it again, and its the same behavior. Below is the new outputs and environment, and also the listener.log data.


    -----------------------------------------
    Output:
    -----------------------------------------

    C:\Users\adgangwa>lsnrctl status

    LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 13-JAN-2013 01:57:05

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

    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 13-JAN-2013 01:56:09
    Uptime 0 days 0 hr. 0 min. 59 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Default Service XE
    Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\listener.ora
    Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\SGH038PKB4\listener\alert\log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SGH038PKB4)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SGH038PKB4)(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

    C:\Users\adgangwa>sqlplus system/system

    SQL*Plus: Release 11.2.0.2.0 Production on Sun Jan 13 01:57:15 2013

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

    Connected to:
    Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

    SQL> quit
    Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

    C:\Users\adgangwa>sqlplus system/system@xe

    SQL*Plus: Release 11.2.0.2.0 Production on Sun Jan 13 01:57:27 2013

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

    ^C
    C:\Users\adgangwa>


    -----------------------------------------
    Corresponding listener.log
    -----------------------------------------

    Sun Jan 13 01:56:05 2013
    System parameter file is C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\listener.ora
    Log messages written to C:\oraclexe\app\oracle\diag\tnslsnr\SGH038PKB4\listener\alert\log.xml
    Trace information written to C:\oraclexe\app\oracle\diag\tnslsnr\SGH038PKB4\listener\trace\ora_5088_5740.trc
    Trace level is currently 0

    Started with pid=5088
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SGH038PKB4)(PORT=1521)))
    Listener completed notification to CRS on start

    TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
    WARNING: Subscription for node down event still pending
    13-JAN-2013 01:56:11 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=adgangwa))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647040)) * status * 0
    Sun Jan 13 01:56:41 2013
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SGH038PKB4)(PORT=8080))(Presentation=HTTP)(Session=RAW))
    13-JAN-2013 01:56:41 * service_register * xe * 0
    Sun Jan 13 01:57:05 2013
    WARNING: Subscription for node down event still pending
    13-JAN-2013 01:57:05 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=adgangwa))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647040)) * status * 0
    Sun Jan 13 01:57:17 2013
    13-JAN-2013 01:57:17 * service_update * xe * 0
    13-JAN-2013 01:57:20 * service_update * xe * 0
    Sun Jan 13 01:57:27 2013
    13-JAN-2013 01:57:27 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)(CID=(PROGRAM=C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe)(HOST=SGH038PKB4)(USER=adgangwa))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=59790)) * establish * XE * 0
    Sun Jan 13 02:00:00 2013
    13-JAN-2013 02:00:00 * service_update * xe * 0


    -----------------------------------------
    hosts file:
    -----------------------------------------

    127.0.0.1 localhost
    ::1 localhost
    127.0.0.1 SGH038PKB4 SGH038PKB4.nsn-intra.net


    -----------------------------------------
    tnsnames.ora
    -----------------------------------------
    XE =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = SGH038PKB4)(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)
    )
    )

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

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

    DEFAULT_SERVICE_LISTENER = (XE)


    Any help on this would be appreciated. Thanks.

    Edited by: 981435 on Jan 12, 2013 12:33 PM
  • 3. Re: "sqlplus system/pswd" OK - but FAILING "sqlplus system/pswd@xe" - Plz HELP
    Paul M. Oracle ACE
    Currently Being Moderated
    I'm not able to reproduce your situation (with your configurations everything works to me), but I'd try this :
    -----------------------------------------
    hosts file:
    -----------------------------------------

    127.0.0.1 SGH038PKB4 SGH038PKB4.nsn-intra.net
    Within that line change 127.0.0.1 with IP address, that is, stop everything, change it, and restart everything.
  • 4. Re: "sqlplus system/pswd" OK - but FAILING "sqlplus system/pswd@xe" - Plz HELP
    984438 Newbie
    Currently Being Moderated
    That also not helping - still the same behaviour - sqlplus hangs. I am not an expert in Oracle, but on the backend what actually is the difference when we do "sqlplus system/system" and "sqlplus system/system@xe" - i mean the problem can only be in the extra logic that kicks in in the latter case.

    One thing - my machine is configured by company and is within company domain too (though not logged in into it) - this also means that I don't have access to windows firewall too - do you think any of it can be a problem and and how ?
  • 5. Re: "sqlplus system/pswd" OK - but FAILING "sqlplus system/pswd@xe" - Plz HELP
    Paul M. Oracle ACE
    Currently Being Moderated
    what actually is the difference when we do "sqlplus system/system" and "sqlplus system/system@xe"
    In the former you're working locally, while in the latter you're going thru the network.
    my machine is configured by company and is within company domain too (though not logged in into it) - this also means that I don't have access to windows firewall too - do you think any of it can be a problem and and how ?
    The above said, yes, that could be the problem, you might be blocked by the firewall. I'd have a talk with network administrators.
  • 6. Re: "sqlplus system/pswd" OK - but FAILING "sqlplus system/pswd@xe" - Plz HELP
    984438 Newbie
    Currently Being Moderated
    It seems that the problem was with some virus infection in my windows - which effected the windows socket dll. Got the indication from my antivirus. Though i corrected the same, but oracle problem was still the same.

    Then i re-installed my windows 7, and then oracle xe, and as expected it is working fine now.
  • 7. Re: "sqlplus system/pswd" OK - but FAILING "sqlplus system/pswd@xe" - Plz HELP
    984438 Newbie
    Currently Being Moderated
    It seems that the problem was with some virus infection in my windows - which effected the windows socket dll. Got the indication from my antivirus. Though i corrected the same, but oracle problem was still the same.

    Then i re-installed my windows 7, and then oracle xe, and as expected it is working fine now.

Legend

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