Dedicating a Listener to a PDB

Multitenant / 14/07/2022

Criando um PDB para os testes deste artigo:

[oracle@quiasma admin]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Apr 1 05:44:01 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
 
SQL> CREATE PLUGGABLE DATABASE HIPOFISE2
ADMIN USER BSS IDENTIFIED BY BSS
DEFAULT TABLESPACE USERS;  2    3
 
Pluggable database created.

Criando um novo listener, editando o arquivo listener.ora:

[oracle@quiasma admin]$ pwd
/oracle/18.0.0/product/network/admin
[oracle@quiasma admin]$ cat listener.ora
# listener.ora Network Configuration File: /oracle/18.0.0/product/network/admin/listener.ora
# Generated by Oracle configuration tools.
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = quiasma.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
 
LISTENER_HIPOFISE2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = quiasma.localdomain)(PORT = 1525))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1525))
    )
  )

Listener criado com sucesso:

[oracle@quiasma admin]$ ps -ef | grep lsnr
oracle    1834     1  0 03:54 ?        00:00:00 /oracle/18.0.0/product/bin/tnslsnr LISTENER -inherit
oracle    5023  2849  0 05:46 pts/0    00:00:00 grep --color=auto lsnr
[oracle@quiasma admin]$ lsnrctl start LISTENER_HIPOFISE2
 
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 01-APR-2021 05:46:08
 
Copyright (c) 1991, 2020, Oracle.  All rights reserved.
 
Starting /oracle/18.0.0/product/bin/tnslsnr: please wait...
 
TNSLSNR for Linux: Version 18.0.0.0.0 - Production
System parameter file is /oracle/18.0.0/product/network/admin/listener.ora
Log messages written to /oracle/18.0.0/base/diag/tnslsnr/quiasma/listener_hipofise2/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=quiasma.localdomain)(PORT=1525)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1525)))
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=quiasma.localdomain)(PORT=1525)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_HIPOFISE2
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                01-APR-2021 05:46:08
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/18.0.0/product/network/admin/listener.ora
Listener Log File         /oracle/18.0.0/base/diag/tnslsnr/quiasma/listener_hipofise2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=quiasma.localdomain)(PORT=1525)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1525)))
The listener supports no services
The command completed successfully
[oracle@quiasma admin]$ ps -ef | grep lsnr
oracle    1834     1  0 03:54 ?        00:00:00 /oracle/18.0.0/product/bin/tnslsnr LISTENER -inherit
oracle    5029     1  1 05:46 ?        00:00:00 /oracle/18.0.0/product/bin/tnslsnr LISTENER_HIPOFISE2 -inherit
oracle    5042  2849  0 05:46 pts/0    00:00:00 grep --color=auto lsnr

Adicionando o Net Alias no arquivo tnsnames.ora:

[oracle@quiasma admin]$ pwd
/oracle/18.0.0/product/network/admin
[oracle@quiasma admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/18.0.0/product/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
 
TALAMO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = quiasma.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TALAMO.localdomain)
    )
  )
 
LISTENER_TALAMO =
  (ADDRESS = (PROTOCOL = TCP)(HOST = quiasma.localdomain)(PORT = 1521))
 
HIPOFISE2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = quiasma.localdomain)(PORT = 1525))
        )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HIPOFISE2.localdomain)
    )
  )
 
LISTENER_HIPOFISE2 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = quiasma.localdomain)(PORT = 1525))

Definindo o parâmetro LISTENER_NETWORKS no nível do PDB:

SQL> ALTER PLUGGABLE DATABASE HIPOFISE2 OPEN
  2  ;
 
Pluggable database altered.
 
SQL> ALTER PLUGGABLE DATABASE HIPOFISE2 SAVE STATE;
 
Pluggable database altered.
 
SQL> ALTER SESSION SET CONTAINER=HIPOFISE2;
 
Session altered.
 
SQL> ALTER SYSTEM SET LISTENER_NETWORKS='((NAME=PDB_NETWORK1) (LOCAL_LISTENER=LISTENER_HIPOFISE2))' SCOPE=BOTH;
 
System altered.

Checando o status do novo listener:

SQL> !lsnrctl status LISTENER_HIPOFISE2
 
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 01-APR-2021 05:56:03
 
Copyright (c) 1991, 2020, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=quiasma.localdomain)(PORT=1525)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_HIPOFISE2
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                01-APR-2021 05:46:08
Uptime                    0 days 0 hr. 9 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/18.0.0/product/network/admin/listener.ora
Listener Log File         /oracle/18.0.0/base/diag/tnslsnr/quiasma/listener_hipofise2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=quiasma.localdomain)(PORT=1525)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1525)))
Services Summary...
Service "bee63bf16abf136de0536a00a8c07d35.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 2 handler(s) for this service...
Service "hipofise2.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 2 handler(s) for this service...
The command completed successfully

Realizando conexão ao PDB utilizando o serviço via TNS:

[oracle@quiasma admin]$ sqlplus BSS/BSS@HIPOFISE2
 
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Apr 1 05:57:20 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
 
SQL>

Via easy connect:

[oracle@quiasma admin]$ sqlplus BSS/BSS@//quiasma:1525/hipofise2.localdomain
 
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Apr 1 05:58:16 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
Last Successful login time: Thu Apr 01 2021 05:57:20 -03:00
 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
 
SQL>

Removendo o PDB e listener, pois não os usarei mais:

[oracle@quiasma admin]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Apr 1 05:58:53 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
 
SQL> ALTER PLUGGABLE DATABASE HIPOFISE2 CLOSE IMMEDIATE;
 
Pluggable database altered.
 
SQL> DROP PLUGGABLE DATABASE HIPOFISE2 INCLUDING DATAFILES;
 
Pluggable database dropped.
 
SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
[oracle@quiasma admin]$ lsnrctl stop LISTENER_HIPOFISE2
 
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 01-APR-2021 05:59:41
 
Copyright (c) 1991, 2020, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=quiasma.localdomain)(PORT=1525)))
The command completed successfully
[oracle@quiasma admin]$

Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish