CONVERT

Syntax

Description of the illustration convert.gif

Description of convertoptionlist.gif follows

Description of the illustration convertoptionlist.gif

Purpose

To convert a datafile, tablespace or database to the format of a destination platform, in preparation for transport across different platforms.

CONVERT TABLESPACE is used at the source database to produce datafiles for the specified tablespaces in the format of a different destination platform. The converted files can then be transported to the destination platform.
CONVERT DATAFILE is used on the destination database to convert datafiles that are in the format of a different source platform. Once all of the datafiles required for a tablespace have been converted, the datafiles can be transported into the destination database.
CONVERT DATABASE is used to transport an entire database from a source platform to a destination platform, converting the datafiles to the format of the destination platform and ensuring the creation of other required database files. Depending upon the requirements of your situation, CONVERT DATABASE on either the source or destination platform.

The following list describes some situations in which CONVERT TABLESPACE and CONVERT DATAFILE can be useful:

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 such as Windows-based workstations or servers.
Read-only tablespaces can be shared across a heterogeneous cluster.

Note:

The CONVERT TABLESPACE and CONVERT DATAFILE commands are only one part of a multiple-step process for transporting datafiles and tablespaces across platforms. You can transport datafiles and tablespaces using your live datafiles with the process described in Oracle Database Administrator's Guide or from backups using the process described in Oracle Database Backup and Recovery Advanced User's Guide. You should refer to that document before attempting to transport a tablespace across platforms.

See Also:

Oracle Database Backup and Recovery Advanced User's Guide for a complete discussion of the use of CONVERT DATAFILE, CONVERT TABLESPACE and CONVERT DATABASE

CONVERT DATAFILE and CONVERT TABLESPACE can also be used to move files into and out of Automated Storage Management (ASM) disk groups. This capability is needed because native operating system file manipulation commands like Unix cp and Windows COPY cannot read from or write to ASM disk groups.

Restrictions and Usage Notes

This discussion includes the following sections:

Restrictions and Usage Notes on All Forms of RMAN CONVERT
Restrictions and Usage Notes on CONVERT DATAFILE and CONVERT TABLESPACE
Restrictions on CONVERT DATABASE

Restrictions and Usage Notes on All Forms of RMAN CONVERT

The following restrictions apply to CONVERT DATAFILE, CONVERT TABLESPACE and CONVERT DATABASE:

The input files are not altered by the CONVERT process. The conversion is not performed in place. Instead, converted files are written to a specified output destination.
Both source and destination databases must be running with initialization parameter COMPATIBLE set to 10.0 or higher.
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.
CONVERT does not process user datatypes that require endian conversions. If you need to transport objects between databases that are built on underlying types that store data in a platform-specific format, then use the Data Pump Import and Export utilities.
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 at least once before they can be transported to a different platform. (It is sufficient to open the tablespace read-write and then immediately make it read-only again.)

Restrictions and Usage Notes on CONVERT DATAFILE and CONVERT TABLESPACE

The following usage notes apply to CONVERT DATAFILE and CONVERT TABLESPACE:

To convert the datafiles of a tablespace on the source host, use CONVERT TABLESPACE... TO and identify the tablespace to be converted and the destination platform. You cannot convert individual datafiles on the source platform using CONVERT DATAFILE.

When converting on the destination host, use CONVERT DATAFILE... FROM to identify the source platform (and the destination platform is, implicitly, the platform of the destination host). You must use CONVERT DATAFILE, rather than CONVERT TABLESPACE, on the destination, because the destination database does not have any information to associate the datafiles being converted with tablespaces during the conversion process.
You can use either the FORMAT or fileNameConversionSpec arguments to control the names of the output files generated by the CONVERT command. If you do not specify either, the rules governing the location of the output files are the same as those governing the output files from a BACKUP AS COPY operation. These rules are described in the "backupTypeSpec" reference entry .
Not all platforms support the use of CONVERT. 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.

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%';
```
Note:
In Release 10g, the CONVERT DATAFILE or CONVERT TABLESPACE command is required when transporting datafiles 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 do not need to use either CONVERT DATAFILE or CONVERT TABLESPACE command to convert the files. You can move or copy the file from the source to the destination with operating system utilities.
Because you cannot use operating system utilities to move files into ASM, you may want to use CONVERT TABLESPACE or CONVERT DATAFILE to move files into ASM even if no change in endian format is required. Using CONVERT in this manner provides the equivalent of an operating system-level file copy command for copying files into ASM. BACKUP AS COPY provides similar functionality but catalogs the file copies created in the RMAN repository, which is only desirable if the file copies created in ASM are intended for use as backups at the target database.

Restrictions on CONVERT DATABASE

Because CONVERT DATABASE uses the same mechanism as CONVERT TABLESPACE and CONVERT DATAFILE to convert the datafiles of the database, the usage notes and restrictions in "Restrictions and Usage Notes on CONVERT DATAFILE and CONVERT TABLESPACE" also apply to the conversion of databases.

The primary additional restriction on transporting entire databases is that the source and target platform must share the same endian format. For example, while you can transport a database from Microsoft Windows to Linux for x86 (both little-endian), or from HP-UX to AIX (both big-endian), you cannot transport a whole database from Solaris to Linux to x86 using this feature. You can, however, create a new database on a target platform manually, and transport individual tablespaces from the source database using the RMAN CONVERT TABLESPACE or CONVERT DATAFILE commands.

Note:

In spite of the fact that the endian formats for the source and destination platform are the same, the datafiles for a transportable database must undergo a conversion process, on either the source or destination host. Unlike transporting tablespaces across platforms, where conversion is not necessary if the endian formats are the same, transporting an entire database requires that certain types of blocks, such as blocks in undo segments, be reformatted to ensure compatibility with the destination platform.

Also note that some parts of the database are not transported directly:

Redo log files and control files from the source database are not transported. New control files and redo log files are created for the target database during the transport process, and an OPEN RESETLOGS is performed once the new database is created.

Note:
The control file for the converted database does not contain a copy of the RMAN repository information from the source database. Backups from the source database cannot be used with the converted database.
BFILEs are not transported. RMAN provides a list of objects using the BFILE datatype in the output for the CONVERT DATABASE command, but users must copy the BFILEs themselves and fix their locations on the target platform.
Datafiles for locally managed temporary tablespaces are not transported. The temporary tablespaces are re-created at the target platform by running the transport script.
External tables and directories are not transported. RMAN provides a list of affected objects as part of the output of the CONVERT DATABASE command, but users must redefine these on the target platform. See Oracle Database Administrator's Guide for more information on managing external tables and directories.
Password files are not transported. If a password file was used with the source database, the output of CONVERT DATABASE includes a list of all usernames and their associated privileges. Create a new password file on the target database using this information. See Oracle Database Security Guide for more information on managing password files.

Keywords and Parameters

convert

Syntax Element	Description
`DATABASE`	Used to transport entire databases across platforms (where the source and destination platforms have the same endian format).
`NEW DATABASE` `'database_name'`	Specifies the `DB_NAME` for the new database produced by the `CONVERT` `DATABASE` command.
`ON TARGET PLATFORM`	Specifies that any `CONVERT` commands required for datafiles should be performed on the destination platform rather than the source database. Useful if you do not want the overhead of the conversion on the source platform, or if you do not know the destination platform (for example, if you are publishing a transportable tablespace to be used by recipients with many different target platforms).
`CONVERT SCRIPT` `'script_name'`	Specifies the location of the file to contain the convert script generated by `CONVERT` `DATABASE`... `ON` `TARGET` `PLATFORM`. If not specified, the convert script is not generated.
skipSpec	Specifies that `CONVERT` `DATABASE` should skip inaccessible, offline or read-only files during the conversion process.
`TRANSPORT SCRIPT` `'script_name'`	Specifies the location of the file to contain the transport script generated by `CONVERT` `DATABASE`. If omitted, the transport script is not generated.
`DATAFILE` `datafile_name`	Specifies the name of a datafile that you want to transport into the destination database. `CONVERT DATAFILE` can only be used at the destination database.
`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 and converting on the source platform.

convertOptionList

Syntax Element	Description
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 number of channels to be used to performing the operation. If not used, then channels allocated or configured for disk determine the number of channels.
`TO` `PLATFORM` `=` `platform_name`	Specifies the name of the destination platform as displayed in the `V$TRANSPORTABLE_PLATFORM.PLATFORM_NAME` output. If used on the destination platform, then this must be the value for the destination platform in `V$TRANSPORTABLE_PLATFORM.PLATFORM_NAME`.

Examples

Examples for Creating Transportable Tablespace Sets

The procedure for creating and using a transportable tablespace set is documented at length in Oracle Database Administrator's Guide. RMAN's CONVERT command is only required in cases where you are moving transportable tablespaces 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:

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.
Make the tablespaces to be moved read-only.
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.
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 TABLESPACE 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.)
Copy the datafiles (converted, if necessary) and the export dump 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.
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.
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 TABLESPACE and CONVERT DATAFILE commands.

