0

I am running oracle 11.2.3 and we have a server with multiple instances running. All of them are registered to port 1521 with the default listener. Now we are required to make one instance available on a new port. I was wondering if there is a way to add a new listener to a new port and restrict it to register only one instance so that this listener can not make connections for other instances.

Current Listener Configuration:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    )
    )
  )
  • We have our qa database instance on the same server as the prod one for some unrelated reasons. We want to create a firewall flow from qa webserver to this server to connect to qa database. This will create a security concern since prod database will also be accessible so we want to keep qa and prod database listener on separate ports and open the firewall flow only to the qa listener. – user211005 Jun 21 at 3:06
  • We have same home for all instances – user211005 Jun 25 at 13:14
1

See the documentation about configuring multiple listeners in listener.ora, and about static service registration for each. https://docs.oracle.com/en/database/oracle/oracle-database/19/netag/configuring-and-administering-oracle-net-listener.html#GUID-C3C40DBA-4282-41E1-9562-4B8B10947C4E

LISTENER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    )
  )

QA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1526))
    )
  )

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=prod.us.example.com)
      (ORACLE_HOME=/oracle12c)
      (SID_NAME=prod))
  )

SID_LIST_QA=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=qa.us.example.com)
      (ORACLE_HOME=/oracle12c)
      (SID_NAME=qa))
  )

If you are working with Oracle Enterprise Edition, then another option would be to use Oracle Connection Manager; it would allow you to restrict client access by IP address and database service name. I wrote a white paper on it several years ago, but the basics are still the same with the latest versions: https://pmdba.wordpress.com/2013/12/01/deploying-an-oracle-11gr2-connection-manager/

| improve this answer | |
  • 1
    You'd probably also want to set DYNAMIC_REGISTRATION_<listener> = off so the listener stays dedicated to the instance(s) in its SID_LIST. – Izzy Jul 12 at 0:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.