Written by Anju Garg

Oracle 12c introduces full transportable export/import,  an  exciting new feature that greatly simplifies the process of database migration. It allows you to upgrade or migrate to Oracle Database 12c easily and quickly with a single import command.  It  employs:

  • Oracle Data Pump export/import to move  all of the system, user, and application metadata needed for a database migration
  • Transportable tablespaces mechanism to move user and application data i.e. data files 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.

Full transportable export/import can be used to migrate from

  • One PDB to another PDB
  • A PDB to a non-CDB
  • A non-CDB database into a PDB: This requires that  your source database is at least Oracle Database 11g Release 2 (11.2.0.3). 

This article focuses on the use of full transportable export/import  to migrate a 11.2.0.3 RAC Database (orcl) into a 12c RAC Pluggable Database (pdb_orcl).

Current setup

For the purpose of this demonstration I have VM setups for two clusters:

  • Oracle Database 11.2.0.3 three node cluster and

  • Oracle Database 12.1.0.2 two node setup.

Source RAC database

Version: 11.2.0.3
Type: Non-CDB
Name: orcl
Shared Storage: ASM

Destination RAC database

Version: 12.1.0.2
Type: PDB
Name: pdb_orcl in container database cdb1
Shared Storage: ASM

Overview

The steps for migrating are as follows:

On Source System

  • Set the user and application tablespaces in the source 11.2.0.3 RAC database (orcl) to be READ ONLY

  • Export from the source database (orcl) using expdp with the FULL=Y, VERSION=12.0 and TRANSPORTABLE=ALWAYS

  • Copy the data files for tablespaces containing user/application data from ASM to file system

On Destination System

  • Create a new PDB (pdb_orcl) in the destination RAC CDB (cdb1) using the create pluggable database command

  • Copy the  dump file from source system to the folder /u01/app/oracle/admin/cdb1 on the one of the nodes (host01) hosting an instance of the destination CDB (cdb1)

  • Create a directory object (dump_dir) in the destination PDB (pdb_orcl) pointing to the folder containing the dump file (/u01/app/oracle/admin/cdb1)

  • Copy the  data files containing user/application from source system to ASM

  • Using an account having the DATAPUMP_IMP_FULL_DATABASE privilege, import into the target pluggable database (pdb_orcl) using impdp with the FULL=Y and TRANSPORT_DATAFILES parameters

  • Check that migration has been successfully performed

Implementation

On Source System

Set the user and application tablespaces in the source 11.2.0.3 RAC database (orcl) to be READ ONLY

  • Check that source database (orcl) is a cluster database having version 11.2.0.3

ORCL> 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

ORCL> sho parameter cluster_database
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     3

  • Find out names of tablespaces in source database (orcl)

ORCL> select tablespace_name , status from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
EXAMPLE                        ONLINE
UNDOTBS3                       ONLINE
SEQ                            ONLINE

  • Find out names of database user accounts in the source database (orcl)

ORCL> select username from dba_users order by 1;
USERNAME
------------------------------
ANONYMOUS
APEX_030200
APEX_PUBLIC_USER
APPQOSSYS
BI
CTXSYS
DBSNMP
DIP
EXFSYS
FLOWS_FILES
HR
IX
JFV
MDDATA
MDSYS
MGMT_VIEW
OE
OLAPSYS
ORACLE_OCM
ORDDATA
ORDPLUGINS
ORDSYS
OUTLN
OWBSYS
OWBSYS_AUDIT
PM
SCOTT
SH
SI_INFORMTN_SCHEMA
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR
SYS
SYSMAN
SYSTEM
WMSYS
XDB
XS$NULL
37 rows selected.

ORCL> select * from hr.tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
COUNTRIES                      TABLE
DEPARTMENTS                    TABLE
EMPLOYEES                      TABLE
EMP_DETAILS_VIEW               VIEW
JOBS                           TABLE
JOB_HISTORY                    TABLE
LOCATIONS                      TABLE
REGIONS                        TABLE
8 rows selected.
ORCL> select count(*) from hr.employees;

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

  • Place the user and application tablespaces in READ ONLY mode