Converting Tablespaces on the Source Platform: Example Suppose you need to convert tablespaces finance (datafiles '/orahome/fin/fin01.dbf' and '/orahome/fin/fin02.dbf') and hr (datafiles '/orahome/fin/hr01.dbf' and '/orahome/fin/hr02.dbf') from a source database running on a Sun Solaris host to a destination database running on an 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 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 Datafiles on the Target Platform: Example Suppose you need to transport tablespaces finance (datafiles '/orahome/fin/fin01.dbf' and '/orahome/fin/fin02.dbf') and hr (datafiles '/orahome/hr/hr01.dbf' and '/orahome/hr/hr02.dbf') from a source database running on a Sun Solaris host to a destination database running on an Linux PC host. 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:

You have set the source tablespaces to be transported to be read-only, used the Original Export utility to create the structural information file (named, in our example, expdat.dmp), gathered expdat.dmp and the unconverted tablespace datafiles to be transported, and copied these files 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 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 has no way of knowing the desired tablespace names.
The FORMAT argument controls the name and location of the converted datafiles.
When converting on the destination host, you must specify the source platform using the FROM argument. Otherwise, RMAN will assume that the source platform is the same as the platform of the host performing 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'
   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 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. Use Import to plug the converted tablespaces into the new database with the import utility, and make the tablespaces read-write if applicable.

Copying Datafiles To and From ASM Using CONVERT DATAFILE: Examples

The following example illustrates copying datafiles into ASM from normal storage. Use CONVERT DATAFILE without specifying a source or destination platform, and specifying ASM disk group +DATAFILE for the output location, as shown here:

RMAN>  convert datafile '/disk1/oracle/dbs/my_tbs_f1.df', '/disk1/oracle/dbs/t_ax1.f'
   format '+datafile';
 
Starting backup at 29-MAY-05
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/disk1/oracle/dbs/t_ax1.f
converted datafile=+DATAFILE/asmv/datafile/sysaux.280.559534477
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting datafile conversion
input filename=/disk1/oracle/dbs/my_tbs_f1.df
converted datafile=+DATAFILE/asmv/datafile/my_tbs.281.559534493
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:04
Finished backup at 29-MAY-05

The following example illustrates copying the datafiles of a tablespace out of ASM storage, to directory /tmp, with uniquely generated filenames.

RMAN> convert tablespace tbs_2 format '/tmp/tbs_2_%U.df';
 
Starting backup at 03-JUN-05
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=20 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=+DATAFILE/tbs_21.f
converted datafile=/tmp/tbs_2_data_D-L2_I-2786301554_TS-TBS_2_FNO-6_11gm2fq9.df
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00007 name=+DATAFILE/tbs_22.f
converted datafile=/tmp/tbs_2_data_D-L2_I-2786301554_TS-TBS_2_FNO-7_12gm2fqa.df
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00019 name=+DATAFILE/tbs_25.f
converted datafile=/tmp/tbs_2_data_D-L2_I-2786301554_TS-TBS_2_FNO-19_13gm2fqb.df
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00009 name=+DATAFILE/tbs_23.f
converted datafile=/tmp/tbs_2_data_D-L2_I-2786301554_TS-TBS_2_FNO-9_14gm2fqc.df
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00010 name=+DATAFILE/tbs_24.f
converted datafile=/tmp/tbs_2_data_D-L2_I-2786301554_TS-TBS_2_FNO-10_15gm2fqd.df
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 03-JUN-05

Transporting Databases: Examples

Transporting databases is documented in Oracle Database Backup and Recovery Advanced User's Guide. Read that discussion in its entirety before attempting any part of the process of transporting a database. The discussion in this document will focus on the specifics of using the CONVERT DATABASE command.

The arguments to CONVERT DATABASE vary depending upon whether the conversion of the datafiles is performed on the source or destination platform. For a description of the conversion process on source and destination platforms and extended examples, refer to Oracle Database Backup and Recovery Advanced User's Guide.

The following example illustrates using the CONVERT DATABASE command and converting the datafiles to the destination platform format on the source host:

RMAN> CONVERT DATABASE NEW DATABASE 'newdb'
        transport script '/tmp/convertdb/transportscript'
        to platform 'Microsoft Windows IA (32-bit)'
        db_file_name_convert '/disk1/oracle/dbs' '/tmp/convertdb'
        ;

The following example illustrates using the CONVERT DATABASE command and converting the datafiles to the destination platform format on the destination host:

CONVERT DATABASE ON TARGET PLATFORM
     CONVERT SCRIPT '/tmp/convertdb/convertscript.rman'
        TRANSPORT SCRIPT '/tmp/convertdb/transportscript.sql'
        new database 'newdb'
        FORMAT '/tmp/convertdb/%U'