Cross-Platform Oracle Database Migration Solution with Different Byte Orders

Overview

Starting from Oracle 10g, databases support cross-platform transportable tablespaces. This document primarily explores how to implement transportable tablespaces for both ASM data files and OS filesystem data files.

If the target database is on a platform with a different byte order, the following steps outline how to migrate a database to a new platform using transportable tablespaces:

  1. Create a new, empty database on the target platform.
  2. Import the objects required for the transport operation from the source database to the target.
  3. Export the transportable metadata for all user tablespaces from the source database.
  4. Transfer the data files for the user tablespaces to the target system.
  5. Use RMAN to convert the data files to the target system’s byte order format.
  6. Import the transportable metadata for all user tablespaces into the target database.
  7. Import the remaining database objects and metadata (those not moved during the transport operation) from the source database to the target.

Alternatively, you can convert the data files on the source platform, and after conversion, transfer them to the target platform.

Migration Solution

Supported Platforms

Query V$TRANSPORTABLE_PLATFORM to check supported platforms and determine the byte order for each platform.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> COLUMN PLATFORM_NAME FORMAT A32
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;
PLATFORM_ID PLATFORM_NAME                    ENDIAN_FORMAT
----------- -------------------------------- --------------
          1 Solaris[tm] OE (32-bit)          Big
          2 Solaris[tm] OE (64-bit)          Big
          7 Microsoft Windows IA (32-bit)    Little
         10 Linux IA (32-bit)                Little
          6 AIX-Based Systems (64-bit)       Big
          3 HP-UX (64-bit)                   Big
          5 HP Tru64 UNIX                    Little
          4 HP-UX IA (64-bit)                Big
         11 Linux IA (64-bit)                Little
         15 HP Open VMS                      Little
          8 Microsoft Windows IA (64-bit)    Little
          9 IBM zSeries Based Linux          Big
         13 Linux 64-bit for AMD             Little
         16 Apple Mac OS                     Big
         12 Microsoft Windows 64-bit for AMD Little
         17 Solaris Operating System (x86)   Little

If the source and target platforms have different byte orders, an additional step is required either on the source or target platform to convert the transported tablespaces to the target format. If the byte orders are the same, no conversion is necessary, and the tablespace can be transported as if it were the same platform.

Transportable Tablespaces

  1. Preparation Before Transporting Tablespaces

a) Check that the tablespace is self-contained.

1
2
SQL> execute sys.dbms_tts.transport_set_check('TBS1,TBS2', true);
SQL> select * from sys.transport_set_violations;

注意：在表空间被传输之前，这些违反传输标准的问题必须被解决。

b) To successfully export a transportable tablespace, the tablespace must be in READ ONLY mode.

1
2
SQL> ALTER TABLESPACE TBS1 READ ONLY;
SQL> ALTER TABLESPACE TBS2 READ ONLY;
  1. Export Metadata

a) Use traditional export tools:

1
exp userid=\'sys/sys as sysdba\' file=tbs_exp.dmp log=tba_exp.log transport_tablespace=y tablespaces=TBS1,TBS2

b) Use Data Pump export:

First, create the directory object used by Data Pump, for example:

1
2
CREATE OR REPLACE DIRECTORY dpump_dir AS '/tmp/subdir' ;
GRANT READ,WRITE ON DIRECTORY dpump_dir TO system;

Then, initialize Data Pump export:

1
expdp system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = TBS1,TBS2

If you wish to enforce strict integrity checks during the transportable tablespace operation, use the TRANSPORT_FULL_CHECK parameter.

1
expdp system/password DUMPFILE=expdat.dmp DIRECTORY = dpump_dir TRANSPORT_TABLESPACES= TBS1,TBS2 TRANSPORT_FULL_CHECK=Y
  1. Use V$TRANSPORTABLE_PLATFORM to check the byte order for each platform. You can run the following query on each platform instance:
1
2
3
SELECT tp.platform_id,substr(d.PLATFORM_NAME,1,30), ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

If you find that the byte order is different, the transportable tablespace set must undergo conversion during the transport process.

1
2
RMAN> convert tablespace TBS1 to platform="Linux IA (32-bit)" FORMAT '/tmp/%U';
RMAN> convert tablespace TBS2 to platform="Linux IA (32-bit)" FORMAT '/tmp/%U';

Copy the data files and exported files to the target environment.

  1. Import Transportable Tablespace

a) Use traditional import tools:

1
imp userid=\'sys/sys as sysdba\' file=tbs_exp.dmp log=tba_imp.log transport_tablespace=y datafiles='/tmp/....','/tmp/...'

b) Use Data Pump import:

1
2
CREATE OR REPLACE DIRECTORY dpump_dir AS '/tmp/subdir';
GRANT READ,WRITE ON DIRECTORY dpump_dir TO system;

Execute the import:

1
impdp system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_DATAFILES='/tmp/....','/tmp/...' REMAP_SCHEMA=(source:target) REMAP_SCHEMA=(source_sch2:target_schema_sch2)

If you want to change the owner of the transported database objects, use the REMAP_SCHEMA parameter.

  1. Put the tablespace into READ/WRITE mode.
1
2
SQL> ALTER TABLESPACE TBS1 READ WRITE;
SQL> ALTER TABLESPACE TBS2 READ WRITE;

2.3 Using DBMS_FILE_TRANSFER

We can also use DBMS_FILE_TRANSFER to copy data files to another host. Starting from 12c and 11.2.0.4, DBMS_FILE_TRANSFER performs automatic conversion by default. When using DBMS_FILE_TRANSFER, if the target database receives a file from a platform with a different byte order, it will convert each block during the transport operation. After the data files are moved to the target database, they can be imported without needing RMAN conversion.

In versions lower than 11.2.0.4, you need to perform the steps mentioned above for ASM files. However, if the byte order is different, you must use RMAN to convert the files after they have been transferred. Files cannot be copied directly between ASM instances on different platforms.

For Filesystem Examples: 

1
2
3
4
5
6
7
8
RMAN> CONVERT DATAFILE
      '/hq/finance/work/tru/tbs_31.f',
      '/hq/finance/work/tru/tbs_32.f',
      '/hq/finance/work/tru/tbs_41.f'
      TO PLATFORM="Solaris[tm] OE (32-bit)"
      FROM PLATFORM="HP TRu64 UNIX"
      DB_FILE_NAME_CONVERT= "/hq/finance/work/tru/", "/hq/finance/dbs/tru"
      PARALLELISM=5;

For ASM Disk Group Examples:

1
2
3
4
5
6
7
8
RMAN> CONVERT DATAFILE
      '/hq/finance/work/tru/tbs_31.f',
      '/hq/finance/work/tru/tbs_32.f',
      '/hq/finance/work/tru/tbs_41.f'
      TO PLATFORM="Solaris[tm] OE (32-bit)"
      FROM PLATFORM="HP TRu64 UNIX"
      DB_FILE_NAME_CONVERT="/hq/finance/work/tru/", "+diskgroup"
      PARALLELISM=5;

Note: When using transportable tablespaces (TTS) to migrate from Solaris, Linux, or AIX to HP/UX, index-organized tables (IOTs) may get corrupted.

2.4 Transportable Tablespace Limitations

  1. The source and target databases must use the same character set and national character set.
  2. If the target database already has a tablespace with the same name, the transport operation cannot proceed. However, you can rename the tablespace to be transported or the existing tablespace in the target database before performing the transport.
  3. Objects with dependent objects (e.g., materialized views) or contained objects (e.g., partitioned tables) cannot be transported, unless all dependent or contained objects are part of the transported tablespace set.
  4. If the tablespace object owner does not exist in the target database, the username must be manually created before importing the transportable tablespace metadata.
  5. Starting from Oracle Database 11gR1, if the tablespace contains XMLType, Data Pump must be used to export and import the tablespace metadata. The following query returns a list of tablespaces containing XMLType:
