28 Transporting Data Across Platforms

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

• Migrating tablespaces across platforms with minimal application downtime

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 database rather than re-create it 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

RMAN enables you to transport data files, tablespaces, or an entire database from one platform to another. When you transport an entire database to a different platform, the destination platform must have the same endian format as the source platform.

Use one of the following methods to transport data across platforms:

• Transport data using image copies

• Transport data using backup sets

  See Also:

  • "Overview of Cross-Platform Data Transport Using Image Copies"

  • "Overview of Cross-Platform Data Transport Using Backup Sets "

The Oracle Database maintains a list of internal names for each platform that supports cross-platform data transport. These names are stored in the V$TRANSPORTABLE_PLATFORM view. Use this view to determine the internal name of the source platform or destination platform. While transporting data across platforms, you may need to specify the exact name of the source or destination platform. Any platform names specified as a parameter of the CONVERT or BACKUP command must be entered exactly as shown in the V$TRANSPORTABLE_PLATFORM view.

Use the following query to obtain the platform name of the connected database:

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

Use the following query to obtain the name of the Linux platform:

SELECT PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM
WHERE UPPER(PLATFORM_NAME) LIKE '%LINUX%';

You can perform tablespace conversion with the RMAN CONVERT TABLESPACE command on the source host, but not on the destination host. The CONVERT TABLESPACE command does not perform in-place conversion of data files. Rather, the command produces output files in the correct format for use on the destination platform. The command does not alter the contents of data files in the source database.

You can use the CONVERT DATAFILE command to convert files. Typically, the CONVERT DATAFILE command is used on the destination host and the CONVERT TABLESPACE command is used on the source host. When you use the CONVERT DATAFILE command on the source host, ensure that data files are cleanly offline or the tablespaces containing those data files are read-only. The Data Pump Export utility generates an export dump file that, with data files manually copied to the destination host, can be imported into the destination database. Until the Data Pump export dump file is imported into the destination database, the data files are not associated with a tablespace name in the database. In this case, RMAN cannot translate the tablespace name into a list of data files. Therefore, you must use CONVERT DATAFILE and identify the data files by file name.

To convert a whole database to a different platform, both platforms must use the same endian format. The RMAN CONVERT DATABASE command automates the movement of an entire database from a source platform to a destination platform. The transported database contains the same data as the source database and also has, with a few exceptions, the same settings as the source database.

Files automatically transported to the destination platform include:

• Data files that belong to permanent tablespaces

  Unlike transporting tablespaces across platforms, transporting entire databases requires that certain types of blocks, such as blocks in undo segments, be reformatted to ensure compatibility with the destination platform. Even though the endian formats for the source and destination platforms are the same, certain types of files must undergo a conversion process. See "Checking the Database Before Cross-Platform Database Conversion" for details about the types of files that need conversion.

• Initialization parameter file or server parameter file

  If the database uses a text-based initialization parameter file, then RMAN transports it. If the database uses a server parameter file, then RMAN generates an initialization parameter file based on the server parameter file, transports it and creates a new server parameter file at the destination based on the settings in the initialization parameter file.

  Usually, some parameters in the initialization parameter file require manual updating for the new database. For example, you may change the DB_NAME and parameters such as CONTROL_FILES that indicate the locations of files on the destination host.

You can convert the format of the data files either on the source platform or on the destination platform. The CONVERT DATABASE ON DESTINATION PLATFORM command does not convert the format of data files. Rather, it generates scripts that you can run manually to perform the conversion. The CONVERT SCRIPT parameter creates a convert script that you can manually execute at the destination host to convert data file copies in batch mode. The TRANSPORT SCRIPT parameter generates a transport script that contains SQL statements to create the new database on the destination platform.

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

1. Any file that matches any pattern provided in the DB_FILE_NAME_CONVERT clause is named based upon this pattern.

2. If you specify a FORMAT clause, then any file not named based on the pattern provided in the 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 the V$TRANSPORTABLE_PLATFORM. The FROM PLATFORM value must match the format of the data files 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.

