CONVERT

Syntax

convert::=

Text description of convert

convertOptionList::=

Text description of convertOptionList

Purpose

To quickly transport a tablespace across different platforms. Important uses of the command include:

• Content providers can publish structured data as transportable tablespaces and distribute it to customers who can easily and efficiently integrate this data into their Oracle databases, regardless of their chosen platform.
• Data from a large data warehouse server can be distributed to data marts on smaller computers (for example, Windows NT workstations).
• Read-only tablespaces can be shared across a heterogeneous cluster.

  Note: The CONVERT command is only one part of a multiple-step process for transporting tablespaces across platforms. The full process is described in Oracle Database Administrator's Guide. You should refer to that document before attempting to transport a tablespace across platforms.

Restrictions and Usage Notes

• Both source and destination databases must be running with initialization parameter COMPATIBLE set to 10.0 or higher.
• A tablespace must have been made read-write at least once in Release 10g before it can be transported to another platform. Hence, any read-only tablespaces (or currently existing transported tablespaces) that exist from an earlier release must be first made read-write before they can be transported to a different platform.
• RMAN does not process user datatypes that require endian conversions. If you need to transport objects built on underlying types that store data in a platform-specific format, then use the Data Pump Import and Export utilities.
• Query V$TRANSPORTABLE_PLATFORM to determine the platforms supported by the CONVERT command. Cross-platform tablespace transport is only supported when both the source and destination platforms are contained in this view.

  In Release 10g, the CONVERT command is required when transporting between platforms for which the value in V$TRANSPORTABLE_PLATFORM.ENDIAN_FORMAT is different. When transporting between platforms for which the ENDIAN_FORMAT column is the same, you can either use the CONVERT command to move the file, or copy the file from the source to the destination with operating system utilities. If the destination host uses ASM storage, you must use CONVERT to move the data into ASM. Operating system utilities cannot be used to move data into ASM.

• The CONVERT command can be run either on the source host or the destination host. When converting on the source host, CONVERT... TO is used to identify the destination platform (and the source platform is, implicitly, the platform of the source host). When converting on the destination host, use CONVERT... FROM to identify the source platform (and the destination platform is, implicitly, the platform of the destination host).
• CONVERT does not do in-place conversion of datafiles. It creates an output file that is readable on the specified platform.
• Prior to Release 10g, CLOBs were created in 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 will interpret the data correctly based on either endian format and write it out in an endian- independent way if the tablespace is writeable.

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

Keywords and Parameters

Syntax Element	Description
DATAFILE datafile_name	Specifies the name of a datafile that you want to transport into the destination database. At the destination, you must use CONVERT DATAFILE instead of CONVERT TABLESPACE, and name each datafile that is being converted. Because those files have not yet been imported into the destination database, RMAN cannot determine which files belong to the tablespace being converted.
convertOptionList	Options for this CONVERT command.
fileNameConversionSpec	A set of string pairs. Whenever any of the input filenames contains one of the first halves of a pair, anywhere in the filename, it will be replaced with the second half of the same pair.You can use as many pairs of replacement strings as required. You can use single or double quotation marks.
FORMAT formatSpec	Specifies the name template for the output file(s). See the BACKUP AS COPY command for the format values that are valid here.
FROM PLATFORM = platform_name	Specifies the name of the source platform. This must be one of the platforms listed in the PLATFORM_NAME column of the V$TRANSPORTABLE_PLATFORM view.
PARALLELISM [=] integer	Specifies the degree of parallelism to use while performing the operation.
TO PLATFORM = platform_name	Specifies the name of the destination platform as displayed in the V$TRANSPORTABLE_PLATFORM.PLATFORM_NAME output.
TABLESPACE tablespace_name	Specifies the name of a tablespace in the source database that you want to transport into the destination database on a different platform. CONVERT TABLESPACE can only be used when connected to the source database, not the destination database. (Until the tablespace transport has been completed, the destination database has no way of recognizing the tablespace name for use with CONVERT TABLESPACE.)

Examples

The procedure for transporting tablespaces is documented at length in Oracle Database Administrator's Guide. RMAN's CONVERT command is only required in cases where you are transporting between platforms with different byte ordering. If your platforms have the same byte ordering, then you can either use CONVERT or copy the files directly.

