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
Hi! I am Bruno, a Brazilian born and bred. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)