1. On the source database, start a SQL*Plus session as a user with the SYSDBA or SYSBACKUP privilege.
2. Open the database in read-only mode.

   SHUTDOWN IMMEDIATE
   STARTUP MOUNT
   ALTER DATABASE OPEN READ ONLY;
   

3. Ensure 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 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 28-1.

   ---

   Table 28-1 CHECK_DB Function Parameters

   Parameter	Description
   target_platform_name	The name of the destination platform as it appears in the V$DB_TRANSPORTABLE_PLATFORM view. 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 data files in offline tablespaces SKIP_READONLY (or 3), which skips checking data files in read-only tablespaces

   ---

   The following example illustrates executing CHECK_DB on a 32-bit Linux platform for transporting a database to 32-bit Windows, skipping read-only tablespaces.

   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 transport the database. Proceed to Step 6.

   If warnings appear, or if DBMS_TDB.CHECK_DB returns FALSE, then you cannot currently transport the database. Proceed to Step 5.

5. Examine the output to learn why the database cannot be transported, fix the problem if possible, and then return to the Step 4.

   See Also:

   Oracle Database PL/SQL Packages and Types Reference for information about DBMS_TDB

6. Execute the DBMS_TDB.CHECK_EXTERNAL function 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 the database directories.

   The following example shows how to call the DBMS_TDB.CHECK_EXTERNAL function.

   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.
   

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

   SHUTDOWN IMMEDIATE;
   STARTUP MOUNT;
   ALTER DATABASE OPEN READ ONLY;
   

2. Start RMAN and connect to the source database as TARGET as described in "Making Database Connections with RMAN".
3. Run the CONVERT DATABASE command.

   The following example 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 data files.

   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 conversion at source at 25-NOV-13
   using channel ORA_DISK_1
    
   External table SH.SALES_TRANSACTIONS_EXT found in the database
   .
   .
   .
   Directory SYS.ORACLE_HOME found in the database
   Directory SYS.ORACLE_BASE 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 SBU with SYSBACKUP 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
   Edit init.ora file init_00gb3vfv_1_0.ora. This PFILE will be used to 
     create the database on the target platform
   Run SQL script /tmp/convertdb/transportscript.sql on the target platform 
     to create database
   To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on 
     the target platform
   To change the internal database identifier, use DBNEWID Utility
   Finished conversion at source at 25-NOV-13
   

4. After CONVERT DATABASE completes, you can open the source database read/write again.
5. Move the data files generated by CONVERT DATABASE to the desired locations on the destination host.

   In Step 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 data files is different on the destination host, then edit the transport script to refer to the new data file locations.
7. If necessary, edit the initialization parameter file to change any settings for the destination database.

   You must 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"
    service_names            = "NEWDBT.example.com"
    db_recovery_file_dest    = "/tmp/convertdb/orcva"
    db_recovery_file_dest_size= 10737418240
    instance_name            = "NEWDBT"
    db_name                  = "NEWDBT"
    plsql_native_library_dir = "/tmp/convertdb/plsqlnld1"
   

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

   In the example in Step 3, the transport script is named /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 data files.

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

   For example, connect as follows:

   SQL> CONNECT / AS SYSBACKUP
   

   If you choose not to use operating system authentication, you can create a password file and then connect with a user name and password.

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

    SQL> @transportscript
    

1. Performing Preliminary Data File Conversion Steps on the Source Host
2. Running the Conversion Scripts on the Destination Host

Before using backup sets to perform cross-platform data transport, it is useful to understand the following terms.

To create the backup set containing data that must be transported to the destination database, use the BACKUP command on the source database. To indicate that you are creating a cross-platform backup, the BACKUP command must contain either the FOR TRANSPORT or TO PLATFORM clause.

When you use FOR TRANSPORT, the backup set that is created can be transported to any destination database. If the destination database uses an endian format that is different from that of the source database, then the required endian format conversion is performed on the destination database. The benefit of this method is that the processing overhead of the conversion operation is offloaded to the destination database.

When you use TO PLATFORM, the endian format conversion is performed on the source database. The target platform specified by the TO PLATFORM clause must be a supported platform for cross-platform transport. The V$TRANSPORTABLE_PLATFORM view contains the list of supported platforms.

You can create cross-platform backups that contain multiple backup sets or multiple backup pieces. See the Oracle Database Backup and Recovery Reference for examples.

You cannot use certain clauses of the BACKUP command when you create a cross-platform backup using either the FOR TRANSPORT or TO PLATFORM clause. See Oracle Database Backup and Recovery Reference for information about the clauses that are not compatible with TO PLATFORM and FOR TRANSPORT.

When you create a cross-platform consistent tablespace backup, the backup set contains the data files that contain data related to the specified tablespaces. A consistent tablespace backup is a backup of one or more tablespaces that is created when the tablespaces are in read-only mode. After you restore this backup in your destination database, the tablespaces must be plugged in to the destination database. To do this, in addition to the backup set containing the tablespace data, you need the metadata for these tablespaces from the source database.

On the source database, use the DATAPUMP clause in the BACKUP command to create the metadata required to plug tablespaces in to the target database. The metadata is stored in a Data Pump export dump file as a separate backup set. Use this backup set to plug the transported tablespaces in to the target database.

You can specify how the backup set containing the tablespace metadata is named by using the FORMAT option with the DATAPUMP clause. If you omit the FORMAT option, then the format specified in the BACKUP command is used to name the export dump file. When no FORMAT option is specified in the BACKUP command, the default format is used.

On the destination database, you use the RESTORE command to restore the database, tablespaces, or data files contained in a cross-platform backup consisting of backup sets. When you perform a cross-platform restore operation, you must use the foreignFileSpec subclause of the RESTORE command. See Oracle Database Backup and Recovery Reference for more information.

When you restore cross-platform backups, you must specify the following information:

• Backup sets that contain data that was backed up on the source database

  Use the BACKUPSET option of the foreignFileSpec subclause to specify the name of the cross-platform backup set from which data must be restored. If the cross-platform backup consists of multiple backup sets, use a separate BACKUPSET clause for each backup set. To restore tablespaces, you must specify the backup sets that contain the tablespace data using the BACKUPSET clause and the backup set that contains the tablespace metadata using the DUMP FILE option of the foreignFileSpec subclause.

  See Also:

  Oracle Database Backup and Recovery Reference for information about BACKPSET and DUMP FILE

• Data file numbers or names of tablespaces as they exist in the source database

  If you are restoring data files or tablespaces, you can restore specific tablespaces or data files that are contained in a cross-platform backup.

  See Also:

  "About Names and Locations for Restored Objects on the Destination Database"

• Location where the restored data files must be stored

  Use the FORMAT clause to specify the location and the names used to store the restored data files.

  If you do not provide a destination, then the DB_FILE_CREATE_DEST initialization parameter must be set in the target platform. RMAN restores the data files to the location specified by this parameter using new Oracle Managed File (OMF) names.

  See Also:

  "About Names and Locations for Restored Objects on the Destination Database"

• Name of the source platform (only when conversion is performed on the destination database)

  Use FROM PLATFORM to specify the name of the source platform on which the backup sets were created. The platform name must exactly match the name specified while creating the backup set. If there is a difference in the platform names, the restore operation fails.

  See Also:

  "About Backing Up Data on the Source Database for Cross-Platform Data Transport"

While restoring data from a cross-platform backup, you can either restore all the data contained in the cross-platform backup or only certain objects.

When you restore a cross-platform backup, specify the data file names and the location to which they are restored using one of the following options in the RESTORE command:

• Use the TO NEW option with the ALL FOREIGN DATAFILES clause to restore the data files to the location specified by the DB_FILE_CREATE_DEST parameter. By default, RMAN uses OMF names for the data files.

• Use the FORMAT option to specify the pattern used to name restored data files. You can also specify the directory for these files as part of the FORMAT specification.

While restoring a cross-platform backup of read-only tablespaces on the destination database, use the DUMP FILE ... FROM BACKUPSET option of the foreignFileSpec subclause to restore the backup set that contains the Data Pump export dump file. The export dump file contains the metadata required to plug the tablespace in to the destination database.

