Exporting from non-CDB and Importing into PDB using Data Pump utility

Administration, Multitenant / 13/07/2022

Neste artigo, vamos reproduzir um processo de Export de tablespace (usando o modo FULL TRANSPORTABLE) de um banco Non-CDB para um Pluggable Database.

No ambiente de origem, vamos analisar as tablespaces existentes, e analisar aquelas que não são de sistema (SYSTEM/SYSAUX/UNDO ou TEMP):

[oracle@oel8 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jun 22 06:14:52 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> SELECT NAME,OPEN_MODE,CDB FROM V$DATABASE;
 
NAME      OPEN_MODE            CDB
--------- -------------------- ---
RMANDB    READ WRITE           NO
 
SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;
 
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TS_CORTEX_CATALOG
TS_HIPO_CATALOG
 
7 rows selected.

Percebo que tenho uma tablespace chamada “TS_CORTEX_CATALOG” que pode ser utilizada para nosso teste. Validando que o owner “CORTEX_CATALOG” utiliza apenas a referida tablespace:

SQL> SELECT DISTINCT TABLESPACE_NAME FROM DBA_TABLES WHERE OWNER='CORTEX_CATALOG';
 
TABLESPACE_NAME
------------------------------
TS_CORTEX_CATALOG

Dentro desse owner, vou pegar uma tabela de referência, para que possamos comparar quando o import for realizado no destino (PDB):

SQL> SELECT COUNT(*) FROM CORTEX_CATALOG.CONFIG;
 
  COUNT(*)
----------
         1

Uma vez avaliado a tablespace de origem, podemos deixá-la como Read Only:

SQL> ALTER TABLESPACE TS_CORTEX_CATALOG READ ONLY;
 
Tablespace altered.

Criando directory no banco de origem, onde o EXPDP será realizado:

[oracle@oel8 ~]$ mkdir EXPORT
[oracle@oel8 ~]$ chmod 775 EXPORT/
[oracle@oel8 ~]$ pwd
/home/oracle
[oracle@oel8 ~]$ cd EXPORT/
[oracle@oel8 EXPORT]$ pwd
/home/oracle/EXPORT
[oracle@oel8 EXPORT]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jun 22 06:26:14 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> CREATE DIRECTORY BSS AS '/home/oracle/EXPORT';
 
Directory created.

Parâmetros do EXPDP:

[oracle@oel8 EXPORT]$ cat exp.par
USERID=system/oracle
DUMPFILE=exp.dmp
TRANSPORT_TABLESPACES=TS_CORTEX_CATALOG
DIRECTORY=BSS
LOGFILE=exp.log

Disparando processo:

[oracle@oel8 EXPORT]$ expdp parfile=exp.par
 
Export: Release 18.0.0.0.0 - Production on Wed Jun 23 05:11:45 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** parfile=exp.par
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /home/oracle/EXPORT/exp.dmp
******************************************************************************
Datafiles required for transportable tablespace TS_CORTEX_CATALOG:
  /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Wed Jun 23 05:12:42 2021 elapsed 0 00:00:55

Agora vamos nos apropriar do ambiente de destino:

[oracle@quiasma ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jun 22 06:35:55 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> SELECT NAME,OPEN_MODE,CDB FROM V$DATABASE;
 
NAME      OPEN_MODE            CDB
--------- -------------------- ---
ASWAN     READ WRITE           YES
 
SQL> SHOW PDBS;
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 HIPOFISE2                      READ WRITE NO
         4 HIPOFISE1                      READ WRITE NO
SQL> ALTER SESSION SET CONTAINER=HIPOFISE1;
 
Session altered.
 
SQL> SELECT NAME FROM V$DATAFILE;
 
NAME
--------------------------------------------------------------------------------
/oracle/dados/ASWAN/ASWAN/C32359B357110BDFE0536A00A8C0F81A/datafile/o1_mf_system
_jbs98k3r_.dbf
 
/oracle/dados/ASWAN/ASWAN/C32359B357110BDFE0536A00A8C0F81A/datafile/o1_mf_sysaux
_jbs98k3v_.dbf
 
/oracle/dados/ASWAN/ASWAN/C32359B357110BDFE0536A00A8C0F81A/datafile/o1_mf_undotb
s1_jbs98k3w_.dbf
 
/oracle/dados/ASWAN/ASWAN/C32359B357110BDFE0536A00A8C0F81A/datafile/o1_mf_users_
jbsg5qtv_.dbf
 
NAME
--------------------------------------------------------------------------------

No ambiente de origem, podemos disparar os comandos de SCP do datafile que é parte da tablespace que selecionamos para migrar. O diretório de destino é o local onde reside os datafiles do PDB HIPOFISE1, conforme coletamos acima:

[oracle@oel8 EXPORT]$ scp /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf oracle@192.168.0.106:/oracle/dados/ASWAN/ASWAN/C32359B357110BDFE0536A00A8C0F81A/datafile/ts_cortex_catalog.dbf
oracle@192.168.0.106's password:
ts_cortex_catalog.dbf                                                                100%  200MB 134.1MB/s   00:01
[oracle@oel8 EXPORT]$

Agora podemos voltar a tablespace de origem para READ WRITE:

SQL> ALTER TABLESPACE TS_CORTEX_CATALOG READ WRITE;
 
Tablespace altered.

Criando um DIRECTORY no banco de destino:

[oracle@quiasma ~]$ mkdir BSS
[oracle@quiasma ~]$ chmod 7775 BSS/
[oracle@quiasma ~]$ cd BSS/
[oracle@quiasma BSS]$ pwd
/home/oracle/BSS
[oracle@quiasma BSS]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Jun 23 04:14:48 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> ALTER SESSION SET CONTAINER=HIPOFISE1;
 
Session altered.
 
SQL> CREATE DIRECTORY BSS AS '/home/oracle/BSS';
 
Directory created.

Copiando o arquivo dump da origem para o destino:

[oracle@oel8 EXPORT]$ pwd
/home/oracle/EXPORT
[oracle@oel8 EXPORT]$ ll
total 3476
-rw-r-----. 1 oracle oinstall 3551232 Jun 22 06:34 exp.dmp
-rw-r--r--. 1 oracle oinstall    2066 Jun 22 06:34 exp.log
-rwxrwxr-x. 1 oracle oinstall     108 Jun 22 06:33 exp.par
[oracle@oel8 EXPORT]$ scp exp.dmp oracle@192.168.0.106:/home/oracle/BSS/exp.dmp
oracle@192.168.0.106's password:
exp.dmp                                                                              100% 3468KB  58.0MB/s   00:00
[oracle@oel8 EXPORT]$

Configurando parâmetros de import (detalhe que o processo será realizado no PDB HIPOFISE1):

[oracle@quiasma BSS]$ cat imp.par
USERID=system/oracle@HIPOFISE1
DUMPFILE=exp.dmp
TRANSPORT_DATAFILES='/oracle/dados/ASWAN/ASWAN/C32359B357110BDFE0536A00A8C0F81A/datafile/ts_cortex_catalog.dbf'
DIRECTORY=BSS
LOGFILE=imp.log

O import é realizado, apesar de algumas triggers ficarem inválidas:

[oracle@quiasma BSS]$ impdp parfile=imp.par
 
Import: Release 18.0.0.0.0 - Production on Wed Jun 23 05:16:35 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/********@HIPOFISE1 parfile=imp.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
ORA-39082: Object type TRIGGER:"CORTEX_CATALOG"."SCRL_TRIGGER" created with compilation warnings
 
ORA-39082: Object type TRIGGER:"CORTEX_CATALOG"."XMLSTORE_INSERT_TRIGGER" created with compilation warnings
 
ORA-39082: Object type TRIGGER:"CORTEX_CATALOG"."SCR_TRIGGER" created with compilation warnings
 
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 3 error(s) at Wed Jun 23 05:16:58 2021 elapsed 0 00:00:22
 
[oracle@quiasma BSS]$

Validando que nossa tablespace foi importada no destino:

[oracle@quiasma BSS]$ sqlplus system/oracle@HIPOFISE1;
 
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Jun 23 05:17:48 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
Last Successful login time: Wed Jun 23 2021 05:16:35 -03:00
 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
 
SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES ORDER BY TABLESPACE_NAME;
 
TABLESPACE_NAME
------------------------------
SYSAUX
SYSTEM
TEMP
TS_CORTEX_CATALOG
UNDOTBS1
USERS
 
6 rows selected.

SQL> SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TS_CORTEX_CATALOG';
 
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/oracle/dados/ASWAN/ASWAN/C32359B357110BDFE0536A00A8C0F81A/datafile/ts_cortex_ca
talog.dbf
TS_CORTEX_CATALOG

Validando nossa tabela de referência:

SQL> SELECT count(*) FROM CORTEX_CATALOG.CONFIG;
 
  COUNT(*)
----------
         1

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.

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish