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:16After 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.”
