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.

 

 

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

News and Troubleshooting tips for Oracle Database and Enterprise Manager

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today