Oracle Services in Multitenant Environment

Multitenant / 14/07/2022

Indiretamente já tivemos contato com este assunto nos outros artigos publicados sobre Multitenant, mas nunca é demais explorar com um pouco mais de detalhes. Para isso, utilizarei o ambiente abaixo como laboratório, que possui um CDB e seu listener default:

[oracle@quiasma ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Apr 1 04:03:46 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> select instance_name,status from v$instance;
 
INSTANCE_NAME    STATUS
---------------- ------------
TALAMO           OPEN
 
SQL> select name,cdb from v$database;
 
NAME      CDB
--------- ---
TALAMO    YES
 
SQL> !ps -ef | grep lsnr
oracle    1834     1  0 03:54 ?        00:00:00 /oracle/18.0.0/product/bin/tnslsnr LISTENER -inherit
oracle    3133  3115  0 04:04 pts/0    00:00:00 /bin/bash -c ps -ef | grep lsnr
oracle    3135  3133  0 04:04 pts/0    00:00:00 grep lsnr
 
SQL>

Neste CDB temos apenas o PDB$SEED criado:

SQL> select name,open_mode from v$pdbs;
 
NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB$SEED
READ ONLY

Os serviços criados atualmente referem-se ao CDB:

SQL> !lsnrctl service
 
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 01-APR-2021 04:06:30
 
Copyright (c) 1991, 2020, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=quiasma.localdomain)(PORT=1521)))
Services Summary...
Service "TALAMO.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "TALAMOXDB.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: quiasma, pid: 2480>
         (ADDRESS=(PROTOCOL=tcp)(HOST=quiasma.localdomain)(PORT=18335))
The command completed successfully

Vou simular agora a criação de um novo PDB, e constatar que automaticamente um serviço é criado pelo processo de background LREG:

SQL> CREATE PLUGGABLE DATABASE HIPOFISE1
ADMIN USER BSS IDENTIFIED BY BSS
DEFAULT TABLESPACE USERS;  2    3
 
Pluggable database created.
 
SQL> ALTER PLUGGABLE DATABASE HIPOFISE1 OPEN;
 
Pluggable database altered.
 
SQL> ALTER PLUGGABLE DATABASE HIPOFISE1 SAVE STATE;
 
Pluggable database altered.

Porém a criação do serviço pode demorar até 60 segundos. No meu exemplo, o mesmo já foi criado e é apresentado conforme abaixo:

SQL> !lsnrctl service
 
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 01-APR-2021 04:11:17
 
Copyright (c) 1991, 2020, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=quiasma.localdomain)(PORT=1521)))
Services Summary...
Service "TALAMO.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "TALAMOXDB.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: quiasma, pid: 2480>
         (ADDRESS=(PROTOCOL=tcp)(HOST=quiasma.localdomain)(PORT=18335))
Service "bee4edb6fb790c2ce0536a00a8c0f129.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "hipofise1.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

Caso queiramos registrar o serviço de maneira pontual podemos executar o comando abaixo:

SQL> ALTER SESSION SET CONTAINER=HIPOFISE1;
 
Session altered.
 
SQL> ALTER SYSTEM REGISTER;
 
System altered.

Podemos consultar os serviços pela query abaixo:

SQL> SELECT SERVICE_ID,NAME,PDB FROM V$SERVICES;
 
SERVICE_ID NAME
---------- ----------------------------------------------------------------
PDB
--------------------------------------------------------------------------------
         8 hipofise1
HIPOFISE1
 
 
SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;
 
Session altered.
 
SQL> SELECT SERVICE_ID,NAME,PDB FROM V$SERVICES;
 
SERVICE_ID NAME
---------- ----------------------------------------------------------------
PDB
--------------------------------------------------------------------------------
         5 TALAMOXDB
CDB$ROOT
 
         6 TALAMO.localdomain
CDB$ROOT
 
         8 hipofise1
HIPOFISE1
 
 
SERVICE_ID NAME
---------- ----------------------------------------------------------------
PDB
--------------------------------------------------------------------------------
         1 SYS$BACKGROUND
CDB$ROOT
 
         2 SYS$USERS
CDB$ROOT

Para conectarmos neste novo PDB, podemos fazer via easy connect:

SQL> !hostname
quiasma
 
SQL> conn BSS/BSS@//quiasma:1521/hipofise1.localdomain
Connected.

Ou podemos conectar via Oracle Net Services. Vamos garantir que o arquivo tnsnames.ora possua a string de conexão:

[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))
 
HIPOFISE1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = quiasma.localdomain)(PORT = 1521))
        )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HIPOFISE1.localdomain)
    )
  )

Realizando conexão:

