Oracle 12c introduces full transportable export/import,  an  exciting new feature that greatly simplifies the process of database migration. It  employs

•  Oracle Data Pump export  to extract  all of the system, user, and application metadata needed for a database migration
•   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.

Thus, you can upgrade or migrate to Oracle Database 12c easily and quickly with a single import command. This requires that  your source database is at least Oracle Database 11g Release 2 (11.2.0.3).

This post focuses on the use of full transportable export/import  to migrate an 11.2.0.3 database to a pluggable database , although this feature can be used for migrations to a Oracle Database 12c non-CDB database as well. 

The steps for migrating from an 11.2.0.3 non-CDB  into a pluggable database are as follows:

• Set the user and application tablespaces in the source database to be READ ONLY
•  Export from the source database using expdp with the FULL=Y, VERSION=12.0 and TRANSPORTABLE=ALWAYS
• Copy the  dump file and data files for tablespaces containing user/application data to the destination
• Create a new PDB in the destination CDB using the create pluggable database command
• Create a directory object in the destination PDB pointing to the folder containing dumpfile
• Create an entry for the destination PDB in tnsnames.ora
•   Using an account that has the DATAPUMP_IMP_FULL_DATABASE privilege, import into the target database using impdp with the FULL=Y and TRANSPORT_DATAFILES parameters
• Restore the user tablespaces in source database to read/write mode

Current scenario:
Source
version : 11.2.0.3
Database : orcl
Platform 32 bit RHEL 5.4
Tablespaces : system, sysaux, users, example, undotbs1, temp
Target  
container database : cdb1
pluggable database : pdb_orcl
version : 12.1.0.1
Platform 64 bit OEL 5.4
Tablespaces : system, sysaux

Objective :

– Upgrade orcl (11.2.0.3 32 bit linux) to pdb_orcl(12.1.0.1 64 bit linux) using expdp transportable database
- While import rename users tablespace to users_orcl in target db

– Implementation –

— Source Database (ORCL) —

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

– Issue export command 
. TRANSPORTABLE=ALWAYS and FULL=Y : These parameter values tell Data Pump to use full transportable rather than conventional export methods.
.  VERSION=12   : Since source database is  11g Release 2 (11.2.0.3), we  must also specify the Data Pump parameter VERSION=12 to denote the fact that the result of the export will be imported into an Oracle Database 12c Release 1 (12.1) or later database. This is the only time that a version number greater than the current version is allowed by the expdp command. If the source database is Oracle Database 12c, with COMPATIBLE=12.0 or higher, then the VERSION parameter is not required.

Note that command fails as user tablespaces have not been put in read only mode.

[oracle@node1 bin]$ expdp system/oracle full=y transportable=always version=12.0 dumpfile=exporcl.dmp logfile=exporcl.log
Export: Release 11.2.0.3.0 - Production on Mon Mar 24 16:53:13 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** full=y version=12.0 transportable=always
Estimate in progress using BLOCKS method...
ORA-39123: Data Pump transportable tablespace job aborted
ORA-39185: The transportable tablespace failure list is

ORA-29335: tablespace 'EXAMPLE' is not read only
 ORA-29335: tablespace 'USERS' is not read only
Job "SYSTEM"."SYS_EXPORT_FULL_01" stopped due to fatal error at 16:53:20

– Set the user and application tablespaces in the source database to be READ ONLY

SQL> alter tablespace example read only;

              alter tablespace users read only;

– Issue expdp command again
After the export command completes, the export log file shows a list of all of the tablespace data files that need to be moved to the target.

[oracle@node1 bin]$ expdp system/oracle full=y transportable=always version=12.0 dumpfile=exporcl.dmp logfile=exporcl.log

Export: Release 11.2.0.3.0 - Production on Tue Mar 25 09:38:23 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** full=y transportable=always version=12.0 dumpfile=exporcl.dmp logfile=exporcl.log
Estimate in progress using BLOCKS method...

...

Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/u01/app/oracle/admin/orcl/dpdump/exporcl.dmp
******************************************************************************
Datafiles required for transportable tablespace EXAMPLE:
 /u01/app/oracle/oradata/orcl/example01.dbf
 Datafiles required for transportable tablespace USERS:
 /u01/app/oracle/oradata/orcl/users01.dbf
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 09:51:17

