Configuring Multiple local_listener

 

 

The configuration of multiple listeners consists of setting up 2 or more listeners for the same database. This is a task that consists of the following steps:

1- Creation of listeners::

1.1: Listener named X1 on port 1539;
1.1: Listener named X2 on port 1537;

[oracle@dbdg admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER_X1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbdg.localdomain)(PORT = 1539))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
)

LISTENER_X2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbdg.localdomain)(PORT = 1537))
    )
)

1.2: Initialization of listeners:

 

oracle@dbdg admin]$ lsnrctl

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 08-MAR-2018 20:18:52

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set curr LISTENER_X1
Current Listener is LISTENER_X1
LSNRCTL> START
Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/dbdg/listener_x1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1539)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbdg.localdomain)(PORT=1539)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_X1
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                08-MAR-2018 20:19:05
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dbdg/listener_x1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1539)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
The listener supports no services
The command completed successfully

LSNRCTL> set curr LISTENER_X2
Current Listener is LISTENER_X2
LSNRCTL> START
Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/dbdg/listener_x2/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1537)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbdg.localdomain)(PORT=1537)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_X2
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                08-MAR-2018 20:19:28
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dbdg/listener_x2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1537)))
The listener supports no services
The command completed successfully

2- Create the entries of each listener for the database in tnsnames.ora:

Example: Database named cbd4;
2.1: Entries will be created in tnsnames:

[oracle@dbdg admin]$ cat tnsnames.ora
X1=
  (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1539))
   (CONNECT_DATA=
     (SERVICE_NAME=cdb4.example.com)))

X2=
  (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1537))
   (CONNECT_DATA=
     (SERVICE_NAME=cdb4.example.com)))

2.2: Testing Inputs Inserted:

[oracle@dbdg admin]$ tnsping X1

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 08-MAR-2018 20:22:36

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1539)) (CONNECT_DATA= (SERVICE_NAME=cdb4)))
OK (0 msec)
[oracle@dbdg admin]$ tnsping X2

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 08-MAR-2018 20:22:38

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1537)) (CONNECT_DATA= (SERVICE_NAME=cdb4)))
OK (0 msec)

2- Define the services in the database listener_local parameter of the database:

How Oracle defines “LOCAL_LISTENER specifies a network name that resolves to an address or address list of Oracle Net local listeners (that is, listeners that are running on the same machine as this instance). The address or address list is specified in the TNSNAMES.ORA file or other address repository as configured for your system.”

 

[oracle@dbdg admin]$ sqlplus "/as sysdba" 

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 8 20:23:37 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select instance_name, status from v$instance;

INSTANCE_NAME	 STATUS
---------------- ------------
cdb4		 OPEN

SQL> select name from v$services;

NAME
----------------------------------------------------------------
noncdb.example.com
mig.example.com
cdbseed2.example.com
cdbseed.example.com
pdbcdb4.example.com
cdb4XDB
cdb4.example.com
SYS$BACKGROUND
SYS$USERS

9 rows selected.

SQL> alter system set LOCAL_LISTENER=X1,X2 SCOPE=BOTH;

System altered.

SQL> show parameters LOCAL_LISTENER

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
local_listener			     string	 X1, X2

3- Register the services:

SQL> ALTER SYSTEM REGISTER;

System altered.

4- Check the active services in the listener:

[oracle@dbdg admin]$ lsnrctl status LISTENER_X1

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 08-MAR-2018 20:25:42

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbdg.localdomain)(PORT=1539)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_X1
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                08-MAR-2018 20:19:05
Uptime                    0 days 0 hr. 6 min. 36 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dbdg/listener_x1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1539)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "cdb4.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "cdb4XDB.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "cdbseed.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "cdbseed2.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "mig.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "noncdb.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "pdbcdb4.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@dbdg admin]$ lsnrctl status LISTENER_X2

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 08-MAR-2018 20:25:50

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbdg.localdomain)(PORT=1537)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_X2
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                08-MAR-2018 20:19:28
Uptime                    0 days 0 hr. 6 min. 22 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dbdg/listener_x2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1537)))
Services Summary...
Service "cdb4.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "cdb4XDB.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "cdbseed.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "cdbseed2.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "mig.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "noncdb.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "pdbcdb4.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
The command completed successfully

 

 

A configuração de múltiplos listeners consiste em configurar 2 ou mais listeners para o mesmo banco de dados. Essa é uma tarefa que consiste dos seguintes steps:

