26 Transporting Data Across Platforms

Overview of Cross-Platform Data Transportation

This section explains the basic concepts and tasks involved in transporting tablespaces and databases across platforms.

Performing Cross-Platform Tablespace Conversion on the Source Host

Refer to the list of CONVERT prerequisites described in Oracle Database Backup and Recovery Reference. Meet all these prerequisites before doing the steps in this section.

For purposes of illustration, assume that you need to transport tablespaces finance and hr from source database prod_source, which runs on a Sun Solaris host. You plan to transport them to destination database prod_dest running on a Linux PC. You plan to store the converted datafiles in the temporary directory /tmp/transport_linux/ on the source host.

To perform cross-platform tablespace conversion on the source host:

1. Start SQL*Plus and connect to the source database prod_source with administrator privileges.

2. Query the name for the destination 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. Query V$TRANSPORTABLE_PLATFORM to get the platform names. The following example queries Linux platform names:

   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).

3. Place the tablespaces to be transported in read-only mode. For example, enter:

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

4. Choose a method for naming the output files.

   You must use the FORMAT or DB_FILE_NAME_CONVERT arguments to CONVERT to control the names of the 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.

   Note:

   You cannot use CONVERT ... DB_FILE_NAME_CONVERT to generate output filenames for CONVERT when the source and destination files have Oracle Managed File names.

5. Start RMAN and connect to the source database (not the destination database) as TARGET. For example, enter:

   % rman
   RMAN> CONNECT TARGET SYS@prod_source
   

6. Run the CONVERT TABLESPACE command to convert the datafiles into the endian format of the destination host.

   In the following example, the FORMAT argument controls the name and location of the converted datafiles:

   RMAN> CONVERT TABLESPACE finance,hr
   2>   TO PLATFORM 'Linux IA (32-bit)'
   3>   FORMAT '/tmp/transport_linux/%U';
   

   The result is a set of converted datafiles in the /tmp/transport_linux/ directory, with data in the correct endian format for the Linux IA (32-bit) platform.

   See Also:

   Oracle Database Backup and Recovery Reference for the full semantics of the CONVERT command

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

   1. Use the Oracle Data Pump Export utility to create the export dump file on the source host.

   2. Move the converted datafiles and the export dump file from the source host to the desired directories on the destination host.

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

   4. If applicable, place the transported tablespaces into read/write mode.

Performing Cross-Platform Datafile Conversion on the Destination Host

Refer to the list of CONVERT prerequisites described in Oracle Database Backup and Recovery Reference. Meet these prerequisites before doing the steps in this section.

Checking the Database Before Cross-Platform Database Conversion

As explained in "Basic Concepts of Cross-Platform Data Transportation", you can use the RMAN CONVERT DATABASE command to automate the copying of an entire database from one platform to another.

Before converting the database, refer to the list of CONVERT DATABASE prerequisites described in Oracle Database Backup and Recovery Reference. Make sure to meet all these prerequisites before attempting the procedure in this section.

One prerequisite is that both the source and destination platform must share the same endian format. For example, you can transport a database from Microsoft Windows to Linux for x86 (both little-endian), or from HP-UX to AIX (both big-endian), but not from HP-UX to Linux for x86 (big-endian to little-endian).

When transporting entire databases, certain files require RMAN conversion in order to ensure compatibility with the destination platform. Even though the endian formats for the source and destination platform are the same, these files cannot be simply copied from the source to the destination system. The following kinds of files require RMAN conversion:

• Any file containing undo segments

• Any file containing Automatic Shared Memory Management (ASMM) segment headers when transporting to or from the HP Tru64 platform

The CONVERT DATABASE command, by default, processes all datafiles in the database using RMAN conversion. The RMAN conversion copies the files from one location to another, even when it does not make any changes to the file. If you have other preferred means to copy those files that do not require RMAN conversion, you can use the SKIP UNNECESSARY DATAFILES option of the CONVERT DATABASE command. If you select this option, then the CONVERT DATABASE command only processes the kinds of files mentioned above. All other files must be copied by the user from the source to the destination database.

The files should be copied while the source database is open in read-only mode. Similarly, if the conversion is performed at the destination, all datafiles should be copied to the destination when the source database is open in read-only mode.

