Creating a PDB by Recolating a PDB

Multitenant / 14/07/2022

A partir da release 12.2, a Oracle disponibilizou um recurso interessante para nós, chamado relocate. Este, consiste basicamente na movimentação de um PDB de um CDB para outro CDB, com um downtime mínimo que será demonstrado neste artigo. Como eu só possuo um CDB de origem, farei a criação do CDB de destino conforme abaixo:

[oracle@oel8 ~]$ export DISPLAY=192.168.0.104:0.0
[oracle@oel8 ~]$ dbca &
[1] 9723

Criando estrutura de diretórios que abrigarão o novo CDB:

[oracle@oel8 oracle]$ pwd
/oracle
[oracle@oel8 oracle]$ mkdir SINAPSE
[oracle@oel8 oracle]$ cd SINAPSE/
[oracle@oel8 SINAPSE]$ mkdir dados archives fra
[oracle@oel8 SINAPSE]$ ll
total 0
drwxr-xr-x. 2 oracle oinstall 6 Mar 28 10:51 archives
drwxr-xr-x. 2 oracle oinstall 6 Mar 28 10:51 dados
drwxr-xr-x. 2 oracle oinstall 6 Mar 28 10:51 fra

O novo CDB se chamará Sinapse e não terá nenhum PDB em sua criação:

Por algum motivo o DBCA não identificou o listener já existente no ambiente, mas depois eu consigo configurar:

É possível ver que a instância já está no ar e que o listener já realizou o registro automaticamente:

[oracle@oel8 ~]$ ps -ef | grep pmon
oracle    1739     1  0 12:10 ?        00:00:00 ora_pmon_SINAPSE
oracle    3734  6688  0 12:17 pts/0    00:00:00 grep --color=auto pmon
[oracle@oel8 ~]$
[oracle@oel8 ~]$
[oracle@oel8 ~]$
[oracle@oel8 ~]$ lsnrctl status
 
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 28-MAR-2021 12:17:10
 
Copyright (c) 1991, 2020, Oracle.  All rights reserved.
 
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                28-MAR-2021 10:38:27
Uptime                    0 days 1 hr. 38 min. 42 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /oracle/18.0.0/base/diag/tnslsnr/oel8/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel8.localdomain)(PORT=1521)))
Services Summary...
Service "SINAPSE.localdomain" has 1 instance(s).
  Instance "SINAPSE", status READY, has 1 handler(s) for this service...
Service "SINAPSEXDB.localdomain" has 1 instance(s).
  Instance "SINAPSE", status READY, has 1 handler(s) for this service...
The command completed successfully

Adicionando string de conexão no tnsnames.ora:

[oracle@oel8 admin]$ pwd
/oracle/18.0.0/product/network/admin
[oracle@oel8 admin]$ cat tnsnames.ora
RMANDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel8.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RMANDB)
    )
  )
 
CORTEX =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel7.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cortex.localdomain)
    )
  )
 
SINAPSE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel8.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sinapse.localdomain)
    )
  )

Testando conexão:

[oracle@oel8 admin]$ sqlplus system/oracle@SINAPSE
 
SQL*Plus: Release 18.0.0.0.0 - Production on Sun Mar 28 12:24:06 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
Last Successful login time: Sun Mar 28 2021 11:42:41 -03:00
 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
 
SQL> SHOW CON_NAME CON_ID
 
CON_NAME
------------------------------
CDB$ROOT
 
CON_ID
------------------------------
1

Exportando o nodo ORACLE_SID e conectando ao ambiente:

[oracle@oel8 admin]$ export ORACLE_SID=SINAPSE
[oracle@oel8 admin]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Sun Mar 28 12:26:34 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

Agora depois de tudo isso podemos pensar no assunto principal do artigo: o procedimento de relocate de PDB. Para facilitar, por conta dos nomes estranhos que estou usando, segue a origem e destino:

CDB DE ORIGEM: TALAMO
PDB DE ORIGEM: HIPOFISE2
CDB DE DESTINO: SINAPSE

Assim, preciso garantir que o destino possua a string de conexão do CDB de origem:

[oracle@oel8 admin]$ pwd
/oracle/18.0.0/product/network/admin
[oracle@oel8 admin]$ cat tnsnames.ora
RMANDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel8.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RMANDB)
    )
  )
 
CORTEX =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel7.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cortex.localdomain)
    )
  )
 
SINAPSE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel8.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sinapse.localdomain)
    )
  )
 
TALAMO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.106)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TALAMO.localdomain)
    )
  )
[oracle@oel8 admin]$ tnsping TALAMO
 
TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 28-MAR-2021 12:37:22
 
Copyright (c) 1997, 2020, Oracle.  All rights reserved.
 
Used parameter files:
/oracle/18.0.0/product/network/admin/sqlnet.ora
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.106)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TALAMO.localdomain)))
OK (0 msec)

Criando no CDB de destino (SINAPSE), um DB_LINK público que acessa o CDB de origem (TALAMO):

SQL> SELECT INSTANCE_NAME,STATUS FROM V$INSTANCE;
 
INSTANCE_NAME    STATUS
---------------- ------------
SINAPSE          OPEN
 
SQL> CREATE DATABASE LINK BSS CONNECT TO system IDENTIFIED BY oracle USING 'TALAMO';
 
Database link created.
 
SQL> SELECT * FROM DUAL@BSS;
 
D
-
X

Na origem, é necessário conceder o privilégio ao usuário system, que foi utilizado no db_link:

SQL> GRANT SYSOPER TO SYSTEM CONTAINER=ALL;
 
Grant succeeded.

Para simularmos o relocate, vamos criar um tabela no PDB HIPOFISE2 para depois consultá-la pós operação:

[oracle@oel8 admin]$ sqlplus system/oracle@HIPOFISE2
 
SQL*Plus: Release 18.0.0.0.0 - Production on Sun Mar 28 19:01:02 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
Last Successful login time: Sun Mar 28 2021 12:39:10 -03:00
 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
 
SQL> CREATE TABLE SYSTEM.BSS ( TEXTO VARCHAR2(100));
 
Table created.
 
SQL> INSERT INTO SYSTEM.BSS (TEXTO) VALUES ('TESTE PRE RELOCATE');
 
1 row created.
 
SQL> COMMIT;
 
Commit complete.

Mantendo um processo aberto no PDB de origem:

SQL> SELECT SYS_CONTEXT('USERENV','HOST') CON_ID FROM DUAL;
 
CON_ID
--------------------------------------------------------------------------------
oel8.localdomain
 
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
 
INSTANCE_NAME
----------------
TALAMO

Conectando no CDB$ROOT de destino e definindo a nível de sessão o destino dos datafiles que serão movimentados:

[oracle@oel8 ~]$ echo $ORACLE_SID
SINAPSE
[oracle@oel8 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Sun Mar 28 19:07:10 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 DB_CREATE_FILE_DEST='/oracle/SINAPSE/dados';
 
Session altered.

No destino, disparando o comando de relocate, utilizando o parâmetro AVAILABILITY MAX:

SQL> CREATE PLUGGABLE DATABASE HIPOFISE2 FROM HIPOFISE2@BSS RELOCATE AVAILABILITY MAX;
 
Pluggable database created.

Realizando mais um insert em nossa tabela de teste, no ambiente de origem:

SQL> INSERT INTO SYSTEM.BSS (TEXTO) VALUES ('TESTE DURANTE RELOCATE');
 
1 row created.
 
SQL> COMMIT;
 
Commit complete.

Avaliando qual é o status do novo PDB no CDB de destino. Percebemos que o mesmo está ainda em RELOCATING, ou seja, ainda falta “oficializarmos” a operação abrindo o PDB:

SQL> SELECT PDB_NAME,STATUS FROM CDB_PDBS WHERE PDB_NAME='HIPOFISE2';
 
PDB_NAME
--------------------------------------------------------------------------------
STATUS
----------
HIPOFISE2
RELOCATING

Já no CDB de origem, o PDB consta como NORMAL, ou seja, ele ainda está operando normalmente:

SQL> SELECT PDB_NAME,STATUS FROM CDB_PDBS WHERE PDB_NAME='HIPOFISE2';
 
PDB_NAME
--------------------------------------------------------------------------------
STATUS
----------
HIPOFISE2
NORMAL

Realizando novamente uma consulta aleatória na origem:

SQL> SELECT SYS_CONTEXT('USERENV','HOST') CON_ID FROM DUAL;
 
CON_ID
--------------------------------------------------------------------------------
oel8.localdomain
 
SQL>

Abrindo o PDB no ambiente de destino. A partir de agora, o relocate pode ser considerado realizado:

SQL> ALTER PLUGGABLE DATABASE HIPOFISE2 OPEN;
 
Warning: PDB altered with errors.

No alert do CDB de destino:

Successfully created internal service HIPOFISE2 at open
****************************************************************
Post plug operations are now complete.
Pluggable database HIPOFISE2 with pdb id - 3 is now marked as NEW.
****************************************************************
Pluggable database HIPOFISE2 dictionary check beginning
Pluggable Database HIPOFISE2 Dictionary check complete
Database Characterset for HIPOFISE2 is AL32UTF8
2021-03-28 19:19:16.649000 -03:00
Violations: Type: 1, Count: 1
Violations: Type: 2, Count: 1
***************************************************************
WARNING: Pluggable Database HIPOFISE2 with pdb id - 3 is
         altered with errors or warnings. Please look into
         PDB_PLUG_IN_VIOLATIONS view for more details.
***************************************************************
2021-03-28 19:19:23.648000 -03:00
Opening pdb with no Resource Manager plan active
Pluggable database HIPOFISE2 opened read write
Completed: ALTER PLUGGABLE DATABASE HIPOFISE2 OPEN
2021-03-28 19:19:39.197000 -03:00
Control autobackup written to DISK device
handle '/oracle/SINAPSE/fra/SINAPSE/autobackup/2021_03_28/o1_mf_s_1068405576_j6207t45_.bkp'

Logo vemos que a natureza no warning foi que, além de um parâmetro de DDL_TIMOUT, o que mais preocupa é que o CDB está em 18.13 e o PDB estava na 13.3:

SQL> SELECT TIME,CAUSE,MESSAGE,TYPE FROM PDB_PLUG_IN_VIOLATIONS WHERE NAME='HIPOFISE2';
 
TIME
---------------------------------------------------------------------------
CAUSE
----------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
TYPE
---------
28-MAR-21 07.19.16.150043 PM
Parameter
CDB parameter ddl_lock_timeout mismatch: Previous 666 Current 0
WARNING
 
 
TIME
---------------------------------------------------------------------------
CAUSE
----------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
TYPE
---------
28-MAR-21 07.19.16.435574 PM
SQL Patch
'18.13.0.0.0 Release_Update 2012231849' is installed in the CDB but '18.3.0.0.0
Release_Update 1806280943' is installed in the PDB
ERROR
 
TIME
---------------------------------------------------------------------------
CAUSE
----------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
TYPE
---------

Por conta disso, realizo um data patch no PDB em questão:

[oracle@oel8 ~]$ cd $ORACLE_HOME/OPatch
[oracle@oel8 OPatch]$ ./datapatch -verbose -pdbs HIPOFISE2
SQL Patching tool version 18.0.0.0.0 Production on Sun Mar 28 19:27:45 2021
Copyright (c) 2012, 2020, Oracle.  All rights reserved.
 
Log file for this invocation: /oracle/18.0.0/base/cfgtoollogs/sqlpatch/sqlpatch_10279_2021_03_28_19_27_45/sqlpatch_invocation.log
 
Connecting to database...OK
Gathering database info...done
 
Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)
 
Bootstrapping registry and package to current versions...done
Determining current state...done
 
Current state of interim SQL patches:
Interim patch 27923415 (OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)):
  Binary registry: Installed
  PDB HIPOFISE2: Applied successfully on 02-MAR-21 06.44.54.948445 AM
 
Current state of release update SQL patches:
  Binary registry:
    18.13.0.0.0 Release_Update 2012231849: Installed
  PDB HIPOFISE2:
    Applied 18.3.0.0.0 Release_Update 1806280943 successfully on 02-MAR-21 06.44.54.938616 AM
 
Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: HIPOFISE2
    No interim patches need to be rolled back
    Patch 32204699 (Database Release Update : 18.13.0.0.210119 (32204699)):
      Apply from 18.3.0.0.0 Release_Update 1806280943 to 18.13.0.0.0 Release_Update 2012231849
    No interim patches need to be applied
 
Installing patches...
Patch installation complete.  Total patches installed: 1
 
