Exploring the Oracle LOCAL_LISTENER Parameter

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?”

Please like us:

25 Comments


  1. // Reply

    Thanks for your blog on local_listener, particularly your comment on default value of local_listener. With the help of your blog, I was able to start another listener.ora with different port#, create tnsnames entry, set it to local_listener and issue alter system register. It allowed me to access different services which were already created for different pdbs I was experimenting with.

    thanks again!


    1. // Reply

      I’m glad you found it helpful. I am a bit curious as to why you want an additional listener with a different port. A single listener is quite capable of –i indeed, was designed to — service multiple databases of multiple versions running from multiple homes. I know there are a few cases where it might be desirable to have multiple listeners, but they are very, very few. I’d be happy to discuss your reasoning and see if there’s something we can both learn from it.


  2. // Reply

    Thank you so much for this! Learning what the default values are enabled me to track down a connection issue that had been plaguing me all week.


  3. // Reply

    Very nice article. Just a friendly suggestion, you may want to put it under tag or category “Oracle_TNS” since this was part of entire tns series.


    1. // Reply

      Good suggestion. I have made the change.


  4. // Reply

    Excellent article! Thank you. I’m a long-time Oracle DBA, but I’ve just been struggling with getting a single listener working as desired on a new, single-node (non-RAC) 12c container database server that uses ASM. I had no ASM experience before this, so I wasn’t used to having the listener in the Grid_home and the database running from a separate Oracle_home. Your article clearly explains how this can work. The important parts are: a tnsnames.ora entry for the listener, setting the “local_listener” parameter to that tns entry, then running the “alter system register” command.


    1. // Reply

      Mark – I’m glad you found the article helpful.
      Actually, you don’t really need the tnsnames entry. If you’ll notice, all that really resolves to is a host (ip address) and a port. The database registration doesn’t know or care which ORACLE_HOME the listener is running out of. It just sends a request to the specified port on the specified host. If there is a listener on that host, listening on that port, we’re good to go. And by default, if there is no value for LOCAL_LISTENER, the instance will send the registration notice to port 1521. I really don’t know why Oracle specifies that the listener should run from the GI home, but since they do, I do.


  5. // Reply

    Another excellent article. As an Oracle developer not DBA I am still required to undertake these tasks and find it frustrating. In my case I needed to read the previous 2 articles and this one in order to diagnose the problem. This is the clearest explanation I have seen. It really shows the inadequacies of Oracle diagnostics and their refusal to provide out of the box solutions. It all starts broken and works if you are lucky, At last some clarity.


    1. // Reply

      I’m glad my articles could help clarify things. I won’t get into a debate over your comments about Oracle itself. Just keep in mind that it is implemented on multiple, disparate platforms (Windows, multiple unix/linux, IBM Z-OS, etc.) and as such is not developed with a Windows-centric view of the world. I will admit that sometimes I find the official docs to be a bit of “you have to understand everything to be able to understand anything”. But that comes from providing everything. I find MS documentation to be at the opposite end of the spectrum. I see their approach one of “click here. If the action we built into the icon doesn’t do what you want, then you want the wrong thing. Don’t ask what it’s doing under the covers. You don’t need to know. It just works.” Until it doesn’t “just work”. 🙂


  6. // Reply

    Well there is a teensy it of a debate afoot all the same, I shall be brief. I can see the other point of view, including multiple platforms and configurations,and agree MS i the other extreme. You can;t roll your sleeves up and call for the hot water and towels when that does wrong. Yet what you have provided here is somewhat at odds with your defence of our giant corporation. You have worked examples, diagnostics, and an inclusion of all relevant context as oppose to being tossed into another document, for which I commend you.


    1. // Reply

      Point taken. I will admit that it can be hard to pull all the pieces together from the Oracle docs, and that is what I try to do here.


  7. // Reply

    I am suddenly getting error “TNS: listener does not currently know of service…” whatever I do. I tried to follow exercises in this article but the error is always there – e.g. for: sqlplus / as sysdba. It is on Oracle DB Developer VM box with default cdb1 instance and orcl. They are not registering with listener. How can I tell that oracle is running at all?
    Thank you for any help.


    1. // Reply

      You should not be getting any listener errors for a simple ‘sqlplus / a sysdba’. That connection syntax is requesting a bequeath connection that does not use the listener. You say you are “suddenly” getting this error. When was the last time it worked and what has changed since? Since you say this is an “Oracle DB Developer VM box” I assume it is running Linux. With that, you can tell the db is at least in START mode by issuing ‘ps -ef | grep pmon ‘. That should list the pmon process for any started databases. I could tell more if you would show the output of that command, as well as the following:

      env | grep ORA

      lsnrctl status


      1. // Reply

        Thank you for helping, here are the details:

        ====
        There were no software changes from last time it was working, last thing I was copying a database from other VM using SQL Developer, no issue that I am aware of, then I was not using it for a few months just now

        ====
        [oracle@localhost ~]$ ps -ef | grep pmon
        oracle 2532 2186 0 09:11 pts/0 00:00:00 grep –color=auto pmon

        ====
        [oracle@localhost ~]$ ps -ef | grep pmon
        oracle 2538 2186 0 09:11 pts/0 00:00:00 grep –color=auto pmon (is different PID each time OK?)

        ====
        [oracle@localhost ~]$ env | grep ORA
        ORACLE_SID=cdb1
        ORACLE_HOME=/home/oracle/app/oracle/product/12.1.0/dbhome_1

        ====
        [oracle@localhost ~]$ lsnrctl status
        LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 29-SEP-2016 09:15:50
        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 29-SEP-2016 09:01:22
        Uptime 0 days 0 hr. 14 min. 28 sec
        Trace Level off
        Security ON: Local OS Authentication
        SNMP OFF
        Listener Log File /home/oracle/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
        Listening Endpoints Summary…
        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
        The listener supports no services
        The command completed successfully

        ====
        [oracle@localhost ~]$ sqlplus / as sysdba
        SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 29 09:22:41 2016
        Copyright (c) 1982, 2014, Oracle. All rights reserved.
        ERROR:
        ORA-12514: TNS:listener does not currently know of service requested in connect
        descriptor
        Enter user-name: (asking for name, why??? providing system/oracle getting same listener error again)
        Enter user-name: system
        Enter password:
        ERROR:
        ORA-12514: TNS:listener does not currently know of service requested in connect
        descriptor


        1. // Reply

          The only way I can reproduce a tns error with a simple ‘sqlplus / as sysdba” (not specifying a net service name) is if I also export the variable TWO_TASK. See my article Another Ora-12514 and TWO_TASK.


          1. //

            Hello,
            db is not registered against listener and there is no process indicating oracle db is running.
            Look at /etc/oratab for the name of db/instance.
            If you are in RAC env, you have to connect to each instance (instance name included instance number).
            set ORACLE_HOME with what you find (db_name or instance_name)
            issue “. oraenv”
            try you sqlplus / as sysdba
            Kind regards.


          2. //

            I’m not sure if you are asking a question or making a statement, or what either might be . . . could you clarify?


      2. // Reply

        startup.log shows:

        /home/oracle/app/oracle/product/12.1.0/dbhome_1/bin/dbstart: Starting up database “cdb1”
        Thu Sep 29 12:05:21 AEST 2016

        SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 29 12:05:22 2016
        Copyright (c) 1982, 2014, Oracle. All rights reserved.
        SQL> ERROR:
        ORA-01017: invalid username/password; logon denied
        SQL> SP2-0640: Not connected
        SQL>
        /home/oracle/app/oracle/product/12.1.0/dbhome_1/bin/dbstart: Database instance “cdb1” warm started.


        1. // Reply

          I’ve never relied on dbstart to startup my databases. My feeling is that if I’ve had to shut down a server, I want to check things out after the restart, before I start the databases. But giving that script a cursory look, I’m inclined to believe that the problem stems from having set TWO_TASK, as mentioned in my previous reply. It will take me some time to try to work out a definitive answer.


  8. // Reply

    Hey Ed-

    Thanks for the concise information. After reading this article, I no longer have to stumbled through my TNS configuration steps; and I just might be better at troubleshooting these kind of things in the future.


    1. // Reply

      Glad you found it useful.


  9. // Reply

    Excellent Post..! Very Much Appreciated Stevens.

    It is very clear and easy to understand.


  10. // Reply

    Thanks. Helped me in diagnosing a 12cR2 installation where I’d got (not sure, yet, if it was me or the installer) a value in LOCAL_LISTENER pointing at a TNS entry, which was obviously hard coded to the local host and which, when I cloned the container (I run various containers on Proxmox), it didn’t work. I don’t think I had to do anything special on 12cR1 to get this to work, so I’m wondering if things changed between R1 and R2 of 12.

    Anyway, by blanking out the LOCAL_LISTENER value I got things to work nicely, so thank you.


    1. // Reply

      I’ve not seen 12.2 and haven’t dipped my toes into 12.1 very much, but I can’t imagine any of this changing there. I haven’t seen it change anywhere across any of the other versions I’ve worked with. Glad the article helped. Without being able to observe everything you did along the way, it would be hard to speculate on why it didn’t work the first time.

Leave a Reply

Your email address will not be published. Required fields are marked *