Full Transportable Export/Import – Things to Know

This blog post is an addition to:

Seth Miller commented the pitfall of having a serious issue during the Data Pump run, Data Pump exiting and not finishing, and you’ll have to do the entire backup/restore/incremental-roll-foward thing again. Without any doubt, this is no fun at all.

So let me point out a few things to take into consideration – and some of them are not obvious I guess.

Do you need to backup/restore/incremental-roll-forward again?

When you let do Data Pump all the manual tasks of Transportable Tablespaces (TTS) this is called Full Transportable Export/Import (FTEX as I abbreviate it). With regular TTS you will take your tablespaces read-write in your destination at the end of the entire process. So whenever something is going wrong during the meta data rebuild you won’t have to restore all your tens of terabytes.

With FTEX things are a bit different. Data Pump will take the tablespaces several times read-write during the impdp run. This has to happen. And the job is not restartable right now. This may change in a future release or patch set. We’ll see.

What are your options now to prevent a full repeat of all your backup/restore/incremental-roll-forward activities?

FLASHBACK DATABASE unfortunately is not an option as you could set a Guaranteed Restore Point – but as soon as the data file headers got adjusted FLASHBACK won’t be able to revert this.

Setting the data files to read-only on the OS level is not an option either as you might force Data Pump to fail when it would like to write something into a tablespace and gets an OS return code for not being able to complete the write operation.

Therefore right now the only valid option I can think of is leveraging storage snapshot technologies in order to restore the files back into the status from “before Data Pump ran“.

We are aware of this pitfall and we discuss alternatives internally for the future.

Character Sets and Time Zone?

Another topic to pay close attention is the database’s character set and national character set. Please find all the guidelines about character sets and national character sets here:

For time zone files please make sure you have the same time zone version in source and destination. As you can’t downgrade your time zone file in the destination usually you’ll have to apply the matching time zone patch in the source before initiating the transport. If you don’t pay attention Data Pump will deny the meta import of the data in the tablespaces as otherwise you may end up with corrupted time zone data.

Please find the precise description in the Oracle 12c documentation:

• General Limitations on Transporting Data

Tablespace Encryption

When your tablespaces are encrypted you’ll have to be a bit more careful when using TTS or FTEX. Even though the FAQ on Oracle.com mentions that there are no issues still there are some limitations. The documentation is more precise:

• Limitations on Transportable Tablespace
  • Transportable tablespaces cannot transport encrypted tablespaces cross-Endianness
  • Transportable tablespaces cannot transport tablespaces containing tables with encrypted columns
• Limitations on Full Transportable Export/Import
  • You cannot transport an encrypted tablespace to a platform with different endianness.
    To transport an encrypted tablespace to a platform with the same endianness, during export set the ENCRYPTION_PWD_PROMPT export utility parameter to YES, or use the ENCRYPTION_PASSWORD export utility parameter. During import, use the equivalent import utility parameter, and set the value to the same password that was used for the export.