How to add a new listener on a new port and restrict it to one instance only

listeneroracleoracle-11goracle-11g-r2Security

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

Best Answer

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/

Related Question