Use the DATAPUMP clause in the RESTORE command to specify the location on the destination host to which the export dump file is restored. If you omit this clause, the dump file is restored to a default operating system-specific location.

By default, RMAN automatically imports the export dump file after all the required foreign data files are restored. You can choose not to import the export dump file by specifying the NOIMPORT clause. If you do not import the export dump file as part of the restore operation, then you must manually import the dump file when you want to plug the tablespaces in to the destination database.

1. Ensure that the prerequisites required to perform cross-platform database transport are met.

   See Also:

   "Prerequisites for Cross-Platform Database Transport Using Backup Sets"

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

   % sqlplus sys@prod_source as SYSDBA
   

   When prompted, enter the password for the sys user.

3. Query the name of the destination platform in V$TRANSPORTABLE_PLATFORM.

   To transport the entire database, the endian formats of the source platform and the destination platform must be the same.

   See Also:

   "Platforms that Support Cross-Platform Data Transport" for information about determining the platform name

4. Choose a method for naming the output files.

   Use the FORMAT clause of the BACKUP command to specify the names of the output files.

   For example, the following FORMAT clause specifies that the output files must be stored using unique names that begin with transport_ in the directory /oradata/backups/special.

   FORMAT '/oradata/backups/special/transport_%U'
   

5. Start RMAN and connect to the source database as TARGET.

   The source database is the database that contains the data that needs to be transported to a different platform.

   In this example, sbu is a user who is granted the SYSBACKUP privilege in the source database prod_source.

   % RMAN
   RMAN> CONNECT TARGET "sbu@prod_source AS SYSBACKUP";
   

   Enter the password for the sbu user when prompted.

6. Place the database in read-only mode.

   SHUTDOWN IMMEDIATE;
   STARTUP MOUNT;
   ALTER DATABASE OPEN READ ONLY;
   

7. Back up the source database using the FOR TRANSPORT or TO PLATFORM clause in the BACKUP command. Using either of these clauses creates a cross-platform backup that uses backup sets.

   The following example creates a cross-platform backup of the entire database. This backup can be restored on any supported platform that uses the same endian format as the source database. The source platform is Sun Solaris x86 64-bit. Because the FOR TRANSPORT clause is used, the conversion is performed on the destination database. The cross-platform database backup is stored in db_trans.bck in the /tmp/xplat_backups directory on the source host.

   BACKUP
   FOR TRANSPORT
   FORMAT '/tmp/xplat_backups/db_trans.bck'
   DATABASE;
   

8. Disconnect from the source database.
9. Move the backup sets created by the BACKUP command to the destination host.

   Use operating system-specific utilities to transfer the created backup sets from the source host to the destination host.

   For example, if the operating system of your source and destination hosts is Linux or UNIX, use the cp command to move files.

10. Connect to the destination database, to which the database must be transported, as TARGET.

    In this example, sbu is a user who is granted the SYSBACKUP privilege in the destination database prod_dest.

    % RMAN
    RMAN> CONNECT TARGET "sbu@prod_dest AS SYSBACKUP";
    

    Enter the password for the sbu user when prompted.

11. Ensure that the destination database is in NOMOUNT state.
12. Restore the backup sets that were transferred from the source by using the RESTORE command with the FOREIGN DATABASE clause.

    The following example restores the cross-platform database backup created in Step 7. The destination database uses the same endian format as the source database. The FROM PLATFORM clause specifies the name of the platform on which the backup was created. This clause is required to convert backups on the destination. The backup set containing the cross-platform database backup is stored in the /tmp/xplat_restores directory on the destination host. The TO NEW option specifies that the restored foreign data files must use new OMF-specified names in the destination database. Ensure that the DB_CREATE_FILE_DEST initialization parameter is set.

    RESTORE
    FROM PLATFORM 'Solaris Operating System (x86-64)'
    FOREIGN DATABASE TO NEW
    FROM BACKUPSET '/tmp/xplat_restores/db_trans.bck';
    

1. Ensure that the prerequisites required to transport tablespaces to another platform are met.

   See Also:

   "Prerequisites for Performing Cross-Platform Tablespace Transport Using Backup Sets"

2. Connect to the source database from which you need to transport tablespaces as TARGET.

   In this example, sbu is a user who is granted the SYSBACKUP privilege on the source database prod_source.

   $ RMAN
   RMAN> CONNECT TARGET "sbu@prod_source AS SYSBACKUP";
   

   Enter the password for the sbu user when prompted.

3. Query the name of the destination platform in V$TRANSPORTABLE_PLATFORM.

   See Also:

   "Platforms that Support Cross-Platform Data Transport" for information about determining the platform name

4. Place the tablespaces to be transported in read-only mode.

   The following command places the tablespace EXAMPLE in read-only mode.

   ALTER TABLESPACE example READ ONLY;
   

5. Choose a method for naming the output files.

   Use the FORMAT clause of the BACKUP command to specify a pattern for naming the output files.

   See Also:

   "About Names and Locations for Restored Objects on the Destination Database"

6. Back up the tablespace on the source database using the BACKUP command with the TO PLATFORM or FOR TRANSPORT clause. Use the DATAPUMP clause to indicate that an export dump file for the tablespaces must be created. The export dump file is created in its own backup piece.

   The following example creates a cross-platform backup of the tablespaces projects and tasks that can be restored on the Solaris[tm] OE (64-bit) platform. This backup is stored in the backup set trans_ts.bck in the /tmp/xplat_backups directory. The Data Pump export dump file containing metadata required to plug the tablespaces in to the destination database is stored in trans_ts_dmp.bck in the /tmp/xplat_backups directory.

   RMAN > BACKUP
             TO PLATFORM 'Solaris[tm] OE (64-bit)'
             FORMAT '/tmp/xplat_backups/trans_ts.bck'
             DATAPUMP FORMAT '/tmp/xplat_backups/trans_ts_dmp.bck'
             TABLESPACE projects, tasks;
   

   Because the TO PLATFORM clause is used, conversion to the endian format of the destination database is performed on the source database.

   Note:

   When you use the DATAPUMP clause, ensure that the target database is open.

7. Disconnect from the source database.
8. Move the backup sets created by the BACKUP command and the Data Pump export dump file to the destination host.

   You can use operating system utilities to move the backup sets from the source host to the destination host.

9. Connect to the destination database, in to which the tablespaces must be transported, as TARGET.

   In this example, sbu is a user who is granted the SYSBACKUP privilege on the destination database prod_dest.

   % RMAN
   RMAN> CONNECT TARGET "sbu@prod_dest AS SYSBACKUP";
   

   Enter the password for the sbu user when prompted.

10. Restore the backup sets that were transported from the source database using the RESTORE command. Use the DUMP FILE clause to import the export dump file containing the tablespace metadata and plug the tablespaces in to the destination database.

    The following example restores the projects and tasks tablespaces from the cross-platform backup created in Step 6. The backup set trans_ts.bck in the /tmp/xplat_restores directory on the destination host. The Data Pump export dump file containing the metadata that is required to plug these tablespaces in to the destination database is stored in the trans_ts_dump.bck in the /tmp/xplat_restores directory.

    RMAN> RESTORE
          FOREIGN TABLESPACE projects, tasks TO NEW
          FROM BACKUPSET '/tmp/xplat_restores/trans_ts.bck'
          DUMP FILE FROM BACKUPSET '/tmp/xplat_restores/trans_ts_dmp.bck';
    

This section describes the steps used to transport inconsistent tablespaces across platforms. These steps apply to cross-platform transport using backup sets or image copies.

The prerequisites for transporting inconsistent tablespaces using backup sets are described in "Prerequisites for Performing Cross-Platform Tablespace Transport Using Backup Sets". The prerequisites for transporting inconsistent tablespaces using image copies (CONVERT command) are described in Oracle Database Backup and Recovery Reference.

Transporting inconsistent tablespaces to a different platform consists of the following tasks:

1. Creating Files Required to Transport Tablespaces to a Different Platform

2. Transferring Files Created on the Source Host to the Destination Host

