How to configure an Oracle Listener without SID_LIST_LISTENER in listener.ora?

Question

I have access to a ORACLE 11g server using the following listener.ora

# listener.ora Network Configuration File: D:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.111)(PORT = 1521))
    )
  )

and it serves all the instances on that server, as I can verify by

lsnrctl status

My problem is, that when I make a fresh install of Oracle 11g in a virtual machine, I have to use a listener.ora like

# listener.ora Network Configuration File: C:\app\oracle\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\oracle\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )

   (SID_DESC =
     (GLOBAL_DBNAME = ORCL)
     (ORACLE_HOME = C:\app\oracle\product\11.2.0\dbhome_1)
     (SID_NAME = orcl)
    )

   (SID_DESC =
     (GLOBAL_DBNAME = BMBK)
     (ORACLE_HOME = C:\app\oracle\product\11.2.0\dbhome_1)
     (SID_NAME = BMBK)
    )

 )

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

ADR_BASE_LISTENER = C:\app\oracle

Without the SID_LIST_LISTENER enties, i.e. using a listener.ora like that from the other server I get

C:\Windows\system32>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 27-NOV-2011 10:14
:24

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

Anmeldung bei (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS des LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
ction
Startdatum                27-NOV-2011 10:14:08
Uptime                    0 Tage 0 Std. 0 Min. 18 Sek.
Trace-Ebene               off
Sicherheit                ON: Local OS Authentication
SNMP                      OFF
Parameterdatei des Listener C:\app\oracle\product\11.2.0\dbhome_1\network\admin\
listener.ora
Log-Datei des Listener    c:\app\oracle\diag\tnslsnr\WSV-BK-W7en64-S\listener\al
ert\log.xml
Zusammenfassung Listening-Endpunkte...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=169.254.200.102)(PORT=1521)))
Der Listener unterst³tzt keine Services
Der Befehl wurde erfolgreich ausgef³hrt.

The last message translates to

The listener supports no Services.

I have no idea, why these two servers behave differently. I found no hints in configuration files nor in registry keys.

Edit:

I guess the answer is somewhere buried in this post by Burleson Consulting, but I don't find the spot, where it is actually configured.

Answer 1

Bit late to the party on this one...

The database should register with the listener automatically, making the SID_LIST entries redundant, and this seems to be happening with your 192.168.111.111 environment. If the listener is started after the database it can take a while for it to register, and there may be situations where it doesn't do so at all.

You can attempt to make it register with an alter system register command. The database uses the LOCAL_LISTENER parameter to identify the listener it should register with. By default that is null, which according to the documentation is equivalent to hostname:1521.

If alter system register doesn't make the service appear in the lsnrctl status output then I'd suspect it's unable to either identify the hostname or resolve it, or it's resolving to a different address than the one the listener is on, or LOCAL_LISTENER is set to something invalid for the virtual box. You can set the LOCAL_LISTENER to either match the listener.ora directly, e.g:

alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' scope=both;
alter system register;

Or you can use an alias that has those details in your tnsnames.ora. For example, if you add a tnsnames.ora entry like:

MY_LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  )

Then you could:

alter system set local_listener='MY_LISTENER' scope=both;
alter system register;

The only real advantage I can see of using the tnsnames.ora version, apart perhaps from brevity, is that you can change the configuration in the SQL*Net files rather than in the database; and even that only really seems useful if you're cloning databases between machines, or have your listeners and databases running under different accounts (e.g. with a grid user for RAC/HA).

There's more on LOCAL_LISTENER here.

Edit: And this seems quite comprehensive.

Answer 2

I had a similar problem and found that just shuttiing down and restarting the instance caused it to register correctly.

This can be done by using the sqlplus command line. You will need to login as a user with SYSDBA priveledges (eg system) syntax:

sqlplus name/pass as SYSDBA

then run 'shutdown' and wait for it to finish. then run 'startup' and wait for it to finish.

fingers crossed you should now have an instance registered with the listener.

To check that this is the case you can fire up lsnrctl and run the command 'status'. if it hasnt worked you'll still see the message 'The listener supports no services' otherwise you'll see something like

Service "horse" has 1 instance(s).
 Instance "horse", status READY, has 1 handler(s) for this service...