FullTransportable
Full transportable tablespace: Oracle 12c
Category: Database Author: Rodrigo Mufalani (Board Member) Date: 4 years ago Comments: 0

Full transportable tablespace: Oracle 12c

This is one of most cool features on Oracle 12c and it is a improvement of a old feature called transportable tablespaces. In this howto artcile we will show to you how to use this feature introduced on 12cR1 and you can use it to migrate data from 11g directly to a PDB using simple commands, in fact you can use this with a dblink and you will not need to create dumpfiles, but we will
cover the method using dump files on this article.
First thing to do is connect on source database and create the tablespaces that we will transport, in our case will be on same CDB for demo purposes, but this is more useful when you intend to migrate data between distinct database, migrations and etc.
On PDB1, create two tablespaces, one user and one table on each tablespace:

 

SQL> alter session set container = pdb1;
 Session altered.

SQL> create tablespace ts_mufalani_01 datafile '/u01/app/oracle/oradata/cdb1/pdb1/ts_mufalani01.dbf' size 10m ;
 Tablespace created.

SQL> create tablespace ts_mufalani_02 datafile '/u01/app/oracle/oradata/cdb1/pdb1/ts_mufalani02.dbf' size 10m ;
 Tablespace created.

SQL> create user mufalani identified by oracle default tablespace ts_mufalani_01;
 User created.

SQL> alter user mufalani quota unlimited on ts_mufalani_01;
 User altered.

SQL> alter user mufalani quota unlimited on ts_mufalani_02;
 User altered.

SQL> create table mufalani.test1 tablespace ts_mufalani_01 as select * from dba_users;
 Table created.

SQL> create table mufalani.test2 tablespace ts_mufalani_02 as select * from v$session;
 Table created.

SQL> alter tablespace ts_mufalani_01 read only;
 Tablespace altered.

SQL> alter tablespace ts_mufalani_02 read only;
 Tablespace altered.

 

Now, the tablespaces are read only, check if the tablespaces are self-contained using dbms_tts.transport_set_check, it means, there is no dependency in any other tablespaces.

 

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('ts_mufalani_01,ts_mufalani_02',TRUE);
 PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
 no rows selected

 

No rows selected is the expected result on this select. Remember that the directory home, just exists on pdb1 so far, so use EZconnect to export data for the PDB1.

 

SQL> create or replace directory home as '/home/oracle' ;
 Directory created.

 SQL> grant read, write on directory home to system;
 Grant succeeded.

 

After create the directory and grant access on it to user system, export the metadata using transportable=ALWAYS and full=y, it instructs Oracle to use the full transportable tablespace feature.

 

