Converting Data Between Platforms

When you perform a transportable operation, and the source platform and the target platform are of different endianness, you must convert the data being transported to the target format.

You can use the DBMS_FILE_TRANSFER package or the RMAN CONVERT command to convert data. To query the endian format of platform:

COLUMN PLATFORM_NAME FORMAT A40

COLUMN ENDIAN_FORMAT A14

SELECT PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT

  FROM V$TRANSPORTABLE_PLATFORM

  ORDER BY PLATFORM_ID;

（一）Converting Data Between Platforms Using the DBMS_FILE_TRANSFER Package

You can use the GET_FILE or PUT_FILE procedure in the DBMS_FILE_TRANSFER package to convert data between platforms during the data file transfer.

When you use one of these procedures to move data files between the source platform and the target platform, each block in each data file is converted to the target platform's endianness. The conversion occurs on the target platform.

The example makes the following assumptions:

1. The GET_FILE procedure will transfer the data file.
2. The mytable.342.123456789 data file is being transferred to a different platform.
3. The endianness of the source platform is different from the endianness of the target platform.
4. The global name of the source database is dbsa.example.com.
5. Both the source database and the target database use Oracle Automatic Storage Management (Oracle ASM).

Note: You can also use the DBMS_FILE_TRANSFER package to transfer data files between platforms with the same endianness.

Complete the following steps to convert the data file by transferring it with the GET_FILE procedure:

1. Connect to the source database as an administrative user that can create directory objects.
2. Create a directory object for the directory that contains the data file that will be transferred to the target database.

CREATE OR REPLACE DIRECTORY sales_dir_source AS '+data/dbsa/datafile';

1. Connect to the target database as an administrative user that can create database links, create directory objects, and run the procedures in the DBMS_FILE_TRANSFER package.
2. Create a database link from the target database to the source database.

The connected user at the source database must have read privilege on the directory object you created in Step 2.

1. Create a directory object to store the data files that will be transferred to the target database.

The user at the local database who will run the procedure in the DBMS_FILE_TRANSFER package must have write privilege on the directory object.

CREATE OR REPLACE DIRECTORY sales_dir_target AS '+data/dbsb/datafile';

1. Run the GET_FILE procedure in the DBMS_FILE_TRANSFER package to transfer the data file.

BEGIN

  DBMS_FILE_TRANSFER.GET_FILE(

    source_directory_object      => 'sales_dir_source',

    source_file_name             => 'mytable.342.123456789',

    source_database              => 'dbsa.example.com',

    destination_directory_object => 'sales_dir_target',

    destination_file_name        => 'mytable');

END;

/

（二）Converting Data Between Platforms Using RMAN

RMAN的CONVERT使用详细见《Transporting Data Across Platforms by RMAN CONVERT》

Running the RMAN CONVERT command is an additional step that can be completed on the source or target platform. It converts the data being transported to the target platform format.

You can convert data with the following RMAN CONVERT commands:

CONVERT DATAFILE

CONVERT TABLESPACE

CONVERT DATABASE

1. Converting Tablespaces on the Source System After Export

An example illustrates how to use the RMAN CONVERT TABLESPACE command to convert tablespaces to a different platform.

The example makes the following assumptions:

1. The sales_1 and sales_2 tablespaces are being transported to a different platform.
2. The endianness of the source platform is different from the endianness of the target platform.
3. You want to convert the data on the source system, before transporting the tablespace set to the target system.
4. You have completed the Data Pump export on the source database.

Complete the following steps to convert the tablespaces on the source system:

1. At a command prompt, start RMAN and connect to the source database:

$ RMAN TARGET /

1. Use the RMAN CONVERT TABLESPACE command to convert the data files into a temporary location on the source platform.

RMAN> CONVERT TABLESPACE sales_1,sales_2

TO PLATFORM 'Microsoft Windows IA (32-bit)'

FORMAT '/tmp/%U';

1. Transfer the data files to the target system.

1. Converting Data Files on the Target System Before Import

An example illustrates how to use the RMAN CONVERT DATAFILE command to convert data files to a different platform.

During the conversion, you identify the data files by file name, not by tablespace name. Until the tablespace metadata is imported, the target instance has no way of knowing the desired tablespace names.

The example makes the following assumptions:

1. You have not yet converted the data files for the tablespaces being transported.
2. The following data files are being transported to a different platform:

C:\Temp\sales_101.dbf

C:\Temp\sales_201.dbf

The data files must be accessible to the target database. If they are not accessible to the target database, then transfer the data files to the target system.

1. The endianness of the source platform is different from the endianness of the target platform.
2. You want to convert the data on the target system, before performing the Data Pump import.
3. The converted data files are placed in C:\app\orauser\oradata\orawin\, which is the location of the existing data files for the target system:

Complete the following steps to convert the tablespaces on the target system:

Use the RMAN CONVERT DATAFILE command to convert the data files on the target platform:

$ RMAN TARGET /

RMAN> CONVERT DATAFILE 'C:\Temp\sales_101.dbf', 'C:\Temp\sales_201.dbf'

TO PLATFORM="Microsoft Windows IA (32-bit)"

FROM PLATFORM="Solaris[tm] OE (32-bit)"

DB_FILE_NAME_CONVERT='C:\Temp\', 'C:\app\orauser\oradata\orawin\'

PARALLELISM=4;

If the source location, the target location, or both do not use Oracle Automatic Storage Management (Oracle ASM), then the source and target platforms are optional. RMAN determines the source platform by examining the data file, and the target platform defaults to the platform of the host running the conversion.

If both the source and target locations use Oracle ASM, then you must specify the source and target platforms in the DB_FILE_NAME_CONVERT clause.