Thinking Out Loud

September 30, 2012

Cross Platform Transportable Tablespace (XTTS) and Data Pump

Filed under: 11g,DataPump,oracle — mdinh @ 12:00 am

There’s a requirement for cross platform database migration from Solaris Operating System (SPARC) (64-bit) to Solaris Operating System (x86-64) and ideas started flying around.

CONVERT DATABASE cannot be used because of endian change.

Dataguard was considered and there are limitations as well.

Data Guard Support for Heterogeneous Primary and Logical Standbys in Same Data Guard Configuration [ID 1085687.1]
Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration [ID 413484.1]

Because of source and target, neither options were available.

Two other options were explored: Cross Platform Transportable Tablespace (XTTS) and Data Pump.

Here’s a brief overview for XTTS:

  1. Verify tablespace is self-contained.
  2. Set the tablespace to READ ONLY.
  3. Export metadata at source
  4. Endian Conversion – Convert at SOURCE: convert tablespace / Convert at TARGET: convert datafile
  5. Transfer datafiles and export dump file to target.
  6. Set the source tablespace to READ WRITE.
  7. Import metadata at target
  8. Set the imported tablespace to READ WRITE

For the test case, endian conversion was performed at source using convert tablespace because it’s much simpler with 3 tablespaces and 45 data files.

The platform name can be determined using the SQL:

ORDER BY name, endian_format, platform_id;

Using RMAN for Conversion:

configure device type disk parallelism 6 backup type to backupset;
configure archivelog deletion policy to none;
convert tablespace tbs1,tbs2,tbs3
to platform="Solaris Operating System (x86-64)"
db_file_name_convert '/oracle/oradata/source','/oracle/oradata/target';

Caveat – TRANSPORT_TABLESPACES do not contain: db_link,function,package,procedure,sequence,synonym,view, to name a few.

Summary of results:

XTTS took 593 minutes and Data Pump took 663 minutes.

However, REF_CONSTRAINT took 337 minutes.

Is it necessary to enable REF_CONSTRAINT if the tablesspace is in READ ONLY or the export is consistent???

Without enabling REF_CONSTRAINT, the total Data Pump time is reduced to 326 minutes.

RMAN CONVERT tablespace total 704GB for all data files and total USED tablespace is 692GB.

Of interest, the total USED tablespace is 445GB when imported at target.

That’s a difference 0f 247GB !!!



expdp schema parallel(18) 68
impdp table parallel(21) 68
index parallel(16) 159
constraint 15
stats parallel(24) 16
subtotal 326
ref_constraint 337
total 663
expdp_tts 183
dbf conversion parallel(6) 274
impdp_tts 136
total 593


Export/Import DataPump: The Minimum Requirements to Use Export DataPump and Import DataPump (System Privileges) [ID 351598.1]
Best Practices for Using Transportable Tablespaces (TTS) [ID 1457876.1]
Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backups [ID 1389592.1]

1 Comment »

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Blog at

%d bloggers like this: