X

Master Note: Transportable Tablespaces (TTS) -- Common Questions and Issues

++++++++++++++++++++++++++++++++++++++++++++++++++++

The complete and the most recent version of this article can be viewed

from My Oracle Support Knowledge Section.



Master Note: Transportable Tablespaces (TTS) -- Common Questions and Issues
(Doc ID 1166564.1)

++++++++++++++++++++++++++++++++++++++++++++++++++++

Applies to:

Oracle
Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.2 - Release: 9.2 to
11.2

Oracle Server - Standard Edition - Version:
9.0.2.0 to 11.2.0.2   [Release: 9.0.1 to 11.2]

Information in this document applies to any platform.

Purpose

This note
gives you a single reference point from which to quickly find answers to common
questions and issues for Transportable Tablespaces (TTS).

Scope and Application

Covered
topics include (1) why use Transportable Tablespaces (TTS), (2) commonly asked
questions, and (3) common issues for using the feature.

Master Note for Transportable
Tablespaces (TTS) -- Common Questions and Issues

Why use Transportable Tablespaces
(TTS)?


  • "Oracle transportable
    tablespaces are the fastest way for moving large volumes of data between
    two Oracle databases."

  • "Using transportable
    tablespaces, Oracle data files (containing table data, indexes, and almost
    every other Oracle database object) can be directly transported from one
    database to another. Furthermore, like import and export, transportable
    tablespaces provide a mechanism for transporting metadata in addition to
    transporting data."



"The transportable tablespace feature is useful in a number of scenarios,
including:


  • Exporting and importing
    partitions in data warehousing tables
  • Publishing structured data
    on CDs
  • Copying multiple read-only
    versions of a tablespace on multiple databases
  • Archiving historical data
  • Performing tablespace
    point-in-time-recovery (TSPITR)
  • Migrating databases among
    RDBMS versions and OS platforms"





(See:  Oracle Database Administrator's Guide 11g Release 2 (11.2)
)

Commonly Asked Questions



Question

Short Answer

Complete Answer

Can I move/migrate to both a different RDBMS
version and OS platform at the same time?

Yes; must be 10g or higher to move across OS
platforms; charactersets must be the same regardless of version.

See "Limitations on Transportable Use"
in Document 371556.1 How to move tablespaces
across platforms using Transportable Tablespaces with RMAN

Do I have to convert the datafiles?

Yes, if the endianness is different.  If the
endianness is not different and no undo is in any of the tablespaces being
transported, then the convert step is not needed. 

Document 243304.1 10g : Transportable
Tablespaces Across Different Platforms confirms the answer.


See question "Is there an easy-to-follow example?" below for
another TTS-usage example.

Can I use TTS with ASM?

Yes, with RMAN, ASM files can be moved.

See "Transportable tablespace EXP/IMP of ASM
files" in Document 371556.1 How to move tablespaces
across platforms using Transportable Tablespaces with RMAN

Can I move raw files?

Yes, with RMAN.

See "Transportable tablespace EXP/IMP of ASM
files" in Document 371556.1 How to move tablespaces
across platforms using Transportable Tablespaces with RMAN

Can I transport just a single partition?

Yes.

See Document 731559.1 How to move or transport
table partition using Transportable Table Space (TTS)?

Is there an easy-to-follow example?

Yes, see note.

See either "Transportable tablespace
EXP/IMP of ASM files" or "Transportable tablespace EXP/IMP with OS
files" in Document 371556.1 How to move tablespaces
across platforms using Transportable Tablespaces with RMAN

Is there a size limitation?

No, except for a couple of size-related bugs, one
which is a potential-corruption bug in < 11g.  Please see note for
description and patch information.

Document 566875.1 Size Limitations On Cross
Platform Transportable Tablespaces

What restrictions/limitations exist for TTS?

  1. Movement between different charactersets
  2. Movement between different OS (depending on
    RDBMS version)
  3. Some objects are not transferred via TTS
  4. Oracle Server -- Standard Edition vs.
    Enterprise Edition; Standard Edition can only import TTS (no export)
  5. Also review size-related bugs in question
    above.

See "Limitations on Transportable Use"
in Document 371556.1 How to move tablespaces
across platforms using Transportable Tablespaces with RMAN


See Feature Availability for Oracle Database Editions
in the Oracle licensing documentation.


See Document 114915.1 Using Dbms_tts.transport_set_check
Results in Entry in Transport_set_violations


See Document 883153.1

What Objects Are Exported With Transportble Tablespaces (TTS)?

What are best practices for TTS, especially when
migrating a database?

  1. Check restrictions/limitations in question
    above.
  2. If you are migrating a database, (a) make
    sure there are no invalid objects in the source database before making
    the export.
  3. Take a full norows export to recreate
    objects that won't be transported with TTS.
  4. Keep the source database viable until you
    have determined all objects are in the target database and there are no
    issues (i.e. the target database has been thoroughly checked out and
    exercised).
  5. Do a dry run to work out any unexpected
    issues and determine timings.



Common
Issues with Transportable Tablespaces



Error/Problem

Suggested Solution

ORA-19721

Document 757795.1 Import of an Exported
Transportable Tablespace get ORA-19721 error

ORA-19721,
IMP-3, IMP-0 (< 11g)

ORA-19721
: Transportable Tablespace Import Fails With Errors
IMP-00003,ORA-19721,IMP-00000 (Doc ID 438683.1)

ORA-29341
The transportable set is not self-contained

Document 114915.1 Using
Dbms_tts.transport_set_check Results in Entry in Transport_set_violations
Document 867246.1 New Partitions or
Subpartitions Are Being Created in the Wrong Tablespace

TTS
import completes successfully, but objects are missing

Document 883153.1 What Objects Are Exported
With Transportble Tablespaces (TTS)?


Also, 'CTXSYS, 'ORDSYS', 'MDSYS', 'ORDPLUGINS', 'LBACSYS', 'XDB',

'SI_INFORMTN_SCHEMA', 'DIP', 'DMSYS', 'DBSNMP are treated as objects owned by
'SYS' and not exported with TTS.

 

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha