In my previous post, Registering the Oracle Database with the listener, I mentioned the role of the initialization parm LOCAL_LISTENER in dynamic registration of the database instance to the listener. Now I’d like to deliver on my promise to explore that piece of the puzzle.
To quickly recap that post, there are two methods by which a listener comes to know what databases it can service. In Oracle terminology, this is referred to as “registering with the listener.”
Static Instance Registration
The first – and older – method is static registration. In this method, the instance is listed in the SID_LIST section of the listener’s configuration file, “listener.ora”. Such a registration would show in the listener.ora like this:
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=tulsa)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = tulsa)
)
)
Fig. 1
And that static registration shows up in the listener status with a status of UNKNOWN:
oracle:tulsa$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 16:24:29 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 23-JAN-2016 16:24:25 Uptime 0 days 0 hr. 0 min. 4 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "tulsa" has 1 instance(s). Instance "tulsa", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
Fig. 2
At Fig. 2, line 24, the status is UNKNOWN because there is no mechanism to guarantee that the specified instance even exists. The listener just assumes that the instance will be there when a connect request is received. In fact, my database was down when I took the status shown in Fig. 2.
Dynamic Instance Registration
With version 9.0 Oracle introduced the concept of dynamic registration. With this, it is no longer necessary to list the database instance in the listener.ora file. Instead, the database instance contacts the listener directly and registers itself. We can observe the result of that in the listener status. First, I’ll “remove” my listner.ora by renaming it, then restart the listener and see what it says about itself. The listener is quite capable of running without a listner.ora file at all. It will simply start and run with all default values.
oracle:tulsa$ lsnrctl stop LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 16:26:32 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1521))) The command completed successfully oracle:tulsa$ mv lsnrctl istener.ora listener.save oracle:tulsa$ lsnrctl start LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 16:27:13 Copyright (c) 1991, 2014, Oracle. All rights reserved. Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 12.1.0.2.0 - Production Log messages written to /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521))) Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 23-JAN-2016 16:27:13 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Log File /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521))) The listener supports no services The command completed successfully
Fig. 3
So we can see in Fig. 3, line 39 that the listener has started but supports no services. If we try to connect at this point we will get the ora-12514:
C:\>tnsping tulsa TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 23-JAN-2 016 16:29:37 Copyright (c) 1997, 2010, Oracle. All rights reserved. Used parameter files: C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = vblnxsrv02)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = tulsa))) OK (60 msec) C:\>sqlplus scott/tiger@tulsa SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 23 16:29:52 2016 Copyright (c) 1982, 2010, Oracle. All rights reserved. ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Fig. 4
In Fig. 4, line 1, a tnsping proves that our tnsnames resolution is correct. But at line 23 we see that an actual attempt to connect to the service proves the listener doesn’t know anything about the service “tulsa”.
Now let’s start the instance and check again:
oracle:tulsa$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 23 16:39:03 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 713031680 bytes Fixed Size 2928488 bytes Variable Size 520093848 bytes Database Buffers 184549376 bytes Redo Buffers 5459968 bytes Database mounted. Database opened. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options oracle:tulsa$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 16:39:26 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 23-JAN-2016 16:27:13 Uptime 0 days 0 hr. 12 min. 13 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Log File /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521))) Services Summary... Service "tulsa" has 1 instance(s). Instance "tulsa", status READY, has 1 handler(s) for this service... Service "tulsaXDB" has 1 instance(s). Instance "tulsa", status READY, has 1 handler(s) for this service... The command completed successfully oracle:tulsa$
Fig. 5
In Fig. 5 we observe that once the instance is started (line 9), when we re-check the listener (line 24) it now knows of service “tulsa”, with a status of READY (line 45). This obviously did not come from listener.ora as I had removed that file. Notice also that, unlike the static registration, this time the status is READY. The listener knows the instance is ready because the instance itself told the listener it was ready. And we can prove it by establishing a connection from a remote system:
C:\>sqlplus scott/tiger@tulsa SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 23 16:40:54 2016 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt ions SQL>
Fig. 6
The LOCAL_LISTENER initialization parameter
So we’ve seen that the listener is able to start up and successfully handle connection requests without a listener.ora configuration file. It does this by using all defaults (including the listener name and port) and the database instance is able to register itself with the listener.
How does the instance know how to contact the listener in order to register itself? It uses the initialization parameter LOCAL_LISTENER. From the Oracle® Database Reference 12c we read
LOCAL_LISTENER specifies a network name that resolves to an address or address list of Oracle Net local listeners
Let’s see what my instance says about that …
SQL> show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string SQL>
Fig. 7
OK, so the instance is supposed to use LOCAL_LISTENER to locate the listener so that it (the instance) can register itself with the listener. But I don’t have LOCAL_LISTENER set to anything. Well, it so happens that LOCAL_LISTENER has a default value that dovetails nicely with the default settings of the listener. Again, from the Reference manual:
Default value: (ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521)) where hostname is the network name of the local host.
I’ve seldom found a good reason NOT to run the listener with anything other the default name and port, but some people insist, and that’s when we need to adjust LOCAL_LISTENER to match up. So let’s set up a test case.
First, I’ll set my listener to use a non-default port. Notice I’ve also removed the SID_LIST section entirely.
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02.localdomain)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
Fig. 8
And restarting the listener
oracle:tulsa$ lsnrctl start LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 16:47:56 Copyright (c) 1991, 2014, Oracle. All rights reserved. Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 12.1.0.2.0 - Production System parameter file is /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1522))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1522))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 23-JAN-2016 16:47:56 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) The listener supports no services The command completed successfully
Fig. 9
At this point my listener is up, listening on the non-default port of 1522 (line 28) and knows of no services. With a default setup, I should be able to connect to the database and force a registration. Remember that at this point, my listener is using the non-default port of 1522, while the database is still trying to contact the listener on the default port of 1521.
SQL> show parameter local listener show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string SQL> alter system register; System altered. SQL>
Fig. 10
I had expected this to return an error, but as you can see, it did not. I also could find no related errors in the alert log. But, as expected, the instance is not registered with the listener:
oracle:tulsa$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 16:55:04 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1522))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 23-JAN-2016 16:47:56 Uptime 0 days 0 hr. 7 min. 7 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) The listener supports no services The command completed successfully oracle:tulsa$
Fig. 11
In order to enable the instance to register with the non-default listener, we need to set LOCAL_LISTENER to an appropriate value. Remember from the documentation that “LOCAL_LISTENER specifies a network name that resolves to an address or address list of Oracle Net local listeners”. So let’s set it:
oracle:tulsa$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 23 17:32:23 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02.localdomain)(PORT = 1522))' scope=both; System altered. SQL> alter system register; System altered. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options oracle:tulsa$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 17:32:44 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1522))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 23-JAN-2016 16:47:56 Uptime 0 days 0 hr. 44 min. 47 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "tulsa" has 1 instance(s). Instance "tulsa", status READY, has 1 handler(s) for this service... Service "tulsaXDB" has 1 instance(s). Instance "tulsa", status READY, has 1 handler(s) for this service... The command completed successfully oracle:tulsa$
Fig. 12
So by setting LOCAL_LISTENER to the values appropriate for the listener, it is again able to contact the listener and register its services.
Did it occur that the setting of LOCAL_LISTENER looks a lot like an entry in tnsnames.ora? Remember that the description of LOCAL_LISTENER “specifies a network name that resolves to an address …” (emphasis mine). As a matter of fact, we can use a tnsnames entry instead of hardcoding the address in LOCAL_LISTENER. To do this, we need to create a special entry in the tnsnames.ora file on the server, then set LOCAL_LISTENER to point to that entry. Unlike the usual tnsnames entry that points to a database service, this entry will point to the listener itself. (By the way, it is often said that the tnsnames.ora file is used only by client processes. This use of tnsames by the database instance is no exception. At this point the instance is acting in the role of a client, just as it does when using a database link to access data on another database.)
Let’s create the tnsnames entry, and test it with tnsping. I’ll add the entry FUBAR for this.
oracle:orcl$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TULSA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tulsa)
)
)
FUBAR =
(ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02.localdomain)(PORT = 1522))
oracle:orcl$ tnsping fubar
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 24-JAN-2016 10:50:26
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02.localdomain)(PORT = 1522))
OK (10 msec)
oracle:orcl$
Fig. 13
In reality I’d want to make the tnsnames alias something more meaningful, but here I wanted to use a name that would very obviously not be some reserved or default “magic” value. Notice that since FUBAR is used to locate the listener itself (rather than the services of a database instance) we do not need to include the CONNECT_DATA section.
Now that we have a tnsnames entry that points specifically to the listener, let’s prove it out.
First, we set LOCAL_LISTENER to reference the tnsnames.ora entry:
SQL> alter system set local_listener='fubar' scope=both; System altered. SQL> show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string fubar SQL>
Fig. 14
Next, I’ll restart the listener, in order to flush the current registrations and start clean.
oracle:orcl$ lsnrctl stop LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 24-JAN-2016 10:53:04 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1522))) The command completed successfully oracle:orcl$ lsnrctl start LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 24-JAN-2016 10:53:10 Copyright (c) 1991, 2014, Oracle. All rights reserved. Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 12.1.0.2.0 - Production System parameter file is /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1522))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1522))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 24-JAN-2016 10:53:10 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) The listener supports no services The command completed successfully oracle:orcl$
Fig. 15
Finally, I’ll force a registration of the instance, then recheck the listener status.
SQL> alter system register; System altered. SQL> !lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 24-JAN-2016 10:54:47 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1522))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 24-JAN-2016 10:53:10 Uptime 0 days 0 hr. 1 min. 37 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "tulsa" has 1 instance(s). Instance "tulsa", status READY, has 1 handler(s) for this service... Service "tulsaXDB" has 1 instance(s). Instance "tulsa", status READY, has 1 handler(s) for this service... The command completed successfully SQL>
Fig. 16
Conclusion
I’ve shown how it is possible for the listener to operate without the use of the listener.ora configuration file. I have also shown how the database instance registers itself with the listener with both default and non-default settings, and how the instance uses the LOCAL_LISTENER initialization parameter and the tnsnames.ora file to locate the listener for self-registration.
I would also like to point out that for the purposes of this demo, I never mixed static and dynamic registrations. I did that to make it perfectly clear that the two are separate and distinct and not inter-related. However, I also need to point out that this does not mean that the two are mutually exclusive. It is actually rather normal to have both at the same time. When doing so, the listener will report both:
oracle:tulsa$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 24-JAN-2016 15:12:35 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1522))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 24-JAN-2016 15:11:49 Uptime 0 days 0 hr. 0 min. 46 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "tulsa" has 2 instance(s). Instance "tulsa", status UNKNOWN, has 1 handler(s) for this service... Instance "tulsa", status READY, has 1 handler(s) for this service... Service "tulsaXDB" has 1 instance(s). Instance "tulsa", status READY, has 1 handler(s) for this service... The command completed successfully oracle:tulsa$
Fig. 17
At this point I have covered just about the entire range of TNS configuration items that cause the vast majority of Oracle database connection issues. In previous posts I covered the “configuration chain” from the initial client connection request, through the network routing, through the listener, to the database instance. In this post I have shown the configuration issues that lead to the listener knowing what database instances it is supposed to be able to service. Hopefully, this series will be of help to those faced with the original question, “Why can’t I connect to my database?”
Ed Stevens, DBA 
What if you are in a 11.2 RAC environment and you have different local listeners on different nodes. You have only one database yet it must register with host=mynode1 and host=mynode2 on the second instance?
Jeff
I have no experience with RAC, so really can’t address this question. I’d take a look at http://www.oracle-base.com/articles/11g/oracle-db-11gr2-rac-installation-on-oracle-linux-6-using-virtualbox.php where Tim talks about setting up a virtual testbed. In there he discusses the networking issues. Also always best to refer back to the official docs at tahiti.oracle.com
hi,
I am training on a local server to prepare for a certification. I did the same tests with the folowing version of oracle.
SQL> select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
PL/SQL Release 11.2.0.1.0 – Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 – Production
NLSRTL Version 11.2.0.1.0 – Production
Everything works fine except :
Service “orcl.localdomain” has 1 instance(s).
Instance “orcl”, status READY, has 1 handler(s) for this service…
Service “orclXDB.localdomain” has 1 instance(s).
Instance “orcl”, status READY, has 1 handler(s) for this service…
It creates a orclXDB.localdomain service. how can i remove it ?
I dont really know what i should use as my localdomain ? should I set DB_DOMAIN to a value of my /etc/host ?
Thx for the great post. It’s really nice and clear.
The ‘localdomain’ in your listener registrations comes from either the SERVICE_NAMES or the DB_DOMAIN initialization parameters. Personally, I never set it and have found no particular use for it.
I just read another post on your website about /etc/conf and DB_DOMAIN. I think I was mixing everything.
As far as orclXDB is concerned. I uninstalled XDB and i did an ALTER SYSTEM SET DISPATCHERS = ”. (the orclXDB service was the only one).
I dont really understand how the dispatchers are used yet. But i archieved my goal.
Thx for the great site.
*/etc/hosts
Hi,
The entry in my listener.ora is :
# listener.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 01hw054895)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
While in the listener status, the o/p is:
LSNRCTL> stat
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 – Produ
ction
Start Date 09-AUG-2012 19:01:42
Uptime 0 days 0 hr. 0 min. 57 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oracle\product\10.2.0\db_1\network\admin\listener.o
ra
Listener Log File D:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=01hw054895.corp.tatacapital.com)(POR
T=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “TESTANIXDB” has 1 instance(s).
Instance “testani”, status READY, has 1 handler(s) for this service…
Service “TESTANI_XPT” has 1 instance(s).
Instance “testani”, status READY, has 1 handler(s) for this service…
Service “testani” has 1 instance(s).
Instance “testani”, status READY, has 1 handler(s) for this service…
The command completed successfully
Plz sugegst how is my instance dynamically registered when there is no entry in listener.ora
“Plz sugegst how is my instance dynamically registered when there is no entry in listener.ora”
That’s the whole point of dynamic registration. It occurs regardless of what is or is not in the SID_LIST section of your listener.ora file. To reiterate my original post:
There are two methods by which a listener comes to know of a database instance.
The first is static registration, which comes from the SID_LIST section of the listener.ora file. We give the listener a list of services that it can service.
The second method is dynamic registration. In dynamic registration, the database itself contacts the listener and says, in effect “hey, I’m here, you need to service any connection requests for me as well.”
Hi,
Thanks for the reply 🙂
I have one query: is it that local_listener parameter is required for dynamic registration of instance for non-default values or for default values as well.
and is it that local_listener is the only parameter which guarantees the dynamic registration of instance.
Actually, you had two queries, not one. 😉
First, you asked about LOCAL_LISTENER if using all default values. If you take another look at Fig.7 and the surrounding explanation, you will see that at that point in the demo I did NOT have a setting for LOCAL_LISTENER. In the absence of a setting, the instance will use the default settings of port 1521 to try to contact the listener. And this is the default value for the listener. So at the very minimum, you can start the listener with NO listener.ora file and will happily start and configure itself to listen on the default port of 1521. And if you start the instance with the default setting of LOCAL_LISTENER – which is no setting at all, aka NULL – it will use the default port of 1521.
Your second question asked if there were any other parameters required to “guarantee” dynamic registration. The idea of “guranteeing” dynamic registration could be a bit broad, but LOCAL_LISTENER is the only parameter I’ve found that is directly involved in dynamic registration.
These things are easily tested for yourself, so I would urge you to take a look at my posting on creating your own test system, at https://edstevensdba.wordpress.com/category/opinion/.
Thank you for this excellent series on troubleshooting connection errors. I’ve been working with Oracle for nearly 10 years, and still learned several new things from your very clear demos. Much appreciated!
Great post ! Thanks a lot !!
Thanks for a great post. Very clear and sharp.
Nice post and well explained!
Pingback: Oracle 11g Release 2: ORA-00119: invalid specification for system parameter LOCAL_LISTENER « Blog about cooking, technology and something else
Thanks..cleared my doubts regarding local listener
Hi I would to say thanks for the valuable information which you have shared with us. I have one doubt, When I try to connect the oracle db with this statement (C:\>sqlplus scott/tiger@oklacity ) in that case I am able to connect but when I do this C:\>sqlplus scott/tiger@10.112.113.121/orcl in this I am getting the below error. Could you please tell me why i am facing this error ? I am this error in the both cases either I try from server or from Client side. Thanks
Error:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
You are trying to use the EZconnect method, which I’ve not seen a reason to spend much time poking at. But as I look up the syntax, it looks like what you are doing is correct. The fact that you get an ORA-12514 means there is a listener on port 1521 of the specified ip address, so I have to believe Oracle. What do you see when you log on to the server at 10.112.113.121 and issue a ‘lsnrctl status’? Do you see a service named ‘orcl’? Not ‘ORCL’, not ‘orcl.some.domain’, just simply ‘orcl’, in lower case letters.
What a pleasure reading your posts. They are crystal clear and make difficult things easy
Excellent Post! Very Helpful
Thanks for the nice post.. Helpful !!
Pingback: Non-Default Oracle Listener | Chris Gilbert
Amazing write-up, thanks a ton!
I have been working on implementing a fix for Oracle Security Alert for CVE-2012-1675, the “TNS Listener Poison Attack”.
My listener.ora has the required (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) and SECURE_REGISTER_LISTENER_PROD = (IPC).
My listener.ora contains a SID_LIST as we manage individual instances thru Enterprise Manager.
The server I’me testing on is a test server with four istances on it but I’m only testing with one.
My local_listener has:
SQL> show parameter local_listener;
NAME TYPE VALUE
———————————— ———– ——————————
local_listener string (DESCRIPTION=(ADDRESS=(PROTOCO
L=IPC)(KEY=EXTPROC1521)))
SQL>
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
————————
Alias listener
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 – Production
Start Date 15-MAY-2014 06:41:42
Uptime 7 days 4 hr. 56 min. 30 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oracle\product\11.2.0\db_1\network\admin\listener.ora
Listener Log File d:\oracle\diag\tnslsnr\ngdwt2\listener\alert\log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ngdwt2.mydomain.com)(PORT=1521)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “dwngdev.mydomain.com” has 1 instance(s).
Instance “dwngdev”, status UNKNOWN, has 1 handler(s) for this service…
Service “dwngtest.mydomain.com” has 1 instance(s).
Instance “dwngtest”, status UNKNOWN, has 1 handler(s) for this service…
Service “eAlpha.mydomain.com” has 2 instance(s).
Instance “ealpha”, status UNKNOWN, has 1 handler(s) for this service…
Instance “ealpha”, status READY, has 1 handler(s) for this service…
Service “fisvtest.mydomain.com” has 1 instance(s).
Instance “fisvtest”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
LSNRCTL>
So as I understand it the “UNKNOWN” is because I have it defined in the SID_LIST and the “READY” is because I have the LOCAL_LISTENER parameter filled in.
My first question is Should I remove the one from the SID_LIST? Will that still use the TCP protocol which we are trying to avoid?
My second is I’m getting the messages indicating requests are being blocked:
22-MAY-2014 11:46:50 * service_register_NSGR * 1194
TNS-01194: The listener command did not arrive in a secure transport
Do you know if there a way to find out where these requests are coming from?
Offhand I’m not sure. Let me review the issues on the tns poison fix.
—
Ok, on second reading a few things jump out at me.
First, you said you configured your listener with “SECURE_REGISTER_LISTENER_PROD = (IPC)”. This means you are setting the SECURE_REGISTER for a listener named LISTENER_PROD, but when you show the output of lisnrctl status, we see a listener with the default name of simply LISTENER. I have an intense dislike of trying to run multiple listeners, or even a single listener with a non-default name. I’ve never seen it fix a real problem. Doing such never accomplishes anything other than adding to the confusion.
Maybe you need to show your entire listener.ora. As it is, all I’m seeing are snippets of it, taken out of context.
Pingback: Another day another database won’t start. | Chris Grabowy's Technical Ramblings
Great post… Clearly explained
Hi ES – good post. Let’s say I have two Oracle homes (11.2.0.4 and 11.2.0.3) on the same database server (test1.x.com). I’m running two databases, ITPT (11.2.0.4) and DXPT (11.2.0.3). My static-service registration entries in the listener.ora file are as follows:
(SID_DESC =
(GLOBAL_DBNAME = ITPT)
(ORACLE_HOME = /oraclesw/11r204)
(SID_NAME = ITPT)
)
(SID_DESC =
(GLOBAL_DBNAME = DXPT)
(ORACLE_HOME = /oraclesw/11r203)
(SID_NAME = DXPT)
)
The listener is running out of the 11r204 home (also location of referenced listener.ora file). If I remove the static-service DXPT entry in above listener.ora file, and add following to the 11r204 tnsnames.ora file:
LISTENER_DXPT =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test1.x.com)(PORT = 1521))
)
And set DXPT init parameter:
SQL> alter system set local_listener=’LISTENER_DXPT’ scope=both;
How does listener know that instance DXPT should be tied to the 11r203 Oracle-home binaries? Desired ORACLE_HOME used to be explicitly set in the now-deleted DXPT entry in listener.ora file. Thanks. -jk
John –
That’s an interesting question I had never really thought about before.
Looking at Oracle® Database Net Services Administrator’s Guide 11g Release 2 (11.2) E41945-02, Section 9.9.3 (http://docs.oracle.com/cd/E11882_01/network.112/e41945/listenercfg.htm#NETAG297 ), we read
“The listener uses the dynamic service information about the database and instance before using statically configured information in the listener.ora file” (emphasis mine).
And in Table 9-1 that follows that quote, in the description of the use of ORACLE_HOME, we read
“On Linux and UNIX, this setting is optional.
On Microsoft Windows, this setting is ignored. The Oracle home specified by the ORACLE_HOME parameter in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID of the Microsoft Windows registry is used”
Of course, the entire purpose of the listener is to catch connection requests from clients, then either connect that client to a dispatcher (shared server) or spawn a dedicated server process to handle the client’s requests to the database. And so I would think that the whole question of why ORACLE_HOME would matter is one of how the listener (in a multi-home, multi-version installation) would know which version binary to load for any particular dedicated server.
Not finding a direct answer in the docs, but I suspected that when the instance registers itself with the listener, part of what it passes is the necessary ORACLE_HOME info. To find out, I enabled tracing in the listener by adding the following line to listener.ora:
TRACE_LEVEL_listener=16
This will trace down to the level of showing actual packets being sent and received by the listener.
My test system is running a single version of Oracle 11.2.0.1 on Oracle Linux 5.6. On that system I did the following:
Open a putty session to work with the listener. Call it ProcL
Open a second putty session to work with the database. Call it ProcD
(I do this so as to be able to minimize the time between necessary commands, and thus the ‘noise’ in the trace file).
In procL, stop the listener
In ProcL, add the TRACE_LEVEL parameter to listener.ora
In ProcD, connect to an idle instance.
In ProcL, start the listener
In ProcD, as soon as the listener is started, start the database.
In ProcL, stop the listener.
Examine the listener.trc file.
Since the listener was up only long enough to allow me to force a dynamic registration, the only information in the trace that identifies my database would necessarily be those associated with the dynamic registration. So with the trace file open in notepad, I simply search for the name of my database. Sure enough, the very first occurance of the database name was in the dump of a packet being received by the listener (nsprecv: packet dump). And in that packet was information
(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=/u01/app/oracle/product/11.2.0/db_1/bin/oracle)(ARGV0=oracleorcl)
However, looking at the rest of the packet in context, it looks like it was simply the client process (the pmon process of the db instance) identifying itself, rather than the kind of information we think would answer your question. Even without a good understanding of the details of these deep traces it is often possible to pull out some useful information, but I’m afraid this isn’t one of them. Let’s post the question on OTN and see what kind of discussion it generates.
nice and clearly………………………
Very well written,
Many thanks
Such a great article!!!
Really informative with clear examples.
Good job and keep up the good work!
So clear explanation, Thanks a lot. This connection method is used fraudulently. Attack is know as TNS Listener Poison Attack – CVE-2012-1675. How to prevent this attack.?
Best regards
CVE-2012-1675 is addressed in My Oracle Support note 1453883.1. As such, it would violate the terms of use agreement at MOS to explain it here.
A brilliantly clear series of posts that helped clarify a number of things for me. Thank you very much for taking the time to write it.
I am having a problem that I don’t think is listener related but may be. Enterprise Manager Express is unreachable on my 12c install. I think this may be particular issue when ASM is used and the GI listener is used instead of the database home (just a hunch – but its fine on my non-ASM 12c installs on identical kit).
I am struggling to find out more about how Enterprise Manager Express works with the listener and how I can investigate it further (where it’s log files are, etc).
My question and more detail is posted here (if you were to have a free moment):
https://community.oracle.com/thread/3739356
Unfortunately I don’t yet have any hands-on with 12c EM. I’ll respond to your OTN posting there.
After spending many hours trying to establish connection to DRCP from remote or local client using the tnsnames.ora file I finaly got it working. And then I found your posts. Thank you for the clear and transparent explanations. I wish that Oracle documentation was as clear as your posts!
Reblogged this on EasyOraDBA | Shadab Mohammad.
I have a question here
I am trying to remove the entry listener.ora and remove the entry of local_listener from spfile.ora so that i can start my instance and then check if it’s connecting.
But when i try to hit ‘startup’..
it gives me error which says that it can’t open the parameter file ‘initorcl.ora’….
But actually this file is not present itself at that location but spfileorcl.ora is present at the location.
However if i reinsert the entry of ‘local_listener’ in spfile.ora and introduce the entry in tnsnames.ora..
it starts the instance and listeners now knows about it without the listener.ora file…
What is going on here?
Note i am using the default port 1521..
It’s hard to say. What version and edition of Oracle? What operating system?
I don’t understand what you mean by “remove the entry listener.ora”. Remove it from what?
I don’t know what you mean by “hit startup”. It sounds like you are trying to start your database from some GUI front end. I don’t know what that is nor how it would actually invoke a startup and specify the parameter file. I always start my databases from a command prompt with sqlplus, so that I have full knowledge and full control over what is happening.
How do you “reinsert the entry of ‘local_listener’ in spfile.ora “?
By the way, I am in the progress of moving all of these demos to my new self-hosted site at edstevensdba.com. This subject has not yet been moved, but please note for future reference.
I have an requirement where I have to create Vm template. wheneve I deploy the VM from that template the oracle 12c should get install in silent mode and local_listener should automatically get updated. I am able to install oracle 12c in silent mode but struggling to configure local listener automatically. can somebody please help me?
This article has been moved to my new blog at edstevensdba.com.
Click here to go to the new location of this article.
Wow, this is really well written and has helped me out hugely. Thank you very much, it was a pleasure to read.
Best explanation I have ever seen for understanding Oracle’s networking.
Thanks for your kind comment. Actually, I retired little over a year ago, but leave this site up because it seems that it continues to prove useful. Note the date on the post to which you refer! 🙂
Well explained. Very helpful. Thank you..