Criando um novo PDB com o conjunto de character set diferente do CDB.

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.
A partir do Oracle Database versão 12.2.0.1, os PDBs no mesmo CDB podem ter conjuntos de caracteres diferentes, desde que o CDB seja criado com o conjunto de caracteres AL32UTF8.

Arquitetura Multitenant:

Hoje iremos criar um PDB com character set WE8ISO8859P15, diferente do CDB e PDBS existentes atualmente no ambiente.

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.

Database de origem servidor srv03, usado como template para criação do pdb com o character set WE8ISO8859P15, caso não tenha nenhum banco com o character set necessário, você poderá criar um CDB vazio para usar como template:

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

CDB character set servidor de origem srv03 que iremos usar como template:

COLUMN parameter FORMAT A30
COLUMN value FORMAT A30

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

PDB existentes servidor de origem srv03 que iremos usar como template:

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 servidor de origem srv03 que iremos usar como template:

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

Servidor de destino para criação do PDB HML com character set WE8ISO8859P15 usando o servidor srv03 como template:

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

Ao verificar se o character set do CDB de destino é AL32UTF8, apenas com este caráter podemos ter diversos PDBs com character set diferentes.

COLUMN parameter FORMAT A30
COLUMN value FORMAT A30

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

PDBs existentes no servidor que iremos criar o novo PDB chamado HML com character set WE8ISO8859P15:

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 <=======

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

[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)
    )
  )

Testando a conexão com TNSPING no 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)

Testando a conexão com SQLPLUS no 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, iremos conectar no PDB HML e criar um usuário com algumas permissões para que possamos 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

Iremos 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.

Testando o acesso do dblink DBLINK_HML:

SQL> select 1 from dual@DBLINK_HML;

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

Iremos configurar o destino dos datafiles para o PDB HML que iremos criar no servidor srv04:

SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/PRD19' scope=memory sid='*';

System altered.

Cirando o PDB HML via dblink conectando na origem srv03 PDB referencia HML:

SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/PRD19' scope=memory sid='*';

System altered.

SQL> create pluggable database HML from HML@DBLINK_HML;

Pluggable database created.

Listando 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            MOUNTED
SQL>

Abrir o PDB HML para uso:

SQL> alter pluggable database HML open;

Warning: PDB altered with errors.

Listando os PDBs no servidor de destino srv04, o PDB HML criado esta com status de RESTRICTED YES:

SQL> show pdbs

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

Como fizemos o clone de PDB HML de origem na versão 19.0.0.0.0 para a versão de destino 19.17.0.0.0, o status do PDB está com RESTRICTED, iremos realizar o processo abaixo do datapatch para resolver este problema.

Observação: Se as versões forem iguais não é necessário este passo.

PDB migration from one CDB to another CDB where the CDBs have the same “Major database release version” but different RU/RUR (this also applies for PSU or BP).

[oracle@srv04 ~]$ cd $ORACLE_HOME/OPatch
[oracle@srv04 OPatch]$ ./datapatch -verbose

Logs do datapatch aplicado:

SQL Patching tool version 19.17.0.0.0 Production on Sat Feb  4 12:41:56 2023
Copyright (c) 2012, 2022, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_7755_2023_02_04_12_41_56/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:
  No interim patches found

Current state of release update SQL patches:
  Binary registry:
    19.17.0.0.0 Release_Update 220924224051: Installed
  PDB CDB$ROOT:
    Applied 19.17.0.0.0 Release_Update 220924224051 successfully on 02-FEB-23 05.49.19.627583 PM
  PDB HML: <========= PDB HML
    No release update patches installed
  PDB ORCLPDB:
    Applied 19.17.0.0.0 Release_Update 220924224051 successfully on 02-FEB-23 05.49.28.432430 PM
  PDB PDB$SEED:
    Applied 19.17.0.0.0 Release_Update 220924224051 successfully on 02-FEB-23 05.49.24.079973 PM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED ORCLPDB
    No interim patches need to be rolled back
    No release update patches need to be installed
    No interim patches need to be applied
  For the following PDBs: HML
    No interim patches need to be rolled back
    Patch 34419443 (Database Release Update : 19.17.0.0.221018 (34419443)):
      Apply from 19.1.0.0.0 Feature Release to 19.17.0.0.0 Release_Update 220924224051
    No interim patches need to be applied

Installing patches...
Patch installation complete.  Total patches installed: 1

Validating logfiles...done
Patch 34419443 apply (pdb HML): SUCCESS  <========= PDB HML
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34419443/24972075/34419443_apply_PRD19_HML_2023Feb04_12_42_22.log (no errors)
SQL Patching tool complete on Sat Feb  4 12:48:16 2023

Abri o PDB HML para uso no servidor de destino srv04:

SQL> alter pluggable database hml close;

Pluggable database altered.

SQL> alter pluggable database hml open;

Pluggable database altered.

SQL> alter pluggable database hml save state;

Pluggable database altered.

Listando os PDBs no servidor de destino srv04, o status RESTRICTED está como NO, resolvido com o datapatch:

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

Character set dos PDBs existentes e do PDB criado HML no servidor de destino srv04:

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       ORCLPDB        NLS_CHARACTERSET  AL32UTF8
5       HML            NLS_CHARACTERSET  WE8ISO8859P15 <=========

Link da documentação:

Character Sets For CDB And PDB in 12.2 (Doc ID 2231602.1)
Moving PDB from Lower Release Update (or Patch Set Update, Bundle Patch) on Same or Higher DB Version (Doc ID 2847234.1)

Leave a Reply

Your email address will not be published. Required fields are marked *

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