— TARGET CONTAINER DATABASE (CDB1) –

– Find out names of  the datafiles of   seed  database in cdb1

SQL> select file_name from cdb_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/system01.dbf
/u01/app/oracle/oradata/cdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/u01/app/oracle/oradata/cdb1/users01.dbf
/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf

– create pluggable database pdb_orcl

SQL> create pluggable database pdb_orcl admin user pdbadmin identified by oracle file_name_convert=('pdbseed','pdb_orcl');

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB_ORCL MOUNTED

-- check that pdb_orcl has  datafiles for system/sysaux tablespaces only

SQL> alter pluggable database pdb_orcl open;

     select file_name from cdb_data_files where con_id=4;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/pdb_orcl/system01.dbf
 /u01/app/oracle/oradata/cdb1/pdb_orcl/sysaux01.dbf

– Add  tnsnames.ora entry for pdb_orcl

PDB_ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = em12.example.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb_orcl)
)
)

– Copy the  export dump file and the data files for example and users tablespaces   from source to target

— copy example01.dbf and users01.dbf belonging to orcl to folder /u01/app/oracle/oradata/cdb1/pdb_orcl  on target machine

— copy exporcl.dmp containing  metadata  to the folder /u01/app/oracle/admin/cdb1 on target machine

– Create a directory object on the destination PDB (PDB_ORCL)  pointing to location of exporcl.dmp i.e. /u01/app/oracle/admin/cdb1

SQL> alter session set container=pdb_orcl;

sho con_name

CON_NAME
------------------------------
PDB_ORCL

SQL> create directory dump_dir as '/u01/app/oracle/admin/cdb1';

– Invoke full transportable import on the destination PDB

Specify following parameters :
- the dumpfile name
– full = Y and
– TRANSPORT_DATAFILES : to secify  the list of user tablespace data files to be transported

[oracle@em12 admin]$ impdp system/oracle@pdb_orcl full=y directory=dump_dir dumpfile=exporcl.dmp transport_datafiles='/u01/app/oracle/oradata/cdb1/pdb_orcl/example01.dbf', '/u01/app/oracle/oradata/cdb1/pdb_orcl/users01.dbf' remap_tablespace=users:users_orcl logfile=import_orcl.log

Import: Release 12.1.0.1.0 - Production on Tue Mar 25 11:41:44 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
Master table "SYSTEM"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Source timezone version is +00:00 and target timezone version is -07:00.
Starting "SYSTEM"."SYS_IMPORT_FULL_02": system/********@pdb_orcl full=y directory=dump_dir dumpfile=exporcl.dmp transport_datafiles=/u01/app/oracle/oradata/cdb1/pdb_orcl/example01.dbf, /u01/app/oracle/oradata/cdb1/pdb_orcl/users01.dbf remap_tablespace=users:users_orcl logfile=import_orcl.log

....
Job "SYSTEM"."SYS_IMPORT_FULL_02" completed with 3395 error(s) at Tue Mar 25 12:07:27 2014 elapsed 0 00:25:40

– Check that target PDB’s metadata is updated with datafiles for example and users tablespaces  

SQL> select name from v$datafile;

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

–  Check that the application tablespaces are automatically placed in read/write mode on the destination database and users tablespace has been renamed to users_orcl

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
EXAMPLE ONLINE
USERS_ORCL ONLINE

 — Check that user HR has been created on the target PDB also 

SQL> conn hr/hr@pdb_orcl
    select tname from tab;

TNAME
--------------------------------------------------------------------------------
COUNTRIES
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
EMP_DETAILS_VIEW

– Restore user tablespaces to read-write mode on the source database

SQL> alter tablespace USERS read write;

alter tablespace example read write;
              select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE

References: 

http://docs.oracle.com/cd/E16655_01/server.121/e17636/transport.htm#ADMIN13727
Full Transportable Export / Import – Things to know
Full Transportable Export / Import – Migrating an 11.2.0.4 Database to Oracle Database 12c – Into the Cloud

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

Related Links:

Home

Database 12c

12c: Transport Database Over Network