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 WE8ISO8859P15PDB 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 WE8ISO8859P15Servidor 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 AL32UTF8PDBs 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 NOIremos 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 -verboseLogs 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)