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:
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 theENCRYPTION_PWD_PROMPT
export utility parameter toYES
, or use theENCRYPTION_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.
- You cannot transport an encrypted tablespace to a platform with different endianness.
Thanks for details on "Tablespace Encryption"
Nice to read always
Hi Mike,
I’ve been doing a lot of TTS testing, and wonder if it’s possible to restart the impdp part after a failure. In my current case, it failed while creating users, so the tablespaces have already been transported and incorporated into the new database, it’s just the meta data, etc that I need to re-do. I’m doing large scale testing, so recreating the datafiles takes several hours and I’m hoping to avoid that.
thanks for any ideas!
Hi Sophie,
actually you remind me on blogging about it as I have gotten this question twice last week after presenting in Denver at RMOUG.
What you’d do:
After creating your fresh database set one guaranteed restore point, and maybe before importing the tablespaces another one.
I’m not 100% sure if flashback database will also revert the imported tablespaces (I think so but I’ll test it today or tomorrow) but even if it doesn’t you could drop the tablespaces including contents and revert to the restore point again.
This way you’d avoid such failed imports.
Do you think this would solve the issue?
Cheers,
Mike
That’s a great idea! it sounds like it should solve my issue. (If I recall correctly, the tablespaces remain read only until the entire impdp is complete, right?) I won’t be able to test it again today (hoping my next import doesn’t fail!), but will try to test tomorrow.
Sophie,
yes, you are right – the tablespaces (as long as you do traditional transportable tablespaces and not Full Transportable Export/Import) remain R/O until you switch them to R/W by yourself.
Cheer,
Mike
Hello Mike,
In my environment, I have some constraints updating the Time Zone files on the source 11.2.0.4 while using FTEX. From the standard oracle documentation that you posted above:
“Transportable tablespaces cannot transport tables with TIMESTAMP WITH TIMEZONE (TSTZ) data across platforms with different time zone file versions. These tables are skipped automatically in a transportable tablespaces operation. These tables can be exported and imported conventionally”.
I scanned all my source 11.2.0.4 database columns, and I found no columns with type=”TIMESTAMP WITH TIMEZONE” in all user tablespaces, except 5 columns in tables related to system schemas like: WMSYS & CTXSYS.
So, in this case, can I continue to use FTEX without upgrading the source 11.2.0.4 TZ files?
(For sure, both source and destination will have the same time zone).
Thanks
Ahmed
Hi Ahmed,
FTEX should work in this case.
Mike