SQL>   alter tablespace users read only;
       alter tablespace example read only;
       alter tablespace seq read only;

SQL>  select tablespace_name , status from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          READ ONLY
UNDOTBS2                       ONLINE
EXAMPLE                        READ ONLY
UNDOTBS3                       ONLINE
SEQ                            READ ONLY
9 rows selected.

Export from the source database (orcl) using expdp with the FULL=Y, VERSION=12.0 and TRANSPORTABLE=ALWAYS

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@host01 root]$ expdp system/oracle full=y transportable=always version=12.0 dumpfile=exporcl.dmp logfile=exporcl.log reuse_dumpfiles=y

Export: Release 11.2.0.3.0 - Production on Fri Aug 5 15:37:13 2016

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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_02":  system/******** full=y transportable=always version=12.0 dumpfile=exporcl.dmp logfile=exporcl.log reuse_dumpfiles=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/TABLE_DATA
object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA

....

. . exported "SYSTEM"."REPCAT$_TEMPLATE_PARMS"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS"           0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS"             0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS"          0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES"             0 KB       0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_02 is:
  /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/log/exporcl.dmp
******************************************************************************
Datafiles required for transportable tablespace EXAMPLE:
  +DATA/orcl/example01.dbf
Datafiles required for transportable tablespace SEQ:
  +DATA/orcl/datafile/seq.265.907947011
Datafiles required for transportable tablespace USERS:
  +DATA/orcl/users01.dbf
Job "SYSTEM"."SYS_EXPORT_FULL_02" successfully completed at 15:43:08

Copy the  data files for tablespaces containing user/application data from ASM to file system

[grid@host01 root]$ mkdir -p /u01/app/oracle/oradata/orcl

ASMCMD> cp +DATA/orcl/example01.dbf /u01/app/oracle/oradata/orcl
copying +DATA/orcl/example01.dbf -> /u01/app/oracle/oradata/orcl/example01.dbf

ASMCMD> cp +DATA/orcl/datafile/seq.265.907947011 /u01/app/oracle/oradata/orcl/seq01.dbf
copying +DATA/orcl/datafile/seq.265.907947011 -> /u01/app/oracle/oradata/orcl/seq01.dbf

ASMCMD> cp +DATA/orcl/users01.dbf /u01/app/oracle/oradata/orcl/users01.dbf
copying +DATA/orcl/users01.dbf -> /u01/app/oracle/oradata/orcl/users01.dbf
 

On Destination System

Create a new PDB (pdb_orcl) in the destination RAC CDB (cdb1) using the create pluggable database command

  • Verify that destination container database (cdb1) is a 12.1.0.2c, two node RAC database currently having two pluggable databases (PDBs)

CDB1>select name, cdb from v$database;
NAME      CDB
--------- ---
CDB1      YES

SQL> sho parameter cluster_database
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2

CDB1> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

CDB1>sho pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

  • Create destination pluggable database pdb_orcl. This newly created database includes a set of administrative tablespaces appropriate to the target environment, complete with Oracle-supplied components and packages.

CDB1> create pluggable database pdb_orcl admin user pdbadmin identified by oracle create_file_dest = '+DATA';
CDB1> sho pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB_ORCL                       MOUNTED

CDB1> alter pluggable database pdb_orcl open;
CDB1>sho pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB_ORCL                       READ WRITE NO

CDB1>select file_name from cdb_data_files where con_id=4;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/CDB1/3967939319C045D7E053B7C909C052F3/DATAFILE/system.307.919189029
+DATA/CDB1/3967939319C045D7E053B7C909C052F3/DATAFILE/sysaux.317.919189029

CDB1>select tablespace_name from cdb_tablespaces where con_id=4;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP

CDB1> select name from v$active_services where con_id = 4;
NAME
----------------------------------------------------------------
pdb_orcl

CDB1>select username, oracle_maintained from cdb_users where con_id = 5 order by 2;
USERNAME                       ORACLE_MAINTAINED
------------------------------ --------------------
PDBADMIN                       N
DVF                            Y
MDSYS                          Y
ORDSYS                         Y
DBSNMP                         Y
WMSYS                          Y
APEX_040200                    Y
APPQOSSYS                      Y
GSMADMIN_INTERNAL              Y
ORDDATA                        Y
CTXSYS                         Y
ANONYMOUS                      Y
XDB                            Y
ORDPLUGINS                     Y
DVSYS                          Y
SI_INFORMTN_SCHEMA             Y
OLAPSYS                        Y
ORACLE_OCM                     Y
OJVMSYS                        Y
SYSKM                          Y
XS$NULL                        Y
LBACSYS                        Y
GSMCATUSER                     Y
MDDATA                         Y
SYSBACKUP                      Y
OUTLN                          Y
DIP                            Y
SYSDG                          Y
APEX_PUBLIC_USER               Y
SPATIAL_CSW_ADMIN_USR          Y
SPATIAL_WFS_ADMIN_USR          Y
GSMUSER                        Y
AUDSYS                         Y
SYSTEM                         Y
SYS                            Y
FLOWS_FILES                    Y

36 rows selected.

Copy the  dump file from source system to the folder /u01/app/oracle/admin/cdb1 on the one of the nodes (host01) hosting an instance of the destination CDB (cdb1)

Create a directory object (dump_dir) in the destination PDB (pdb_orcl) pointing to the folder containing the dump file (/u01/app/oracle/admin/cdb1)

This directory object must be created by a user connected to the destination PDB (pdb_orcl)

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';

Copy the  data files containing user/application data from source system to ASM

  • Copy the  data files containing user/application data from source system to the folder /home/grid on the one of the nodes (host01) hosting an instance of the destination CDB (cdb1)

  • Copy the  data files containing user/application data to ASM

ASMCMD> cp /home/grid/example01.dbf +DATA/CDB1/3967939319C045D7E053B7C909C052F3/DATAFILE/
copying /home/grid/example01.dbf -> +DATA/CDB1/3967939319C045D7E053B7C909C052F3/DATAFILE/example01.dbf

ASMCMD>  cp /home/grid/users01.dbf +DATA/CDB1/3967939319C045D7E053B7C909C052F3/DATAFILE/
copying /home/grid/users01.dbf -> +DATA/CDB1/3967939319C045D7E053B7C909C052F3/DATAFILE/users01.dbf

ASMCMD> cp /home/grid/seq01.dbf +DATA/CDB1/3967939319C045D7E053B7C909C052F3/DATAFILE/
copying /home/grid/seq01.dbf -> +DATA/CDB1/3967939319C045D7E053B7C909C052F3/DATAFILE/seq01.dbf

Using an account having the DATAPUMP_IMP_FULL_DATABASE privilege, import into the target pluggable database (pdb_orcl) using impdp with the FULL=Y and TRANSPORT_DATAFILES parameters

[oracle@host01 admin]$ impdp system/oracle@host01.example.com:1521/PDB_ORCL \
                       full=y directory=dump_dir dumpfile=exporcl.dmp\
                       transport_datafiles= \                   '+DATA/CDB1/3967939319C045D7E053B7C909C052F3/DATAFILE/example01.dbf',\
'+DATA/CDB1/3967939319C045D7E053B7C909C052F3/DATAFILE/users01.dbf',\
'+DATA/CDB1/3967939319C045D7E053B7C909C052F3/DATAFILE/seq01.dbf' \
 logfile=import_orcl.log
Import: Release 12.1.0.2.0 - Production on Sat Aug 6 18:37:07 2016

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, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Source time zone is +00:00 and target time zone is -07:00.
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@host01.example.com:1521/PDB_ORCL full=y directory=dump_dir dumpfile=exporcl.dmp transport_datafiles=+DATA/CDB1/3967939319C045D7E053B7C909C052F3/DATAFILE/example01.dbf, +DATA/CDB1/3967939319C045D7E053B7C909C052F3/DATAFILE/users01.dbf, +DATA/CDB1/3967939319C045D7E053B7C909C052F3/DATAFILE/seq01.dbf logfile=import_orcl.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-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_RENDER_CALENDAR2" created with compilation warnings
ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_RENDER_CHART2" created with compilation warnings
ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_RENDER_REPORT3" created with compilation warnings
ORA-39082: Object type TRIGGER:"APEX_030200"."WWV_BIU_FLOW_SESSIONS" created with compilation warnings
ORA-39082: Object type TRIGGER:"SYSMAN"."MGMT_CREDS_INS_UPD" created with compilation warnings
ORA-39082: Object type TRIGGER:"OE"."ORDERS_TRG" created with compilation warnings
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 681 error(s) at Sat Aug 6 20:24:03 2016 elapsed 0 01:46:53

Following errors were encountered by the import.

  • Administrative information stored in SYSTEM and SYSAUX tablespaces is neither exported nor imported

[oracle@host01 cdb1]$ egrep -i "object type user.*already exists" import_orcl.log
ORA-31684: Object type USER:"OUTLN" already exists
ORA-31684: Object type USER:"ORDDATA" already exists
ORA-31684: Object type USER:"OLAPSYS" already exists
ORA-31684: Object type USER:"MDDATA" already exists
ORA-31684: Object type USER:"SPATIAL_WFS_ADMIN_USR" already exists
ORA-31684: Object type USER:"SPATIAL_CSW_ADMIN_USR" already exists
ORA-31684: Object type USER:"FLOWS_FILES" already exists
ORA-31684: Object type USER:"APEX_PUBLIC_USER" already exists

  • Tables having columns with data type Timestamp with local time zone are not transportable using TTS if there is time zone mismatch between source and target databases

[oracle@host01 cdb1]$ cat import_orcl.log | grep TSLTZ
ORA-39360: Table "OE"."ORDERS" was skipped due to transportable import and TSLTZ issues resulting from time zone mismatch.
[oracle@host01 cdb1]$ oerr ORA 39360
39360, 00000, "Table %s was skipped due to transportable import and TSLTZ issues resulting from time zone mismatch."
// *Cause:    The time zone of the source database is different than the time
//            zone of the target database and this table contains TIMESTAMP
//            WITH LOCAL TIME ZONE data.
// *Action:   Convert the target database to the same time zone as the source
//            database or use Oracle Data Pump with conventional data movement
//            to export then import this table.

  • Tables containing XML data types are not transportable using TTS

ORA-39083: Object type TABLE:"OE"."WAREHOUSES" failed to create with error:
ORA-39945: Token conflicting with existing tokens.
Failing sql is:
CREATE TABLE "OE"."WAREHOUSES" ("WAREHOUSE_ID" NUMBER(3,0), "WAREHOUSE_SPEC" "SYS"."XMLTYPE" , "WAREHOUSE_NAME" VARCHAR2(35 BYTE),  "LOCATION_ID" NUMBER(4,0), "WH_GEO_LOCATION" "MDSYS"."SDO_GEOMETRY" ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1  MAXTRANS 255 NOCOMPRESS NOLOGGING STORAGE(SEG_FILE 5 SEG_BLOCK 378 OBJNO_REUSE 75474 INITIAL 65536 NEXT 1048
[oracle@host01 cdb1]$oerr ora 39945
39945, 0000, "Token conflicting with existing tokens."
// *Cause:  The binary XML tokens contained in the imported tablespace
//          conflicted with the existing tokens.
// *Action: Use the dump file export or import instead of transportable tablespace.

Rest of the errors are a consequence of above errors. 

Check that migration has been successfully performed

CDB1>sho con_name
CON_NAME
------------------------------
PDB_ORCL

CDB1>select file_name, tablespace_name tablespace from dba_data_files;
FILE_NAME                                                                 TABLESPACE
------------------------------------------------------------------------- ------------
+DATA/CDB1/3967939319C045D7E053B7C909C052F3/DATAFILE/sysaux.317.919189029 SYSAUX
+DATA/CDB1/3967939319C045D7E053B7C909C052F3/DATAFILE/system.307.919189029 SYSTEM
+DATA/CDB1/3967939319C045D7E053B7C909C052F3/DATAFILE/example01.dbf        EXAMPLE
+DATA/CDB1/3967939319C045D7E053B7C909C052F3/DATAFILE/seq01.dbf            SEQ
+DATA/CDB1/3967939319C045D7E053B7C909C052F3/DATAFILE/users01.dbf          USERS

CDB1>select username, oracle_maintained from dba_users order by 2;
USERNAME                       ORACLE_MAINTAINED
------------------------------ ------------------------------
APEX_030200                    N
PDBADMIN                       N
IX                             N
OE                             N
PM                             N
BI                             N
OWBSYS_AUDIT                   N
OWBSYS                         N
JFV                            N
MGMT_VIEW                      N
HR                             N
SH                             N
SYSMAN                         N
SCOTT                          N
ORDPLUGINS                     Y
DVSYS                          Y
OLAPSYS                        Y
OJVMSYS                        Y
XS$NULL                        Y
LBACSYS                        Y
GSMCATUSER                     Y
SYSBACKUP                      Y
OUTLN                          Y
APEX_PUBLIC_USER               Y
SPATIAL_CSW_ADMIN_USR          Y
AUDSYS                         Y
ORACLE_OCM                     Y
SYSKM                          Y
MDDATA                         Y
DIP                            Y
SYSDG                          Y
SPATIAL_WFS_ADMIN_USR          Y
GSMUSER                        Y
SYSTEM                         Y
SYS                            Y
APPQOSSYS                      Y
ORDSYS                         Y
DVF                            Y
SI_INFORMTN_SCHEMA             Y
ANONYMOUS                      Y
CTXSYS                         Y
ORDDATA                        Y
GSMADMIN_INTERNAL              Y
APEX_040200                    Y
WMSYS                          Y
DBSNMP                         Y
MDSYS                          Y
FLOWS_FILES                    Y
XDB                            Y

49 rows selected.
CDB1>select count(*) from hr.employees;
  COUNT(*)
----------
       107

Thus we have successfully upgraded and migrated a 3 node 11.2.0.3 RAC database (non-CDB) into a 2 node 12.1.0.2 RAC pluggable database (PDB).

Summary

  • Full transportable export/import greatly simplifies the process of database migration while taking advantage of

    • Data Pump to move the metadata needed for a database migration and

    • Transportable tablespaces mechanism to move user and application data

  • With full transportable export/import , a full database migration can be accomplished very quickly and easily with a single import command , even for very large volumes of data. Complex set of steps required for a traditional transportable tablespaces operation are not needed.

  • During full transportable export/import

    • Administrative information stored in SYSTEM and SYSAUX tablespaces is neither exported nor imported

    • Tables having columns with data type Timestamp with local time zone are not transportable using TTS if there is time zone mismatch between source and target databases. To transport such tables,

      • Convert the target database to the same time zone as the source database and use full transportable export/import or

      • Use Oracle Data Pump with conventional data movement

    • Tables containing XML data types are not transportable using TTS. In order to transport such tables, use the dump file export or import instead of transportable tablespace.

  • Using full transportable export/import , we can upgrade or migrate the source Oracle Database 11g Release 2 (11.2.0.3) or higher to Oracle Database 12c pluggable database in a single operation.