3. Restoring Tablespaces and Plugging them in to the Destination Database

1. Connect to the source database as a user who is granted the SYSBACKUP privilege.

   RMAN> CONNECT TARGET "sbu@prod_source AS SYSBACKUP";
   

2. Ensure that the prerequisites required to transport tablespaces to another platform are met.

   See Also:

   "Prerequisites for Performing Cross-Platform Tablespace Transport Using Backup Sets"

3. Create a cross-platform level 0 inconsistent backup of the tablespace my_tbs when the tablespace is read/write mode. This backup is stored in a backup set named my_tbs_incon.bck in the directory /tmp/xplat_backups.

   BACKUP
   FOR TRANSPORT
   ALLOW INCONSISTENT
   INCREMENTAL LEVEL 0
   TABLESPACE my_tbs FORMAT '/tmp/xplat_backups/my_tbs_incon.bck';
   

   Because FOR TRANSPORT is used instead of TO PLATFORM, this cross-platform backup can be restored on any platform. The conversion will be performed on the destination database.

4. Create a cross-platform level 1 incremental backup of the tablespace my_tbs that contains the changes made after backup in Step 2 was created. The tablespace is still in read/write mode. This incremental backup is stored in my_tbs_incon1.bck in the directory /tmp/xplat_backups.

   BACKUP
   FOR TRANSPORT
   ALLOW INCONSISTENT
   INCREMENTAL LEVEL 1
   TABLESPACE my_tbs FORMAT '/tmp/xplat_backups/my_tbs_incon1.bck';
   

   To minimize application downtime, the level 0 and level 1 incremental backups created in Steps 2 and 3 can be restored and applied on the destination database while the source tablespace is still in read/write mode. When the destination database catches up with last level 1 incremental backup, you can create the final incremental backup with the tablespace placed in read-only mode.

5. Place the tablespace my_tbs in read-only mode.

   ALTER TABLESPACE my_tbs READ ONLY;
   

6. Create the final cross-platform level 1 incremental backup of the tablespace my_tbs. This backup contains changes made to the database after the backup that was created in Step 3. It must include the export dump file that contains the tablespace metadata.

   BACKUP
   FOR TRANSPORT
   INCREMENTAL LEVEL 1
   TABLESPACE my_tbs
   FORMAT '/tmp/xplat_backups/my_tbs_incr.bck'
   DATAPUMP FORMAT '/tmp/xplat_backups/my_tbs_incr_dp.bck';
   

   The incremental backup is stored in my_tbs_incr.bck. The export dump file containing the tablespace metadata is stored in a backup set named my_tbs_incr_dp.bck.

   The following is a formatted output of the BACKUP command that was run in this step. The output is edited to display only the relevant information. Observe that the dump file is called backup_tts_RDBMS_13462.dmp, which is a name assigned by the operating system, and is stored in the directory specified by the DESTINATION clause.

   Starting backup at 12-SEP-12
   ……
   Performing export of metadata for specified tablespaces...
   EXPDP> Starting "SYS"."TRANSPORT_EXP_RDBMS_zocc":
      .........
      EXPDP>
    ***************************************************************************
      EXPDP> Dump file set for SYS.TRANSPORT_EXP_RDBMS_zocc is:
      EXPDP>   /ade/b/191802369/oracle/backup_tts_RDBMS_13462.dmp
      EXPDP>
    **************************************************************************
     .......
   Export completed
   .......
   ……
   channel ORA_DISK_1: specifying datafile(s) in backup set
   input datafile file number=00006 name=/ade/b/191802369/oracle/dbs/tbs_11.f
   input datafile file number=00007 name=/ade/b/191802369/oracle/dbs/tbs_12.f
   input datafile file number=00020 name=/ade/b/191802369/oracle/dbs/tbs_14.f
   input datafile file number=00010 name=/ade/b/191802369/oracle/dbs/tbs_13.f
   ……
   ……
   Finished backup at 12-SEP-12
   

7. Move the backup sets and the export dump file generated in Steps 2, 3, and 5 from the source host to the desired directories on the destination host.

   In this example, all the required files are moved to the directory /tmp/xplat_restores on the destination host.

