asked on 6/17/2021

Configure multiple Oracle listeners

Hi,
My Oracle version is: Oracle Database 19c EE on Oracle Linux Server 7.9

I've installed 3 Oracle databases single instance on hostname = oracledbsq01, PORT = 1521

[oracle@oracledbsq01]$ cat /etc/oratab

ORCLSDB:/u01/oracle/install:N
ORCLBDB:/u01/oracle/install:N
ORCLMDB:/u01/oracle/install:N

[oracle@oracledbsq01]$ ps -ef | grep -i pmon
oracle 1671 1 0 22:53 ? 00:00:00 ora_pmon_ORCLSDB
oracle 2492 1 0 22:55 ? 00:00:00 ora_pmon_ORCLBDB
oracle 3151 1 0 22:57 ? 00:00:00 ora_pmon_ORCLMDB

[oracle@oracledbsq01 admin]$ ls -ltr
-rw-r--r--. 1 oracle oinstall 1536 Feb 14 2018 shrept.lst
-rw-r-----. 1 oracle oinstall 175 Apr 13 23:48 sqlnet.ora
drwxr-xr-x. 2 oracle oinstall 64 Apr 26 23:24 samples

I need to understand how to Configure multiple Oracle listeners one for each database.

For example I'd like to configure the listener.ora file to have 3 listeners (LISTENER_ORCLSDB, LISTENER_ORCLBDB, LISTENER_ORCLMDB).

Thanks

Networking Oracle Database

Last Comment

dbadm

6/19/2021

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

6/17/2021

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

dbadm

6/18/2021

ASKER

I've created the following listener.ora file:

SID_LIST_LISTENER_ORCLSDB =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLSDB)
      (ORACLE_HOME = /u01/oracle/install)
      (SID_NAME = ORCLSDB)
    )
  )

LISTENER_ORCLSDB =
  (DESCRIPTION_LIST =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracledbsq01)(PORT = 1521))
    )
  )

SID_LIST_LISTENER_ORCLBDB =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLBDB)
      (ORACLE_HOME = /u01/oracle/install)
      (SID_NAME = ORCLBDB)
    )
  )

LISTENER_ORCLBDB =
  (DESCRIPTION_LIST =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracledbsq01)(PORT = 1521))
    )
  )
 
SID_LIST_LISTENER_ORCLMDB =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLMDB)
      (ORACLE_HOME = /u01/oracle/install)
      (SID_NAME = ORCLMDB)
    )
  )

LISTENER_ORCLMDB =
  (DESCRIPTION_LIST =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracledbsq01)(PORT = 1521))
    )
  )

Open in new window

Is correct?

David Favor

6/18/2021

What you're describing will be difficult to get working.

As @slightwv mentioned, the only approach is to use either multiple IPs with same port or 1x IP with multiple ports.

There is no way multiple Oracle listeners can share an IP, as TCP only works with 1x listener per IP/port pair.

You can also use HAProxy... maybe... then somehow... parse + reroute requests to different IP:port pairs... and you'll pay roughly a 50% throughput penalty when using HAProxy or similar routing software.

slightwv (䄆 Netminder)

6/18/2021

>>I've created the following listener.ora file:

Please re-read what I posted: #a43303339

>>Is correct?

No. You cannot do what you are trying to do and there is no reason to do it.

If you think there is a reason, please explain what makes you think you need to try..

dbadm

6/19/2021

ASKER

OK I've created one listener for 3 instances on a single IP and port:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLSDB)
      (ORACLE_HOME = /u01/oracle/install)
      (SID_NAME = ORCLSDB)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLBDB)
      (ORACLE_HOME = /u01/oracle/install)
      (SID_NAME = ORCLBDB)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLMDB)
      (ORACLE_HOME = /u01/oracle/install)
      (SID_NAME = ORCLMDB)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracledbsq01)(PORT = 1521))
    )
  )

Open in new window

Thanks!