Exploring the LOCAL_LISTENER parameter

In my previous post ORA-12514: TNS:listener does not currently know of service 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 of a database instance. 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:

[oracle@lnxsrv01 admin]$ cat listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /ora00/app/oracle/product/11.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME=OKLACITY)
      (ORACLE_HOME = /ora00/app/oracle/product/11.2.0/db_1)
      (SID_NAME = OKLACITY)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnxsrv01.vmdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )
[oracle@lnxsrv01 admin]$

Fig. 1

And that static registration shows up in the listener status with a status of UNKNOWN:

[oracle@lnxsrv01 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 28-JUL-2011 20:20:48

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=lnxsrv01.vmdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                28-JUL-2011 20:20:42
Uptime                    0 days 0 hr. 0 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /ora00/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /ora00/app/oracle/diag/tnslsnr/lnxsrv01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lnxsrv01.vmdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "OKLACITY" has 1 instance(s).
  Instance "OKLACITY", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@lnxsrv01 admin]$

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 was no longer necessary to list the database instance in the listener.ora file.  Instead, the database instance could contact the listener directly and register 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@lnxsrv01 admin]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 28-JUL-2011 20:21:56

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=lnxsrv01.vmdomain)(PORT=1521)))
The command completed successfully
[oracle@lnxsrv01 admin]$ mv listener.ora listener.sav
[oracle@lnxsrv01 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 28-JUL-2011 20:22:26

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

Starting /ora00/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Log messages written to /ora00/app/oracle/diag/tnslsnr/lnxsrv01/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lnxsrv01.vmdomain)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                28-JUL-2011 20:22:26
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /ora00/app/oracle/diag/tnslsnr/lnxsrv01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lnxsrv01.vmdomain)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@lnxsrv01 admin]$

Fig. 3

So we can see in Fig. 3, line 35 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 oklacity

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 28-JUL-2
011 20:23:43

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 = lnxsrv01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = OKLACITY)))
OK (20 msec)

C:\>sqlplus scott/tiger@oklacity

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 28 20:23:53 2011

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 ‘OKLACITY’.

Now let’s start the instance and check again:

[oracle@lnxsrv01 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jul 28 20:25:31 2011

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1347480 bytes
Variable Size             637534312 bytes
Database Buffers          205520896 bytes
Redo Buffers                5128192 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnxsrv01 admin]$
[oracle@lnxsrv01 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 28-JUL-2011 20:26:38

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                28-JUL-2011 20:22:26
Uptime                    0 days 0 hr. 4 min. 12 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /ora00/app/oracle/diag/tnslsnr/lnxsrv01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lnxsrv01.vmdomain)(PORT=1521)))
Services Summary...
Service "OKLACITY" has 1 instance(s).
  Instance "OKLACITY", status READY, has 1 handler(s) for this service...
Service "OKLACITYXDB" has 1 instance(s).
  Instance "OKLACITY", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@lnxsrv01 admin]$

Fig. 5

In Fig. 5 we observe that once the instance is started (line 9), when we re-check the listener (line 23) it now knows of service “OKLACITY”, with a status of READY (line 44). 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@oklacity

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 28 20:26:49 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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 11g Release 2 (11.2) 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.

[oracle@lnxsrv01 admin]$ cat listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnxsrv01.vmdomain)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )
[oracle@lnxsrv01 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 28-JUL-2011 20:30:53

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

Starting /ora00/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.2.0 - Production
System parameter file is /ora00/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /ora00/app/oracle/diag/tnslsnr/lnxsrv01/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lnxsrv01.vmdomain)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=lnxsrv01.vmdomain)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                28-JUL-2011 20:30:53
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /ora00/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /ora00/app/oracle/diag/tnslsnr/lnxsrv01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lnxsrv01.vmdomain)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
The listener supports no services
The command completed successfully
[oracle@lnxsrv01 admin]$

Fig. 8

At this point my listener is up, listening on the non-default port of 1522 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.

