1024programmer Blog Full Transportable Export/Import experiment_dingdingfish’s blog

Full Transportable Export/Import experiment_dingdingfish’s blog

author: admin Post: August 30, 2023 22read 0Comment

class=”markdown_views prism-atom-one-dark”>

A brief description of the environment

Two instances, the source library is non-CDB, and the target is CDB. Both versions are 19.3. on the same host.
Experiment to migrate non-CDB to PDB in CDB: hr_pdb

Experimental process

Make sure the platform and endianness are the same:

 SELECT d.PLATFORM_NAME,ENDIAN_FORMAT
 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
 WHERE tp.PLATFORM_ID = d.PLATFORM_ID;
 PLATFORM_NAMEENDIAN_FORMAT
 -------------------- --------------
 Linux x86 64-bit Little

Confirm that the tablespace is self-contained:

ORCLCDB2 EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('users', TRUE);

 PL/SQL procedure successfully completed.

 ORCCCDB2 SELECT * FROM TRANSPORT_SET_VIOLATIONS;

 no rows selected

Create Directory:

ORCLCDB2 CREATE DIRECTORY dp_dir AS '/u01/app/datafiles'  ;

 Directory created.

Make the tablespace read-only:

ORCLCDB2 ALTER TABLESPACE users READ ONLY;

 Tablespace altered.

Enable full transportable tablespace export, if the source database is 11gR2, you also need to specify the VERSION=12 parameter:

