Thinking Out Loud

September 30, 2012

Cross Platform Transportable Tablespace (XTTS) and Data Pump

Filed under: 11g,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:

SELECT tp.platform_id, tp.PLATFORM_NAME, ENDIAN_FORMAT,d.name
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME(+)
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 !!!

Action

Mins

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

References:

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]

About these ads

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 314 other followers

%d bloggers like this: