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 fazer o unplug do PDB HML com character set WE8ISO8859P15, e plug no CDB localizado no servidor srv04.
Informações dos servidores:
Hosts:
srv03 - 192.168.13.131 - Database 19.0.0.0.0 CDB character set WE8ISO8859P15.
srv04 - 192.168.13.134 - Database 19.17.0.0.0 CDB character set AL32UTF8.Database de origem 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 02/07/2023 11:09:27
CDB character set 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 WE8ISO8859P15PDB existentes servidor de origem srv03:
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:
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 srv04 para plug do PDB HML com character set WE8ISO8859P15:
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
Verificando 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 sv04 que iremos fazer o plug 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 <=======Verificando o sqlpatch aplicado no servidor de origem srv03 PDB HML com id=3:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 HML READ WRITE NO <============ CON_ID=3
4 DEV READ WRITE NO
5 PRD READ WRITE NO
SQL> alter session set container=HML;
SQL> select con_id,patch_id,action,status,description from cdb_registry_sqlpatch where con_id = 3 order by action_time;
no rows selectedPDB que iremos migrar para o ambiente srv04:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 HML READ WRITE NO <==== PDB que iremos migrar para o servidor srv04.
4 DEV READ WRITE NO
5 PRD READ WRITE NOVerificando o local dos datafiles do PDB HML no servidor srv03:
SQL> alter session set container=HML;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
HML
SET LINESIZE 3000
col file_name format a120
col NAME format a120
select con_id,name from v$datafile where con_id=3; CON_ID NAME
---------- ------------------------------------------------------------------------------------------------------------------------
3 /u01/app/oracle/oradata/ORCL/F3E158F2E74D0F7CE053830DA8C044D3/datafile/o1_mf_system_kxwsryjf_.dbf
3 /u01/app/oracle/oradata/ORCL/F3E158F2E74D0F7CE053830DA8C044D3/datafile/o1_mf_sysaux_kxwsryjn_.dbf
3 /u01/app/oracle/oradata/ORCL/F3E158F2E74D0F7CE053830DA8C044D3/datafile/o1_mf_undotbs1_kxwsryjo_.dbf
3 /u01/app/oracle/oradata/ORCL/F3E158F2E74D0F7CE053830DA8C044D3/datafile/o1_mf_users_kxwss70c_.dbf
Fechando o PDB HML no servidor srv03.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter pluggable database HML close immediate;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 HML MOUNTED
4 DEV READ WRITE NO
5 PRD READ WRITE NO
Realizando a operação de unplug do PDB HML no servidor srv03:
[oracle@srv03 ~]$ mkdir -p /home/oracle/PDB_HML
SQL> alter pluggable database HML unplug into '/home/oracle/PDB_HML/PDB_HML_desc.xml';
Pluggable database altered.
[oracle@srv03 ~]$ ls -lat /home/oracle/PDB_HML
total 12
drwxr-xr-x 2 oracle oinstall 30 Feb 4 13:23 .
-rw-r--r-- 1 oracle oinstall 7574 Feb 4 13:23 PDB_HML_desc.xml
drwx------ 6 oracle oinstall 4096 Feb 4 13:23 ..
Verificando o status do PDB HML depois da operação de unplug no servidor srv03:
COL PDB_NAME FOR A30
select pdb_name, status from cdb_pdbs;PDB_NAME STATUS
------------ ----------
HML UNPLUGGED
PDB$SEED NORMAL
DEV NORMAL
PRD NORMALCriando o diretório para o PDB HML no servidor de destino srv04 para a operação de pluggable no CDB:
[oracle@srv04 PRD19]$ mkdir -p hml
[oracle@srv04 hml]$ pwd
/u01/app/oracle/oradata/PRD19/hmlCopiando os datafiles e arquivo xml gerado do PDB HML com origem srv03 para o servidor srv04:
[oracle@srv04 hml]$ rsync -e ssh -Pav [email protected]:/u01/app/oracle/oradata/ORCL/F3E158F2E74D0F7CE053830DA8C044D3/datafile/ /u01/app/oracle/oradata/PRD19/hml/ --recursive
[oracle@srv04 hml]$ rsync -e ssh -Pav [email protected]:/home/oracle/PDB_HML/PDB_HML_desc.xml /u01/app/oracle/oradata/PRD19/hml/ --recursive
[oracle@srv04 hml]$ ls -lat /u01/app/oracle/oradata/PRD19/hml/
total 666680
drwxr-x--- 2 oracle oinstall 4096 Feb 4 13:31 .
drwxr-x--- 6 oracle oinstall 4096 Feb 4 13:26 ..
-rw-r--r-- 1 oracle oinstall 7574 Feb 4 13:23 PDB_HML_desc.xml
-rw-r----- 1 oracle oinstall 262152192 Feb 4 13:23 o1_mf_sysaux_kxwsryjn_.dbf
-rw-r----- 1 oracle oinstall 272637952 Feb 4 13:23 o1_mf_system_kxwsryjf_.dbf
-rw-r----- 1 oracle oinstall 104865792 Feb 4 13:23 o1_mf_undotbs1_kxwsryjo_.dbf
-rw-r----- 1 oracle oinstall 5251072 Feb 4 13:23 o1_mf_users_kxwss70c_.dbf
-rw-r----- 1 oracle oinstall 37756928 Feb 4 12:00 o1_mf_temp_kxwsryjo_.dbf
No CDB de destino srv04, execute a verificação de compatibilidade. Nesse caso, a saída informa que o PDB não é compatível.
SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => '/u01/app/oracle/oradata/PRD19/hml/PDB_HML_desc.xml', pdb_name => 'HML')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/NO
PL/SQL procedure successfully completed.
Verificação dos problemas de violação do PDB HML no servidor srv04:
set lines 300
col status for a10
col type for a10
col cause for a20
col message for a90
SELECT status, type, cause, message FROM pdb_plug_in_violations where name ='' and status <> 'RESOLVED' and type <> 'WARNING';Por curiosidade podemos ver que existe uma violação do tipo WARNING:
set lines 300
col status for a10
col type for a10
col cause for a20
col message for a90
SELECT status, type, cause, message FROM pdb_plug_in_violations;STATUS TYPE CAUSE MESSAGE
---------- ---------- -------------------- ------------------------------------------------------------------------------------------
PENDING WARNING PDB not Unicode PDB not in Unicode (AL32UTF8) character set. PDB character set WE8ISO8859P15.
Iremos realizar a operação de pluggable do PDB HML informando o caminho anterior e o novo dos datafiles no servidor srv04:
SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/PRD19/hml/' scope=memory;
System altered.
SQL> create pluggable database HML as clone using '/u01/app/oracle/oradata/PRD19/hml/PDB_HML_desc.xml' source_file_name_convert=('/u01/app/oracle/oradata/ORCL/F3E158F2E74D0F7CE053830DA8C044D3/datafile/','/u01/app/oracle/oradata/PRD19/hml/') move;
Pluggable database created.
Obtendo informações do PDB HML que fizemos o processo do pluggable no CDB do servidor srv04:
SQL> select pdb_name, status from cdb_pdbs;
PDB_NAME STATUS
------------- ----------
ORCLPDB NORMAL
PDB$SEED NORMAL
HML NEW <===============
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------- ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
7 HML MOUNTED
Listando os datafiles do PDB HML que fizemos o processo do pluggable no CDB do servidor srv04:
SET LINESIZE 3000
col file_name format a120
col NAME format a120
select con_id,name from v$datafile where con_id=7; CON_ID NAME
---------- ------------------------------------------------------------------------------------------------------------------------
7 /u01/app/oracle/oradata/PRD19/hml/PRD19/F3E3768CC29B2B3FE053860DA8C00886/datafile/o1_mf_system_kxx2txx1_.dbf
7 /u01/app/oracle/oradata/PRD19/hml/PRD19/F3E3768CC29B2B3FE053860DA8C00886/datafile/o1_mf_sysaux_kxx2txx3_.dbf
7 /u01/app/oracle/oradata/PRD19/hml/PRD19/F3E3768CC29B2B3FE053860DA8C00886/datafile/o1_mf_undotbs1_kxx2txx3_.dbf
7 /u01/app/oracle/oradata/PRD19/hml/PRD19/F3E3768CC29B2B3FE053860DA8C00886/datafile/o1_mf_users_kxx2txx4_.dbf
Iremos abrir o PDB HML para uso no CDB do srv04, o PDB HML pluggable esta com status de RESTRICTED YES:
SQL> alter pluggable database HML open;
Warning: PDB altered with errors.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
7 HML READ WRITE YES
SQL>
Como fizemos o pluggable do 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 esta com RESTRICTED, iremos realizar o processo abaixo do datapatch para resolver este problema.
Observação: Se as versões e patchs 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:
[oracle@srv04 OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.17.0.0.0 Production on Sat Feb 4 13:52:17 2023
Copyright (c) 2012, 2022, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_12081_2023_02_04_13_52_17/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:
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_13_52_45.log (no errors)
SQL Patching tool complete on Sat Feb 4 13:58:22 2023
Abrir 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 esta como NO, resolvido com o datapatch:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
------- --------------- ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
7 HML READ WRITE NO <=============Verificação dos problemas de violação do PDB HML no servidor srv04 com status de RESOLVED:
set lines 300
col status for a10
col type for a10
col cause for a20
col message for a90
SELECT status, type, cause, message FROM pdb_plug_in_violations;STATUS TYPE CAUSE MESSAGE
---------- ---------- -------------------- ------------------------------------------------------------------------------------------
RESOLVED ERROR SQL Patch '19.17.0.0.0 Release_Update 2209242240' is installed in the CDB but no release updates are installed in the PDB
RESOLVED WARNING PDB not Unicode Character set mismatch: PDB character set WE8ISO8859P15. CDB character set AL32UTF8.Verificando o sqlpatch no PDB HML do CDB no servidor srv04:
SQL> alter session set container=HML;
SQL> select con_id,patch_id,action,status,description from cdb_registry_sqlpatch where con_id = 7 order by action_time; CON_ID PATCH_ID ACTION STATUS DESCRIPTION
---------- ---------- --------------- ---------- ----------------------------------------------------------------------------------------------------
7 34419443 APPLY SUCCESS Database Release Update : 19.17.0.0.221018 (34419443)
SQL>
Character set dos PDBs existentes e do PDB 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
7 HML NLS_CHARACTERSET WE8ISO8859P15 <=============Link da documentação:
How to Unplug and Plugin PDB (Doc ID 2728046.1)
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)