expdp system/Welcome1 full=y transportable=always  \
 directory=dp_dir dumpfile=full_tts.dmp \
 metrics=y exclude=statistics \
 logfile=full_tts_export.log

 Export: Release 19.0.0.0.0 - Production on Sat Oct 26 20:39:56 2019
 Version 19.3.0.0.0

 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
 Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** full=y transportable=always directory=dp_dir dumpfile=full_tts.dmp metrics=y exclude=statistics logfile=full_tts_export.log
 W-1 Startup took 1 second
 W-1 Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
 W-1 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
 W-1 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
 W-1 Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
 W-1 Completed PLUGTS_TABLESPACE objectsin seconds
 W-1 Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
 W-1 Completed 1 PLUGTS_BLK objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/END_PLUGTS_BLK
 W-1 Completed 1 PLUGTS_BLK objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
 W-1 Completed 1 MARKER objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
 W-1 Completed 1 MARKER objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/TABLESPACE
 W-1 Completed 2 TABLESPACE objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/PROFILE
 W-1 Completed 1 PROFILE objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/USER
 W-1 Completed 9 USER objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/RADM_FPTM
 W-1 Completed 1 RADM_FPTM objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
 W-1 Completed 7 PROC_SYSTEM_GRANT objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
 W-1 Completed 77 SYSTEM_GRANT objects in 1 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
 W-1 Completed 19 ROLE_GRANT objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
 W-1 Completed 13 DEFAULT_ROLE objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
 W-1 Completed 10 ON_USER_GRANT objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
 W-1 Completed 8 TABLESPACE_QUOTA objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/RESOURCE_COST
 W-1 Completed 1 RESOURCE_COST objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
 W-1 Completed 1 TRUSTED_DB_LINK objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
 W-1 Completed 42 SEQUENCE objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
 W-1 Completed 10 DIRECTORY objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
 W-1 Completed 38 OBJECT_GRANT objects in 1 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
 W-1 Completed 14 SYNONYM objects in 1 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TYPE/INC_TYPE
 W-1 Completed 4 INC_TYPE objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
 W-1 Completed 32 TYPE objects in 1 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
 W-1 Completed 10 OBJECT_GRANT objects in 2 seconds
 W-1 Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
 W-1 Completed 2 PROCACT_SYSTEM objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
 W-1 Completed 23 PROCOBJ objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
 W-1 Completed 4 PROCACT_SYSTEM objects in 1 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
 W-1 Completed 15 PROCACT_SCHEMA objects in 1 seconds
 W-1 Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
 W-1 Completed 1 TABLE objects in 8 seconds
 W-1 Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
 W-1 Completed 1 MARKER objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/XMLSCHEMA/XMLSCHEMA
 W-1 Completed 1 XMLSCHEMA objects in 1 seconds
 W-1 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
 W-1 Completed 63 TABLE objects in 53 seconds
 W-1 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
 W-1 Completed 18 TABLE objects in 29 seconds
 W-1 Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
 W-1 Completed 1 MARKER objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
 W-1 Completed 119 TABLE objects in 44 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
 W-1 Completed 35 OBJECT_GRANT objects in 2 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
 W-1 Completed 1580 COMMENT objects in 2 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
 W-1 Completed 1 FUNCTION objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
 W-1 Completed 3 PROCEDURE objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
 W-1 Completed 1 ALTER_FUNCTION objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
 W-1 Completed 3 ALTER_PROCEDURE objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
 W-1 Completed 21 VIEW objects in 4 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
 W-1 Completed 5 OBJECT_GRANT objects in 1 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
 W-1 Completed 4 COMMENT objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY
 W-1 Completed 3 TYPE_BODY objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
 W-1 Completed 64 INDEX objects in 2 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
 W-1 Completed 1 INDEX objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
 W-1 Completed 270 CONSTRAINT objects in 3 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
 W-1 Completed 30 REF_CONSTRAINT objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/BITMAP_INDEX/INDEX
 W-1 Completed 15 INDEX objects in 1 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/SECONDARY_TABLE/TABLE
 W-1 Completed 4 TABLE objects in 39 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/SECONDARY_TABLE/INDEX/INDEX
 W-1 Completed 1 INDEX objects in 1 seconds
 W--1 Completed 21 VIEW objects in 3 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
 W-1 Completed 5 OBJECT_GRANT objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
 W-1 Completed 4 COMMENT objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY
 W-1 Completed 3 TYPE_BODY objects in 1 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
 W-1 Completed 64 INDEX objects in 4 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
 W-1 Completed 1 INDEX objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
 W-1 Completed 270 CONSTRAINT objects in 8 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
 W-1 Completed 30 REF_CONSTRAINT objects in 1 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/BITMAP_INDEX/INDEX
 W-1 Completed 15 INDEX objects in 2 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/SECONDARY_TABLE/TABLE
 W-1 Completed 4 TABLE objects in 2 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/SECONDARY_TABLE/INDEX/INDEX
 W-1 Completed 1 INDEX objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/SECONDARY_TABLE/CONSTRAINT
 W-1 Completed 1 CONSTRAINT objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/INDEX
 W-1 Completed 1 INDEX objects in 2 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
 W-1 Completed 3 TRIGGER objects in 1 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER
 W-1 Completed 2 TRIGGER objects in 1 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW
 W-1 Completed 2 MATERIALIZED_VIEW objects in 3 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/DIMENSION
 W-1 Completed 5 DIMENSION objects in 1 seconds
 W-1 Processing object type DATABASE_EXPORT/END_PLUGTS_BLK
 W-1 Completed 1 PLUGTS_BLK objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
 W-1 Completed 1 MARKER objects in 22 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
 W-1 Completed 15 PROCACT_INSTANCE objects in 3 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
 W-1 Completed 10 PROCDEPOBJ objects in 1 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
 W-1 Completed 6 PROCOBJ objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
 W-1 Completed 58 PROCACT_SCHEMA objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY
 W-1 Completed 1 AUDIT_POLICY objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
 W-1 Completed 4 AUDIT_POLICY_ENABLE objects in 0 seconds
 W-1 Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
 W-1 Completed 1 MARKER objects in 8 seconds
 W-1 Completed 1 DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA objects in 2 seconds
 W-1 Completed 64 DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA objects in 1751 seconds
 W-1 Completed 18 DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA objects in 0 seconds
 Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 7  error(s) at Sat Oct 26 21:17:45 2019 elapsed 0 00:03:44

Restore the source database tablespace to read-write:

ORCLCDB2 ALTER TABLESPACE users READ WRITE;

 Tablespace altered.

Authentication:

