11 Replies Latest reply on Jul 22, 2016 12:26 PM by Nelate

    Oracle Net Service Configuration

    Nelate

      I was going through the Oracle Net Service administrator guide and I came across this statement a number of times

      "Resolve the listener name alias for the LOCAL_LISTENER and REMOTE_LISTENER setting through a tnsnames.ora file on the database host."


      e.g. Refer to Example 9-1 in the document. https://docs.oracle.com/database/121/NETAG/listenercfg.htm#NETAG302


      From my understanding (and apparently limited experience) the only time I interact with the tnsnames.ora file is when I want to register services. All listener configuration information is in the listener.ora file. So if someone could please explain to me the meaning of the above statement I would appreciate very much. What is the essence of the entry made in the tnsname.ora file with regards to the listener? Is it to be used as a net service name?


       

        • 1. Re: Oracle Net Service Configuration
          top.gun

          The link you pasted refers to a remote listener. ie the listener is on a different host to the DB.

           

          To configure the database to automatically dynamically register with the local listener.... and if using the default listener name and port 1521, then just add the listener to the tnsnames.ora file.

          LISTENER_LISTENER = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

          • 2. Re: Oracle Net Service Configuration
            Nelate

            Thanks for the respose Top.gun. Sorry about the link. Maybe this one https://docs.oracle.com/database/121/NETAG/listenercfg.htm#NETAG1154 is better.

            So when you say

            "....and if using the default listener name and port 1521, then just add the listener to the tnsnames.ora file."

             

            does it mean that we can work without the listener.ora file?

            And sorry for asking this but is dynamic registration 12c feature only or did it start in previous versions? I just came across it recently. Worked with 11g but never heard of it

            • 3. Re: Oracle Net Service Configuration
              top.gun

              Dynamic registration has always been around.

              I have this in my listener.ora file

               

              LISTENER =

                (DESCRIPTION_LIST =

                  (DESCRIPTION =

                    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

                  )

                )

              • 4. Re: Oracle Net Service Configuration
                Nelate

                Ok. So the normal entry in the listener.ora file implies dynamic registration? I thought it was somehow linked to why we make those entries in the tnsnames.ora file. If so, can you please demonstrate. So a demo of my understanding is as follows. I use netmgr to create a listener, do database registration and create a service. May be this will show why my mind seems to be blocked on the listener entries in the tnsname file.

                So I create a new listener LISTENER_TEST from netmgr. The following becomes my entry in the listener.ora file (including the default listener LISTENER)

                LISTENER_TEST =

                  (DESCRIPTION =

                    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1421))

                  )

                )

                LISTENER =

                  (DESCRIPTION_LIST =

                    (DESCRIPTION =

                      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

                    )

                    (DESCRIPTION =

                      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

                    )

                  )

                 

                After I do a database service registration i get this

                 

                LISTENER_TEST =

                  (DESCRIPTION =

                    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1421))

                  )

                 

                SID_LIST_LISTENER_TEST =

                  (SID_LIST =

                    (SID_DESC =

                      (GLOBAL_DBNAME = Prod12c)

                      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)

                      (SID_NAME = Prod12c)

                    )

                  )

                 

                ADR_BASE_LISTENER_TEST = /u01/app/oracle

                 

                LISTENER =

                  (DESCRIPTION_LIST =

                    (DESCRIPTION =

                      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

                    )

                    (DESCRIPTION =

                      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

                    )

                  )

                 

                All these are in the listener.ora file. The tnsname.ora has not been modified

                 

                I then create a service for a pdb and the following gets added to my tnsnames.ora

                TEST =

                  (DESCRIPTION =

                    (ADDRESS_LIST =

                      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1421))

                    )

                    (CONNECT_DATA =

                      (SERVICE_NAME = pdb1)

                    )

                  )


                So the reason I didnt understand dynamic registration is because I thought with it then the part

                SID_LIST_LISTENER1 =

                  (SID_LIST =

                    (SID_DESC =

                      (GLOBAL_DBNAME = Prod12c)

                      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)

                      (SID_NAME = Prod12c)

                    )

                  )

                Is unnecessary for any listener added to the tnsnames.ora. So, why have a listener entry in the tnsnames.ora?

                • 5. Re: Oracle Net Service Configuration
                  top.gun

                  The key is to use a listener with a default name (not test), and a default port 1521 (not 1421).

                  If you deviate from the defaults then you do need to define the listener in the listener.ora and tnsnames.ora.

                  • 6. Re: Oracle Net Service Configuration
                    EdStevens

                    Nelate wrote:

                     

                    Ok. So the normal entry in the listener.ora file implies dynamic registration? I thought it was somehow linked to why we make those entries in the tnsnames.ora file. If so, can you please demonstrate. So a demo of my understanding is as follows. I use netmgr to create a listener, do database registration and create a service. May be this will show why my mind seems to be blocked on the listener entries in the tnsname file.

                    So I create a new listener LISTENER_TEST from netmgr. The following becomes my entry in the listener.ora file (including the default listener LISTENER)

                    LISTENER_TEST =

                      (DESCRIPTION =

                        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1421))

                      )

                    )

                    LISTENER =

                      (DESCRIPTION_LIST =

                        (DESCRIPTION =

                          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

                        )

                        (DESCRIPTION =

                          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

                        )

                      )

                     

                    After I do a database service registration i get this

                     

                    LISTENER_TEST =

                      (DESCRIPTION =

                        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1421))

                      )

                     

                    SID_LIST_LISTENER_TEST =

                      (SID_LIST =

                        (SID_DESC =

                          (GLOBAL_DBNAME = Prod12c)

                          (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)

                          (SID_NAME = Prod12c)

                        )

                      )

                     

                    ADR_BASE_LISTENER_TEST = /u01/app/oracle

                     

                    LISTENER =

                      (DESCRIPTION_LIST =

                        (DESCRIPTION =

                          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

                        )

                        (DESCRIPTION =

                          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

                        )

                      )

                     

                    All these are in the listener.ora file. The tnsname.ora has not been modified

                     

                    I then create a service for a pdb and the following gets added to my tnsnames.ora

                    TEST =

                      (DESCRIPTION =

                        (ADDRESS_LIST =

                          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1421))

                        )

                        (CONNECT_DATA =

                          (SERVICE_NAME = pdb1)

                        )

                      )


                    So the reason I didnt understand dynamic registration is because I thought with it then the part

                    SID_LIST_LISTENER1 =

                      (SID_LIST =

                        (SID_DESC =

                          (GLOBAL_DBNAME = Prod12c)

                          (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)

                          (SID_NAME = Prod12c)

                        )

                      )

                    Is unnecessary for any listener added to the tnsnames.ora. So, why have a listener entry in the tnsnames.ora?

                    A few basics:

                     

                    1) the listener itself doesn't give a flying fig about what is in tnsnames.ora.  That file (tnsnames.ora) is used ONLY by client processes.  In the case of dynamic registration, the database IS the client process.

                     

                    2) the listener is quite capable of starting with no listener.ora file at all.  In this case, it will start with all default values, including the default name of LISTENER and default port of 1521. For most people, most of the time, this is sufficient.

                     

                    3) If the local_listener parameter is not set (null) the database will try a default registration, sending the request to port 1521.  Notice that 1521 is also the default port of of the listener. 

                     

                    4) if you choose to set local_listener, you can either use a full connect string ((ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1421))) or simply reference an entry in tnsnames.ora, which should resolve to the address (server name and port) of the listener.

                     

                    5) The SID_LIST section of listener.ora has NOTHING to do with dynamic registration.  Quite the opposite.  The SID_LIST section is how you implement STATIC registration.

                     

                    (As others observed, why is your listener on port 1421?  )

                    • 7. Re: Oracle Net Service Configuration
                      Nelate

                      Thanks top.gun. Finally I have understood the concept. Also after a little digging and finding this post and this one with some good examples on static and dynamic registration. Didn't know you can acctualy  I wish it had some examples on those entries in tnsnames.ora though.

                      • 8. Re: Oracle Net Service Configuration
                        Nelate

                        Now it makes sense. Your point number 4 "if you choose to set local_listener, you can ... or simply reference an entry in tnsnames.ora..." explains what has been bugging me. And I created the listener with port 1421 for testing. Didn't really understand why things seemed to be working fine with the default port. Thanks for making things clear

                        • 9. Re: Oracle Net Service Configuration
                          top.gun

                          ok - so were any of these replies helpful to solve your problem?

                          • 10. Re: Oracle Net Service Configuration
                            robinsc

                            Basically when the database starts up pmon will register all services with the listeners that are defined in local listener and remote listener. This is dynamic registration. If you want other listeneres to know about the database and its servioces you can add static entries in listener.ora file. these kind of entries are essential if you want to do things like starting up a database over a network connection etc.

                             

                            Local listener and remote listener help in dynamic load balancing in case of RAC databases as pmon will send information to all the listenrs mentoined in remote -Listeners and this allows those lkisteners to independantly channel client connections to the least loaded instance ( actually the instance will the smallest number of connections at that given time.)

                            • 11. Re: Oracle Net Service Configuration
                              Nelate

                              Yes. Very much. Thank you