How to configure an Oracle Listener without SID_LIST_LISTENER in listener.ora?
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
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 22.214.171.124.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 126.96.36.199.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.
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.
Bit late to the party on this one...
The database should register with the listener automatically, making the
SID_LISTentries redundant, and this seems to be happening with your
192.168.111.111environment. 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 registercommand. The database uses the
LOCAL_LISTENERparameter to identify the listener it should register with. By default that is null, which according to the documentation is equivalent to
alter system registerdoesn't make the service appear in the
lsnrctl statusoutput 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_LISTENERis set to something invalid for the virtual box. You can set the
LOCAL_LISTENERto either match the
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
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.oraversion, 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
griduser for RAC/HA).
There's more on
Edit: And this seems quite comprehensive.
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...