SQL> !expdp system/oracle_4U@vm2:1521/PDB1 directory=home dumpfile=tts.dmp logfile=tts.log transportable=always full=y 
 Export: Release 12.1.0.2.0 - Production on Fri Sep 13 21:17:05 2019
 Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/@vm2:1521/PDB1 directory=home dumpfile=tts.dmp logfile=tts.log transportable=always full=y 
 Estimate in progress using BLOCKS method…
 Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
 Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
 Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
 Total estimation using BLOCKS method: 4.265 MB
 Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
 Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
 Processing object type DATABASE_EXPORT/TABLESPACE
 Processing object type DATABASE_EXPORT/PROFILE
 Processing object type DATABASE_EXPORT/SYS_USER/USER
 Processing object type DATABASE_EXPORT/SCHEMA/USER
 Processing object type DATABASE_EXPORT/RADM_FPTM
 Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
 Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
 Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
 Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
 Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
 Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
 Processing object type DATABASE_EXPORT/RESOURCE_COST
 Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
 Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
 Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
 Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
 Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
 Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
 Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
 Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
 Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
 Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
 Processing object type DATABASE_EXPORT/STATISTICS/MARKER
 Processing object type DATABASE_EXPORT/END_PLUGTS_BLK
 Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
 Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
 Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
 Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
 Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
 . . exported "SYS"."KU$_USER_MAPPING_VIEW"               6.109 KB      38 rows
 . . exported "ORDDATA"."ORDDCM_DOCS"                     252.9 KB       9 rows
 . . exported "WMSYS"."WM$CONSTRAINTS_TABLE$"                 0 KB       0 rows
 . . exported "WMSYS"."WM$LOCKROWS_INFO$"                     0 KB       0 rows
 . . exported "WMSYS"."WM$UDTRIG_INFO$"                       0 KB       0 rows
 . . exported "LBACSYS"."OLS$AUDIT_ACTIONS"               5.757 KB       8 rows
 . . exported "LBACSYS"."OLS$DIP_EVENTS"                  5.539 KB       2 rows
 . . exported "LBACSYS"."OLS$INSTALLATIONS"               6.960 KB       2 rows
 . . exported "LBACSYS"."OLS$PROPS"                       6.234 KB       5 rows
 . . exported "SYS"."DAM_CONFIG_PARAM$"                   6.531 KB      14 rows
 . . exported "SYS"."TSDP_PARAMETER$"                     5.953 KB       1 rows
 . . exported "SYS"."TSDP_POLICY$"                        5.921 KB       1 rows
 . . exported "SYS"."TSDP_SUBPOL$"                        6.328 KB       1 rows
 . . exported "SYSTEM"."REDO_DB"                          25.59 KB       1 rows
 . . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$"         0 KB       0 rows
 . . exported "WMSYS"."WM$CONS_COLUMNS$"                      0 KB       0 rows
 . . exported "WMSYS"."WM$ENV_VARS$"                      6.015 KB       3 rows
 . . exported "WMSYS"."WM$EVENTS_INFO$"                   5.812 KB      12 rows
 . . exported "WMSYS"."WM$HINT_TABLE$"                    9.453 KB      75 rows
 . . exported "WMSYS"."WM$MODIFIED_TABLES$"                   0 KB       0 rows
 . . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$"         0 KB       0 rows
 . . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$"        0 KB       0 rows
 . . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$"              0 KB       0 rows
 . . exported "WMSYS"."WM$NEXTVER_TABLE$"                 6.375 KB       1 rows
 . . exported "WMSYS"."WM$REMOVED_WORKSPACES_TABLE$"          0 KB       0 rows
 . . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$"          0 KB       0 rows
 . . exported "WMSYS"."WM$RIC_LOCKING_TABLE$"                 0 KB       0 rows
 . . exported "WMSYS"."WM$RIC_TABLE$"                         0 KB       0 rows
 . . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$"                0 KB       0 rows
 . . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$"             0 KB       0 rows
 . . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$"       5.984 KB       1 rows
 . . exported "WMSYS"."WM$VERSION_TABLE$"                     0 KB       0 rows
 . . exported "WMSYS"."WM$VT_ERRORS_TABLE$"                   0 KB       0 rows
 . . exported "WMSYS"."WM$WORKSPACES_TABLE$"              12.10 KB       1 rows
 . . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$"          7.054 KB      10 rows
 . . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$"        0 KB       0 rows
 . . exported "LBACSYS"."OLS$AUDIT"                           0 KB       0 rows
 . . exported "LBACSYS"."OLS$COMPARTMENTS"                    0 KB       0 rows
 . . exported "LBACSYS"."OLS$DIP_DEBUG"                       0 KB       0 rows
 . . exported "LBACSYS"."OLS$GROUPS"                          0 KB       0 rows
 . . exported "LBACSYS"."OLS$LAB"                             0 KB       0 rows
 . . exported "LBACSYS"."OLS$LEVELS"                          0 KB       0 rows
 . . exported "LBACSYS"."OLS$POL"                             0 KB       0 rows
 . . exported "LBACSYS"."OLS$POLICY_ADMIN"                    0 KB       0 rows
 . . exported "LBACSYS"."OLS$POLS"                            0 KB       0 rows
 . . exported "LBACSYS"."OLS$POLT"                            0 KB       0 rows
 . . exported "LBACSYS"."OLS$PROFILE"                         0 KB       0 rows
 . . exported "LBACSYS"."OLS$PROFILES"                        0 KB       0 rows
 . . exported "LBACSYS"."OLS$PROG"                            0 KB       0 rows
 . . exported "LBACSYS"."OLS$SESSINFO"                        0 KB       0 rows
 . . exported "LBACSYS"."OLS$USER"                            0 KB       0 rows
 . . exported "LBACSYS"."OLS$USER_COMPARTMENTS"               0 KB       0 rows
 . . exported "LBACSYS"."OLS$USER_GROUPS"                     0 KB       0 rows
 . . exported "LBACSYS"."OLS$USER_LEVELS"                     0 KB       0 rows
 . . exported "SYS"."AUD$"                                    0 KB       0 rows
 . . exported "SYS"."DAM_CLEANUP_EVENTS$"                     0 KB       0 rows
 . . exported "SYS"."DAM_CLEANUP_JOBS$"                       0 KB       0 rows
 . . exported "SYS"."TSDP_ASSOCIATION$"                       0 KB       0 rows
 . . exported "SYS"."TSDP_CONDITION$"                         0 KB       0 rows
 . . exported "SYS"."TSDP_FEATURE_POLICY$"                    0 KB       0 rows
 . . exported "SYS"."TSDP_PROTECTION$"                        0 KB       0 rows
 . . exported "SYS"."TSDP_SENSITIVE_DATA$"                    0 KB       0 rows
 . . exported "SYS"."TSDP_SENSITIVE_TYPE$"                    0 KB       0 rows
 . . exported "SYS"."TSDP_SOURCE$"                            0 KB       0 rows
 . . exported "SYSTEM"."REDO_LOG"                             0 KB       0 rows
 . . exported "SYSTEM"."SCHEDULER_JOB_ARGS"                   0 KB       0 rows
 . . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS"           9.515 KB      12 rows
 . . exported "SYS"."AUDTAB$TBS$FOR_EXPORT"               5.953 KB       2 rows
 . . exported "SYS"."DBA_SENSITIVE_DATA"                      0 KB       0 rows
 . . exported "SYS"."DBA_TSDP_POLICY_PROTECTION"              0 KB       0 rows
 . . exported "SYS"."FGA_LOG$FOR_EXPORT"                      0 KB       0 rows
 . . exported "SYS"."NACL$_ACE_EXP"                       9.929 KB       1 rows
 . . exported "SYS"."NACL$_HOST_EXP"                      6.914 KB       1 rows
 . . exported "SYS"."NACL$_WALLET_EXP"                        0 KB       0 rows
 . . exported "WMSYS"."WM$EXP_MAP"                        7.718 KB       3 rows
 Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
 
 Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
   /home/oracle/tts.dmp
 
 Datafiles required for transportable tablespace TS_MUFALANI_01:
   /u01/app/oracle/oradata/cdb1/pdb1/ts_mufalani01.dbf
 Datafiles required for transportable tablespace TS_MUFALANI_02:
   /u01/app/oracle/oradata/cdb1/pdb1/ts_mufalani02.dbf
 Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Fri Sep 13 21:30:32 2019 elapsed 0 00:13:10

 

As we can see, the metadata and some data where exported and also Oracle marked to us on final of the log the datafiles required to transport and plug into the other database.
It’s time to copy the datafiles to the pdb on destination database, in our case, is the same CDB, but could be a different database, as you want.

 

SQL> !cp /u01/app/oracle/oradata/cdb1/pdb1/tsmufalani* /u01/app/oracle/oradata/cdb1/pdb2/ 
On destination PDB, we need to create the directory pointing to the same path where we put the dumpfiles, so, we just use the same command to create the directory inside PDB2 as we have created on PDB1, HOME, using the same path and grant to system user.
 
SQL> alter session set container = PDB2;
 Session altered.

SQL> create or replace directory home as '/home/oracle' ;
 Directory created.

SQL> grant read, write on directory home to system;
 Grant succeeded.
As the setup is fine on pdb2, we can import the metadata of the plugged datafiles, we must use the parameter transport_datafiles and put the files enclosed by (‘) and separaterd by (,) as we can see below. On the output we will see some errors related to APEX component, please ignore it, it will not cause any issue on our procedure. Keep in mind it is an VM for test purposes, on a real environment, you may want to remove apex before to execute this procedure. I have a blog note for it, please check it.
 
SQL> !impdp system/oracle_4U@vm2:1521/PDB2 full=Y dumpfile=tts.dmp directory=home transport_datafiles='/u01/app/oracle/oradata/cdb1/pdb2/ts_mufalani01.dbf','/u01/app/oracle/oradata/cdb1/pdb2/ts_mufalani02.dbf' logfile=import.log
 Import: Release 12.1.0.2.0 - Production on Fri Sep 13 21:34:50 2019
 Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
 Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/@vm2:1521/PDB2 full=Y dumpfile=tts.dmp directory=home transport_datafiles=/u01/app/oracle/oradata/cdb1/pdb2/ts_mufalani01.dbf,/u01/app/oracle/oradata/cdb1/pdb2/ts_mufalani02.dbf logfile=import.log 
 Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
 Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
 Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
 Processing object type DATABASE_EXPORT/TABLESPACE
 ORA-31684: Object type TABLESPACE:"TEMP" already exists
 Processing object type DATABASE_EXPORT/PROFILE
 Processing object type DATABASE_EXPORT/SYS_USER/USER
 Processing object type DATABASE_EXPORT/SCHEMA/USER
 Processing object type DATABASE_EXPORT/RADM_FPTM
 Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
 Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
 Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
 Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
 Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
 Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
 Processing object type DATABASE_EXPORT/RESOURCE_COST
 Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
 Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
 ORA-31684: Object type DIRECTORY:"HOME" already exists
 Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
 Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
 Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
 Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
 Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
 Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
 Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
 . . imported "SYS"."KU$_EXPORT_USER_MAP"                 6.109 KB      38 rows
 Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
 . . imported "ORDDATA"."ORDDCM_DOCS_TRANSIENT"           252.9 KB       9 rows
 . . imported "WMSYS"."E$CONSTRAINTS_TABLE$"                  0 KB       0 rows
 . . imported "WMSYS"."E$LOCKROWS_INFO$"                      0 KB       0 rows
 . . imported "WMSYS"."E$UDTRIG_INFO$"                        0 KB       0 rows
 . . imported "SYS"."AMGT$DP$DAM_CONFIG_PARAM$"           6.531 KB      14 rows
 . . imported "SYS"."DP$TSDP_PARAMETER$"                  5.953 KB       1 rows
 . . imported "SYS"."DP$TSDP_POLICY$"                     5.921 KB       1 rows
 . . imported "SYS"."DP$TSDP_SUBPOL$"                     6.328 KB       1 rows
 . . imported "SYSTEM"."REDO_DB_TMP"                      25.59 KB       1 rows
 . . imported "WMSYS"."E$BATCH_COMPRESSIBLE_TABLES$"          0 KB       0 rows
 . . imported "WMSYS"."E$CONS_COLUMNS$"                       0 KB       0 rows
 . . imported "WMSYS"."E$ENV_VARS$"                       6.015 KB       3 rows
 . . imported "WMSYS"."E$EVENTS_INFO$"                    5.812 KB      12 rows
 . . imported "WMSYS"."E$HINT_TABLE$"                     9.453 KB      75 rows
 . . imported "WMSYS"."E$MODIFIED_TABLES$"                    0 KB       0 rows
 . . imported "WMSYS"."E$MP_GRAPH_WORKSPACES_TABLE$"          0 KB       0 rows
 . . imported "WMSYS"."E$MP_PARENT_WORKSPACES_TABLE$"         0 KB       0 rows
 . . imported "WMSYS"."E$NESTED_COLUMNS_TABLE$"               0 KB       0 rows
 . . imported "WMSYS"."E$NEXTVER_TABLE$"                  6.375 KB       1 rows
 . . imported "WMSYS"."E$REMOVED_WORKSPACES_TABLE$"           0 KB       0 rows
 . . imported "WMSYS"."E$RESOLVE_WORKSPACES_TABLE$"           0 KB       0 rows
 . . imported "WMSYS"."E$RIC_LOCKING_TABLE$"                  0 KB       0 rows
 . . imported "WMSYS"."E$RIC_TABLE$"                          0 KB       0 rows
 . . imported "WMSYS"."E$RIC_TRIGGERS_TABLE$"                 0 KB       0 rows
 . . imported "WMSYS"."E$UDTRIG_DISPATCH_PROCS$"              0 KB       0 rows
 . . imported "WMSYS"."E$VERSION_HIERARCHY_TABLE$"        5.984 KB       1 rows
 . . imported "WMSYS"."E$VERSION_TABLE$"                      0 KB       0 rows
 . . imported "WMSYS"."E$VT_ERRORS_TABLE$"                    0 KB       0 rows
 . . imported "WMSYS"."E$WORKSPACES_TABLE$"               12.10 KB       1 rows
 . . imported "WMSYS"."E$WORKSPACE_PRIV_TABLE$"           7.054 KB      10 rows
 . . imported "WMSYS"."E$WORKSPACE_SAVEPOINTS_TABLE$"         0 KB       0 rows
 . . imported "LBACSYS"."OLS_DP$OLS$AUDIT"                    0 KB       0 rows
 . . imported "LBACSYS"."OLS_DP$OLS$COMPARTMENTS"             0 KB       0 rows
 . . imported "LBACSYS"."OLS_DP$OLS$GROUPS"                   0 KB       0 rows
 . . imported "LBACSYS"."OLS_DP$OLS$LAB"                      0 KB       0 rows
 . . imported "LBACSYS"."OLS_DP$OLS$LEVELS"                   0 KB       0 rows
 . . imported "LBACSYS"."OLS_DP$OLS$POL"                      0 KB       0 rows
 . . imported "LBACSYS"."OLS_DP$OLS$POLS"                     0 KB       0 rows
 . . imported "LBACSYS"."OLS_DP$OLS$POLT"                     0 KB       0 rows
 . . imported "LBACSYS"."OLS_DP$OLS$PROFILE"                  0 KB       0 rows
 . . imported "LBACSYS"."OLS_DP$OLS$PROG"                     0 KB       0 rows
 . . imported "LBACSYS"."OLS_DP$OLS$USER"                     0 KB       0 rows
 . . imported "SYS"."AMGT$DP$AUD$"                            0 KB       0 rows
 . . imported "SYS"."AMGT$DP$DAM_CLEANUP_EVENTS$"             0 KB       0 rows
 . . imported "SYS"."AMGT$DP$DAM_CLEANUP_JOBS$"               0 KB       0 rows
 . . imported "SYS"."DP$TSDP_ASSOCIATION$"                    0 KB       0 rows
 . . imported "SYS"."DP$TSDP_CONDITION$"                      0 KB       0 rows
 . . imported "SYS"."DP$TSDP_FEATURE_POLICY$"                 0 KB       0 rows
 . . imported "SYS"."DP$TSDP_PROTECTION$"                     0 KB       0 rows
 . . imported "SYS"."DP$TSDP_SENSITIVE_DATA$"                 0 KB       0 rows
 . . imported "SYS"."DP$TSDP_SENSITIVE_TYPE$"                 0 KB       0 rows
 . . imported "SYS"."DP$TSDP_SOURCE$"                         0 KB       0 rows
 . . imported "SYSTEM"."REDO_LOG_TMP"                         0 KB       0 rows
 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
 . . imported "SYSTEM"."SCHEDULER_JOB_ARGS_TMP"               0 KB       0 rows
 . . imported "SYSTEM"."SCHEDULER_PROGRAM_ARGS_TMP"       9.515 KB      12 rows
 . . imported "SYS"."AMGT$DP$AUDTAB$TBS$FOR_EXPORT"       5.953 KB       2 rows
 . . imported "SYS"."DP$DBA_SENSITIVE_DATA"                   0 KB       0 rows
 . . imported "SYS"."DP$DBA_TSDP_POLICY_PROTECTION"           0 KB       0 rows
 . . imported "SYS"."AMGT$DP$FGA_LOG$FOR_EXPORT"              0 KB       0 rows
 . . imported "SYS"."NACL$_ACE_IMP"                       9.929 KB       1 rows
 . . imported "SYS"."NACL$_HOST_IMP"                      6.914 KB       1 rows
 . . imported "SYS"."NACL$_WALLET_IMP"                        0 KB       0 rows
 . . imported "WMSYS"."E$EXP_MAP"                         7.718 KB       3 rows
 Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
 Processing object type DATABASE_EXPORT/STATISTICS/MARKER
 Processing object type DATABASE_EXPORT/END_PLUGTS_BLK
 Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
 Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
 ORA-39083: Object type PROCOBJ:"APEX_040200"."ORACLE_APEX_PURGE_SESSIONS" failed to create with error:
 ORA-27477: "APEX_040200"."ORACLE_APEX_PURGE_SESSIONS" already exists
 Failing sql is:
 BEGIN 
 dbms_scheduler.create_job('"ORACLE_APEX_PURGE_SESSIONS"',
 job_type=>'STORED_PROCEDURE', job_action=>
 'WWV_FLOW_CACHE.PURGE_SESSIONS'
 , number_of_arguments=>0,
 start_date=>TO_TIMESTAMP_TZ('03-SEP-2019 10.15.30.179792000 PM +02:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=> 
 'FREQ
 ORA-39083: Object type PROCOBJ:"APEX_040200"."ORACLE_APEX_MAIL_QUEUE" failed to create with error:
 ORA-27477: "APEX_040200"."ORACLE_APEX_MAIL_QUEUE" already exists
 Failing sql is:
 BEGIN 
 dbms_scheduler.create_job('"ORACLE_APEX_MAIL_QUEUE"',
 job_type=>'STORED_PROCEDURE', job_action=>
 'WWV_FLOW_MAIL.PUSH_QUEUE_IMMEDIATE'
 , number_of_arguments=>0,
 start_date=>TO_TIMESTAMP_TZ('03-SEP-2019 10.15.30.239709000 PM +02:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=> 
 'FREQ=MINUTE
 ORA-39083: Object type PROCOBJ:"APEX_040200"."ORACLE_APEX_WS_NOTIFICATIONS" failed to create with error:
 ORA-27477: "APEX_040200"."ORACLE_APEX_WS_NOTIFICATIONS" already exists
 Failing sql is:
 BEGIN 
 dbms_scheduler.create_job('"ORACLE_APEX_WS_NOTIFICATIONS"',
 job_type=>'STORED_PROCEDURE', job_action=>
 'WWV_FLOW_WORKSHEET_API.DO_NOTIFY'
 , number_of_arguments=>0,
 start_date=>TO_TIMESTAMP_TZ('03-SEP-2019 10.15.30.244043000 PM +02:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval
 ORA-39083: Object type PROCOBJ:"APEX_040200"."ORACLE_APEX_DAILY_MAINTENANCE" failed to create with error:
 ORA-27477: "APEX_040200"."ORACLE_APEX_DAILY_MAINTENANCE" already exists
 Failing sql is:
 BEGIN 
 dbms_scheduler.create_job('"ORACLE_APEX_DAILY_MAINTENANCE"',
 job_type=>'STORED_PROCEDURE', job_action=>
 'WWV_FLOW_MAINT.DAILY_MAINTENANCE'
 , number_of_arguments=>0,
 start_date=>TO_TIMESTAMP_TZ('03-SEP-2019 10.15.30.248433000 PM +02:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_inter
 Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
 Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
 Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
 Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 6 error(s) at Fri Sep 13 21:40:12 2019 elapsed 0 00:05:16

After the import is done, we can check the data on the destination PDB, PDB2.

SQL> alter session set container =pdb2;
 Session altered.

SQL> select file_name from dba_data_files;

 FILE_NAME
--------------------------------------------------------
 /u01/app/oracle/oradata/cdb1/pdb2/sysaux01.dbf
 /u01/app/oracle/oradata/cdb1/pdb2/ts_mufalani01.dbf
 /u01/app/oracle/oradata/cdb1/pdb2/system01.dbf
 /u01/app/oracle/oradata/cdb1/pdb2/ts_mufalani02.dbf
 
SQL> select count(1) from mufalani.test1;

COUNT(1)
------------------------
 38

SQL> select count(1) from mufalani.test2;
 COUNT(1)
------------------------
42
 
 SQL> show con_name;

 CON_NAME
------------------------
 PDB2
 
So, this is one of most used technique to migrate databases to Oracle 12cR1, you can check more info on Oracle documentation: 
https://docs.oracle.com/database/121/ADMIN/transport.htm#GUID-1901E9C3-8FCE-4D4E-AB65-34D703474E52
and also on Mike Dietrich blog – https://mikedietrichde.com/tag/transportable-tablespaces/

 

This article was written by André Ontalba and me.
 

Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited  to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful.”