5 Replies Latest reply: Mar 26, 2010 5:49 AM by Billy~Verreynne RSS

    XE database listener problem

    676948
      I have an XE database installed on my laptop (Windows XP professional 2002 SP3) for some demo and lab applications. After not using the database for about a month and a half, i tried to connect to it and got the error message "ORA-12541: TNS: no listener". I googled and i googled and saw that others had similar problems (but not identical). I tried the following:

      Manually starting the listener with "lsnrctl start" from a command prompt. Error message: TNS-12560: TNS: protocol adapter error

      Starting the listener service from Control panel->Administrative tools->Services. The service tries to start and then immediately stops again with a message saying:
      "The OrcleXETNSListener service on Local Computer started and then stopped. Some services stop automatically if they have no work to do..."

      Starting the database works, but i get a message saying the OracleTNSListener service could not be started (no additional information)

      Stopping the database works fine.

      The "Go to database homepage" URL returns an "Unable to connect" error message.

      The XE database part of my TNSNAMES.ora file looks like this:
      XE =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = PC50053.kentor.se)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
      )
      )

      EXTPROC_CONNECTION_DATA =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      )
      (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
      )
      )

      ORACLR_CONNECTION_DATA =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      )
      (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
      )
      )

      And my LISTENER.ORA file looks like this:
      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
      (PROGRAM = extproc)
      )
      (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
      (PROGRAM = extproc)
      )
      )

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = PC50053.kentor.se)(PORT = 1521))
      )
      )

      DEFAULT_SERVICE_LISTENER = (XE)

      Is there anyone out there who can point me in the right direction, so i can solve this?
        • 1. Re: XE database listener problem
          Billy~Verreynne
          Here's a bare bones listener configuration. I've tested it with XE on my Ubuntu PC. No smelly stuff like having to deal with Windows Services. But the configuration is standard and the approach basically the same as for Windows.

          The very basic Listener simply needs an endpoint (IP address(es) and port(s)) to listen on.

          E.g.
          oracle:/usr/lib/oracle/xe> cat /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/listener.ora
          # bare bones listener config
          
          LISTENER =
                  (DESCRIPTION_LIST =
                          (DESCRIPTION =
                                  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.251.93.58)(PORT = 1521))
                          )
                  )
          
          #eof
          That's it. Single IP and single port. And the implicit instruction to "+go forth and Listen!+".


          The database instance will register its services with the Listener. Just note that my XE instance has an instance name of VLK - I have a couple of XE instances configured, and VLK happens to be the default one I started. Do not let this confuse the issue though.

          Okay, start the database instance and force it to immediately register with the Listener:
          oracle:/usr/lib/oracle/xe> sqlplus "/ as sysdba"
          
          SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 25 12:52:42 2010
          
          Copyright (c) 1982, 2005, Oracle.  All rights reserved.
          
          Connected to an idle instance.
          
          SQL> startup
          ORACLE instance started.
          
          Total System Global Area  134217728 bytes
          Fixed Size                  1257596 bytes
          Variable Size              58724228 bytes
          Database Buffers           71303168 bytes
          Redo Buffers                2932736 bytes
          Database mounted.
          Database opened.
          SQL> alter system register;
          
          System altered.
          
          SQL>
          If the database instance is already up, you can use the "+alter system register+" command to speed up things.. but as far as I recall, the database instance should not take more than a minute or so to discover there's a Listener and register with it.

          Once registered, the Listener shows the services that's provided by that database instance. E.g.
          oracle:/usr/lib/oracle/xe> lsnrctl services
          
          LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 25-MAR-2010 12:53:39
          
          Copyright (c) 1991, 2005, Oracle.  All rights reserved.
          
          Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.251.93.58)(PORT=1521)))
          Services Summary...
          Service "vlk" has 1 instance(s).
            Instance "vlk", status READY, has 1 handler(s) for this service...
              Handler(s):
                "DEDICATED" established:0 refused:0 state:ready
                   LOCAL SERVER
          Service "vlkXDB" has 1 instance(s).
            Instance "vlk", status READY, has 1 handler(s) for this service...
              Handler(s):
                "D000" established:0 refused:0 current:0 max:972 state:ready
                   DISPATCHER <machine: vilk, pid: 26981>
                   (ADDRESS=(PROTOCOL=tcp)(HOST=vilk.telkom.co.za)(PORT=60553))
          Service "vlk_XPT" has 1 instance(s).
            Instance "vlk", status READY, has 1 handler(s) for this service...
              Handler(s):
                "DEDICATED" established:0 refused:0 state:ready
                   LOCAL SERVER
          The command completed successfully
          In this case, the VLK instance supports both shared server connections (these will be handled by the dispatcher process that was registered with the Listener) and dedicated server connections (these will be started using the Listener's ORACLE_HOME environment settings).

          Now to connect to the instance via the Listener...
          /home/billy> cat $ORACLE_HOME/network/admin/tnsnames.ora
          # file TNSNAMES.ORA
          vlk.mydomain.com =
            (DESCRIPTION=
              (ADDRESS=
                (PROTOCOL=TCP)(HOST=10.251.93.58)(PORT=1521)
              )
              (CONNECT_DATA=
                (SID=vlk)
                (SERVER=dedicated)
              )
            )
          A bare bones TNS connection alias defined. SID (Oracle System ID) for the required database, plus the endpoint (IP and port) of the Listener.

          We can then test this TNS alias and connect via SQL*Plus:
          /home/billy> tnsping vlk
          
          TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 25-MAR-2010 12:54:28
          
          Copyright (c) 1997, 2005, Oracle.  All rights reserved.
          
          Used parameter files:
          /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/sqlnet.ora
          
          
          Used TNSNAMES adapter to resolve the alias
          Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=10.251.93.58)(PORT=1521)) (CONNECT_DATA= (SID=vlk) (SERVER=dedicated)))
          OK (0 msec)
          
          /home/billy> sqlplus scott/tiger@vlk
          
          SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 25 13:07:15 2010
          
          Copyright (c) 1982, 2005, Oracle.  All rights reserved.
          
          
          Connected to:
          Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
          
          SQL>
          And this is the very basics.

          The networking can be made a lot more complex with different database services, fail-over, load balancing and so on. But rather start with the very basics and get that working first.
          • 2. Re: XE database listener problem
            676948
            OK, i don't know how i ended up with less than minimal config files, but i took your advice and brought them down to the simplicity level you suggested. "lsnrctl services" returns a "TNS-12541:TNS:no listener" error. tnsping of the database returns the same error.
            • 3. Re: XE database listener problem
              Billy~Verreynne
              GoodCoffee wrote:
              OK, i don't know how i ended up with less than minimal config files, but i took your advice and brought them down to the simplicity level you suggested. "lsnrctl services" returns a "TNS-12541:TNS:no listener" error.
              This means that the Listener process (service on Windows) is not running. Fire that up first.

              A "+lsnrctl start+" does the trick on Linux/Unix - should also start the corresponding service on Windows.
              • 4. Re: XE database listener problem
                676948
                Starting the listener is my problem. Please refer to my original post.

                While waiting impatiently for help, i decided to blow my installation of Oracle XE and reinstall it from scratch. This resulted in a new set of errors so i'll have to address those now.

                Thanks for trying to help. Maybe i'll see you in a new thread :-)
                • 5. Re: XE database listener problem
                  Billy~Verreynne
                  GoodCoffee wrote:
                  Starting the listener is my problem. Please refer to my original post.
                  12560, 00000, "TNS:protocol adapter error"
                  // *Cause: A generic protocol adapter error occurred.
                  // *Action: Check addresses used for proper protocol specification. Before
                  // reporting this error, look at the error stack and check for lower level
                  // transport errors. For further details, turn on tracing and reexecute the
                  // operation. Turn off tracing when the operation is complete.

                  Which is why I said make the listener.ora file the bare basics - as to eliminate any potential config problems from it. For example, specifying an invalid IP address or hostname as the HOST parameter for the Listener, will result in this error.

                  Or not having an IPv4 protocol stack loaded in the kernel. (Windows networking can work with NetBIOS over NetBEUI - no IP stack needed)

                  While waiting impatiently for help, i decided to blow my installation of Oracle XE and reinstall it from scratch. This resulted in a new set of errors so i'll have to address those now.
                  Mistake IMO. To paraphrase Rumsfeld (?), you have now given up on "+known unknowns+" and swapped it with "+unknown unknowns+". :-)

                  Thanks for trying to help. Maybe i'll see you in a new thread :-)
                  Will be around. ;-)