1
2
3
4
5
select distinct p.tablespace_name
from dba_tablespaces p, dba_xml_tables x, dba_users u, all_all_tables t
where t.table_name=x.table_name and
      t.tablespace_name=p.tablespace_name and
      x.owner=u.username;
  1. Advanced Queues transportable tablespaces do not support 8.0-compatible version Advanced Queues with multiple containers.
  2. The SYSTEM tablespace or objects owned by SYS cannot be transported.
  3. Opaque types (such as RAW, BFILE, and AnyTypes) can be transported, but they will not be converted during cross-platform transport operations. Their actual format is known only to the application, so the application must handle the byte-order issue after moving these types to the new platform.
  4. Floating-point types (BINARY_FLOAT and BINARY_DOUBLE) can be transported, but they must use Data Pump tools and cannot use the older export tool (EXP).

2.5 ASM Files Transportable Tablespace Export/Import

2.5.1 Using RMAN CONVERT

There is no direct method to export/import ASM files as transportable tablespaces, but this can be achieved through RMAN. Follow the steps below:

a) Prepare the tablespace for export:

Check that the tablespace is self-contained.

1
2
SQL>execute sys.dbms_tts.transport_set_check('TBS1,TBS2', true);
SQL> select * from sys.transport_set_violations;

Note: Any violation of restrictions must be addressed before transporting the tablespace.

b) To successfully perform a transportable tablespace export, the tablespaces must be in READ ONLY mode.

1
2
SQL> ALTER TABLESPACE TBS1 READ ONLY;
SQL> ALTER TABLESPACE TBS2 READ ONLY;
  1. Export Metadata:
    a) Use traditional export tools:
1
exp userid=\'sys/sys as sysdba\' file=tbs_exp.dmp log=tba_exp.log transport_tablespace=y tablespaces=TBS1,TBS2

b) Use Data Pump export:

1
2
CREATE OR REPLACE DIRECTORY dpump_dir AS '/tmp/subdir';
GRANT READ,WRITE ON DIRECTORY dpump_dir TO system;

Execute the export:

1
expdp system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = TBS1,TBS2

If you want to perform strict inclusion checks during the transportable tablespace operation, use the TRANSPORT_FULL_CHECK parameter:

1
expdp system/password DUMPFILE=expdat.dmp DIRECTORY = dpump_dir TRANSPORT_TABLESPACES= TBS1,TBS2 TRANSPORT_FULL_CHECK=Y

If the transported tablespace is not self-contained, the export will fail.

  1. Use V$TRANSPORTABLE_PLATFORM to identify the target database’s platform name. Execute the following query on the target platform instance.
1
2
3
SELECT tp.platform_id,substr(d.PLATFORM_NAME,2,30), ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
  1. Generate an OS file from ASM files in the target platform format.
1
2
3
4
RMAN> CONVERT TABLESPACE TBS1
      TO PLATFORM 'HP-UX (64-bit)' FORMAT '/tmp/%U';
RMAN> CONVERT TABLESPACE TBS2
      TO PLATFORM 'HP-UX (64-bit)' FORMAT '/tmp/%U';
  1. Copy the generated file to the target server (if not the same machine as the source).
  2. Import Transportable Tablespace
    a) Use traditional import tools:
1
imp userid=\'sys/sys as sysdba\' file=tbs_exp.dmp log=tba_imp.log transport_tablespace=y datafiles='/tmp/....','/tmp/...'

b) Use Data Pump import:

1
2
CREATE OR REPLACE DIRECTORY dpump_dir AS '/tmp/subdir';
GRANT READ,WRITE ON DIRECTORY dpump_dir TO system;

Execute the import:

1
impdp system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_DATAFILES='/tmp/....','/tmp/...' REMAP_SCHEMA=(source:target) REMAP_SCHEMA=(source_sch2:target_schema_sch2)

If you wish to change the owner of the transported database objects, use the REMAP_SCHEMA parameter.

  1. Put the tablespace in READ/WRITE mode.
1
2
SQL> ALTER TABLESPACE TBS1 READ WRITE;
SQL> ALTER TABLESPACE TBS2 READ WRITE;

If you are transferring data files from ASM to the filesystem, the process ends here. However, if you want to transfer a tablespace between two ASM environments, follow the additional steps below.

  1. Use RMAN to copy the file to the ASM environment (e.g., /tmp/….dbf).
