Cross-Platform Tranportable Tablespace: CONVERT DATAFILE or TABLESPACE

Transportable tablespaces allow you to copy an entire tablespace between Oracle databases. A full discussion of transportable tablespaces, their uses, and the different techniques for creating and using them can be found in the Oracle Database Administrator's Guide.

Cross-platform transportable tablespaces are transportable tablespaces in which the destination hosts are of a different platform from the source host. Typical uses of cross-platform transportable tablespaces include:

Publishing structured data as transportable tablespaces for distribution to customers, who can convert the tablespaces for integration into their existing databases regardless of platform
Moving data from a large data warehouse server to data marts on smaller computers such as Linux-based workstations or servers
Sharing read-only tablespaces across a heterogeneous cluster where all hosts share the same endian format

When transporting tablespaces between databases where the endian format of the source platform is different from that of the destination platform, the endian format of the datafiles in the transportable tablespace set must be converted to match the destination platform. This conversion can be performed using the RMAN CONVERT TABLESPACE command (when converting on the source host) or CONVERT DATAFILE command (when converting on the destination host).

Note:

Using the RMAN CONVERT command to convert the datafiles of a transportable tablespace set for use on platforms with different endian formats is only one step in using cross-platform transportable tablespaces. Read the discussion of transportable tablespaces in Oracle Database Administrator's Guide in its entirety before attempting to use transportable tablespaces or the procedures in this section.

CONVERT does not perform in-place conversion of datafiles. It produces output files in the correct format for use on the destination platform, but does not alter the contents of the source datafiles.

Differences between the conversion process on the source and destination platforms are described in the following discussion. The CONVERT TABLESPACE command must be used on the source platform, while the CONVERT DATAFILE command is used on the destination platform.

This discussion contains the following sections:

Using CONVERT TABLESPACE... TO PLATFORM on the Source Platform
Using CONVERT DATAFILE... FROM PLATFORM on the Destination Host

Using CONVERT TABLESPACE... TO PLATFORM on the Source Platform

The CONVERT TABLESPACE... TO PLATFORM command is used to convert tablespaces on the source platform to the format of a destination platform. Invoke CONVERT TABLESPACE, specifying the names of one or more tablespaces in the target database to convert and the destination platform for the conversion, as shown in this example:

RMAN> CONVERT TABLESPACE ts_1, ts_2...
   TO PLATFORM 'platform_name';

The TO PLATFORM clause is mandatory with CONVERT TABLESPACE. Supported values for platform_name can be found in V$TRANSPORTABLE_PLATFORM. Note that you can only convert entire tablespaces, on the source platform, not individual datafiles.

Optional parameters for CONVERT TABLESPACE ... TO PLATFORM include:

PARALLELISM n

Used to specify that n server sessions should perform the work of conversion in parallel to improve performance. Each datafile is assigned to a single server session for conversion, that is, you cannot improve performance on converting a single datafile by assigning a greater degree of parallelism.

Note:

The optimal degree of parallelism to use is a function of the number of effective disk heads available for reading and writing. Setting the degree of parallelism too high for a given number of spindles can actually increase the time required for conversion. It is never useful to specify a degree of parallelism greater than the number of datafiles to be processed.

fileNameConversionSpec

A series of patterns used to generate new file names for the converted datafiles, based on the input datafile names.
FORMAT formatSpec

Provides a format used as a template to generate new, unique filenames for the converted datafiles. If no FORMAT is specified, then RMAN uses a platform-dependent destination and format.

The full semantics of these parameters are described in the reference entry for the CONVERT command in the Oracle Database Backup and Recovery Reference.

Rules for Renaming Files When Converting Tablepsaces Across Platforms

You can use the FORMAT and fileNameConversionSpec arguments to control the names of the files generated by the CONVERT command, or leave the renaming of converted files to default rules provided by RMAN. The following list describes the order of precedence among these rules:

Files that match any patterns provided in a fileNameConversionSpec clause are renamed based upon that pattern.
If you specify a FORMAT clause, then any file not renamed based upon a fileNameConversionSpec pattern are renamed according to the specified formatSpec.
Any file not renamed by fileNameConversionSpec or FORMAT is assigned a default location based upon the following rules:
- If the channel used for output has a default CONFIGURE... FORMAT setting, that setting is used to generate output file names
- If a flash recovery area is configured, the converted datafiles are placed in the flash recovery area (though they are not usable backups).
- Otherwise a platform-specific default FORMAT (which includes a %U for generating a unique filename) is used.

Note:

These are the same rules that govern default locations for backups. These rules are described in the reference entry for BACKUP AS COPY in Oracle Database Backup and Recovery Reference.

Converting Tablespaces on the Source Host: Example

Suppose you need to transport tablespaces finance and hr from a source database running on a Sun Solaris host to a destination database running on a Linux PC. You plan to store the converted datafiles in the temporary directory /tmp/transport_linux/ on the source host.

The example assumes that you have carried out the following steps in preparation for the tablespace transport:

You have set the tablespaces to be transported to be read-only.
You have looked up the name for the destination platform in V$TRANSPORTABLE_PLATFORM.

The database has a list of its own internal names for each platform supporting cross-platform data transport. You may need the exact name of the source or target platform as a parameter to the CONVERT command. Query V$TRANSPORTABLE_PLATFORM to get the platform name from SQL*Plus as follows:
```
SQL> SELECT PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT
   FROM V$TRANSPORTABLE_PLATFORM 
   WHERE UPPER(PLATFORM_NAME) LIKE 'LINUX';
```
The PLATFORM_NAME for Linux on a PC is 'Linux IA (32-bit)'.

Now use RMAN to convert the datafiles into the endian format of the destination host. In this example, the FORMAT argument controls the name and location of the converted datafiles.

% rman TARGET /
RMAN> CONVERT TABLESPACE finance,hr
   TO PLATFORM 'Linux IA (32-bit)'
   FORMAT='/tmp/transport_linux/%U';

The result is a set of converted datafiles in the /tmp/transport_linux/ directory, with data in the correct endian format for the Linux IA (32-bit) platform.

From this point, follow the rest of the general outline for creating a transportable tablespace set:

Use the export utility to create the export dump file
Move the converted datafiles from /tmp/transport_linux/ and the export dump file from the source host to the desired directories on the destination host
Plug the tablespace into the new database with the Import utility.

Using CONVERT DATAFILE... FROM PLATFORM on the Destination Host

The CONVERT DATAFILE... FROM PLATFORM command is used on the destination platform to convert tablespaces from the format of a source platform into a format that can be plugged into a database on the destination platform.

Note:

You cannot use CONVERT TABLESPACE on the destination platform. Until the datafiles are transported into the destination database, the datafiles are not associated with a tablespace name in the database, so RMAN cannot translate the tablespace name into a list of datafiles. Therefore, you must use CONVERT DATAFILE and identify the datafiles by filename.

CONVERT DATAFILE takes as arguments the names of one or more datafiles to convert, and the name of the source platform for the datafiles, as shown in this example:

RMAN> CONVERT DATAFILE datafile_1, datafile_2...
   FROM PLATFORM 'platform_name';

The destination platform is the platform on which the destination database is running.

The value provided for FROM PLATFORM must match the actual format of the datafiles to be converted, or RMAN returns an error. Supported values for platform_name can be found in V$TRANSPORTABLE_PLATFORM.

Note:

The FROM PLATFORM clause is optional with CONVERT DATAFILE. If omitted, however, it is assumed that the datafiles to be converted are already in the format of the destination platform. The effect of CONVERT DATAFILE without FROM PLATFORM is to copy datafiles from one location to another without changing their format, and without recording the copies created in the RMAN repository as backups of the copied datafiles.

The primary use of CONVERT DATAFILE without FROM PLATFORM is in copying datafiles into and out of Automated Storage Management (ASM) disk groups. Operating system-level tools cannot be used to read or write files into ASM, but RMAN provides the required functionality. See "Using RMAN CONVERT to Copy Files Between ASM and Non-ASM Storage" for more details on this procedure.

