Configuring OCI TNS Entry for PDBs

OCI / 13/07/2022

Ao criarmos nosso banco de dados em arquitetura Multitenant no OCI, vimos que a string de conexão no arquivo tnsnames.ora possuirá apenas o CDB$ROOT, como abaixo:

[opc@luxor ~]$ sudo su - oracle
Last login: Wed May 12 08:06:03 UTC 2021
[oracle@luxor ~]$ cd $ORACLE_HOME/network/admin
[oracle@luxor admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
 
LISTENER_CORTEX =
  (ADDRESS = (PROTOCOL = TCP)(HOST = luxor.luxorsubnet.luxorvcn.oraclevcn.com)(PORT = 1521))
 
 
CORTEX_PHX1HB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = luxor.luxorsubnet.luxorvcn.oraclevcn.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CORTEX_phx1hb.luxorsubnet.luxorvcn.oraclevcn.com)
    )
  )

Realizando teste de conexão ao CDB$ROOT usando o TNS:

[oracle@luxor admin]$ tnsping CORTEX_PHX1HB
 
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 12-MAY-2021 08:07:47
 
Copyright (c) 1997, 2016, Oracle.  All rights reserved.
 
Used parameter files:
/u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/sqlnet.ora
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = luxor.luxorsubnet.luxorvcn.oraclevcn.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CORTEX_phx1hb.luxorsubnet.luxorvcn.oraclevcn.com)))
OK (0 msec)
[oracle@luxor admin]$ sqlplus system@CORTEX_PHX1HB
 
SQL*Plus: Release 12.2.0.1.0 Production on Wed May 12 08:07:55 2021
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
Enter password:
Last Successful login time: Mon Oct 19 2020 15:40:19 +00:00
 
Connected to:
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production
 
SQL> SHOW USER;
USER is "SYSTEM"

Vamos coletar as informações necessárias para criarmos a nova string de conexão do PDB. Porém, percebo que o service name exibido no PDB é o mesmo do CDB:

SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;
 
NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB$SEED
READ ONLY
 
HIPOFISE1
READ WRITE
 
 
SQL> ALTER SESSION SET CONTAINER=HIPOFISE1;
 
Session altered.
 
SQL> sho parameter service
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      CORTEX_phx1hb.luxorsubnet.luxo
                                                 rvcn.oraclevcn.com

Desse modo, alterarei o service name manualmente, colocando como prefixo o nome do PDB, conforme exemplo:

[oracle@luxor admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
 
LISTENER_CORTEX =
  (ADDRESS = (PROTOCOL = TCP)(HOST = luxor.luxorsubnet.luxorvcn.oraclevcn.com)(PORT = 1521))
 
 
CORTEX_PHX1HB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = luxor.luxorsubnet.luxorvcn.oraclevcn.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CORTEX_phx1hb.luxorsubnet.luxorvcn.oraclevcn.com)
    )
  )
 
HIPOFISE1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = luxor.luxorsubnet.luxorvcn.oraclevcn.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HIPOFISE1_phx1hb.luxorsubnet.luxorvcn.oraclevcn.com)
    )
  )

Ao tentar conectar no PDB, o seguinte erro é exibido:

[oracle@luxor admin]$ sqlplus system@HIPOFISE1
 
SQL*Plus: Release 12.2.0.1.0 Production on Wed May 12 08:22:57 2021
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Após algumas pesquisas, chego no Oracle Note \”Connection To an OCI DBCS PDB Failed With \”ORA-12154: TNS:could not resolve the connect identifier specified\” (Doc ID 2674380.1)\” (link AQUI), que me traz a solução: adicionar a linha \”NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)\” no arquivo sqlnet.ora:

[oracle@luxor admin]$ cp sqlnet.ora sqlnet.ora.bkp
[oracle@luxor admin]$ cat sqlnet.ora
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/dcs/commonstore/wallets/tde/$ORACLE_UNQNAME)))
 
SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256,AES192,AES128)
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=(SHA1)
SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256,AES192,AES128)
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA1)
NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)

Após isso, ao reinicializar o listener, os serviços dos PDBs são exibidos (até de um segundo novo PDB que eu havia criado, enquanto fazia a investigação do problema):

[opc@luxor ~]$ sudo su - grid
Last login: Wed May 12 08:49:04 UTC 2021
[grid@luxor ~]$ lsnrctl stop
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 12-MAY-2021 08:49:59
 
Copyright (c) 1991, 2020, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
The command completed successfully
[grid@luxor ~]$ lsnrctl start
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 12-MAY-2021 08:50:05
 
Copyright (c) 1991, 2020, Oracle.  All rights reserved.
 
Starting /u01/app/19.0.0.0/grid/bin/tnslsnr: please wait...
 
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/19.0.0.0/grid/network/admin/listener.ora
Log messages written to /u01/app/grid/diag/tnslsnr/luxor/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                12-MAY-2021 08:50:06
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/19.0.0.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/luxor/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
The listener supports no services
The command completed successfully
[grid@luxor ~]$ lsnrctl service
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 12-MAY-2021 08:50:21
 
Copyright (c) 1991, 2020, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
Services Summary...
Service "CORTEXXDB.luxorsubnet.luxorvcn.oraclevcn.com" has 1 instance(s).
  Instance "CORTEX", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: luxor, pid: 8457>
         (ADDRESS=(PROTOCOL=tcp)(HOST=luxor.luxorsubnet.luxorvcn.oraclevcn.com)(PORT=50057))
Service "CORTEX_phx1hb.luxorsubnet.luxorvcn.oraclevcn.com" has 1 instance(s).
  Instance "CORTEX", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "c1cf6dcea12f134de0539501000a20c1.luxorsubnet.luxorvcn.oraclevcn.com" has 1 instance(s).
  Instance "CORTEX", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "c21efdff5eb40dcde0539501000ac2b8.luxorsubnet.luxorvcn.oraclevcn.com" has 1 instance(s).
  Instance "CORTEX", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "hipofise1.luxorsubnet.luxorvcn.oraclevcn.com" has 1 instance(s).
  Instance "CORTEX", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "hipofise2.luxorsubnet.luxorvcn.oraclevcn.com" has 1 instance(s).
  Instance "CORTEX", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
[grid@luxor ~]$

Ajustando o service name no arquivo tnsnames.ora:

[oracle@luxor admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
 
LISTENER_CORTEX =
  (ADDRESS = (PROTOCOL = TCP)(HOST = luxor.luxorsubnet.luxorvcn.oraclevcn.com)(PORT = 1521))
 
 
CORTEX_PHX1HB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = luxor.luxorsubnet.luxorvcn.oraclevcn.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CORTEX_phx1hb.luxorsubnet.luxorvcn.oraclevcn.com)
    )
  )
 
HIPOFISE1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = luxor.luxorsubnet.luxorvcn.oraclevcn.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HIPOFISE1.luxorsubnet.luxorvcn.oraclevcn.com)
    )
  )

Realizando teste, finalmente com sucesso:

[oracle@luxor admin]$ sqlplus system@HIPOFISE1
 
SQL*Plus: Release 12.2.0.1.0 Production on Wed May 12 09:00:08 2021
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
Enter password:
Last Successful login time: Wed May 12 2021 08:59:34 +00:00
 
Connected to:
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production
 
SQL> SELECT * FROM DUAL;
 
D
-
X

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.

2 thoughts on “Configuring OCI TNS Entry for PDBs”

  1. Pingback: Opening Port Number 1521 in the OCI Security Lists – Bruno Santos da Silva

  2. Pingback: Opening Port Number 1521 in the OCI Security Lists – SWIV

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish