cesardba

Como clonar um PDB remoto

O Oracle Multitenant já está presente em nossas vidas há algum tempo, esta arquitetura nos ajuda a consolidar vários bancos de dados como PDBs em um CDB.

Arquitetura Multitenant:

Hoje irei clonar um PDB remotamente.

Informações dos servidores:

Hosts: 
srv03 - 192.168.13.131 - Database 19.0.0.0.0 CDB character set WE8ISO8859P15 (Template)
srv04 - 192.168.13.134 - Database 19.17.0.0.0 CDB character set AL32UTF8.

Informações do servidor que irei clonar o PDB HML com origem no servidor srv03:

set lines 200
col DATABASE_HOST for a25;
col DB_UNIQUE_NAME for a25
col HOST_NAME for a15;
col DATABASE_ROLE for a10
col OPEN_MODE for a10
col STARTUP_TIME for a20
alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';
SELECT i.HOST_NAME "DATABASE_HOST" ,
i.INSTANCE_NAME "DB_NAME",
d.db_unique_name "DB_UNIQUE_NAME" , 
d.DATABASE_ROLE " DATABASE_ROLE",
i.VERSION,
d.OPEN_MODE " OPEN_MODE ",
d.LOG_MODE
,STARTUP_TIME 
from GV$DATABASE d, gv$instance i 
where i.INST_ID=d.INST_ID;
DATABASE_HOST             DB_NAME          DB_UNIQUE_NAME             DATABASE_ROLE   VERSION            OPEN_MODE           LOG_MODE     STARTUP_TIME
------------------------- ---------------- ------------------------- ---------------- ----------------- -------------------- ------------ --------------------
srv03                     orcl             orcl                      PRIMARY          19.0.0.0.0        READ WRITE           NOARCHIVELOG 04/02/2023 11:09:27

Character set do servidor de origem srv03:

COLUMN parameter FORMAT A30
COLUMN value FORMAT A30

SELECT *
FROM   nls_database_parameters
WHERE  parameter = 'NLS_CHARACTERSET';
PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               WE8ISO8859P15

PDB HML que irei clonar:

SQL> show pdbs

   CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 HML                            READ WRITE NO
         4 DEV                            READ WRITE NO
         5 PRD                            READ WRITE NO

Character set dos PDBs existentes do servidor de origem srv03:

SET LINESIZE 1000
col parameter format a20
col value format a20
select * from (
select con_id, (select pdb_name from cdb_pdbs p where p.con_id=l.con_id) pdb_name, parameter,value
from containers(nls_database_parameters)  l
where parameter = 'NLS_CHARACTERSET'
) t where pdb_name is not null order by con_id;
CON_ID PDB_NAME    PARAMETER            VALUE
---------- ----- -------------------- --------------------
         3 HML    NLS_CHARACTERSET     WE8ISO8859P15
         4 DEV    NLS_CHARACTERSET     WE8ISO8859P15
         5 PRD    NLS_CHARACTERSET     WE8ISO8859P15

Informações do servidor de destino que irei criar o PDB HML_NOVO clonado:

set lines 200
col DATABASE_HOST for a25;
col DB_UNIQUE_NAME for a25
col HOST_NAME for a15;
col DATABASE_ROLE for a10
col OPEN_MODE for a10
col STARTUP_TIME for a20
alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';
SELECT i.HOST_NAME "DATABASE_HOST" ,
i.INSTANCE_NAME "DB_NAME",
d.db_unique_name "DB_UNIQUE_NAME" , 
d.DATABASE_ROLE " DATABASE_ROLE",
i.VERSION,
d.OPEN_MODE " OPEN_MODE ",
d.LOG_MODE
,STARTUP_TIME 
from GV$DATABASE d, gv$instance i 
where i.INST_ID=d.INST_ID;
DATABASE_HOST             DB_NAME          DB_UNIQUE_NAME             DATABASE_ROLE   VERSION            OPEN_MODE           LOG_MODE     STARTUP_TIME
------------------------- ---------------- ------------------------- ---------------- ----------------- -------------------- ------------ --------------------
srv04                     prd19            prd19                     PRIMARY          19.0.0.0.0        READ WRITE           ARCHIVELOG   04/02/2023 11:18:05

Verifiquei os PDBs existentes e se o character set do CDB de destino é AL32UTF8, apenas com este caráter podemos ter diversos PDBs com character set diferentes, recomendo sempre realizar esta verificação do charset:

COLUMN parameter FORMAT A30
COLUMN value FORMAT A30

SELECT *
FROM   nls_database_parameters
WHERE  parameter = 'NLS_CHARACTERSET';
PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               AL32UTF8
SQL> show pdbs

    CON_ID CON_NAME      OPEN MODE  RESTRICTED
