News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Upgrading from Oracle 11gR2 to Oracle 12c using Full Transportable Export and Import

  • Posted by Gavin Soorma
  • On December 11, 2014
  • 1 Comments
  • 12c, data pump, export, full transport, import, tts, upgrade, XTTS

The Transportable Tablespace (TTS) feature was introduced in Oracle 8i and the Cross-Platform Transportable Tablespace (XTTS) feature was introduced in Oracle 10g.

Since it only involved the copy of the data files of the tablespace at the OS level and the copy (and maybe conversion) of the data files over the network, it was potentially faster than the traditional Export/Import or Data Pump which required pretty much a block by block scan of every table in the database.

But Data Pump did have a number of advantages and TTS and XTTS did have some disadvantages – it was more complex to setup and use and had some limitations like objects in the SYSTEM and SYSAUX tablespaces could not be moved as well as the DBA had to ensure that the tablespaces being transported were ‘self-contained’. Certain applications when installed created objects in the SYSTEM schema residing in the SYSTEM tablespace and they would be ignored if we used the TTS method of migrating data.

The Full Transportable Export and Import feature combines the best features of TTS/XTTS and Data Pump.

It can help us in upgrading a database to Oracle 12c or migrating a database to a 12c Container Database (CDB) – it however requires the source database to be at least Oracle 11.2.0.3.

Full Transportable Export and Import considers tablespaces to be of two kinds – Administrative and User.

Administrative tablespaces include the tablespaces supplied by Oracle when we create a database – SYSTEM, SYSAUX, UNDO and TEMP. These tablespaces contain the procedures, packages, and seed data for the core Oracle database functionality and Oracle-provided database components such as Oracle Spatial, Oracle Text, OLAP, JAVAVM, and XML Database.

The User tablespaces are the tablespaces we create which holds the application data.

In Full Transportable Export/Import, the export process will extract the metadata for all objects contained in both user as well as administrative tablespaces.

In Oracle 12c, Oracle supplied objects are neither exported or imported by Data Pump. But Full Transportable Export/Import will use the Data Pump method to scan the data as well as meta data for any user defined objects residing in Administrative tablespaces and use TTS functionality for all other User tablespaces – they will be moved to the destination database as database file copies.

Here is a example of migrating as well as upgrading data from an Oracle 11.2.0.4 database to Oracle 12c (12.1.0.2) using Full Transportable Export and Import.

To compare this with a conventional Data Pump export, I took a full export of the database – note the time and the size of the export dump file.


Dump file set for SYS.SYS_EXPORT_FULL_01 is:
  /home/oracle/exp1.dmp
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Tue Dec 9 02:22:57 2014 elapsed 0 00:05:48


[oracle@csmsdc-vosap02 ~]$ ls -l /home/oracle/exp1.dmp
-rw-r----- 1 oracle oinstall 2043740160 Dec  9 02:22 /home/oracle/exp1.dmp


To test this, we create objects in the administrative tablespace – both SYSTEM and SYSAUX . (this is just for the test case – too be avoided!)

SQL> alter user arisbp quota unlimited on system;

User altered.

SQL> create table arisbp.mytables
  2  tablespace system
  3  as select * from all_tables;

Table created.

SQL> create table arisbp.myindexes
  2  tablespace sysaux
  3  as select * from all_indexes;

Table created.

These are the tablespaces which exist in the source 11.2.0.4 database


SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
ARISBPDATA
ARISBPINDEX

The first thing we need to do is to make all the user tablespaces read only (downtime for upgrade and migration starts here)


SQL> alter tablespace ARISBPDATA read only;

Tablespace altered.

SQL> alter tablespace ARISBPINDEX read only;

Tablespace altered.

We have to use the TRANSPORTABLE=ALWAYS and FULL=Y clause to instruct Data Pump to treat this not as a conventional Export of a database but as a Full Transportable Export and Import.

We also have to use the VERSIONS parameter as well in this case if the COMPATIBLE parameter of the source database is not set to at least 12.0 and in this case it is not since the source database is 11.2.0.4.

Since the APEX related tables are located in an administrative tablespace (SYSAUX), we can see that the export process will load the data as well as metadata for these tables.

It will also provide a list of the data files belonging to the user tablespaces and these data files will need to be physically copied from the source to the target server.

Note the size of the export dump file – it is just 170 MB copmpared to over 2 GB when we did the conventional full export of the same database earlier.


$ expdp directory=exp_dir dumpfile=exp2.dmp full=y transportable=always logfile=exp_dir:full_export.log version=12

Export: Release 11.2.0.4.0 - Production on Tue Dec 9 19:03:49 2014

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

Username: sys as sysdba
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_01":  sys/******** AS SYSDBA directory=exp_dir dumpfile=exp2.dmp full=y transportable=always logfile=exp_dir:full_export.log version=12
Estimate in progress using BLOCKS method...

…
…

. . exported "APEX_030200"."WWV_FLOW_LIST_OF_VALUES_DATA"  392.0 KB    4184 rows
. . exported "APEX_030200"."WWV_FLOW_MESSAGES$"          348.4 KB    3706 rows
. . exported "APEX_030200"."WWV_FLOW_PAGE_PLUG_TEMPLATES"  165.8 KB     166 rows
. . exported "APEX_030200"."WWV_FLOW_WORKSHEETS"         233.3 KB      30 rows
. . exported "SYSMAN"."MGMT_JOB_CRED_PARAMS"             56.64 KB     187 rows
. . exported "APEX_030200"."WWV_FLOW_CUSTOM_AUTH_SETUPS"  21.56 KB      11 rows

….
….

. . exported "SYSTEM"."REPCAT$_SITE_OBJECTS"                 0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_SNAPGROUP"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_OBJECTS"             0 KB       0 rows
. . 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 "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
  /home/oracle/exp2.dmp
******************************************************************************
Datafiles required for transportable tablespace ARISBPDATA:
  /app/oracle/oradata/aristsbp/arisbpdata.266.818548233
Datafiles required for transportable tablespace ARISBPINDEX:
  /app/oracle/oradata/aristsbp/arisbpindex.267.818548235
Datafiles required for transportable tablespace USERS:
  /app/oracle/oradata/aristsbp/users.259.818547637
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Tue Dec 9 19:08:11 2014 elapsed 0 00:04:03



[oracle@csmsdc-vosap02 ARIS12BP]$ ls -lrt /home/oracle/exp2.dmp
-rw-r----- 1 oracle oinstall 174755840 Dec  9 19:08 /home/oracle/exp2.dmp

Copy the data files of the ARISBPDATA, ARISBPINDEX and USERS tablespaces to the server hosting the Oracle 12.1.0.2 database

$ cp /app/oracle/oradata/aristsbp/arisbpdata.266.818548233 /app/oracle/oradata/ARIS12BP
$ cp /app/oracle/oradata/aristsbp/arisbpindex.267.818548235 /app/oracle/oradata/ARIS12BP
$ cp /app/oracle/oradata/aristsbp/users.259.818547637 /app/oracle/oradata/ARIS12BP

Now that we have finished copying the data files of the user tablespaces, in the source database we can now make the tablespaces read write.


$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 9 19:13:41 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter tablespace arisbpdata read write;

Tablespace altered.

SQL> alter tablespace arisbpindex read write;

Tablespace altered.

These are the tablespaces we have currenytly in the target 12.1.0.2 database


$ sqlplus sys as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 9 19:17:18 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select tablespace_name from dba_tablespaces;

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

Create a directory object in the target database

SQL> create directory imp_dir as '/home/oracle';

Directory created.

Create the Import parameter file

directory=imp_dir
dumpfile=exp2.dmp
FULL=Y
VERSION=12
TRANSPORT_DATAFILES='/app/oracle/oradata/ARIS12BP/arisbpdata.266.818548233','/app/oracle/oradata/ARIS12BP/arisbpindex.267.818548235','/app/oracle/oradata/ARIS12BP/users.259.818547637'
REMAP_TABLESPACE=USERS:USERS_NEW
LOGFILE=imp_dir:full_import.log

When we run the import in the Oracle 12c database, we can see that the tables which existed in the administrative tablespaces in the source database have both meta data as well as table data included in the dump file – the MYTABLES and MYINDEXES tables we created in the source 11.2.0.4 database are being imported with the table data.

. . imported "ARISBP"."MYINDEXES"                        1.191 MB    5123 rows
. . imported "SYSMAN"."MGMT_METRICS"                     4.966 MB   19620 rows
. . imported "APEX_030200"."WWV_FLOW_STEPS"              788.2 KB    1755 rows
. . imported "SYSMAN"."MGMT_IP_REPORT_ELEM_PARAMS"       623.0 KB    1869 rows
. . imported "ARISBP"."MYTABLES"                         811.4 KB    3262 rows
. . imported "APEX_030200"."WWV_FLOW_LIST_ITEMS"         590.3 KB    3048 rows

Check the status of the imported tablespaces in the Oracle 12c database


$ sqlplus sys as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 9 20:15:20 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
ARISBPDATA                     ONLINE
ARISBPINDEX                    ONLINE
USERS_NEW                      ONLINE

Do an object count of the ARISBP schema in 11g source and 12c target – all objects are present

SQL> select object_type,count(*) from dba_objects where owner='ARISBP'
  2  group by cube(object_type);

OBJECT_TYPE               COUNT(*)
----------------------- ----------
                              2592
LOB                            433
VIEW                             8
INDEX                         1698
TABLE                          453

SQL> quit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

$ . oraenv
ORACLE_SID = [ARIS12BP] ? aristsbp1
The Oracle base for ORACLE_HOME=/app/oracle/product/11.2.0.4/dbhome_1 is /app/oracle
$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 9 20:16:36 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select object_type,count(*) from dba_objects where owner='ARISBP'
  2   group by cube(object_type);

OBJECT_TYPE           COUNT(*)
------------------- ----------
                          2592
LOB                        433
VIEW                         8
INDEX                     1698
TABLE                      453

We can also do the same 11g to 12c upgrade by using Full Transportable Export/Import in Data Pump network mode. So there is no export dump file in this case and we create a database link from 12c target to 11gR2 source.

The Import command in such a case would look like this:

$ impdp NETWORK_LINK=imp_link FULL=Y VERSION=12 TRANSPORTABLE=ALWAYS TRANSPORT_DATAFILES=’/app/oracle/oradata/ARIS12BP/arisbpdata.266.818548233′,’/app/oracle/oradata/ARIS12BP/arisbpindex.267.818548235′,’/app/oracle/oradata/ARIS12BP/users.259.818547637′ REMAP_TABLESPACE=USERS:USERS_NEW

 1

1 Comments

Glen
  • May 4 2017
Great article - I tried this for the first time, on a 11.2.0.3 database with only three small user tablespaces with datafiles totaling just over 200MB. However, when I did the expdp with full=y transportable=always set I got a dumpfile almost 5GB in size, while the regular expdp with just full=y the dumpfile was only 166MB in size. seemed a little strange to me..

Leave Reply

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