To prepare for database conversion:

1. Start a SQL*Plus session as SYSDBA on the source database.

2. Open the database in read-only mode.

   ALTER DATABASE OPEN READ ONLY;
   

3. Make sure that server output is on in SQL*Plus.

   For example, enter the following SQL*Plus command:

   SET SERVEROUTPUT ON
   

4. Execute the DBMS_TDB.CHECK_DB function.

   This check ensures that no conditions would prevent the transport of the database, such as incorrect compatibility settings, in-doubt or active transactions, or incompatible endian formats between the source platform and destination platform.

   You can call CHECK_DB without arguments to see if a condition at the source database prevents transport. You can also call this function with the arguments shown in Table 26-1.

   Table 26-1 CHECK_DB Procedure Parameters

   Parameter	Description
   target_platform_name	The name of the destination platform as it appears in V$DB_TRANSPORTABLE_PLATFORM. This parameter is optional, but is required when the skip_option parameter is used. If omitted, it is assumed that the destination platform is compatible with the source platform, and only the conditions not related to platform compatibility are tested.
   skip_option	Specifies which, if any, parts of the database to skip when checking whether the database can be transported. Supported values (of type NUMBER) are: SKIP_NONE (or 0), which checks all tablespaces SKIP_OFFLINE (or 2), which skips checking datafiles in offline tablespaces SKIP_READONLY (or 3), which skips checking datafiles in read-only tablespaces

   
   

   Example 26-1 illustrates executing CHECK_DB on a 32-bit Linux platform for transporting a database to 32-bit Windows, skipping read-only tablespaces.

   Example 26-1 Executing DBMS_TDB.CHECK_DB

   DECLARE
     db_ready BOOLEAN;
   BEGIN
     db_ready := 
          DBMS_TDB.CHECK_DB('Microsoft Windows IA (32-bit)',DBMS_TDB.SKIP_READONLY);
   END;
   /
   
   PL/SQL procedure successfully completed.
   

   If no warnings appear, or if DBMS_TDB.CHECK_DB returns TRUE, then you can currently transport the database. Proceed to step 6.

   If warnings appears, or if DBMS_TDB.CHECK_DB returns FALSE, then you cannot currently transport the database. Proceed to the next step.

5. Examine the output to learn why the database cannot be transported, fix the problem if possible, and then return to the preceding step. Refer to the DBMS_TDB documentation for the conditions present.

6. Execute DBMS_TDB.CHECK_EXTERNAL to identify any external tables, directories, or BFILEs. RMAN cannot automate the transport of these files, so you must copy the files manually and re-create database directories.

   Example 26-2 shows how to call DBMS_TDB.CHECK_EXTERNAL.

   Example 26-2 Executing DBMS_TDB.CHECK_EXTERNAL

   DECLARE
        external BOOLEAN;
   BEGIN
       /* value of external is ignored, but with SERVEROUTPUT set to ON
        * dbms_tdb.check_external displays report of external objects
        * on console */
       external := DBMS_TDB.CHECK_EXTERNAL;
   END;
   /
   

   If no external objects exist, then the procedure completes with no output. If external objects exist, however, then the output is similar to the following:

   The following external tables exist in the database:
   SH.SALES_TRANSACTIONS_EXT
   The following directories exist in the database:
   SYS.DATA_PUMP_DIR, SYS.MEDIA_DIR, SYS.DATA_FILE_DIR, SYS.LOG_FILE_DIR
   The following BFILEs exist in the database:
   PM.PRINT_MEDIA
    
   PL/SQL procedure successfully completed.
   

Converting Datafiles on the Source Host When Transporting a Database

When transporting entire databases, certain types of blocks, such as blocks in undo segments, ar required to be reformatted in order to ensure compatibility with the destination platform. Even though the endian formats for the source and destination platform are the same, the datafiles for a transportable database must undergo a conversion process. You cannot simply copy datafiles from one platform to another as you can when transporting tablespaces.

Datafiles with undo information and those from the HP Tru64 platform must be converted. By default, all datafiles are converted when CONVERT DATABASE is executed. If, however, SKIP UNNECESARY DATAFILES is used in the CONVERT DATABASE statement, then the datafiles with undo segments and those from the HP Tru64 platform are converted. The datafiles without undo segments can be copied to the new database using FTP, operating system copy or some other mechanism.

This section assumes that you have already met all of the CONVERT DATABASE prerequisites and followed the steps in "Checking the Database Before Cross-Platform Database Conversion". The goal of this procedure is to convert the format of datafiles on the source host as part of a cross-platform database transport.

Assume that you want to convert a database running on Solaris to a database that runs on Windows.

To convert the database on the source platform:

1. Open the source database in read-only mode.

   ALTER DATABASE OPEN READ ONLY;
   

2. Start RMAN and connect to the source database as TARGET. For example, enter the following commands:

   % rman
   RMAN> CONNECT TARGET SYS@source_db
   

3. Run the CONVERT DATABASE command.

   Example 26-3 shows a CONVERT DATABASE command (sample output included). The TRANSPORT SCRIPT parameter specifies the location of the generated SQL script that you can use to create the new database. The TO PLATFORM parameter indicates the platform of the destination database. The DB_FILE_NAME_CONVERT parameter specifies the naming scheme for the generated datafiles.

   Example 26-3 Converting a Database on the Source Host

   RMAN> CONVERT DATABASE
   2>   NEW DATABASE 'newdb'
   3>   TRANSPORT SCRIPT '/tmp/convertdb/transportscript.sql'
   4>   TO PLATFORM 'Microsoft Windows IA (32-bit)'
   5>   DB_FILE_NAME_CONVERT '/disk1/oracle/dbs' '/tmp/convertdb';
    
   Starting convert at 25-NOV-06
   using channel ORA_DISK_1
    
   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 datafile conversion
   input datafile fno=00001 name=/disk1/oracle/dbs/tbs_01.f
   converted datafile=/tmp/convertdb/tbs_01.f
   channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
   channel ORA_DISK_1: starting datafile conversion
   input datafile fno=00002 name=/disk1/oracle/dbs/tbs_ax1.f
   converted datafile=/tmp/convertdb/tbs_ax1.f
   channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
   .
   .
   .
   channel ORA_DISK_1: starting datafile conversion
   input datafile fno=00016 name=/disk1/oracle/dbs/tbs_52.f
   converted datafile=/tmp/convertdb/tbs_52.f
   channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
   Run SQL script /tmp/convertdb/transportscript.sql on the destination platform 
     to create database
   Edit init.ora file init_00gb3vfv_1_0.ora. This PFILE will be used to 
     create the database on the destination platform
   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 25-NOV-06
   

4. After CONVERT DATABASE completes, you can open the source database read/write again.

5. Move the datafiles generated by CONVERT DATABASE to the desired locations on the destination host.

   In Example 26-3, the command creates the files in the /tmp/convertdb/ directory on the source host. Move these files to the directory on the destination host that will contain the destination database files.

6. If the path to the datafiles is different on the destination host, then edit the transport script to refer to the new datafile locations.

7. If necessary, edit the initialization parameter file to change any settings for the destination database.

   You should edit several entries at the top of the initialization parameter file when the database is moved 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.example.com"
    plsql_native_library_dir = "/tmp/convertdb/plsqlnld1"
    db_name                  = "NEWDBT"
   

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

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

9. On the destination host, start SQL*Plus and connect to the destination database instance as SYSDBA using operating system authentication.

   For example, connect as follows:

   SQL> CONNECT / AS SYSDBA
   

   If you choose not to use operating system authentication, then you must first configure Oracle Net files, create a password file, and start the listener. You can then connect to the instance with a net service name.

10. Execute the transport script in SQL*Plus to create the new database on the destination host.

    SQL> @transportscript
    

When the transport script finishes, the creation of the new database is complete.

Converting Datafiles on the Destination Host When Transporting the Database

This section assumes that you have already met all of the CONVERT DATABASE prerequisites and followed the steps in "Checking the Database Before Cross-Platform Database Conversion". The goal of this procedure is to convert the format of datafiles on the destination host as part of a cross-platform database transport.

Performing the datafile conversion in the following phases:

1. Performing Preliminary Datafile Conversion Steps on the Source Host

2. Converting Datafiles on the Destination Host