How to Upgrade 11gR2 RAC Database to 12c Pluggable Database using full transportable export/import

    Sep 24, 2018 3:09:23 PM 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.

    Tags: Oracle

    Anju Garg

    Written by Anju Garg

    Anju Garg is an Oracle Ace with over 14 years of experience in IT Industry in various roles. Since 2010, she has been involved in teaching and has trained more than a hundred DBAs from across the world in various core DBA technologies like RAC, Data guard, Performance Tuning, SQL statement tuning, Database Administration etc. She is a regular speaker at Sangam and OTNYathra. She writes articles about Oracle and is one of the reviewers of the following book published by Pearson Oracle Problem-Solving and Troubleshooting Handbook She is certified for : Oracle 9i Database Administration OCP Oracle 11g Database Administration OCP Oracle 11g Performance Tuning OCE Oracle 11g R2 RAC OCE Oracle 11g SQL Tuning OCE Oracle 12c Database Administration OCP Oracle Real Application Clusters 12c Certified Implementation Specialist She is passionate about learning and has keen interest in RAC and Performance Tuning. She shares her knowledge via her technical blog at http://oracleinaction.com/