The PARALLELISM, FORMAT, and fileNameConversionSpec arguments, described in "Using CONVERT TABLESPACE... TO PLATFORM on the Source Platform", behave the same on the source and destination platforms.

Converting Datafiles on the Destination Platform: Example

Suppose you need to transport tablespaces finance (datafiles 'fin/fin01.dbf' and 'fin/fin02.dbf') and hr (datafiles 'hr/hr01.dbf' and 'hr/hr02.dbf') from a source database running on a Sun Solaris host to a destination database running on a Linux PC host. You plan to perform conversion on the destination host.

When the datafiles are plugged into the destination database, you plan to store them in /orahome/dbs and preserve the current directory structure (that is, datafiles for the hr tablespace go in the /orahome/dbs/hr subdirectory, and datafiles for the finance tablespace go in the /orahome/dbs/fin directory).

The example assumes that you have carried out the following steps in preparation for converting the tablespaces and plugging them in at the destination:

You have set the source tablespaces to be transported to be read-only, used the Original Export utility to create the export dump file (named, in our example, expdat.dmp), and copied expdat.dmp and the unconverted datafiles of the transportable tablespace set to the destination host, in the /tmp/transport_solaris/' directory. You have preserved the subdirectory structure from the files' original location, that is, the datafiles are stored as:
- /tmp/transport_solaris/fin/fin01.dbf
- /tmp/transport_solaris/fin/fin02.dbf
- /tmp/transport_solaris/hr/hr01.dbf
- /tmp/transport_solaris/hr/hr02.dbf

Now use RMAN's CONVERT command to convert the datafiles to be transported to the destination host's format and store the converted datafiles in /orahome/dbs.

% rman TARGET /
RMAN> CONVERT DATAFILE
   '/tmp/transport_solaris/fin/fin01.dbf',
   '/tmp/transport_solaris/fin/fin02.dbf',
   '/tmp/transport_solaris/hr/hr01.dbf',
   '/tmp/transport_solaris/hr/hr02.dbf'
   DB_FILE_NAME_CONVERT
        '/tmp/transport_solaris/fin','/orahome/dbs/fin',
        '/tmp/transport_solaris/hr','/orahome/dbs/hr'

Note the following:

The FORMAT argument controls the name and location of the converted datafiles.
When converting on the destination platform, you must specify the source platform using the FROM PLATFORM argument. Otherwise, RMAN will assume that the source platform is the same as the platform of the host performing the conversion.

The result is a set of converted datafiles in the /orahome/dbs/ directory, named thus:

/orahome/dbs/fin/fin01.dbf
/orahome/dbs/fin/fin02.dbf
/orahome/dbs/hr/hr01.dbf
/orahome/dbs/hr/hr02.dbf

From this point, follow the rest of the general outline for tablespace transport. Plug the converted tablespaces into the new database with the import utility, and make the tablespaces read-write if applicable.

Restrictions on CONVERT TABLESPACE and CONVERT DATAFILE

Note the following restrictions on CONVERT TABLESPACE and CONVERT DATAFILE:

Both source and destination databases must be running with the COMPATIBLE initialization parameter set to 10.0 or higher.
Not all combinations of source and destination platforms are supported. To determine whether your source and destination platforms are supported, query V$TRANSPORTABLE_PLATFORM. If both the source and destination platforms are listed in this view, then CONVERT can be used to prepare datafiles from one platform for use on the other.
A tablespace must be made read-write at least once in release 10g before it can be transported to another platform using CONVERT. Hence, any read-only tablespaces (or previously transported tablespaces) from a previous release must first be made read-write before they can be transported to another platform.
RMAN does not process user datatypes that require endian conversions.
Prior to release 10g, CLOBs were created with a variable-width character set and stored in an endian-dependent format. The CONVERT command does not perform conversions on these CLOBs. Instead, RMAN captures the endian format of each LOB column and propagates it to the target database. Subsequent reads of this data by the SQL layer interpret the data correctly based upon either endian format, and write the data out in an endian-independent format if the tablespace is writeable.

CLOBs created in Oracle Database Release 10g are stored in character set AL16UTF16, which is platform-independent.