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.
Best Answer
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 your192.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 theLOCAL_LISTENER
parameter to identify the listener it should register with. By default that is null, which according to the documentation is equivalent tohostname:1521
.If
alter system register
doesn't make the service appear in thelsnrctl 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, orLOCAL_LISTENER
is set to something invalid for the virtual box. You can set theLOCAL_LISTENER
to either match thelistener.ora
directly, e.g:Or you can use an alias that has those details in your
tnsnames.ora
. For example, if you add atnsnames.ora
entry like:Then you could:
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 agrid
user for RAC/HA).There's more on
LOCAL_LISTENER
here.Edit: And this seems quite comprehensive.