1
2
rman nocatalog target /
RMAN> backup as copy datafile '/tmp/....dbf' format '+DGROUPA';

The +DGROUPA represents the ASM disk group name.

  1. 将数据文件交换到这个拷贝。
    如果是 10g 数据库，首先要将数据文件离线：
1
SQL> alter database datafile '/tmp/....dbf' offline;

Switch the data files to this copy.

1
2
rman nocatalog target /
RMAN> switch datafile '/tmp/....dbf' to copy;

Note the name of the copy created in the +DGROUPA disk group, such as +DGROUPA/s101/datafile/tts.270.5.

  1. Bring the file back online and recover it.
1
2
SQL> recover datafile '+DGROUPA/s101/datafile/tts.270.5';
SQL> alter database datafile '+DGROUPA/s101/datafile/tts.270.5' online;
  1. Verify that the data file is part of the ASM environment and online. The output should indicate the file is properly online.
1
2
3
SQL> select name, status from v$datafile;

+DGROUPA/s101/datafile/tts.270.5 ONLINE

2.5.2 Using DBMS_FILE_TRANSFER
We can also use DBMS_FILE_TRANSFER to copy data files from one ASM disk group to another, or even to another host. Starting with 10gR2, DBMS_FILE_TRANSFER can also be used to copy data files from ASM to a filesystem and vice versa.

The PUT_FILE procedure reads a local file or ASM file and connects to a remote database to create a copy of the file in the remote filesystem. The copied file is the target file, and the original file is the source. The target file is not closed until the procedure completes successfully.

Syntax:

1
2
3
4
5
6
7
DBMS_FILE_TRANSFER.PUT_FILE(
   source_directory_object       IN  VARCHAR2,
   source_file_name              IN  VARCHAR2,
   destination_directory_object  IN  VARCHAR2,
   destination_file_name         IN  VARCHAR2,
   destination_database          IN  VARCHAR2);

Where:

  • source_directory_object: The directory object where the file is located on the local source. It must exist on the source side.
  • source_file_name: The file name to copy from the local filesystem. This file must exist in the directory specified by source_directory_object.
  • destination_directory_object: The directory object on the target where the file will be placed. It must exist in the target filesystem.
  • destination_file_name: The name of the file in the remote filesystem. No file with the same name should exist in the target directory.
  • destination_database: The database link name pointing to the target database where the file will be copied.

If we want to use DBMS_FILE_TRANSFER.PUT_FILE to transfer a file from the source to the target host, modify steps 3, 4, and 5 as follows:

  1. Create a directory on the target database host and grant permissions to the local user. This directory object must exist on the remote filesystem.
1
2
CREATE OR REPLACE DIRECTORY target_dir AS '+DGROUPA';
GRANT WRITE ON DIRECTORY target_dir TO "USER";
  1. Create a directory on the source database host for the file to be copied from. This directory must exist on the source side.
1
2
3
CREATE OR REPLACE DIRECTORY source_dir AS '+DGROUPS/subdir';
GRANT READ,WRITE ON DIRECTORY source_dir TO "USER";
CREATE OR REPLACE DIRECTORY source_dir_1 AS '+DGROUPS/subdir/subdir_2';
  1. Create a database link to the target database host:
1
2
CREATE DATABASE LINK DBS2 CONNECT TO 'user' IDENTIFIED BY 'password' USING 'target_connect';
Here, target_connect is the connection string for the target database, and USER is the user transferring the data files.
  1. Connect to the source instance. The following items will be used:
  • dbs1: The connection string to the source database
  • dbs2: The database link to the target database
  • a1.dat: The source database file name
  • a4.dat: The target database file name
1
2
3
4
5
6
7
8
9
CONNECT user/password@dbs1
-- - put a1.dat to a4.dat (using dbs2 dblink)
-- - level 2 sub dir to parent dir
-- - user has read privs on source_dir_1 at dbs1 and write on target_dir
-- - in dbs2
BEGIN
    DBMS_FILE_TRANSFER.PUT_FILE('source_dir_1', 'a1.dat',
                                'target_dir', 'a4.dat', 'dbs2' );
END;