My Oracle Support Banner

How to Move a Database Using Transportable Tablespaces (Doc ID 1493809.1)

Last updated on APRIL 11, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.0 [Release 10.1 to 11.2]
Oracle Database - Enterprise Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2]
Oracle Database - Enterprise Edition - Version 12.2.0.1 to 12.2.0.1 [Release 12.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Information in this document applies to any platform.

Goal

This document provides step-by-step approach to moving a database cross platforms using Transportable Tablespace (TTS).

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
 How to move a database using transportable tablespaces:
 Assumptions:
 Metadata residing in the system or sysaux tablespaces:
 System-owned objects in the system or sysaux tablespaces:
 Databases using text indexes and spatial indexes
 Text:
 Spatial:
 1. Stop materialized view refresh jobs:
 2. Check endianness:
 3. Create list of tablespaces and par files
 A. Run this script to list all of the tablespaces that are available to be transported and their block sizes:
 B.  Ensure you have a valid directory for datapump:
  C.  Run the following script to create TTS export, and TTS import Data Pump parameter files.
 4.  Check for user-created objects in the system and sysaux tablespaces:
 5. Create the 'Create user script':
 6.  Check containment:
 7.  Export source metadata:
 8.  Check for tablespaces using a non-default block size.  These tablespaces will need a db_nK_cache_size parameter at the target database to support the tablespace.
 9.  Create tablespace scripts:
  A. This script creates the tts_tsro.sql script from the source database. Use this script to set all tablespaces to be transported to READ ONLY mode.
  B. Run script tts_tsro.sql to set the tablespaces to be transported to read only mode.
 C.  This script creates the tts_tsrw.sql script.  Use script tts_tsrw.sql later in step 13 to set all tablespaces to READ WRITE mode after the transport tablespace import process.
  D. Save script tts_tsrw.sql to be run on the destination database later in step 13.
 10.  Export the tablespaces:
 11.  Endianness conversion:
 12. Copy the following files to a place that is accessible to the destination database.
 13.  Add the db_32k_cache_size parameter to the target database to support the BIG_DATA tablespace being transported from step 8.
 14.  Create users:
 15.  Import the tablespaces:
 16.  Make tablespace read write:
 17.  Import source metadata:
 18.  Verification:
 A.  At the source database:
 B.  Export table t1 to destination database.
 C.  Import table t1 into destination database.
 D.  At destination database:
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.