1 2 Previous Next 23 Replies Latest reply: Jun 1, 2012 2:25 AM by AlleT RSS

    Attach a listener port to a specific database instance

    171663
      Hello,

      I have two 11g databases in the same host, with service names db1 and db2.

      The aim is to configure a listener, with two listening ports (1521 and 1522), but 1521 only attached to db1, and 1522 only attached to db2. That is, connections to 1522 port and service name db1 should be rejected, and so with connections to 1521 port and service name db2. The host has only a net interface.

      I am unable to configure that properly. These are my settings:

      In listener.ora:
      LISTENER_DB1= (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST = <hostname>)(PORT = 1521)))
      LISTENER_DB2= (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST = <hostname>)(PORT = 1522)))

      In tnsnames.ora:
      LISTENER_DB1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=<hostname>)(Port = 1521)))
      LISTENER_DB2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=<hostname>)(Port = 1522)))

      In instance db1, the local_listener parameter was modified:
      SQL> alter system set local_listener='LISTENER_DB1';

      In instance db2, the local_listener parameter was modified:
      SQL> alter system set local_listener='LISTENER_DB2';


      With this setup, connections to 1521 port and service name db1 are succesfull, as well as connections to 1522 and service name db2.

      But connections to 1522 port and service name db1 are not rejected (indeed, they are successfull, and connect to the proper instance db1), as well as connections to 1521 port and service name db2.

      Any comment will be wellcome.

      Best regards
        • 1. Re: Attach a listener port to a specific database instance
          mBk77
          Only 1 listner is enough for both the databases, however if you want to configur more than 1 listner use (NETCA or ORACLE NET MANAGER) also go through the link below ....

          http://docs.oracle.com/cd/E11882_01/network.112/e10836/listenercfg.htm
          • 2. Re: Attach a listener port to a specific database instance
            AlleT
            check which databases have registered with each listener with

            lsnrctl status

            Did you bounce the databases after having configured the local_listener parameters?
            • 3. Re: Attach a listener port to a specific database instance
              sybrand_b
              By default Oracle runs a listener on port 1521, and as far as I know every database will register against the default port.

              So the question is what you exactly are trying to accomplish. You don't need two listeners!

              ------------
              Sybrand Bakker
              Senior Oracle DBA
              • 4. Re: Attach a listener port to a specific database instance
                171663
                AlleT wrote:
                check which databases have registered with each listener with

                lsnrctl status

                Did you bounce the databases after having configured the local_listener parameters?
                Thanks for your response.

                lsnrctl status outputs:

                ..............
                Listening Endpoints Summary...
                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>)(PORT=1522)))
                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>)(PORT=1521)))
                Services Summary...
                Service "db1" has 1 instance(s).
                Instance "db1", status READY, has 1 handler(s) for this service...
                Service "db2" has 1 instance(s).
                Instance "db1", status READY, has 1 handler(s) for this service...

                Thus, it seems to be that both service names are attached to the both listener ports, despite of local_listener parameter.

                I do not understand the sentence: "bounce the database". Database is up and running, with the local_listener parameter as I said before.

                Do you think that it is possible to achieve my requeriments? Or am I trying to do something weird? My customer needs a such that configuration: "a listener port only serves to a database, not several".

                Regards
                • 5. Re: Attach a listener port to a specific database instance
                  171663
                  mBk77 wrote:
                  Only 1 listner is enough for both the databases, however if you want to configur more than 1 listner use (NETCA or ORACLE NET MANAGER) also go through the link below ....

                  http://docs.oracle.com/cd/E11882_01/network.112/e10836/listenercfg.htm
                  Thanks for your response

                  I have read the documentation many times, but it is not clear for me wether or not is possible to attach a port to a specific database. I will try with NETCA ...

                  Regards
                  • 6. Re: Attach a listener port to a specific database instance
                    AlleT
                    I ahve done it in the past, so what you are trying to achieve is not weird IMO

                    I think that you will have to use static registration for the 2nd listener, because as sybrand_b says the dynamic registration occurs only on 1521 port
                    • 7. Re: Attach a listener port to a specific database instance
                      171663
                      jjuanino wrote:
                      Services Summary...
                      Service "db1" has 1 instance(s).
                      Instance "db1", status READY, has 1 handler(s) for this service...
                      Service "db2" has 1 instance(s).
                      Instance "db1", status READY, has 1 handler(s) for this service...
                      Sorry, that last line should show "db2", not db1
                      • 8. Re: Attach a listener port to a specific database instance
                        Billy~Verreynne
                        jjuanino wrote:

                        The aim is to configure a listener, with two listening ports (1521 and 1522), but 1521 only attached to db1, and 1522 only attached to db2.
                        Why? What are the reasons for this?

                        And yes, it does need sound justification as it does NOT make any sense.
                        • 9. Re: Attach a listener port to a specific database instance
                          AlleT
                          if you have to grant access to a specific db through a firewall, then using different ports is a a simple way to restrict access
                          • 10. Re: Attach a listener port to a specific database instance
                            Billy~Verreynne
                            Does not make sense - a user on database1 can easily gain access to database2 via an existing Oracle session to database1 and using escalated privs.

                            If existing username and password authentication does not suffice to ensure that users are using the correct database instance, using multiple listener ports and a firewall are addressing the symptoms and not the root problem.
                            • 11. Re: Attach a listener port to a specific database instance
                              AlleT
                              username and password authentication ARE used: using different ports is a way to further protect access to the other database (of course it would be better to keep databases in different boxes).


                              And in this case I think it is a customer requirement....
                              • 12. Re: Attach a listener port to a specific database instance
                                171663
                                AlleT wrote:
                                I ahve done it in the past, so what you are trying to achieve is not weird IMO

                                I think that you will have to use static registration for the 2nd listener, because as sybrand_b says the dynamic registration occurs only on 1521 port
                                Well, It seems to be that the key is the undocumented parameter
                                ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER. In order to achive the requeriments (each port attached to an unique database) you need a listener.ora as following:

                                LISTENER_db1= (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST = <hostname>)(PORT = 1521)))
                                LISTENER_db2= (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST = <hostname>)(PORT = 1522)))

                                ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

                                With this setup, I get:

                                $ lsnrct status listener_db1
                                ................
                                Listening Endpoints Summary...
                                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=punto)(PORT=1521)))
                                ................

                                $ lsnrct status listener_db2
                                ................
                                Listening Endpoints Summary...
                                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=punto)(PORT=1522)))
                                ................


                                But sometimes(!!!) grid control adds these lines:

                                ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_db1=ON # line added by Agent
                                ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_db2=ON # line added by Agent

                                and then I get:


                                $ lsnrctl status listener_db1

                                Listening Endpoints Summary...
                                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>)(PORT=1522)))
                                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>)(PORT=1521)))

                                ....
                                Service "db1" has 1 instance(s).
                                .......
                                Service "db2" has 1 instance(s).

                                That is completely wrong for my purposes.

                                Regards
                                • 13. Re: Attach a listener port to a specific database instance
                                  sb92075
                                  jjuanino wrote:
                                  That is completely wrong for my purposes.
                                  Pardon my English.
                                  security by obscurity is neither.

                                  The "requirement" is daft!
                                  • 14. Re: Attach a listener port to a specific database instance
                                    user503699
                                    jjuanino wrote:
                                    Hello,

                                    I have two 11g databases in the same host, with service names db1 and db2.

                                    The aim is to configure a listener, with two listening ports (1521 and 1522), but 1521 only attached to db1, and 1522 only attached to db2. That is, connections to 1522 port and service name db1 should be rejected, and so with connections to 1521 port and service name db2. The host has only a net interface.
                                    Ed has written some excellent articles on database listener concepts and configuration.
                                    I think this post, in particular, may give you some hints.
                                    1 2 Previous Next