-- The table space has been imported, that is, the last user01.dbf
 hr_pdb select name from v$datafile;

 name
 --------------------------------------------------  --------------------------------------------------  --------------------
 /u01/app/oracle/oradata/ORCLCDB/95D0BBBB049C5788E0530100007F48C9/datafile/o1_mf_system_gv8jcnj7_.dbf
 /u01/app/oracle/oradata/ORCLCDB/95D0BBBB049C5788E0530100007F48C9/datafile/o1_mf_sysaux_gv8jcnjj_.dbf
 /u01/app/oracle/oradata/ORCLCDB/95D0BBBB049C5788E0530100007F48C9/datafile/o1_mf_undotbs1_gv8jcnjl_.dbf
 /opt/oracle/oradata/hr_pdb/users01.dbf
 -- data imported
 hr_pdb select count(*) from hr. employees;

   COUNT(*)
 ----------
        107

Conclusion

Full transportable export/import is a new feature in Oracle Database 12c that greatly simplifies the
process of database migration. Combining the ease of use of Oracle Data Pump with the performance of transportable tablespaces, full transportable export/import gives you the ability to upgrade or migrate to Oracle Database 12c in a single operation if your source database is at least Oracle Database 11g Release 2 (11.2.0.3). Full transportable export/import is a valuable tool for migrating to pluggable databases, allowing you to take advantage of the cost savings and economies of scale inherent in moving to a multitenant architecture.

Reference

  1. Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
  2. An Oracle White Paper: January 2014: Oracle Database 12c: Full Transportable Export/Import

T/MARKER
W-1 Completed 1 MARKER objects in 8 seconds
W-1 Completed 1 DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA objects in 2 seconds
W-1 Completed 64 DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA objects in 1751 seconds
W-1 Completed 18 DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA objects in 0 seconds
Job “SYSTEM”.“SYS_IMPORT_TRANSPORTABLE_01” completed with 7 error(s) at Sat Oct 26 21:17:45 2019 elapsed 0 00:03:44

Restore the source database tablespace to read-write:

ORCLCDB2 ALTER TABLESPACE users READ WRITE;

 Tablespace altered.

Authentication:

-- The table space has been imported, that is, the last user01.dbf
 hr_pdb select name from v$datafile;

 name
 --------------------------------------------------  --------------------------------------------------  --------------------
 /u01/app/oracle/oradata/ORCLCDB/95D0BBBB049C5788E0530100007F48C9/datafile/o1_mf_system_gv8jcnj7_.dbf
 /u01/app/oracle/oradata/ORCLCDB/95D0BBBB049C5788E0530100007F48C9/datafile/o1_mf_sysaux_gv8jcnjj_.dbf
 /u01/app/oracle/oradata/ORCLCDB/95D0BBBB049C5788E0530100007F48C9/datafile/o1_mf_undotbs1_gv8jcnjl_.dbf
 /opt/oracle/oradata/hr_pdb/users01.dbf
 -- data imported
 hr_pdb select count(*) from hr. employees;

   COUNT(*)
 ----------
        107

Conclusion

Full transportable export/import is a new feature in Oracle Database 12c that greatly simplifies the
process of database migration. Combining the ease of use of Oracle Data Pump with the performance of transportable tablespaces, full transportable export/import gives you the ability to upgrade or migrate to Oracle Database 12c in a single operation if your source database is at least Oracle Database 11g Release 2 (11.2.0.3). Full transportable export/import is a valuable tool for migrating to pluggable databases, allowing you to take advantage of the cost savings and economies of scale inherent in moving to a multitenant architecture.

Reference

  1. Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
  2. An Oracle White Paper: January 2014: Oracle Database 12c: Full Transportable Export/Import

This article is from the internet and does not represent1024programmerPosition, please indicate the source when reprinting:https://www.1024programmer.com/full-transportable-export-import-experiment_dingdingfishs-blog/
Reward
tag:

author: admin

Previous article
Next article

Related recommendations

Leave a Reply

Your email address will not be published. Required fields are marked *

Contact Us

Contact us

181-3619-1160

Online consultation: QQ交谈

E-mail: [email protected]

Working hours: Monday to Friday, 9:00-17:30, holidays off

Follow wechat
Scan wechat and follow us

Scan wechat and follow us

Follow Weibo
Back to top
首页
微信
电话
搜索