Oracle Database 10g Cross Platform Transportable Tablespace

Paul Tsien, Server Technologies, Oracle Corporation

Introduction

Oracle's transportable tablespace feature allows users to quickly move a user tablespace across Oracle databases.  It is the most efficient way to move bulk data between databases.

Moving data using transportable tablespaces can be much faster than performing either an export/import or unload/load of the same data.  This is because transporting a tablespace only requires the copying of datafiles and integrating the tablespace structural information.  You can also use transportable tablespaces to move both table and index data, thereby avoiding the index rebuilds you would have to perform when importing or loading table 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)

Prior to Oracle Database 10g, if you want to transport a tablespace, both source and target databases need to be on the same platform. 

Oracle Database 10g adds the cross platform support for transportable tablespaces.  With the cross platform transportable tablespace, you can transport tablespaces across platforms. This functionality can be used to:

• Provide an easier and more efficient means for content providers to publish structured data and distribute it to customers running Oracle on a different platform
• Simplify the distribution of data from a data warehouse environment to data marts which are often running on smaller systems on different platforms
• Enable the sharing of read only tablespaces across a heterogeneous cluster (the nodes must have same endianess)
• Allow a database to be migrated from one platform to another (use with Data Pump or Import/Export)

Figure 1 shows the supported platforms for cross-platform tablespace transport. You can also query the V$TRANSPORTABLE_PLATFORM view to see the platforms that are supported, and to determine their platform IDs and their endian format (byte ordering).

If the source platform and the target platform are of different endianness, then an additional conversion step must be done on either the source or target platform to convert the tablespace being transported to the target format.  If they are of the same endianness, then no conversion is necessary and tablespaces can be transported as if they were on the same platform.

Before a tablespace can be transported to a different platform, it must have been read/write a least once in an Oracle Database 10g with compatibility set to 10.0.0 or higher. This is because that action makes the datafiles within the tablespace platform aware, meaning that each file identifies the platform to which it belongs.

How the Transportable Tablespace Works

Figure 2 shows the general procedure of how you can transport a tablespace from one database to another. 

How the Cross Platform Transportable Tablespace Works

Figure 3 shows the procedure of how you can transport a tablespace from one platform to another.  See the Oracle Database 10g Documentation: Administrator's Guide, Transporting Tablespaces Between Databases section, for detailed steps and example

Procedure for Transporting Tablespaces Between Different Platforms

To move or copy a set of tablespaces across different platforms, perform the following steps.

1. Check the endian format of both platforms by querying the V$TRANSPORTABLE_PLATFORM view.

   Determine if the source and target platforms are supported and their endianness. If both platforms have the same endianness, no conversion is necessary. Otherwise you must do a conversion of the tablespace set either at the source or target database.

2. Pick a self-contained set of tablespaces.

3. Generate a transportable tablespace set.

   A transportable tablespace set consists of datafiles for the set of tablespaces being transported and an export file containing structural information for the set of tablespaces.

   If you are transporting the tablespace set to a platform with different endianness from the source platform, you must convert the tablespace set to the endianness of the target platform. You can perform a source-side conversion at this step in the procedure, or you can perform a target-side conversion as part of step 4.

4. Transport the tablespace set.

   Copy the datafiles and the export file to the target database. You can do this using any facility for copying flat files (for example, an operating system copy utility, ftp, the DBMS_FILE_COPY package, or publishing on CDs).

   If you have transported the tablespace set to a platform with different endianness from the source platform, and you have not performed a source-side conversion to the endianness of the target platform, you should perform a target-side conversion now.

5. Plug in the tablespace.

   Invoke the Data Pump utility to plug the set of tablespaces into the target database.

References

1. Oracle Database High Availability web site on OTN
2. Oracle Database 10g Documentation

Paul Tsien (Paul.Tsien@oracle.com) is a product manager with Oracle's High Availability and Storage Management Group.  He also manages a number of Oracle storage programs including the Oracle Storage Compatibility Program and Oracle's Hardware Assisted Resilient Data Initiative.  His extensive product management and product marketing experience includes server systems, storage systems, and database products.