Master Note: Transportable Table…

Master Note: Transportable Tablespaces (TTS) -- Common Questionsand Issues

++++++++++++++++++++++++++++++++++++++++++++++++++++
The complete and the most recent version of this article can beviewed
from My Oracle Support Knowledge Section.

Master Note: Transportable Tablespaces (TTS) -- Common Questionsand 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 to11.2.0.2   [Release: 9.0.1 to11.2]
Information in this document applies to any platform.
Purpose
This note gives you a single reference point from which to quicklyfind answers to common questions and issues for TransportableTablespaces (TTS).
Scope and Application
Covered topics include (1) why use Transportable Tablespaces (TTS),(2) commonly asked questions, and (3) common issues for using thefeature.
Master Note for Transportable Tablespaces (TTS) -- Common Questionsand Issues
Why use Transportable Tablespaces (TTS)?
•"Oracle transportable tablespaces are the fastest way for movinglarge volumes of data between two Oracle databases."•"Usingtransportable tablespaces, Oracle data files (containing tabledata, indexes, and almost every other Oracle database object) canbe directly transported from one database to another. Furthermore,like import and export, transportable tablespaces provide amechanism for transporting metadata in addition to transportingdata."•"Transportable tablespaces have some limitations: source andtarget systems must be running Oracle8i (or higher), must usecompatible character sets, and, prior to Oracle Database 10g, mustrun on the same operating system."

(See:  Oracle Database Data Warehousing Guide 11gRelease 2 (11.2) )
"The transportable tablespace feature is useful in a number ofscenarios, including:
•Exporting and importing partitions in data warehousingtables•Publishing structured data on CDs•Copying multiple read-onlyversions of a tablespace on multiple databases•Archiving historicaldata•Performing tablespace point-in-time-recovery(TSPITR)•Migrating databases among RDBMS versions and OSplatforms"

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

Commonly Asked Questions
Question
 Short Answer
 Complete Answer

 ----------------------------------------
Can I move/migrate to both a different RDBMS version and OSplatform at the same time?
 Yes; must be 10g or higher to move across OSplatforms; charactersets must be the same regardless ofversion.
 See "Limitations on Transportable Use" inDocument 371556.1 How to move tablespaces across platforms usingTransportable Tablespaces with RMAN

----------------------------------------
Do I have to convert the datafiles?
 Yes, if the endianness isdifferent.  If the endianness is not different andno undo is in any of the tablespaces being transported, then theconvert step is not needed.  
 Document 243304.1 10g : Transportable TablespacesAcross Different Platforms confirms the answer.

See question "Is there an easy-to-follow example?" below foranother TTS-usage example.
 
----------------------------------------
Can I use TTS with ASM?
 Yes, with RMAN, ASM files can be moved.
 See "Transportable tablespace EXP/IMP of ASMfiles" in Document 371556.1 How to move tablespaces acrossplatforms using Transportable Tablespaces with RMAN
 
----------------------------------------
Can I move raw files?
 Yes, with RMAN.
 See "Transportable tablespace EXP/IMP of ASMfiles" in Document 371556.1 How to move tablespaces acrossplatforms using Transportable Tablespaces with RMAN
 
----------------------------------------
Can I transport just a single partition?
 Yes.
 See Document 731559.1 How to move or transporttable partition using Transportable Table Space (TTS)?
 
----------------------------------------
Is there an easy-to-follow example?
 Yes, see note.
 See either "Transportable tablespace EXP/IMP ofASM files" or "Transportable tablespace EXP/IMP with OS files" inDocument 371556.1 How to move tablespaces across platforms usingTransportable Tablespaces with RMAN
 
----------------------------------------
Is there a size limitation?
 No, except for a couple of size-related bugs, onewhich is a potential-corruption bug in <11g.  Please see note for description and patchinformation.
 Document 566875.1 Size Limitations On CrossPlatform Transportable Tablespaces
 
----------------------------------------
What restrictions/limitations exist for TTS?
 1.Movement between differentcharactersets2.Movement between different OS (depending on RDBMSversion)3.Some objects are not transferred via TTS4.Oracle Server-- Standard Edition vs. Enterprise Edition; Standard Edition canonly import TTS (no export)5.Also review size-related bugs inquestion above. See "Limitations on Transportable Use" in Document371556.1 How to move tablespaces across platforms usingTransportable Tablespaces with RMAN

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

See Document 114915.1 Using Dbms_tts.transport_set_check Results inEntry 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 adatabase?
 1.Check restrictions/limitations in questionabove.2.If you are migrating a database, (a) make sure there are noinvalid objects in the source database before making theexport.3.Take a full norows export to recreate objects that won'tbe transported with TTS.4.Keep the source database viable until youhave determined all objects are in the target database and thereare no issues (i.e. the target database has been thoroughly checkedout and exercised).5.Do a dry run to work out any unexpected issuesand determine timings.  


Common Issues with Transportable Tablespaces
Error/Problem
 Suggested Solution
 
----------------------------------------
ORA-19721
----------------------------------------
 Document 757795.1 Import of an ExportedTransportable Tablespace get ORA-19721 error
 
----------------------------------------
ORA-19721, IMP-3, IMP-0 (< 11g)
----------------------------------------
 ORA-19721 : Transportable Tablespace Import FailsWith Errors IMP-00003,ORA-19721,IMP-00000 (Doc ID 438683.1)

----------------------------------------
ORA-29341 The transportable set is not self-contained
----------------------------------------
 Document 114915.1 UsingDbms_tts.transport_set_check Results in Entry inTransport_set_violations
Document 867246.1 New Partitions or Subpartitions Are Being Createdin the Wrong Tablespace

 ----------------------------------------
TTS import completes successfully, but objects are missing
----------------------------------------
 Document 883153.1 What Objects Are Exported WithTransportble Tablespaces (TTS)?

Also, 'CTXSYS, 'ORDSYS', 'MDSYS', 'ORDPLUGINS', 'LBACSYS','XDB',
'SI_INFORMTN_SCHEMA', 'DIP', 'DMSYS', 'DBSNMP are treated asobjects owned by 'SYS' and not exported with TTS.