1- Criação dos listeners:

1.1: Listener chamado X1 na porta 1539;
1.1: Listener chamado X2 na porta 1537;

[oracle@dbdg admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER_X1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbdg.localdomain)(PORT = 1539))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
)

LISTENER_X2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbdg.localdomain)(PORT = 1537))
    )
)

1.2: Inicialização dos listeners:

 

oracle@dbdg admin]$ lsnrctl

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 08-MAR-2018 20:18:52

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set curr LISTENER_X1
Current Listener is LISTENER_X1
LSNRCTL> START
Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/dbdg/listener_x1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1539)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbdg.localdomain)(PORT=1539)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_X1
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                08-MAR-2018 20:19:05
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dbdg/listener_x1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1539)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
The listener supports no services
The command completed successfully

LSNRCTL> set curr LISTENER_X2
Current Listener is LISTENER_X2
LSNRCTL> START
Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/dbdg/listener_x2/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1537)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbdg.localdomain)(PORT=1537)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_X2
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                08-MAR-2018 20:19:28
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dbdg/listener_x2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1537)))
The listener supports no services
The command completed successfully

2- Criar as entradas de cada listener para o banco de dados no tnsnames.ora:

Exemplo: Banco de dados chamado cbd4;
2.1:Será criado as entradas no tnsnames

[oracle@dbdg admin]$ cat tnsnames.ora
X1=
  (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1539))
   (CONNECT_DATA=
     (SERVICE_NAME=cdb4.example.com)))

X2=
  (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1537))
   (CONNECT_DATA=
     (SERVICE_NAME=cdb4.example.com)))

2.2:Testando as entradas inseridas:

[oracle@dbdg admin]$ tnsping X1

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 08-MAR-2018 20:22:36

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1539)) (CONNECT_DATA= (SERVICE_NAME=cdb4)))
OK (0 msec)
[oracle@dbdg admin]$ tnsping X2

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 08-MAR-2018 20:22:38

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1537)) (CONNECT_DATA= (SERVICE_NAME=cdb4)))
OK (0 msec)

2- Definir os serviços no parâmetro listener_local do banco dados:

Como a Oracle define “LOCAL_LISTENER specifies a network name that resolves to an address or address list of Oracle Net local listeners (that is, listeners that are running on the same machine as this instance). The address or address list is specified in the TNSNAMES.ORA file or other address repository as configured for your system.”

 

[oracle@dbdg admin]$ sqlplus "/as sysdba" 

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 8 20:23:37 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select instance_name, status from v$instance;

INSTANCE_NAME	 STATUS
---------------- ------------
cdb4		 OPEN

SQL> select name from v$services;

NAME
----------------------------------------------------------------
noncdb.example.com
mig.example.com
cdbseed2.example.com
cdbseed.example.com
pdbcdb4.example.com
cdb4XDB
cdb4.example.com
SYS$BACKGROUND
SYS$USERS

9 rows selected.

SQL> alter system set LOCAL_LISTENER=X1,X2 SCOPE=BOTH;

System altered.

SQL> show parameters LOCAL_LISTENER

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
local_listener			     string	 X1, X2

3- Registrar os serviços:

SQL> ALTER SYSTEM REGISTER;

System altered.

4- Verificar os serviços ativos no listener:

[oracle@dbdg admin]$ lsnrctl status LISTENER_X1

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 08-MAR-2018 20:25:42

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbdg.localdomain)(PORT=1539)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_X1
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                08-MAR-2018 20:19:05
Uptime                    0 days 0 hr. 6 min. 36 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dbdg/listener_x1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1539)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "cdb4.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "cdb4XDB.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "cdbseed.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "cdbseed2.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "mig.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "noncdb.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "pdbcdb4.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@dbdg admin]$ lsnrctl status LISTENER_X2

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 08-MAR-2018 20:25:50

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbdg.localdomain)(PORT=1537)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_X2
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                08-MAR-2018 20:19:28
Uptime                    0 days 0 hr. 6 min. 22 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dbdg/listener_x2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1537)))
Services Summary...
Service "cdb4.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "cdb4XDB.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "cdbseed.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "cdbseed2.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "mig.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "noncdb.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
Service "pdbcdb4.example.com" has 1 instance(s).
  Instance "cdb4", status READY, has 1 handler(s) for this service...
The command completed successfully

Related posts

Leave a Comment