[oracle@lnxsrv01 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jul 28 20:31:50 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system register;

System altered.

SQL>

Fig. 9

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@lnxsrv01 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 28-JUL-2011 20:32:45

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=lnxsrv01.vmdomain)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                28-JUL-2011 20:30:53
Uptime                    0 days 0 hr. 1 min. 52 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /ora00/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /ora00/app/oracle/diag/tnslsnr/lnxsrv01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lnxsrv01.vmdomain)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
The listener supports no services
The command completed successfully
[oracle@lnxsrv01 admin]$

Fig. 10

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@lnxsrv01 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jul 28 20:33:23 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL=TCP)(HOST=lnxsrv01)(PORT=1522))' scope=both;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnxsrv01 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 28-JUL-2011 20:34:12

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=lnxsrv01.vmdomain)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                28-JUL-2011 20:30:53
Uptime                    0 days 0 hr. 3 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /ora00/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /ora00/app/oracle/diag/tnslsnr/lnxsrv01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lnxsrv01.vmdomain)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "OKLACITY" has 1 instance(s).
  Instance "OKLACITY", status READY, has 1 handler(s) for this service...
Service "OKLACITYXDB" has 1 instance(s).
  Instance "OKLACITY", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@lnxsrv01 admin]$

Fig. 11

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@lnxsrv01 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: $ORACLE_HOME/network/admin

FUBAR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnxsrv01.vmdomain)(PORT = 1522))
    )
  )

OKLACITY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnxsrv01.vmdomain)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = OKLACITY)
    )
  )
[oracle@lnxsrv01 admin]$ tnsping fubar

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 28-JUL-2011 20:37:00

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = lnxsrv01.vmdomain)(PORT = 1522))))
OK (10 msec)
[oracle@lnxsrv01 admin]$

Fig. 12

In reality I’d want to make the tnsnames alias something more meaningful, but here I wanted to use a name that would 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:

[oracle@lnxsrv01 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jul 28 20:40:03 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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. 13

Next, I’ll restart the listener, in order to flush the current registrations and start clean.

SQL> !lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 28-JUL-2011 20:41:19

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=lnxsrv01.vmdomain)(PORT=1522)))
The command completed successfully

SQL>
SQL> !lsnrctl start

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 28-JUL-2011 20:41:48

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

Starting /ora00/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.2.0 - Production
System parameter file is /ora00/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /ora00/app/oracle/diag/tnslsnr/lnxsrv01/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lnxsrv01.vmdomain)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=lnxsrv01.vmdomain)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                28-JUL-2011 20:41:48
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /ora00/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /ora00/app/oracle/diag/tnslsnr/lnxsrv01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lnxsrv01.vmdomain)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
The listener supports no services
The command completed successfully

Fig. 14

Finally, I’ll force a registration of the instance, then recheck the listener status.

SQL> alter system register;

System altered.

SQL>
SQL> !lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 28-JUL-2011 20:42:49

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=lnxsrv01.vmdomain)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                28-JUL-2011 20:41:48
Uptime                    0 days 0 hr. 1 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /ora00/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /ora00/app/oracle/diag/tnslsnr/lnxsrv01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lnxsrv01.vmdomain)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "OKLACITY" has 1 instance(s).
  Instance "OKLACITY", status READY, has 1 handler(s) for this service...
Service "OKLACITYXDB" has 1 instance(s).
  Instance "OKLACITY", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL>

Fig. 15

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.

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

About these ads

22 thoughts on “Exploring the LOCAL_LISTENER parameter

  1. 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

  2. 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.

  3. 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.

  4. 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 http://edstevensdba.wordpress.com/category/opinion/.

  5. 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!

  6. Pingback: Oracle 11g Release 2: ORA-00119: invalid specification for system parameter LOCAL_LISTENER « Blog about cooking, technology and something else

  7. 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.

  8. Pingback: Non-Default Oracle Listener | Chris Gilbert

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s