8. Connect to the destination database as a user who is granted the SYSBACKUP privilege.

   RMAN> CONNECT TARGET "sbu@prod_dest AS SYSBACKUP";
   

   sbu is a user who is granted the SYSBACKUP privilege in the destination database.

9. Restore the cross-platform level 0 inconsistent backup created in Step 2.

   Use the FOREIGN DATAFILE clause to specify the data files that must be restored. The FROM PLATFORM clause specifies the name of the platform on which the backup was created. This clause is required to convert backups on the destination database.

   In this example, the data files with numbers 6, 7, 20, and 10 are restored to the names specified in the FORMAT clause corresponding to that data file. The data file numbers must be the numbers used on the source database. You can obtain the data file numbers from the RMAN output of the inconsistent backup created in Step 2.

   RESTORE
   FROM PLATFORM 'Solaris[tm] OE (64-bit)'
   FOREIGN DATAFILE 6
   FORMAT '/tmp/aux/mytbs_6.df',
   7
   FORMAT '/tmp/aux/mytbs_7.df',
   20
   FORMAT '/tmp/aux/mytbs_20.df',
   10
   FORMAT '/tmp/aux/mytbs_10.df'
   FROM BACKUPSET '/tmp/xplat_restores/my_tbs_incon.bck';
   

10. Recover the foreign data files obtained in Step 8 by applying the first cross-platform level 1 incremental backup that was created Step 3.

    RECOVER
    FROM PLATFORM 'Solaris[tm] OE (64-bit)'
    FOREIGN DATAFILECOPY '/tmp/aux/mytbs_6.df','/tmp/aux/mytbs_7.df','/tmp/aux/mytbs_20.df','/tmp/aux/mytbs_10.df'
    FROM BACKUPSET '/tmp/xplat_restores/my_tbs_incon1.bck';
    

    In this example, the incremental backup that is being applied to the restored foreign data files is stored in /tmp/xplat_restores/my_tbs_incon1.bck.

11. Recover the foreign data files obtained in Step 8 by applying the final cross-platform level 1 incremental backup that was created in Step 5. This backup was created with the tablespaces in read-only mode.

    RECOVER
    FROM PLATFORM 'Solaris[tm] OE (64-bit)'
    FOREIGN DATAFILECOPY '/tmp/aux/mytbs_6.df','/tmp/aux/mytbs_7.df','/tmp/aux/mytbs_20.df','/tmp/aux/mytbs_10.df'
    FROM BACKUPSET '/tmp/xplat_restores/my_tbs_incr.bck';
    

    In this example, the incremental backup that is being applied to the restored foreign data files is stored in /tmp/xplat_restores/my_tbs_incr.bck.

12. Restore the backup set containing the export dump file. This dump file contains the tablespace metadata required to plug the tablespaces into the destination database.

    RESTORE
    FROM PLATFORM 'Solaris[tm] OE (64-bit)'
    DUMP FILE 'my_tbs_restore_md.dmp'
    DATAPUMP DESTINATION '/tmp/dump'
    FROM BACKUPSET '/tmp/xplat_restores/my_tbs_incr_dp.bck';
    

    In this example, the dump file is restored to a file called my_tbs_restore_md.dmp in the directory /tmp/dump. You can omit the name of the dump file and the DATAPUMP DESTINATION clause and allow RMAN to use operating-system defaults for these parameters.

13. Plug the tablespace in to the destination database. Use the Data Pump import utility to import the dump file containing the tablespace metadata in to the destination database.

    # impdp directory=dp_dir dumpfile=backup_tts_RDBMS_13462.dmp transport_datafiles='/tmp/aux/mytbs_6.df','/tmp/aux/mytbs_7.df','/tmp/aux/mytbs_20.df','/tmp/aux/mytbs_10.df' nologfile=Y
    

    When prompted for a user name and password, enter the credentials of the SYS user. In this example, dp_dir is a directory object that was created using CREATE DIRECTORY command and is mapped to the /tmp directory.