7 Replies Latest reply: Jan 17, 2013 11:32 PM by 984438 RSS

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

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