[oracle@quiasma admin]$ sqlplus BSS/BSS@HIPOFISE1
 
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Apr 1 04:26:00 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
Last Successful login time: Thu Apr 01 2021 04:22:15 -03:00
 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
 
SQL>

Caso não tenhamos o Oracle Restart configurado no ambiente (que é o meu caso), para realizar algumas operações de administração dos serviços, devemos utilizar a DBMS_SERVICE. No exemplo abaixo, farei a criação de um novo serviço:

SQL> ALTER SESSION SET CONTAINER=HIPOFISE1;
 
Session altered.
 
SQL> BEGIN
  DBMS_SERVICE.create_service(
    service_name => 'servicobruno',
    network_name => 'servicobruno'
  );
END;
/  2    3    4    5    6    7
 
PL/SQL procedure successfully completed.

SQL> SELECT name,
       network_name
FROM   dba_services
ORDER BY 1;  2    3    4
 
NAME
----------------------------------------------------------------
NETWORK_NAME
--------------------------------------------------------------------------------
HIPOFISE1
HIPOFISE1
 
servicobruno
servicobruno

Iniciando o serviço:

SQL> BEGIN
  DBMS_SERVICE.start_service(
    service_name => 'servicobruno'
  );
END;
/  2    3    4    5    6
 
PL/SQL procedure successfully completed.

SQL> !lsnrctl service
 
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 01-APR-2021 04:49:15
 
Copyright (c) 1991, 2020, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=quiasma.localdomain)(PORT=1521)))
Services Summary...
Service "TALAMO.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2 refused:0 state:ready
         LOCAL SERVER
Service "TALAMOXDB.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: quiasma, pid: 2480>
         (ADDRESS=(PROTOCOL=tcp)(HOST=quiasma.localdomain)(PORT=18335))
Service "bee4edb6fb790c2ce0536a00a8c0f129.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2 refused:0 state:ready
         LOCAL SERVER
Service "hipofise1.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2 refused:0 state:ready
         LOCAL SERVER
Service "servicobruno.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

Adicionado uma nova string de conexão ao tnsnames.ora com o novo serviço:

[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))
 
HIPOFISE1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = quiasma.localdomain)(PORT = 1521))
        )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HIPOFISE1.localdomain)
    )
  )
 
HIPOFISE1_NEWSERVICE =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = quiasma.localdomain)(PORT = 1521))
        )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = servicobruno.localdomain)
    )
  )

Conexão é permitida:

[oracle@quiasma admin]$ sqlplus BSS/BSS@HIPOFISE1_NEWSERVICE
 
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Apr 1 04:52:42 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
Last Successful login time: Thu Apr 01 2021 04:26:00 -03:00
 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
 
SQL>

Parando o serviço:

SQL> ALTER SESSION SET CONTAINER=HIPOFISE1;
 
Session altered.
 
SQL> BEGIN
  DBMS_SERVICE.stop_service(
    service_name => 'servicobruno'
  );
END;
/  2    3    4    5    6
 
PL/SQL procedure successfully completed.

Realizando a remoção do serviço:

SQL> BEGIN
  DBMS_SERVICE.delete_service(
    service_name => 'servicobruno'
  );
END;
/  2    3    4    5    6
 
PL/SQL procedure successfully completed.

Vemos que o serviço não é mais listado:

SQL> !lsnrctl service
 
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 01-APR-2021 04:56:41
 
Copyright (c) 1991, 2020, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=quiasma.localdomain)(PORT=1521)))
Services Summary...
Service "TALAMO.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0 state:ready
         LOCAL SERVER
Service "TALAMOXDB.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: quiasma, pid: 2480>
         (ADDRESS=(PROTOCOL=tcp)(HOST=quiasma.localdomain)(PORT=18335))
Service "bee4edb6fb790c2ce0536a00a8c0f129.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0 state:ready
         LOCAL SERVER
Service "hipofise1.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

Se droparmos o PDB, o seu serviço será removido automaticamente:

SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;
 
Session altered.
 
SQL> ALTER PLUGGABLE DATABASE HIPOFISE1 CLOSE IMMEDIATE;
 
Pluggable database altered.
 
SQL> DROP PLUGGABLE DATABASE HIPOFISE1 INCLUDING DATAFILES;
 
Pluggable database dropped.
 
SQL> !lsnrctl service
 
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 01-APR-2021 04:58:18
 
Copyright (c) 1991, 2020, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=quiasma.localdomain)(PORT=1521)))
Services Summary...
Service "TALAMO.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0 state:ready
         LOCAL SERVER
Service "TALAMOXDB.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: quiasma, pid: 2480>
         (ADDRESS=(PROTOCOL=tcp)(HOST=quiasma.localdomain)(PORT=18335))
The command completed successfully

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