Monday Jul 04, 2016

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: 


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.
    • 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.
      .
--Mike
.

Monday Jun 27, 2016

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

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

Wednesday Apr 20, 2016

Data Pump - Exclude Stats differently for TTS and FTEX

Nice little best practice for statistics and Data Pump when doing either Transportable Tablespaces or Full Transportable Export-Import (credits to Roy and Dean Gagne).
.

Transport Statistics via a Staging Table

First of all we always recommend to exclude statistics when doing a Data Pump export as the import of such stats takes way longer than transporting them via a stats table. If you are unfamiliar with transporting stats between databases please see the Oracle Performance Tuning Guide with a nice tutorial

The basic steps to transport statistics from one database to another fast and efficient consist of: 

  1. Create a staging table in your source database with DBMS_STATS.CREATE_STAT_TABLE
  2. Export your local stats into this staging table by using DBMS_STATS.EXPORT_SCHEMA_STATS
  3. Export the staging table and import it into your destination database with Data Pump
  4. Import the statistics held in the staging table by using DBMS_STATS.IMPORT_SCHEMA_STATS

For the regular Data Pump exports we always recommend to set:

EXCLUDE=STATISTICS

to avoid performance penalties during impdp.

But this does not affect Transportable Tablespaces and Full Transportable Export/Import.
.

How to exclude Statistics for TTS and FTEX?

For reasons I don't know the metadata heterogeneous object for "transportable" is different than all of the others Therefore in order to exclude statistics for Transportable Tablespaces and Full Transportable Export/Import you must set:

EXCLUDE=TABLE_STATISTICS,INDEX_STATISTICS

Thursday Jul 24, 2014

Why "We'll wait for the 2nd release!" is a misconception ...

Oh, how often have I heard this phrase:

"We'll wait for the second release!"

And sometimes it makes me really anxious and angry at the same time when I hear that.

Anxious because it means that somebody has no strategy for the database upgrades/migrations and is just postponing necessary tasks to sometime in the future. Easy deal but not very clever

Angry because this is a way of thinking from the 90s/00s when Oracle had this "10.1", then later "10.2" strategy in database releases. But we are in 2014 now. And things have changed. Changed a lot in fact.

I'm probably not the only person who would love to see if we'd remove this "first" and "second" release tags. This has become obsolete with Oracle 11.1. We were telling customers officially that this is the brand new fantastic Oracle Database 11g release. But in fact it was - from the coding perspective - more or less a very stable 10g. In my workshops I did call it Oracle 10.3 with a marketing sticker on it. And as far as I can see the customer's I've had helped with going live on Oracle Database 11.1.0.7 were quite happy. Some really large shops still work with this release today with hundreds of databases in production.

But honestly most of the changes got introduced with Oracle Database 11.2. And not for a small number of customers this meant waiting for the first patch set (which since then has become a full release). Plenty of people went live with Oracle Database 11.2.0.2. But a lot of the remarkable changes got introduced not in Oracle 11.1 but in Oracle 11.2. Just remember things such as the move from Clusterware to Grid Infrastructure. But also minor things such as DEFERRED_SEGMENT_CREATION and plenty of optimizer news.

Now with Oracle Database 12c and the first patch set (full release) 12.1.0.2 I have heard this again - and I see it on the mailing list as well once a week:

"When will Oracle 12.2 be available?"

Well, that's the misconception. It's true, Oracle Database 12.1.0.2 has new features and extensions. But it has also many fixes over the already very stable Oracle 12.1.0.1. Why should anybody wait for Oracle 12.2 now? Because it's supposed to be THE SECOND release? Forget this - this is thinking from the old days.
We are in year 2014 now.

And then spend a minute to look closer to the Support Policy.

  • Oracle Database 11.2 will go out of Premier Support in 6 months. Yes!!! 6 months
  • And correct, we'll give everybody on Oracle 11.2.0.4 more than one full year of Extended Support for free
  • For Oracle 11.2.0.3 Extended Support will end 27-AUG-2015

That means if you plan to stay on Oracle 11.2.0.3/4 for a longer period you'll either have to calculate 20% extra of your support fee for the 2nd year of Extended Support. Or you prefer to "hope".

I can't tell you when Oracle 12.2 will be available - and I don't care. Usually people wait for the first patch set anyways which gets releases based on experience from the past 3 databases releases roughly a year and a bit after the initial release. Just do the math and you'll see where you end up with this strategy.

My recommendations are:

  • Stop thinking about THE SECOND release
  • Evaluate Oracle Database 12.1.0.2 now - not tomorrow
  • Look at the Support Policy - you need to start your upgrades as soon as possible

PLEASE READ ON:

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

-Mike 

Thursday Jul 18, 2013

Full Transportable Export/Import White Paper

One of the really cool and helpful features in Oracle Database 12c is called Full Transportable Export/Import. It combines the basics of transportable tablespaces - of course cross-platform, cross-endianess, cross-version - with Data Pump taking care on all the stuff not stored in tables and indexes, such as views, synonyms, trigger, packages etc. And you can even reduce downtime by combining the feature with incrementally converted RMAN backups.

And the best thing: this will work with an Oracle Database 11.2.0.3/4 to Oracle Database 12c - so you don't have to be on Oracle Database 12c in order to leverage from that cool new feature.

See the brand new White Paper about Full Transportable Export/Import here:
http://www.oracle.com/technetwork/database/enterprise-edition/full-transportable-wp-12c-1973971.pdf

-Mike

About

Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle

Based in Germany. Interlink between customers/partners and the Upgrade Development. Running workshops between Arctic and Antartica. Assisting customers in their reference projects onsite and remotely. Connect via:

- -

Search

Archives
« July 2016
SunMonTueWedThuFriSat
     
1
2
3
6
8
9
10
11
12
13
14
15
16
17
18
19
22
23
24
25
26
27
28
29
30
31
      
Today
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
Workshop Map
x Oracle related Tech Blogs
This week on my Rega & Pono
Upgrade Reference Papers