8 Replies Latest reply: Apr 15, 2013 7:03 PM by mojave RSS

    lsnrctl services does not show service XE - new install of Oracle Express

    mojave
      It's a time-consuming and problematic installation of Oracle Express 11g, but for the moment just one question: why isn't service XE showing up in lsnrctl services? Here's that output, with Service XE missing:

      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
      The command completed successfully



      listener.ora is:

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

      DEFAULT_SERVICE_LISTENER = (XE)



      tnsnames.ora is:

      XE =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
      )
      )




      slqnet.ora is:

      SQLNET.AUTHENTICATION_SERVICES = (NONE)


      NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
        • 1. Re: lsnrctl services does not show service XE - new install of Oracle Express
          clcarter
          why isn't service XE showing up
          One possible cause ... the database is not running, or the instance has not yet registered with the listener. That should only take a moment or three, and you can also force the issue (well, if the database is up ... and the client network details are correct )
          sqlplus /nolog
          conn system
            ... password ...
          connected.
          alter system register;
          exit
          lsnrctl stat 
            ...
          • 2. Re: lsnrctl services does not show service XE - new install of Oracle Express
            clcarter
            listener.ora is: ... (HOST = 127.0.0.1) ...
            That's the loopback address. Remote connections won't work, but that shouldn't keep the database from registering with the listener.

            Depends on how the host is set up. If the box uses DHCP, just about has to be the hostname. If the host has a static IP, either one will do. As long as HOST= is set to the correct hostname and/or IP address, and network client setup is all OK.

            Or using HOST=0.0.0.0 will let the listener set up endpoints on any IP on the host. If there is a wireless NIC along with an ethernet port, you may not want that to happen.

            Lastly, the "easy way out" is let the listener use its default setup- with no listener.ora file at all. Stop the listener, move the listener.ora file, i.e. to "listener.ora.bak" so that you get the "out of the box" default listener, named "LISTENER". Start the listener, do the system ... register; and see if there's an improvement.
            • 3. Re: lsnrctl services does not show service XE - new install of Oracle Express
              mojave
              Well, I think this is progress, attempting to connect in sqlplus gives the error I had in SQL Developer when I had service XE showing up in lsnrctl services (that was the 2nd installation, now on the 3rd)


              SQL> connect system/xxxxxxx
              ERROR:
              ORA-01034: ORACLE not available
              ORA-27101: shared memory realm does not exist
              Process ID: 0
              Session ID: 0 Serial number: 0


              So, couldn't connect and no luck with alter system register.


              Yes, I should be OK with 127.0.0.1, both client and server are my home PC. Thanks for the idea of testing with no listener.ora at all - I renamed and moved listener.ora, for this interesting result: in sqlplus I either get the same ORA-01034 and ORA-27101, or 2 pages of a sqlplus help file, depending on the logon syntax used. I'm sure that's user error. In SQL Developer New Database Connection Test I get ORA-12514.


              I've been closely following the thread from Marco ORA-01034 , ORA-27101 in windows Domain, and have learned a lot from you, Srini and TSharma. I think I'm leaning towards the possibility of a bad install. Task Manager shows process oracle.exe running (this may not mean much) and Services shows all the Oracle services Started. I don't know enough to interpret what I'm seeing for oracle.exe in Windows Process Explorer, although it looks like something is running. But suppose, hypothetically, that it was a bad installation, how would I test that?


              Looking at the log files, I'm seeing clsc1.log is nearly identical to what Marco reported in that other thread:

              Oracle Database 11g Clusterware Release 11.2.0.2.0 - Production Copyright 1996, 2010 Oracle. All rights reserved.
              2013-04-12 01:25:47.968: [ default][3228]ut_read_reg:2:ocr registry key SOFTWARE\Oracle\olr cannot be opened. error 2
              [    CLSE][3228]clse_get_crs_home: Error retrieving OLR configuration [0] [Error opening olr registry key. The system cannot find the file specified.
              ]



              This may be another clue, the listener log file has this after a SQL Developer New Database Connection Test (test of Connection Type TNS + Network Alias XE, XE shows up just fine in the dropdown). My computer name is newamptron, and that host_addr IP is my DSL provider. Note the completely random port 2051. Essentially same message data with or without listener.ora in place.

              <msg time="2013-04-12T23:55:46.593+00:00" org_id="oracle" comp_id="tnslsnr" type="UNKNOWN" level="16" host_id="NEWAMPTRON" host_addr="75.79.62.xxxx">

              <txt>12-APR-2013 23:55:46 * (CONNECT_DATA=(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=Paul))(SERVER=DEDICATED)(SERVICE_NAME=XE)) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=2051)) * establish * XE * 12514</txt>

              </msg>

              <msg time="2013-04-12T23:55:46.609+00:00" org_id="oracle" comp_id="tnslsnr" type="UNKNOWN" level="16" host_id="NEWAMPTRON" host_addr="75.79.62.xxxx">

              <txt>TNS-12514: TNS:listener does not currently know of service requested in connect descriptor</txt>

              </msg>




              Thanks!
              • 4. Re: lsnrctl services does not show service XE - new install of Oracle Express
                yoonas
                Hi,

                Your database is not up yet

                Start sqlplus session and try to start the database
                sqlplus / as sysdba
                startup
                post the output from above command, check alert_XE.log and post a few lines from the end

                Regards
                Yoonas
                • 5. Re: lsnrctl services does not show service XE - new install of Oracle Express
                  mojave
                  By the way, thanks for the specific sqlplus syntax, I've always used SQL Developer. The test results are: the sqlplus log on failed, didn't get to startup:

                  sqlplus / as sysdba

                  SQL*Plus: Release 11.2.0.2.0 Production on Sat Apr 13 15:28:30 2013

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

                  ERROR:
                  ORA-28056: Writing audit records to Windows Event Log failed
                  OSD-00512: Message 512 not found; product=RDBMS; facility=SOSD
                  O/S-Error: (OS 1717) The interface is unknown.
                  ORA-01031: insufficient privileges


                  another log on attempt here
                  Enter user-name: system
                  Enter password:
                  ERROR:
                  ORA-01034: ORACLE not available
                  ORA-27101: shared memory realm does not exist
                  Process ID: 0
                  Session ID: 0 Serial number: 0



                  and here is the conclusion of alert_XE.log:

                  Starting ORACLE instance (normal)
                  Sat Apr 13 11:39:57 2013
                  AUD: OS Error = 1717 encountered while writing audit record
                  AUD: OS Error = 1717 encountered while writing audit record
                  Sat Apr 13 11:39:57 2013
                  AUD: OS Error = 1717 encountered while writing audit record
                  AUD: OS Error = 1717 encountered while writing audit record
                  Sat Apr 13 11:39:57 2013
                  AUD: OS Error = 1717 encountered while writing audit record
                  AUD: OS Error = 1717 encountered while writing audit record
                  Sat Apr 13 11:39:57 2013
                  AUD: OS Error = 1717 encountered while writing audit record
                  AUD: OS Error = 1717 encountered while writing audit record



                  For whatever it's worth, both Task Manager and Windows Process Explorer show oracle.exe running.
                  • 6. Re: lsnrctl services does not show service XE - new install of Oracle Express
                    mojave
                    OK, outstanding, Oracle Express Edition 11g Release 2 is up and running on my home PC, with yoonus having pointed out the problem, plus a key assist from clcarter. Thanks. The problem appears to have been maxed-out Windows Event Logs. I cleared one and increased the size on 2 others, then uninstalled and reinstalled Oracle XE 11g. Connected with sqlplus, best select * from dual ever. I will venture a guess that in the XE installer, there is no error handling for a full Windows Event Log.

                    If anyone is reading this later, just study the procedure to resolve ORA-28056: Writing audit records to Windows Event Log failed, that was the key error message.

                    I still can't get in with SQL Developer, but that's a SQL Developer question which will be posted in the SQL Developer forum after I give up Googling "no more data to read from socket".
                    • 7. Re: lsnrctl services does not show service XE - new install of Oracle Express
                      clcarter
                      I should be OK with 127.0.0.1
                      Nope, tried using the localhost IP and the instance does not auto register with the listener, the XE and XEXDB services do not show.

                      To get the XE and XEXDB service entries working on localhost, set the local_listener parameter then try the register. Its a dynamic parameter, the database does not need to be restarted to make the change:
                      sqlplus /nolog
                      conn system
                       ... password ... connected.
                      alter system set local_listener = '(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' scope = both;
                      ... system altered ...
                      alter system register;
                      exit
                      lsnrctl stat
                      ... Service "XEXDB" has 1 instance ... Service "xe" has 1 instance ...
                      And before going with the "localhost" make sure the tcp setup is OK ... ping localhost ... -> pinging <hostname> [127.0.0.1]

                      A database connection to 127.0.0.1 might still work, but the apex pages will definitely be broken without encouraging the database to use the loopback IP by setting the local_listener parameter.

                      Edited by: clcarter on Apr 15, 2013 10:07 AM
                      typo
                      • 8. Re: lsnrctl services does not show service XE - new install of Oracle Express
                        mojave
                        OK, thanks, I see what you're getting at. In SQL Developer, a TNS connection on XE is ORA-12520, but a TNS connection on XEXDB gets in just fine. Why is that?


                        But I may still be missing something, seeing strange results on XEXDB:

                        LSNRCTL> services
                        Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
                        Services Summary...
                        Service "XE" has 2 instance(s).
                        Instance "XE", status UNKNOWN, has 1 handler(s) for this service...
                        Handler(s):
                        "DEDICATED" established:0 refused:0
                        LOCAL SERVER
                        Instance "xe", status READY, has 1 handler(s) for this service...
                        Handler(s):
                        "DEDICATED" established:0 refused:0 state:ready
                        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: NEWAMPTRON, pid: 656>
                        (ADDRESS=(PROTOCOL=tcp)(HOST=newamptron)(PORT=2381))
                        The command completed successfully


                        I was curious about that port 2381, no clue where that came from. I may need to learn more about how to refuse a DHCP suggestion, because my PC understands my computer name "newamptron" to have the IP of my DSL provider. That's going to cause me trouble with the web browser interface, Oracle XE installed with port 8080 operating as newamptron:8080


                        Here's the tnsping of newamptron:8080, with HOST=75.79.xx.xxxx. Needless to say, my DSL provider does not have anything at port 8080.

                        C:\Documents and Settings\Paul.NEWAMPTRON>tnsping newamptron:8080

                        TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 15-APR-2
                        013 23:58:56

                        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 EZCONNECT adapter to resolve the alias
                        Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTO
                        COL=TCP)(HOST=75.79.xx.xxxx)(PORT=8080)))
                        TNS-12537: TNS:connection closed