Exploring the Oracle LOCAL_LISTENER Parameter

This content has been relocated to edstevens.wordpress.com.

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


  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.


  11. // Reply

    This page is great. I love it! It has given me a much better understanding of the Oracle Listener and how it work. The step by step instructions are spot on.


  12. // Reply

    On Windows, I have the situation where the instance does not register with the listener and I suspect it’s because an incorrect value in local_listener. But I can’t connect to the instance because sqlplus refuses to connect (“TNS:listener does not currently know of service requeste…”). So I can’t check the value of local_listener in order to fix listener.ora (someone messed with configuration without knowing what he was doing)


    1. // Reply

      You have to use a local, interprocess connection – that does not use the listener.
      Log on to the machine that is running the database, using the logon credentials of an OS user that is a member of the ‘ORA_DBA’ group.
      Then do the following:

      c:> set ORACLE_SID=
      c:> sqlplus / as sysdba

      That should give you a local, interprocess connection and you can then do whatever you need with LOCAL_LISTENER.


  13. // Reply

    Hello Ed,

    Thanks for a great article with detailed explanations. We’ve a 2 node RAC cluster that hosts about 5 databases. All of them are registered to the same default listener, listening at port 1521. We’ve a need to set a different listener for one of the databases for some testing.

    I configured listener.ora and tnsnames.ora and started the listener on both the nodes. By default, the local_listener was set to ‘(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))’ and ‘(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))’ respectively. And remote_listener parameter was set to “:1521″ on both the instances.

    I tried to update it as follows :

    > alter system set local_listener = ” scope=both sid = ” ;
    > alter system remote_listener=’:1541′ scope=both sid = ‘*’ ;
    > alter system register ;

    I tried this on both the instances (using the sid value accordingly), but the local_listener didn’t change at all. It was showing the old value only. But the remote_listener had been changed.

    I tried bouncing a few times, but no luck yet.

    Can you explain this behavior ? How both the parameters work together and affect each other ? How can I fix this issue ?

    Thanks


    1. // Reply

      Sean –
      A shortcoming of my career is that I’ve never had the opportunity to work with RAC. That said, your syntax for specifying SID= caught my attention. And after assembling a short test, discovered that a null string, like you specified doesn’t work.:

      First, I’ll establish a starting postion and show the result


      SQL> alter system set local_listener='' scope=both;

      System altered.

      SQL> show parameter local_listener

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      local_listener string
      SQL> --

      Then, we’ll set it with your SID spec and look at the result

      SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))' scope=both sid='';

      System altered.

      SQL> show parameter local_listener

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      local_listener string

      No change.
      Now set it with SID=’*’

      SQL> --
      SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))' scope=both sid='*';

      System altered.

      SQL> show parameter local_listener

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      local_listener string (ADDRESS=(PROTOCOL=TCP)(HOST=)
      (PORT=1521))
      SQL> --

      That time it “took”.
      Now clear it:


      SQL> alter system set local_listener='' scope=both sid='*';

      System altered.
      SQL> show parameter local_listener

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      local_listener string
      SQL> spo off

      And, I don’t see in the docs where sid=” (null string) is supported. What I do see is this:

      SID

      The SID clause lets you specify the SID of the instance where the value will take effect.

      Specify SID = ‘*’ if you want Oracle Database to change the value of the parameter for all instances that do not already have an explicit setting for this parameter.

      Specify SID = ‘sid’ if you want Oracle Database to change the value of the parameter only for the instance sid. This setting takes precedence over previous and subsequent ALTER SYSTEM SET statements that specify SID = ‘*’.

      If you do not specify this clause, then:

      If the instance was started up with a pfile (traditional plain-text initialization parameter file), then Oracle Database assumes the SID of the current instance.

      If the instance was started up with an spfile (server parameter file), then Oracle Database assumes SID = ‘*’.

      If you specify an instance other than the current instance, then Oracle Database sends a message to that instance to change the parameter value in the memory of that instance.

      To quote Tim Hall, “Hope this helps”.


  14. // Reply

    Hi Ed, thanks for the excellent write-up on this topic! That said, I do have a question.

    Situation: A listener is born. It is not in the directory structure of any database–it’s installed on it’s very own drive. Junior DBA #173 installs a new Oracle DB on a different drive and wants to use said listener. As the listener is managed by someone else (Junior DBA #58), DBA #173 is given a TNSNAMES alias to use for the LOCAL_LISTENER parameter.

    Question: How does his database know where to find the TNSNAMES file to begin with?

    Is the assumption here that his database will need to have a configured LISTENER.ORA or TNSNAMES file that will direct to the listener in question? (If that’s the case, why? I thought the DB and the Listener were two separate “things” entirely. I.E., I should be able to delete all the files from the \NETWORK\ADMIN directory and the DB would just keep on truckin’.)


    1. // Reply

      It appears you have several mis-conceptions here. What do you mean that a listener is “installed in its very own drive”. A listener is a process. The binary, executable files for that process are located under $ORACLE_HOME. Do you mean that DBA #173 installed an entire new ORACLE_HOME just to run a listener? Do not confuse the process with the file that it uses to get its run-time values. You state “Is the assumption here that his database will need to have a configured LISTENER.ORA .” The database instance does not use the listener.ora file at all. That is used strictly by the listener, and only when the listener starts. When contacting the listener process to register, the database instance is acting just like any other client process that needs to contact the listener. If LOCAL_LISTENER is set to a fully qualified address, then it doesn’t need tnsnames.ora to resolve it. If it uses a net service name, then it resolves it by locating a tnsnames.ora, just like any client process going through the listener to get a connection. First it looks in the current directory of the proces. If not found there, it checks for a settng of TNS_ADMIN in the environment the db instance is running in and check there. Last, it will look in $ORACLE_HOME/network/admin, with $ORACLE_HOME being that of the database making the request. You are correct in that the db instance and the listener are two different “things” (processes). But they both, by default, any TNS related files (sqlnet.ora and tnsnames.ora for the db and sqlnet.ora and listener.ora for the listener) are located in $ORACLE_HOME/network/admin. If you drop tnsnames.ora, the database will keep on truckin’, but will most likely NOT be able to continue to register itself with the listener, so the listener will not know about the database and not be able to service connection requests. If you drop listener.ora, the listener will start with all default values, which should be sufficient for the vast majority of installations.

      Your questions also seem to suggest that you think each database should have its own listener. Nothing could be further from the truth. One single listener, running on the default port of 1521 and using the default name of LISTENER is quite capable of – indeed, was designed to – service multiple databases of multiple versions running from multiple ORACLE_HOMEs.


  15. // Reply

    Ed, I’ve been head-banging for days on a -12514 error. Googling eventually turned up this series of articles, which saved my life.

    I must say they are the best technical articles on oracle I’ve ever seen, clear and detailed explanations and excellent examples.

    some long time ago I had the misfortune of having to learn how to code a db client using OCI, with nothing but the OCI programmer’s reference manual, having previously had no experience with oracle or any other database, and I wished many times to have explanations as good as what you’ve given here.

    thanks a bunch!


    1. // Reply

      I’m glad you found it helpful.

Leave a Reply

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