Oracle 12c introduces full transportable database import over network . It  employs

•  Oracle Data Pump import  to extract  all of the system, user, and application metadata needed to transport the database from the source database over network.
•   transportable tablespaces mechanism to move user and application data i.e. datafiles containing user and application data are physically copied to the target. This results in a migration that is very fast, even for very large volumes of data.

This post focuses on the use of full transportable import  to transport user and application tablespaces from a non-CDB to another non-CDB over the network . The intermediate dumpfile containing the metadata need not be created   as metadata is transferred over network by means of a database link.

– source database : orcl on RHEL5.4 64-bit server
. with sample schemas
. filesystem
. noarchivelog

-- destination database dest on same server
. no sample schemas
. filesystem
. noarchivelog

Overview:

– Create a source non-CDB  orcl with sample schemas
– Create destination non-CDB dest on the same  server without sample schemas
– Set the user and application tablespaces in the source database (orcl) to be READ ONLY
– Copy the  data files for tablespaces containing user/application data to the destination location
– Set system tablespace as default permanent tablespace in destination database dest
– Drop users tablespace from destination database
– Create a database link from destination  to source database orcl which connects as the user with datapump_imp_full_database privilege (system)
– Using an account that has the DATAPUMP_IMP_FULL_DATABASE privilege (system), import into the target database using
impdp with network_link , FULL=Y, TRANSPORT_DATAFILES parameters
– Restore the user tablespaces in source database to read/write mode

Implementation :

– Put application tablespaces (example and users) in read only mode in source database (orcl)

ORCL>conn / as sysdba

select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf

ORCL> alter tablespace example read only;
      alter tablespace users  read only;

-  Copy the  data files for tablespaces containing user/application data to the destination

ORCL> ho cp /u01/app/oracle/oradata/orcl/example01.dbf /u01/app/oracle/oradata/dest/example01_orcl.dbf

ho cp /u01/app/oracle/oradata/orcl/users01.dbf /u01/app/oracle/oradata/dest/users01_orcl.dbf

– Create a database link from destination database (dest) to source database (orcl) which connects as the user (system) with datapump_imp_full_database privilege

DEST>create public database link orcl_link connect to system identified by oracle using 'orcl';

- Using an account(system)  that has the DATAPUMP_IMP_FULL_DATABASE privilege , import into the target database using impdp with  network_link , FULL=Y, TRANSPORT_DATAFILES parameters

DEST> ho impdp system/oracle full=y network_link=orcl_link transportable=always transport_datafiles='/u01/app/oracle/oradata/dest/users01_orcl.dbf','/u01/app/oracle/oradata/dest/example01_orcl.dbf' version=12 logfile=import.log

Import: Release 12.1.0.1.0 - Production on Tue Aug 5 04:19:47 2014

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** full=y network_link=orcl_link transportable=always directory=imp_dir transport_datafiles=/u01/app/oracle/oradata/dest/users01_orcl.dbf,/u01/app/oracle/oradata/dest/example01_orcl.dbf version=12 logfile=import.log
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
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29349: tablespace 'USERS' already exists

Job "SYSTEM"."SYS_IMPORT_FULL_01" stopped due to fatal error at Tue Aug 5 04:20:30 2014 elapsed 0 00:00:30

DEST> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

DEST>drop tablespace users including contents and datafiles;

drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace

– Assign system as default permanent tablespace and drop users tablespace.

– Perform import

DEST> alter database default tablespace system;

      drop tablespace users including contents and datafiles;

       ho impdp system/oracle full=y network_link=orcl_link transportable=always transport_datafiles='/u01/app/oracle/oradata/dest/users01_orcl.dbf','/u01/app/oracle/oradata/dest/example01_orcl.dbf' version=12 logfile=import.log

Import: Release 12.1.0.1.0 - Production on Tue Aug 5 04:25:58 2014

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** full=y network_link=orcl_link transportable=always directory=imp_dir transport_datafiles=/u01/app/oracle/oradata/dest/users01_orcl.dbf,/u01/app/oracle/oradata/dest/example01_orcl.dbf version=12 logfile=import.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE

...

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/AUDIT
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 30 error(s) at Tue Aug 5 04:36:40 2014 elapsed 0 00:10:38

– Verify that tablespaces example and users have been successfully imported into target database

DEST> col tablespace_name for a10
col file_name for a50
set pagesize 200
set line 500

select tablespace_name, file_name, status, online_status from dba_data_files;

TABLESPACE FILE_NAME                                          STATUS    ONLINE_
---------- -------------------------------------------------- --------- -------
EXAMPLE    /u01/app/oracle/oradata/dest/example01_orcl.dbf    AVAILABLE ONLINE
UNDOTBS1   /u01/app/oracle/oradata/dest/undotbs01.dbf         AVAILABLE ONLINE
SYSAUX     /u01/app/oracle/oradata/dest/sysaux01.dbf          AVAILABLE ONLINE
SYSTEM     /u01/app/oracle/oradata/dest/system01.dbf          AVAILABLE SYSTEM
USERS      /u01/app/oracle/oradata/dest/users01_orcl.dbf      AVAILABLE ONLINE

– Restore the user tablespaces in source database to read/write mode

ORCL> alter tablespace example read write;
           alter tablespace users read write;

I hope this post was useful. Your comments and suggestions are always welcome!

References :

http://docs.oracle.com/database/121/ADMIN/transport.htm#ADMIN13729

—————————————————————————————

Related Links:

Home

Database 12c

 12c: Transportable Database

———————————————————————————