This discussion is archived
8 Replies Latest reply: Apr 15, 2013 5:03 PM by mojave RSS

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

mojave Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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