25 Transporting Data Across Platforms

Purpose of Cross-Platform Data Transportation

You can transport tablespaces in a database that runs on one platform into a database that runs on a different platform. Typical uses of cross-platform transportable tablespaces include the following:

• 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 in which all hosts share the same endian format

A full discussion of transportable tablespaces, their uses, and the different techniques for creating and using them is found in Oracle Database Administrator's Guide.

You can also use RMAN to transport an entire database from one platform to another. For example, business requirements demand that you run your databases on less expensive servers that use a different platform. In this case, you can use RMAN to transport the entire databases rather than re-create the databases from scratch and use import utilities or transportable tablespaces to repopulate the data.

You can convert a database on the destination host or source host. Reasons for converting on the destination host include:

• Avoiding performance overhead on the source host due to the conversion process

• Distributing a database from one source system to multiple recipients on several different platforms

• Evaluating a migration path for a new platform

Basic Concepts of Cross-Platform Data Transportation

You must use the RMAN CONVERT command in a transportable tablespace operation when the source platform is different from the destination platform and the endian formats are different. When transporting between platforms for which the endian format is the same, you do not need to use CONVERT. You can use operating system utilities to copy the files from the source to the destination.

About Cross-Platform Datafile Conversion on the Destination Host

Datafile conversion necessitates that you choose a technique for naming the output files. You must use the FORMAT or DB_FILE_NAME_CONVERT arguments to CONVERT to control the naming of output files. The rules are listed in order of precedence:

1. Files that match any patterns provided in CONVERT ... DB_FILE_NAME_CONVERT clause are named based upon this pattern.

2. If you specify a FORMAT clause, then any file not named based on patterns provided in CONVERT ... DB_FILE_NAME_CONVERT clause is named based on the FORMAT pattern.

If the source and destination platforms differ, then you must specify the FROM PLATFORM parameter. View platform names by querying V$TRANSPORTABLE_PLATFORM. The FROM PLATFORM value must match the format of the datafiles to be converted to avoid an error. If you do not specify FROM PLATFORM, then this parameter defaults to the value of the destination platform.

Using CONVERT DATAFILE to Convert Datafile Formats

This section explains how to use CONVERT DATAFILE. The section assumes that you intend 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 named prod_source. The database runs on a Sun Solaris host. You plan to transport these tablespaces into a destination database named prod_dest, which runs on a Linux PC. 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 will be stored in the /orahome/dbs/hr subdirectory, and datafiles for the finance tablespace will be stored in the /orahome/dbs/fin directory.

To perform cross-platform datafile conversion on the destination host:

1. Start SQL*Plus and connect to the source database.

   For example, enter the following command:

   % sqlplus
   SQL> CONNECT SYS/password@prod_source AS SYSDBA
   

2. Query the name for the source 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 destination platform as a parameter to the CONVERT command. For example, you can obtain the platform name of the connected database as follows:

   SELECT PLATFORM_NAME
   FROM   V$TRANSPORTABLE_PLATFORM 
   WHERE  PLATFORM_ID =
          ( SELECT PLATFORM_ID
            FROM   V$DATABASE );
   

   For this scenario, assume the PLATFORM_NAME for the source host is Solaris[tm] OE (64-bit).

3. Identify the tablespaces to be transported from the source database and place them in read-only mode.

   For example, enter the following SQL statements to place finance and hr in read-only mode:

   ALTER TABLESPACE finance READ ONLY;
   ALTER TABLESPACE hr READ ONLY;
   

4. On the source host, use Data Pump Export to create the export dump file

   In this example, the dump file is named expdat.dmp.

5. Make the export dump file and the datafiles to be transported available to the destination host.

   You can use NFS to make the dump file and current database files (not copies) accessible. Alternatively, you can use an operating system utility to copy these files to the destination host.

   In this example, you store the files in the in the /tmp/transport_solaris/ directory of the destination host. You preserve the subdirectory structure from the original location of the files, 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

6. Start RMAN and connect to the destination database (not the source database). For example, enter the following command:

   % rman
   RMAN> CONNECT TARGET SYS/password@prod_dest
   

