Creating a new PDB using the DBMS_PDB package on a Non-CDB

Multitenant / 14/07/2022

Neste artigo vou simular a criação de um PDB utilizando a DBMS_PDB. No ambiente de origem (Non-CDB), vou deixar o banco de dados em read only:

[oracle@oel8 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Sat Mar 20 15:48:25 2021
Version 18.3.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.3.0.0.0
 
SQL> SELECT NAME,OPEN_MODE,CDB FROM V$DATABASE;
 
NAME      OPEN_MODE            CDB
--------- -------------------- ---
RMANDB    READ WRITE           NO
 
SQL> SHU IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.
 
Total System Global Area 1610612016 bytes
Fixed Size                  8658224 bytes
Variable Size             520093696 bytes
Database Buffers         1073741824 bytes
Redo Buffers                8118272 bytes
Database mounted.
SQL> ALTER DATABASE OPEN READ ONLY;
 
Database altered.

Gerando o arquivo XML com os dados do banco de dados a partir da DBMS_PDB:

SQL> exec DBMS_PDB.DESCRIBE( PDB_DESCR_FILE => '/home/oracle/RMANDB.xml');
 
PL/SQL procedure successfully completed.
 
SQL> ! ls -lthr /home/oracle/RMANDB.xml
-rw-r--r--. 1 oracle oinstall 7.7K Mar 20 15:51 /home/oracle/RMANDB.xml

Copiando o arquivo gerado da máquina de origem para a máquina de destino:

[oracle@oel8 ~]$ cd
[oracle@oel8 ~]$ scp RMANDB.xml oracle@192.168.0.106:/home/oracle/RMANDB.xml
The authenticity of host '192.168.0.106 (192.168.0.106)' can't be established.
ECDSA key fingerprint is SHA256:4UQCI0pPac0916hthopcW6hQhmzzxfPVXuhSwCuqlLQ.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.0.106' (ECDSA) to the list of known hosts.
oracle@192.168.0.106's password:
RMANDB.xml                                                                                            100% 7877     4.0MB/s   00:00
[oracle@oel8 ~]$ rm RMANDB.xml

No ambiente de destino, vou criar o diretório que abrigará os dados do novo PDB (mantive o mesmo diretório da origem):

[oracle@oel8 dados]$ ps -ef | grep pmon
oracle    2322     1  0 15:44 ?        00:00:00 ora_pmon_TALAMO
oracle    4761  3076  0 15:57 pts/0    00:00:00 grep --color=auto pmon
[oracle@oel8 dados]$ mkdir -p /oracle/dados/RMANDB/datafile

Na máquina de origem, vou disparar o SQL dinâmico abaixo para gerar os comandos de cópia de todos os datafiles:

SQL> SET PAGESIZE 20
SQL> SET LINESIZE 200
SQL> SELECT 'scp -p ' || NAME || ' oracle@192.168.0.106:/oracle/dados/RMANDB/datafile' as SCP FROM V$DATAFILE ORDER BY 1;
 
SCP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
scp -p /oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf oracle@192.168.0.106:/oracle/dados/RMANDB/datafile
scp -p /oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf oracle@192.168.0.106:/oracle/dados/RMANDB/datafile
scp -p /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf oracle@192.168.0.106:/oracle/dados/RMANDB/datafile
scp -p /oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf oracle@192.168.0.106:/oracle/dados/RMANDB/datafile
scp -p /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf oracle@192.168.0.106:/oracle/dados/RMANDB/datafile
 
SQL>

Baixando o banco de origem:

SQL> SHU IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

Realizando a cópia dos arquivos:

[oracle@oel8 datafile]$ scp -p /oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf oracle@192.168.0.106:/oracle/dados/RMANDB/datafile
oracle@192.168.0.106's password:
o1_mf_sysaux_h8nyq35q_.dbf                                                                            100%  630MB  42.8MB/s   00:14
[oracle@oel8 datafile]$ scp -p /oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf oracle@192.168.0.106:/oracle/dados/RMANDB/datafile
oracle@192.168.0.106's password:
o1_mf_system_h8nynqfx_.dbf                                                                            100%  860MB  49.1MB/s   00:17
[oracle@oel8 datafile]$ scp -p /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf oracle@192.168.0.106:/oracle/dados/RMANDB/datafile
oracle@192.168.0.106's password:
o1_mf_undotbs1_h8nyrjdr_.dbf                                                                          100%  305MB  40.6MB/s   00:07
[oracle@oel8 datafile]$ scp -p /oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf oracle@192.168.0.106:/oracle/dados/RMANDB/datafile
oracle@192.168.0.106's password:
o1_mf_users_h8nyrkn7_.dbf                                                                             100% 5128KB  46.3MB/s   00:00
[oracle@oel8 datafile]$ scp -p /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf oracle@192.168.0.106:/oracle/dados/RMANDB/datafile
oracle@192.168.0.106's password:
ts_cortex_catalog.dbf                                                                                 100%  200MB  57.6MB/s   00:03
[oracle@oel8 datafile]$

Checando compatibilidade do PDB com o CDB:

[oracle@oel8 dados]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Sat Mar 20 16:06:22 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> SET SERVEROUTPUT ON
begin
        IF DBMS_PDB.CHECK_PLUG_COMPATIBILITY( PDB_DESCR_FILE =>
                '/home/oracle/RMANDB.xml', PDB_NAME => 'BSS') THEN
                DBMS_OUTPUT.PUT_LINE('COMPATIBLE');
        ELSE
                DBMS_OUTPUT.PUT_LINE('NOT COMPATIBLE');
        END IF;
end;
/SQL>   2    3    4    5    6    7    8    9
NOT COMPATIBLE
 
PL/SQL procedure successfully completed.

Percebo que a falta de compatibilidade acontece pois na origem possui um 18.3.0.0.0, e no destino um 18.13.0.0.0 (apesar de acontecer alguns Warning devido diferença de valores de parâmetros entre os ambientes, como SGA). A View me reporta:

Warning: Non-CDB to PDB (PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.)
Warning: Parameter (CDB parameter nls_date_format mismatch: Previous 'YYYY-MM-DD:HH24:MI:SS' Current)
Warning: Parameter (CDB parameter sga_target mismatch: Previous 1536M Current 2352M)
Warning: Parameter (CDB parameter pga_aggregate_target mismatch: Previous 512M Current 781M)
SQL Patch: Error ('18.3.0.0.0 Release_Update 1806280943' is installed in the CDB but '18.3.0.0.0 Release_Update 180628094' is installed in the PDB)

Em vez de aplicar um contorno igual ESTE artigo que fiz, vou aplicar logo o PSU JAN21 na origem. Após isso, farei novamente o processo de geração de XML e check de compatibilidade:

[oracle@oel8 OPatch]$ cd $ORACLE_HOME/OPatch
[oracle@oel8 OPatch]$ ps -ef | grep pmon
oracle   17962     1  0 16:38 ?        00:00:00 ora_pmon_RMANDB
oracle   18666  2452  0 16:39 pts/0    00:00:00 grep --color=auto pmon
[oracle@oel8 OPatch]$ ./opatch lsinventory | grep escript
ARU platform description:: Linux x86-64
Patch description:  "Database Release Update : 18.13.0.0.210119 (32204699)"
Patch description:  "OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)"
Patch description:  "OCW RELEASE UPDATE 18.3.0.0.0 (28090553)"

Após gerar novamente o arquivo XML (omiti aqui para não ficar redundante), rodando novamente o check:

SQL> SET SERVEROUTPUT ON
begin
        IF DBMS_PDB.CHECK_PLUG_COMPATIBILITY( PDB_DESCR_FILE =>
                '/home/oracle/RMANDB.xml', PDB_NAME => 'BSS') THEN
                DBMS_OUTPUT.PUT_LINE('COMPATIBLE');
        ELSE
                DBMS_OUTPUT.PUT_LINE('NOT COMPATIBLE');
        END IF;
end;
/SQL>   2    3    4    5    6    7    8    9
COMPATIBLE
 
PL/SQL procedure successfully completed.

Criando efetivamente o novo PDB:

SQL> CREATE PLUGGABLE DATABASE BSS USING '/home/oracle/RMANDB.xml';
 
Pluggable database created.

Realizando a conversão de banco non-CDB para PDB:

SQL> ALTER SESSION SET CONTAINER=BSS;
 
Session altered.
 
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL> SET VERIFY OFF
SQL>
SQL> -- save settings
SQL> STORE SET ncdb2pdb.settings.sql REPLACE
Wrote file ncdb2pdb.settings.sql
 
...
 
SQL> set timing OFF
SQL> set trimout ON
SQL> set trimspool ON
SQL> set underline "-"
SQL> set verify OFF
SQL> set wrap ON
SQL> set xmloptimizationcheck OFF

Abrindo o novo PDB e validando.

SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;
 
Session altered.
 
SQL> ALTER PLUGGABLE DATABASE BSS OPEN;
 
Pluggable database altered.
 
SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;
 
NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB$SEED
READ ONLY
 
HIPOFISE1
MOUNTED
 
HIPOFISE2
READ WRITE
 
BSS
READ WRITE
 
 
4 rows selected.

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 new PDB using the DBMS_PDB package on a Non-CDB”

  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