---------- ------------ ---------- ----------
         2 PDB$SEED      READ ONLY  NO
         3 ORCLPDB       READ WRITE NO

CON_ID  PDB_NAME   PARAMETER         VALUE
------  ---------  ----------------- ---------
 3      ORCLPDB     NLS_CHARACTERSET AL32UTF8 <=======

Criei a entrada no TNSNAMES com as informações do PDB de origem (HML) que irei usar para clonar no destino, lembrando que se o ambiente for RAC, todos os nodes devem ter o mesmo tnsnames e apontamentos.

[oracle@srv04 ~]$ cat /u01/app/oracle/product/19.3.0.0/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.3.0.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_PRD19 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = srv04)(PORT = 1521))


hml =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = srv03)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hml)
    )
  )

Testei a conexão com TNSPING no servidor srv04 para o servidor srv03:

[oracle@srv04 ~]$ tnsping hml

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 04-FEB-2023 11:52:52

Copyright (c) 1997, 2022, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.3.0.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = srv03)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = hml)))
OK (0 msec)

Testei a conexão com SQLPLUS acessando o servidor srv04:

[oracle@srv04 ~]$ tnsping hml

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 04-FEB-2023 11:52:52

Copyright (c) 1997, 2022, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.3.0.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = srv03)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = hml)))
OK (0 msec)
[oracle@srv04 ~]$ sqlplus sys@hml as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 4 11:53:14 2023
Version 19.17.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

No servidor de origem srv03, irei conectar no PDB HML e criar um usuário com as permissões necessárias para que eu possa conectar via dblink para realizar o clone do PDB.

SQL> alter session set container=HML;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
HML
SQL> CREATE USER ADM_HML IDENTIFIED BY "oracle";

User created.

SQL> GRANT CONNECT,CREATE PLUGGABLE DATABASE TO ADM_HML;

Grant succeeded.

Se for necessário uma cópia exata do PDB HML sem modificações, recomendo que o PDB fique com status de READ ONLY, servidor srv03:

SQL> ALTER PLUGGABLE DATABASE HML CLOSE;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE HML OPEN READ ONLY;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME        OPEN MODE  RESTRICTED
---------- -------------- ---------- ----------
         2 PDB$SEED        READ ONLY  NO
         3 HML             READ ONLY  NO <======== READ ONLY
         4 DEV             READ WRITE NO
         5 PRD             READ WRITE NO

Irei criar o dblink apontando para o PDB HML com o usuário criado ADM_HML no servidor de destino srv04:

DATABASE_HOST             DB_NAME          DB_UNIQUE_NAME             DATABASE_ROLE   VERSION            OPEN_MODE           LOG_MODE     STARTUP_TIME
------------------------- ---------------- ------------------------- ---------------- ----------------- -------------------- ------------ --------------------
srv04                     prd19            prd19                     PRIMARY          19.0.0.0.0        READ WRITE           ARCHIVELOG   04/02/2023 11:18:05

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> create database link DBLINK_HML connect to ADM_HML identified by oracle using 'hml';

Database link created.

Testei o acesso do dblink DBLINK_HML:

SQL> select 1 from dual@DBLINK_HML;

         1
----------
         1

Verifiquei se o parâmetro db_create esta configurado:

SYS@db AS SYSDBA> show parameter db_create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATAC1
db_create_online_log_dest_1          string      +DATAC1
db_create_online_log_dest_2          string      +RECOC1
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string

Criei o PDB HML_NOVO via dblink remoto conectando na origem srv03 PDB referência HML:

SQL> create pluggable database HML_NOVO from HML@DBLINK_HML;

Pluggable database created.

Listei os PDBs no servidor de destino srv04:

SQL> show pdbs

    CON_ID CON_NAME       OPEN MODE  RESTRICTED
---------- -------------- ---------- ----------
         2 PDB$SEED       READ ONLY  NO
         3 ORCLPDB        READ WRITE NO
         5 HML_NOVO      MOUNTED
SQL>

Abrir o PDB HML_NOVO para uso:

SQL> alter pluggable database HML_NOVO open instances=all;
SQL> alter pluggable database all save state instances=all;

Listei os PDBs no servidor de destino srv04:

SQL> show pdbs

    CON_ID CON_NAME       OPEN MODE  RESTRICTED
---------- -------------- ---------- ----------
         2 PDB$SEED       READ ONLY  NO
         3 ORCLPDB        READ WRITE NO
         5 HML_NOVO       READ WRITE NO
SQL>

Agora que finalizei o clone do pdb com os passos acima, ja podemos utilizar.

search previous next tag category expand menu location phone mail time cart zoom edit close