How to Move a Database Using Transportable Tablespaces
(Doc ID 1493809.1)
Last updated on FEBRUARY 21, 2024
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
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Information in this document applies to any platform.
Goal
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
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 |