Creating a Refreshable PDB

Multitenant / 14/07/2022

Outro recurso legal disponibilizado na arquitetura multitenant é o Refreshable PDB, que nos permite atualizar as informações de um PDB (que fica em read only) através de outro PDB de origem remota. Podemos usar este novo tipo de PDB por exemplo para rodar relatórios pesados, trazendo mais fôlego para a operação no pdb original.

Para isso, precisaremos de um DB_LINK público no CDB de destino apontando para o CDB de origem. Como eu já tenho um criado por conta de outros artigos, vou simular que o mesmo está funcionando:

[oracle@oel8 dados]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 30 05:07:45 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
---------------- ------------
SINAPSE          OPEN
 
SQL> SELECT * FROM DUAL@BSS;
 
D
-
X

Criando um PDB na origem apenas para este nosso teste:

[oracle@quiasma OPatch]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 30 05:14:28 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> CREATE PLUGGABLE DATABASE BRUNO
ADMIN USER BSS IDENTIFIED BY BSS
DEFAULT TABLESPACE USERS
STORAGE (MAXSIZE 2G);  2    3    4
 
Pluggable database created.
 
SQL> ALTER PLUGGABLE DATABASE BRUNO OPEN;
 
Pluggable database altered.
 
SQL> ALTER PLUGGABLE DATABASE BRUNO SAVE STATE;
 
Pluggable database altered.

Criando o Refreshable PDB no CDB de destino:

SQL> ALTER SESSION SET DB_CREATE_FILE_DEST='/oracle/SINAPSE/dados';
 
Session altered.
 
SQL> CREATE PLUGGABLE DATABASE BRUNOREPORT FROM BRUNO@BSS REFRESH MODE MANUAL;
 
Pluggable database created.

Como dito, este tipo de PDB deve estar no modo Read Only (perceba que tentar abrí-lo provoca uma mensagem de erro):

SQL> ALTER PLUGGABLE DATABASE BRUNOREPORT OPEN READ ONLY;
 
Pluggable database altered.
 
SQL> col PDB_NAME format a10
SQL> SELECT CON_ID, PDB_NAME, STATUS, REFRESH_MODE FROM CDB_PDBS WHERE PDB_NAME='BRUNOREPORT';
 
    CON_ID PDB_NAME   STATUS     REFRES
---------- ---------- ---------- ------
         5 BRUNOREPOR REFRESHING MANUAL
           T
 
         5 BRUNOREPOR REFRESHING MANUAL
           T

SQL> ALTER PLUGGABLE DATABASE BRUNOREPORT CLOSE IMMEDIATE;
 
Pluggable database altered.
 
SQL> ALTER PLUGGABLE DATABASE BRUNOREPORT OPEN;
ALTER PLUGGABLE DATABASE BRUNOREPORT OPEN
*
ERROR at line 1:
ORA-65341: cannot open pluggable database in read/write mode
 
 
SQL> ALTER PLUGGABLE DATABASE BRUNOREPORT OPEN READ ONLY;
 
Pluggable database altered.
 
SQL> ALTER PLUGGABLE DATABASE BRUNOREPORT SAVE STATE;
 
Pluggable database altered.

Percebemos que automaticamente já foi criado um serviço para o novo PDB. Assim, podemos realizar um teste de conexão:

[oracle@oel8 dados]$ lsnrctl service
 
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 30-MAR-2021 05:28:59
 
Copyright (c) 1991, 2020, Oracle.  All rights reserved.
 
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "SINAPSE.localdomain" has 1 instance(s).
  Instance "SINAPSE", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:5 refused:0 state:ready
         LOCAL SERVER
Service "SINAPSEXDB.localdomain" has 1 instance(s).
  Instance "SINAPSE", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: oel8.localdomain, pid: 16750>
         (ADDRESS=(PROTOCOL=tcp)(HOST=oel8.localdomain)(PORT=16771))
Service "bdd9ab0055da2c52e0536b00a8c09fce.localdomain" has 1 instance(s).
  Instance "SINAPSE", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:5 refused:0 state:ready
         LOCAL SERVER
Service "beaa0d2dc3c85bebe0536b00a8c05826.localdomain" has 1 instance(s).
  Instance "SINAPSE", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:5 refused:0 state:ready
         LOCAL SERVER
Service "bebd7f6a46cb09e9e0536b00a8c077a5.localdomain" has 1 instance(s).
  Instance "SINAPSE", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:5 refused:0 state:ready
         LOCAL SERVER
Service "brunoreport.localdomain" has 1 instance(s).
  Instance "SINAPSE", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:5 refused:0 state:ready
         LOCAL SERVER
Service "hipofise2.localdomain" has 1 instance(s).
  Instance "SINAPSE", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:5 refused:0 state:ready
         LOCAL SERVER