Validating logfiles...done
Patch 32204699 apply (pdb HIPOFISE2): SUCCESS
  logfile: /oracle/18.0.0/base/cfgtoollogs/sqlpatch/32204699/24011084/32204699_apply_SINAPSE_HIPOFISE2_2021Mar28_19_28_34.log (no errors)
SQL Patching tool complete on Sun Mar 28 19:34:04 2021

Após item resolvido, banco reaberto:

SQL> ALTER PLUGGABLE DATABASE HIPOFISE2 OPEN;
ALTER PLUGGABLE DATABASE HIPOFISE2 OPEN
*
ERROR at line 1:
ORA-65019: pluggable database HIPOFISE2 already open
 
 
SQL> ALTER PLUGGABLE DATABASE HIPOFISE2 CLOSE;
 
Pluggable database altered.
 
SQL> ALTER PLUGGABLE DATABASE HIPOFISE2 OPEN;
 
Pluggable database altered.

É possível notar que nossa sessão no PDB de origem caiu. Esse é o comportamento esperado, pois é no momento da abertura do PDB no destino que o processo finda:

SQL> SELECT SYS_CONTEXT('USERENV','HOST') CON_ID FROM DUAL;
SELECT SYS_CONTEXT('USERENV','HOST') CON_ID FROM DUAL
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4825
Session ID: 70 Serial number: 64636

No destino, podemos ver o conteúdo da nossa tabela de testes:

SQL> ALTER SESSION SET CONTAINER=HIPOFISE2;
 
Session altered.
 
SQL> SELECT * FROM SYSTEM.BSS;
 
TEXTO
--------------------------------------------------------------------------------
TESTE PRE RELOCATE
TESTE DURANTE RELOCATE

O mais legal deste recurso vem agora: ao tentar conectar no PDB pela origem, percebemos que o Listener realiza o direcionamento para o PDB de destino:

[oracle@oel8 admin]$ sqlplus system/oracle@HIPOFISE2
 
SQL*Plus: Release 18.0.0.0.0 - Production on Sun Mar 28 20:21:02 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
Last Successful login time: Sun Mar 28 2021 12:39:10 -03:00
 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
 
SQL> SELECT SYS_CONTEXT('USERENV','INSTANCE_NAME') FROM DUAL;
 
SYS_CONTEXT('USERENV','INSTANCE_NAME')
--------------------------------------------------------------------------------
SINAPSE
 
SQL>

Ao checar o status do PDB no ambiente de origem, vemos que o mesmo já consta como RELOCATED:

SQL> SELECT PDB_NAME,STATUS FROM CDB_PDBS WHERE PDB_NAME='HIPOFISE2';
 
PDB_NAME
--------------------------------------------------------------------------------
STATUS
----------
HIPOFISE2
RELOCATED

Já no ambiente de destino, o PDB aparece como NORMAL:

SQL> SELECT PDB_NAME,STATUS FROM CDB_PDBS WHERE PDB_NAME='HIPOFISE2';
 
PDB_NAME
--------------------------------------------------------------------------------
STATUS
----------
HIPOFISE2
NORMAL

Esse direcionamento realizado da origem para o destino só é possível pelo Listener, que altera o serviço para FORWARD SERVER:

[oracle@oel8 ~]$ lsnrctl service
 
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 29-MAR-2021 04:42:45
 
Copyright (c) 1991, 2020, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel8.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: oel8.localdomain, pid: 2602>
         (ADDRESS=(PROTOCOL=tcp)(HOST=oel8.localdomain)(PORT=24161))
Service "bdd9ab0055da2c52e0536b00a8c09fce.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 2 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: oel8.localdomain, pid: 2602>
         (ADDRESS=(PROTOCOL=tcp)(HOST=oel8.localdomain)(PORT=24161))
      "COMMON" established:0 refused:0 state:ready
         FORWARD SERVER
         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel8.localdomain)(PORT=1521)))
Service "hipofise2.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 2 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: oel8.localdomain, pid: 2602>
         (ADDRESS=(PROTOCOL=tcp)(HOST=oel8.localdomain)(PORT=24161))
      "COMMON" established:0 refused:0 state:ready
         FORWARD SERVER
         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel8.localdomain)(PORT=1521)))
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.

1 thought on “Creating a PDB by Recolating a 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