7. Execute the CONVERT DATAFILE command to convert the datafiles into the endian format of the destination host.

   In this example, you use DB_FILE_NAME_CONVERT to control the name and location of the converted datafiles. You also specify the FROM PLATFORM clause.

   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'
     FROM PLATFORM 'Solaris[tm] OE (64-bit)';
   

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

   • /orahome/dbs/fin/fin01.dbf

   • /orahome/dbs/fin/fin02.dbf

   • /orahome/dbs/hr/hr01.dbf

   • /orahome/dbs/hr/hr02.dbf

8. Follow the rest of the general outline for transporting tablespaces:

   1. Plug the tablespace into the new database with the Import utility.

   2. If applicable, place the transported tablespaces into read-only mode.

Performing Preliminary Datafile Conversion Steps on the Source Host

In this procedure, you execute the CONVERT DATABASE command on the source host. This command generates an initialization parameter file and scripts that you can edit for use on the destination host. You also copy the unconverted datafiles from the source host to the destination host.

To perform preliminary datafile conversion steps on the source host:

1. Ensure that the database is open in read-only mode.

2. Start an RMAN session on the source database.

   For example, enter the following commands:

   % rman
   RMAN> CONNECT TARGET SYS/password@source_db
   

3. Execute the CONVERT DATABASE ON DESTINATION command.

   Example 25-4 shows a sample CONVERT DATABASE command (sample output included). The ON DESTINATION PLATFORM parameter specifies that any CONVERT commands required for datafiles should be performed on the destination platform rather than the source database. The FORMAT parameter specifies the naming scheme for the generated files.

   Example 25-4 Executing CONVERT DATABASE ON DESTINATION PLATFORM

   CONVERT DATABASE 
     ON DESTINATION PLATFORM
     CONVERT SCRIPT '/tmp/convertdb/convertscript-target'
     TRANSPORT SCRIPT '/tmp/convertdb/transportscript-target'
     NEW DATABASE 'newdbt'
     FORMAT '/tmp/convertdb/%U';
    
   Starting convert at 28-JAN-05
   using target database control file instead of recovery catalog
   allocated channel: ORA_DISK_1
   channel ORA_DISK_1: sid=39 devtype=DISK
    
   External table SH.SALES_TRANSACTIONS_EXT found in the database
    
   Directory SYS.DATA_PUMP_DIR found in the database
   Directory SYS.MEDIA_DIR found in the database
   Directory SYS.DATA_FILE_DIR found in the database
   Directory SYS.LOG_FILE_DIR found in the database
    
   BFILE PM.PRINT_MEDIA found in the database
    
   User SYS with SYSDBA and SYSOPER privilege found in password file
   User OPER with SYSDBA privilege found in password file
   channel ORA_DISK_1: starting to check datafiles
   input datafile fno=00001 name=/disk1/oracle/dbs/tbs_01.f
   channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
   channel ORA_DISK_1: starting to check datafiles
   input datafile fno=00002 name=/disk1/oracle/dbs/tbs_ax1.f
   channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
   channel ORA_DISK_1: starting to check datafiles
   input datafile fno=00017 name=/disk1/oracle/dbs/tbs_03.f
   channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
   .
   .
   .
   channel ORA_DISK_1: starting to check datafiles
   input datafile fno=00015 name=/disk1/oracle/dbs/tbs_51.f
   channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
   channel ORA_DISK_1: starting to check datafiles
   input datafile fno=00016 name=/disk1/oracle/dbs/tbs_52.f
   channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
   Run SQL script /tmp/convertdb/transportscript-target on the destination platform to create database
   Edit init.ora file /tmp/convertdb/init_00gb9u2s_1_0.ora. This PFILE will be used to create the database on the destination platform
   Run RMAN script /tmp/convertdb/convertscript-target on destination platform to convert datafiles
   To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the destination platform
   To change the internal database identifier, use DBNEWID Utility
   Finished backup at 28-JAN-05
   

   The command in Example 25-4 creates a transport script, an initialization parameter file for the new database, and a convert script containing RMAN CONVERT DATAFILE commands for each datafile being converted.

   Note:

   CONVERT DATABASE ON DESTINATION PLATFORM does not produce converted datafile copies. The command only creates scripts.

4. Use an operating system utility to copy the following files to a temporary location on the destination host:

   • The datafiles to be converted

   • The convert script

   • The transport script

   • The initialization file for the destination database

5. Make the source database read/write.