Service "hipoproxy.localdomain" has 1 instance(s).
  Instance "SINAPSE", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:5 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
[oracle@oel8 dados]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 30 05:29:27 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> CONN system/oracle@//oel8.localdomain:1521/brunoreport.localdomain
Connected.
SQL>

Para simular o funcionamento deste refreshable pdb, vamos criar uma nova tablespace, schema e tabela no ambiente de origem, conforme abaixo:

[oracle@quiasma OPatch]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 30 05:33:24 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 SESSION SET CONTAINER=BRUNO;
CREATE TABLESPACE TBS_BRUNO;
Session altered.
 
SQL>
 
Tablespace created.
 
SQL> CREATE USER DATAMART IDENTIFIED BY DATAMART DEFAULT TABLESPACE TBS_BRUNO QUOTA UNLIMITED ON TBS_BRUNO;
 
User created.
 
SQL> GRANT CREATE SESSION, CREATE TABLE TO DATAMART;
 
Grant succeeded.
 
SQL> CREATE TABLE DATAMART.TESTE ( RID NUMBER );
 
Table created.
 
SQL> INSERT INTO DATAMART.TESTE VALUES (1);
 
1 row created.
 
SQL> COMMIT;
 
Commit complete.
 
SQL>

Já no PDB de destino, vemos que as informações ainda não foram replicadas:

SQL> CONN system/oracle@//oel8.localdomain:1521/brunoreport.localdomain
Connected.
SQL> SELECT NAME FROM V$TABLESPACE WHERE NAME='TBS_BRUNO';
 
no rows selected
 
SQL> SELECT * FROM DATAMART.TESTE;
SELECT * FROM DATAMART.TESTE
                       *
ERROR at line 1:
ORA-00942: table or view does not exist

Realizando o refresh do PDB de destino:

[oracle@oel8 dados]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 30 05:44:17 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> CONN sys/oracle@//oel8.localdomain:1521/brunoreport.localdomain as sysdba
Connected.
SQL> ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
 
Pluggable database altered.
 
SQL> !pwd
/oracle/SINAPSE/dados
 
SQL> ALTER SESSION SET DB_CREATE_FILE_DEST='/oracle/SINAPSE/dados';
 
Session altered.
 
SQL> ALTER PLUGGABLE DATABASE REFRESH;
 
Pluggable database altered.
 
SQL> ALTER PLUGGABLE DATABASE OPEN READ ONLY;
 
Pluggable database altered.

Agora podemos ver que as informações de origem foram replicadas ao destino:

SQL> SELECT NAME FROM V$TABLESPACE WHERE NAME='TBS_BRUNO';
 
NAME
------------------------------
TBS_BRUNO
 
SQL> SELECT * FROM DATAMART.TESTE;
 
       RID
----------
         1

Interessante vermos o alert.log reportando a operação de refresh:

2021-03-30 05:44:58.178000 -03:00
ALTER PLUGGABLE DATABASE REFRESH
2021-03-30 05:44:59.475000 -03:00
Applying media recovery for pdb-4099 from SCN 3011587 to SCN 3015297
Remote log information: count-1
thr-1, seq-35, logfile-/oracle/archives/parlog_1_35_f6398a0_1066113021.arc, los-2930063, nxs-18446744073709551615
Media Recovery Start
Serial Media Recovery started
max_pdb is 5
Media Recovery Log /oracle/archives/parlog_1_35_f6398a0_1066113021.arc
2021-03-30 05:45:02.192000 -03:00
Successfully added datafile 21 to media recovery
Datafile #21: '/oracle/SINAPSE/dados/SINAPSE/BEBD7F6A46CB09E9E0536B00A8C077A5/datafile/o1_mf_tbs_brun_j65s8dhp_.dbf'
Resize operation completed for file# 18, old size 378880K, new size 389120K
Incomplete Recovery applied until change 3015297 time 03/30/2021 05:44:58
Media Recovery Complete (SINAPSE)
Completed: ALTER PLUGGABLE DATABASE REFRESH
2021-03-30 05:45:08.895000 -03:00
ALTER PLUGGABLE DATABASE OPEN READ ONLY
Autotune of undo retention is turned on.
Endian type of dictionary set to little
Undo initialization finished serial:0 start:91007274 end:91007274 diff:0 ms (0.0 seconds)
Database Characterset for BRUNOREPORT is AL32UTF8
Opening pdb with no Resource Manager plan active
Pluggable database BRUNOREPORT opened read only
Completed: ALTER PLUGGABLE DATABASE OPEN READ ONLY

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.

1 thought on “Creating a Refreshable PDB”

  1. Pingback: ALL options for creating a PDB using Create Puggable Database command – Bruno Santos da Silva

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish