X

Archived Database Upgrade Blog

Full Transportable Export/Import - Migrating an 11.2.0.4 database to Oracle Database 12c- into the Oracle Cloud

Mike Dietrich
Master Product Manager

Full Transportable Export/Import - one of the coolest features in Oracle Database 12c 

We blogged about Full Transportable Export/Import a while back. It is - no doubt - one of the coolest features in Oracle Database 12c. And it is part of our Hands-On Lab exercise (Hands On Lab - Upgrade, Migrate, Consolidate to Oracle Database 12c) as well.

It utilizes the technique of Transportable Tablesspaces - cross-platform, cross- Endianness, cross-version - but lets Oracle Data Pump do all the "dirty" work of rebuilding everything kept in your SYSTEM and SYSAUX tablespace including views, synonyms, public objects, grants, sequences etc etc etc.

You can even combine it with RMAN Incremental Backups - of course cross-platform and cross-Endianness as well - in order to decrease the downtime for large databases.
Please find our presentation about the combination of these feature here in the Slides Download Center to the right:

In addition: You can use this Oracle Database 12c feature with your source database Oracle 11.2.0.3 or 11.2.0.4. Just the destination database you are upgrading or migrating to must be at least an Oracle 12.1.0.1 database (or newer of course). 

See the feature in action

Roy recorded a 20 minute video demonstrating how to:

  • Migrate an Oracle Database 11.2.0.4 database into an Multitenant's pluggable database using
  • Full Transportable Export/Import
  • into the Oracle DBaaS Cloud
    .

And of course the feature works also without migrating into a Container database environment - and without migrating to the Oracle DBaaS Cloud. 

If you need to migrate a database from Oracle 11.2.0.3 or newer to Oracle Database 12c Full Transportable Export/Import can become your best friend.

--Mike

View Comments

Comments ( 14 )
Commenting is closed on this post.

  • Seth Miller Monday, June 27, 2016

    This is a really cool feature and it is the first method I try when migrating customers to 12c. Unfortunately, it has one shortcoming that makes it unusable for all but very small databases.

    If during the metadata import the import fails at any point, all of the data files that were successfully imported prior to the failure must be copied again from the source. The reason is that the data file header is updated with the new database information and any attempt to re-import it will fail.

    I ran into this a number of times where I would copy over 12TB of data files, run the metadata import and have it fail on one of the last steps and have to copy all 12TB of data files over just to have it fail again on something else.

    This same behavior can happen with standard TTS as well but there is much less that can go wrong. There is also the ability to break up the TTS export into multiple exports so that a failure can be limited to just the data files that were included in the export.

    This was all confirmed from MOS with the response, "If you are trying to reimport, then the tablespace has to be dropped and copy/place the read only datafiles again to the Target location. This is because on the first attempt, the datafiles were already attached to the tablespaces".

  • Mike Dietrich Tuesday, June 28, 2016

    Seth,

    you touch one of the points of weakness of this feature. And you are right. The Data Pump job is not restartable right now. There is a project planned for it - but it's not there yet.

    And as far as I see a GRP won't solve this either as FLASHBACK in my experience won't flash back an update to the data file header.

    For FULL TRANSPORTABLE we need to put the datafiles (or tablespaces) several times into read/write mode - something we do not need to do for regular TTS as you'll put them into R/W just at the end when everything has been finished.

    Currently the only solution I see is a storage snapshot of your restored files (of course including the inc backups once you deal with incrementally rolled forward backups).

    If you want to discuss this further please feel free to drop me an email. We are aware of this and I think I will write about the caveats as well pointing out a few typical things such as timezone version, encrypted tablespaces, character set mismatches - and R/O on source - and R/W on target.

    Cheers!!
    Mike

  • John Wednesday, October 19, 2016

    Hi Mike,

    Actually, I had a question on the white paper: Full Transportable Export/Import White Paper

    https://blogs.oracle.com/UPGRADE/entry/full_transportable_export_import_white

    In the white paper page 11, step no 8, the impdp does not have the parameter 'full=y'.

    While checking the manual (https://docs.oracle.com/database/121/ADMIN/transport.htm#ADMIN13873), it specified the parameter 'full=y'.

    Does it matter?
    Thanks

  • Mike Tuesday, November 1, 2016

    Thanks for your comment - but as this is a impdp NOT going over the dblink you won't need to do it.

    I will publish some examples within the next days.

    Cheers
    Mike

  • ORACELUS Friday, November 4, 2016

    Hi Mike,

    does this Feature "Full Transportable Export/Import" work with Oracle Standard Edition?

    Regards

  • Mike Friday, November 4, 2016

    Does FTEX work with SE databases?

    Yes, but only in the direction into SE as Transportable Tablespaces works from EE=>SE but not the other way round.

    Cheers
    Mike

  • guest Wednesday, December 21, 2016

    Can you use full transportable export/imp from 12c to 11gr2?

  • Mike Thursday, December 22, 2016

    Noooooooooo! :-)

    Clearly a feature to migrate TO Oracle 12.1.0.2 or higher.
    It's a 12c feature - and we wanted to give people the ability to use it with a source database 11.2.0.3/4.

    And there is zero reason to migrate to 11.2.0.4 right now. It goes out of Waived Extended Support in 5 months.

    Cheers
    Mike

  • Oracle Cloud Tuesday, January 17, 2017

    It isn't mentioned anywhere that I've seen, but Oracle 12.2 in the Cloud requires encrypted tablespaces. That means you can't import unencrypted tablespaces, at all.
    The error is:
    ORA-39123: Data Pump transportable tablespace job aborted
    ORA-28427: cannot create, import or restore unencrypted tablespace: _________ in Oracle Cloud

    Of course, if you change the new encrypt_new_tablespaces parameter, you can still created unencrypted tablespaces, you just can't import them.

  • Mike Thursday, January 26, 2017

    Yep - you are right.
    And we are working on this. Please stay tuned for updates in the following weeks on the blog.

    Mike

  • guest Saturday, February 4, 2017

    Hi Mike,

    we are planning to migrate 11.2.0.3/11.2.0.4 enterprise databases (sizes up to 6 TB) from Solaris x86 zones (Veritas FS / ZFS) to Solaris SPARC zones (ASM, DB EE 12.1.0.2) with minimal or no impact/changes for the source database and less downtime. Got the challenge?

    First idea:
    Use of full transportable export/import (FTEX) in combination with incremental RMAN backups, maybe in combination with a standby database to minimize the impact for the source database.

    Second idea:
    If incremental RMAN backups are not possible (technically or in case of support), we want to use a standby database in an interim Solaris x86 zone for doing the RMAN CONVERT job from Veritas FS / ZFS to ASM. The ASM disks are based on LUNs from SAN, so we would move the LUNs after the copy job to the target Solaris SPARC Zone and mount the ASM diskgroups on the target (we don't want to copy the datafiles over network). I could'nt figure out, if ASM disks/diskgroups are moveable between different endian platforms (I know this for ZFS) and if it is supported.

    Regards,
    Stefan

  • Sean Tuesday, February 7, 2017

    Hi Mike,

    I have question about migration and upgrade a database from oracle 11.2.0.3 with apex 5.0.3 to 12.1.0.2 cdb standalone pdb with default APX 4.2, Can I use Full Transportable Export/Import to do that? how to handle APEX? they are in sysaux tablespace.

    Thanks a lot !

    Sean

  • Mike Wednesday, February 8, 2017

    Hi Sean,

    is APEX installed in the CDB$ROOT or only in the PDB - in source AND in target? Not sure if I've got your setup correctly but to me it sounds as:
    Source: 11.2.0.3 non-CDB with APEX 5.0.3
    Target: 12.1.0.2 CDB with APEX 4.2

    Please see this VERY long blog post about the complications with APEX in the CDB$ROOT:https://blogs.oracle.com/UPGRADE/entry/apex_in_pdb_does_not

    So completely apart from Full Transportable you'll have to make sure:
    - APEX is not in the CDB$ROOT.
    - If APEX is in the CDB$ROOT remove it
    - Then make sure APEX 5.0.3 is installed in the destination home software wise
    - Try to provision a fresh PDB - and see if APEX is not there in COMP_ID in DBA_REGISTRY
    - Then install APEX 5.0.3 only locally in this PDB

    Then approach your migration with FTEX.

    See here for FTEX limitations:http://www.oracle.com/technetwork/database/enterprise-edition/full-transportable-wp-12c-1973971.pdf
    (AWR and XDB are not supported).

    Basically to avoid any issues I would export the APEX application, EXCLUDE the APEX schema from the FTEX run and import it afterwards. But it 'should' work out of the box with FTEX as well.

    If not, please let me know the error pattern and I will check with the APEX and the Data Pump team.

    Cheers
    MIke

  • Mike Wednesday, February 8, 2017

    Hi Stefan,

    this is a tricky case.

    Officially RMAN Inc Backups with our PERL scripts are supported with Linux as a target only. But it should work with all other platforms as well in theory. My recommendation: Download the PERL scripts, use my "Inc Backups" presentation from the blog as a quick guidance and try it.

    You can't unfortunately mount ASM disk groups from a little Endian (x86 Solaris) into a big Endian (SPARC) platform.

    So I fear option 2 is not an option for you.

    Cheers
    Mike

Recent Content

Oracle

Integrated Cloud Applications & Platform Services