Converting Datafiles on the Destination Host

This section explains how to use the script created in the previous section to convert the datafiles on the destination host.

The convert script created in the previous phase uses the original datafile names of the source database files. The FORMAT parameter specifies the name that was generated with the FORMAT or DB_FILE_NAME_CONVERT parameter of CONVERT DATABASE.

If the datafiles of the source database are accessible from the destination host with the same path names, then so long as the source database is read-only you can run the convert script on the destination host without any changes. For example, if the source and destination hosts both use NFS to mount a disk containing the source datafiles, and if the mount point for both hosts is /fs1/dbs/, then no editing is needed.

To convert the datafiles on the destination host:

1. If necessary, edit the convert script.

   In the script, one CONVERT DATAFILE command exists for each datafile to be converted. The convert script should indicate the current temporary filenames of the unconverted datafiles and the output filenames of the converted datafiles. A typical convert script looks as follows:

   RUN 
   { 
     CONVERT DATAFILE '/disk1/oracle/dbs/tbs_01.f'
     FROM PLATFORM 'Linux IA (32-bit)'
     FORMAT '/tmp/convertdb/data_D-TV_I-1778429277_TS-SYSTEM_FNO-1_7qgb9u2s';
    
     CONVERT DATAFILE '/disk1/oracle/dbs/tbs_ax1.f'
     FROM PLATFORM 'Linux IA (32-bit)'
     FORMAT '/tmp/convertdb/data_D-TV_I-1778429277_TS-SYSAUX_FNO-2_7rgb9u2s';
    
     CONVERT DATAFILE '/disk1/oracle/dbs/tbs_03.f'
     FROM PLATFORM 'Linux IA (32-bit)'
     FORMAT '/tmp/convertdb/data_D-TV_I-1778429277_TS-SYSTEM_FNO-17_7sgb9u2s';
     .
     .
     .
     CONVERT DATAFILE '/disk1/oracle/dbs/tbs_51.f'
     FROM PLATFORM 'Linux IA (32-bit)'
     FORMAT '/tmp/convertdb/data_D-TV_I-1778429277_TS-TBS_5_FNO-15_8egb9u2u';
    
     CONVERT DATAFILE '/disk1/oracle/dbs/tbs_52.f'
     FROM PLATFORM 'Linux IA (32-bit)'
     FORMAT '/tmp/convertdb/data_D-TV_I-1778429277_TS-TBS_5_FNO-16_8fgb9u2u';
   }
   

   Edit each CONVERT DATAFILE command in the convert script to specify the temporary location of each datafile as input. Also, edit the FORMAT parameter of each CONVERT command to specify the desired final location of the datafiles of the transported database.

2. If necessary, edit the initialization parameter file on the destination host to change settings for the destination database.

   You should edit several entries at the top of the initialization parameter file before moving the database to the destination platform. For example, the initialization parameter file may look as follows:

   # Please change the values of the following parameters:
   control_files            = "/tmp/convertdb/cf_D-NEWDBT_id-1778429277_00gb9u2s"
   db_recovery_file_dest    = "/tmp/convertdb/orcva"
   db_recovery_file_dest_size= 10737418240
   instance_name            = "NEWDBT"
   service_names            = "NEWDBT.regress.rdbms.dev.us.oracle.com"
   plsql_native_library_dir = "/tmp/convertdb/plsqlnld1"
   db_name                  = "NEWDBT"
   

3. On the destination host, use SQL*Plus to start the database instance in NOMOUNT mode.

   Specify the initialization parameter file that you copied in the preceding step. For example, enter the following command:

   SQL> STARTUP NOMOUNT PFILE='/tmp/init_convertdb_00i2gj63_1_0.ora'
   

4. Run the convert script.

5. Shut down the database instance.

   This step is necessary because the transport script that need to execute already includes a STARTUP NOMOUNT command.

6. If necessary, edit the transport script to use the new names for the converted datafiles.

   In Example 25-3, the transport script is /tmp/convertdb/transportscript.sql. You run this script on the destination host to create the database. Thus, you must edit this script with the correct names for the datafiles.

7. Execute the transport script in SQL*Plus.

   For example, create the new database on the destination host as follows:

   SQL> @/tmp/convertdb/transportscript
   

When the transport script completes, the destination database is created.