The basic outline of the process is as follows:

1. Identify the tablespaces that will be transported. Depending on relations between objects in the tablespaces you want to transport and objects in other tablespaces, this may require careful planning.
2. Make the tablespaces to be moved read-only.
3. Use the Original Export utility to generate a file containing structural information from the data dictionary for the tablespaces to be transported. This file will be used when plugging the tablespaces into the destination database.
4. If you need to convert your datafiles for transport and you wish to use the source system's resources for the conversion, then use the RMAN CONVERT command at this point on the source platform to convert the tablespaces for the target platform. See the first example following this outline. (If you prefer to use the destination system's resources for the conversion, do nothing in this step.)
5. Copy the datafiles (converted, if necessary) and the structural information file to the target database. You may move these by any means that is convenient: an operating system copy, ftp, or even distribution on removable media like CDs or tapes.
6. If you are transporting across platforms where endian conversion is required, and you did not perform the conversion on the source platform, then perform the conversion on the destination platform. See the second example following this outline.
7. Invoke the Original Import utility to plug the set of tablespaces into the target database.

For more details on this process, see Oracle Database Administrator's Guide. Read that discussion in its entirety before attempting any part of the tablespace transport process. The discussion in this document will focus on the specifics of using the CONVERT command, as you would use it on the source and destination platforms.

Converting Tablespaces on the Source Platform: Example

In this scenario you need to transport the following tablespaces from a source database running on a Sun Solaris host to a destination database running on a Linux PC:

• finance (datafiles `/orahome/fin/fin01.dbf' and `/orahome/fin/fin02.dbf')
• hr (datafiles `/orahome/fin/hr01.dbf' and `/orahome/fin/hr02.dbf')

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 it runs on. 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 to be transported to the destination host's format on the source host. 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 right endian-order for the Linux IA (32-bit) platform.

From this point, you follow the rest of the general outline for tablespace transport. Use the export utility to create the file of structural information, if you have not already, move the structural information file and the converted datafiles from /tmp/transport_linux/ to the desired directories on the destination host, and plug the tablespace into the new database with the Import utility.

Converting Tablespaces on the Target Platform: Example

In this scenario you need to transport the following tablespaces from a source database running on a Sun Solaris host to a destination database running on a Linux PC:

• finance (datafiles `/orahome/fin/fin01.dbf' and `/orahome/fin/fin02.dbf')
• hr (datafiles `/orahome/fin/hr01.dbf' and `/orahome/fin/hr02.dbf')

You plan to perform conversion on the target host. You will temporarily store the unconverted datafiles in the directory /tmp/transport_solaris/ on the target host. When the datafiles are plugged into the destination database, they will be stored in /orahome/dbs.

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

• Set the source tablespaces to be transported to be read-only
• Used the Original Export utility to create the structural information file (named, in this scenario, expdat.dmp)
• Copied expdat.dmp and the unconverted tablespace datafiles to be transported to the destination host, in the /tmp/transport_solaris/ directory, preserving the subdirectory structure from the files' original location, so that 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

You can now use RMAN's CONVERT command to convert the datafiles to be transported to the destination host's format and deposit the results in /orahome/dbs.

Note the following:

• You have to identify the datafiles by filename, not by tablespace name. Until the datafiles are plugged in, the local instance cannot recognize the desired tablespace names.
• The DB_FILE_NAME_CONVERT argument controls the name and location of the converted datafiles.
• You must specify the source platform. Otherwise, RMAN will assume that the source platform is the platform on which the conversion process is run. 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 'SOLARIS';
  
  

  The PLATFORM_NAME for the source platform in this example is 'Solaris[tm] OE (32-bit)'.

• You do not need to specify the target platform. The target platform defaults to the platform of the host running the conversion.

  % 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'
     FROM PLATFORM 'Solaris[tm] OE (32-bit)'
     DB_FILE_NAME_CONVERT
          '/tmp/transport_solaris/fin','/orahome/dbs/fin',
          '/tmp/transport_solaris/hr','/orahome/dbs/hr'
  

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

• /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. Use Import to plug the converted tablespaces into the new database with the import utility, and make the tablespaces read-write if applicable.