15 RMAN Cross-Platform Transportable Databases and Tablespaces

Rules for Renaming Files with CONVERT TABLESPACE

You can use the FORMAT and fileNameConversionSpec arguments to control the names of the files generated by the CONVERT command, or leave the renaming of converted files to default rules provided by RMAN. The following list describes the order of precedence among these rules:

• Files that match any patterns provided in a fileNameConversionSpec clause are renamed based upon that pattern.

• If you specify a FORMAT clause, then any file not renamed based upon a fileNameConversionSpec pattern are renamed according to the specified formatSpec.

• Any file not renamed by fileNameConversionSpec or FORMAT is assigned a default location based upon the following rules:

  • If the channel used for output has a default CONFIGURE... FORMAT setting, that setting is used to generate output file names

  • If a flash recovery area is configured, the converted datafiles are placed in the flash recovery area (though they are not usable backups).

  • Otherwise a platform-specific default FORMAT (which includes a %U for generating a unique filename) is used.

Converting Tablespaces on the Source Host: Example

Suppose you need to transport tablespaces finance and hr from a source database running on a Sun Solaris host to a destination database running on a Linux PC. You plan to store the converted datafiles in the temporary directory /tmp/transport_linux/ on the source host.

The example assumes that you have carried out the following steps in preparation for the tablespace transport:

• You have set the tablespaces to be transported to be read-only.

• You have looked up 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 target platform as a parameter to the CONVERT command. Query V$TRANSPORTABLE_PLATFORM to get the platform name from SQL*Plus as follows:

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

Now use RMAN to convert the datafiles into the endian format of the destination host. In this example, the FORMAT argument controls the name and location of the converted datafiles.

% rman TARGET /
RMAN> CONVERT TABLESPACE finance,hr
   TO PLATFORM 'Linux IA (32-bit)'
   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.

From this point, follow the rest of the general outline for creating a transportable tablespace set:

• Use the export utility to create the export dump file

• Move the converted datafiles from /tmp/transport_linux/ and the export dump file from the source host to the desired directories on the destination host

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

Converting Datafiles on the Destination Platform: Example

Suppose you need 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 running on a Sun Solaris host to a destination database running on a Linux PC host. 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 go in the /orahome/dbs/hr subdirectory, and datafiles for the finance tablespace go in the /orahome/dbs/fin directory).

The example assumes that you have carried out the following steps in preparation for converting the tablespaces and plugging them in at the destination:

• You have set the source tablespaces to be transported to be read-only, used the Original Export utility to create the export dump file (named, in our example, expdat.dmp), and copied expdat.dmp and the unconverted datafiles of the transportable tablespace set to the destination host, in the /tmp/transport_solaris/' directory. You have preserved the subdirectory structure from the files' original location, 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

Now use RMAN's CONVERT command to convert the datafiles to be transported to the destination host's format and store the converted datafiles in /orahome/dbs.

% rman TARGET /
RMAN> 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'

Note the following:

• The FORMAT argument controls the name and location of the converted datafiles.

• When converting on the destination platform, you must specify the source platform using the FROM PLATFORM argument. Otherwise, RMAN will assume that the source platform is the same as the platform of the host performing the conversion.

The result is a set of converted datafiles in the /orahome/dbs/ directory, named thus:

• /orahome/dbs/fin/fin01.dbf

• /orahome/dbs/fin/fin02.dbf

• /orahome/dbs/hr/hr01.dbf

• /orahome/dbs/hr/hr02.dbf

From this point, follow the rest of the general outline for tablespace transport. Plug the converted tablespaces into the new database with the import utility, and make the tablespaces read-write if applicable.

Using DBMS_TDB.CHECK_DB to Check Database State

DBMS_TDB.CHECK_DB checks whether a database can be transported to a desired destination platform, and whether the current state of the database permits transport. It can be called without arguments to see if any condition at the source database prevents transport. It can also be called with one or both of the following arguments:

DBMS_TDB.CHECK_DB returns TRUE if the source database can be transported using CONVERT DATABASE, and FALSE otherwise.

Make sure your database is open in read-only mode, then call DBMS_TDB.CHECK_DB with appropriate parameters.

If SERVEROUTPUT is ON, and DBMS_TDB.CHECK_DB returns FALSE, then the output includes the reason why the database cannot be transported. Possible conditions preventing the use of CONVERT DATABASE and their resolution are listed in the following table:

This example illustrates the use of CHECK_DB on a 32-bit Linux platform for transporting a database to 32-bit Windows, skipping read-only tablespaces, with a database that is currently open read-write.

SQL> set serveroutput on
SQL> declare
       db_ready boolean;
     begin
       db_ready := dbms_tdb.check_db('Microsoft Windows IA (32-bit)',dbms_tdb.skip_readonly);
     end;
     /
  
Database is not open READ ONLY. Please open database READ ONLY and retry.

PL/SQL procedure successfully completed.

If you call DBMS_TDB.CHECK_DB and no messages are displayed indicating conditions preventing transport before the PL/SQL procedure successfully completed message, then your database is ready for transport.

Using DBMS_TDB .CHECK_EXTERNAL to Identify External Objects

DBMS_TDB.CHECK_EXTERNAL must be used to identify any external tables, directories or BFILEs. RMAN cannot automate the transport of such files.

DBMS_TDB.CHECK_EXTERNAL takes no parameters. With SERVEROUTPUT set to ON, the output of DBMS_TDB.CHECK_EXTERNAL lists the external tables, directories and BFILEs of your database.

The following example shows how to call DBMS_TDB.CHECK_EXTERNAL:

SQL> set serveroutput on
SQL> 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 there are no external objects, then this procedure completes with no output. If there are external objects, however, the output will be similar to the following example:

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.

CONVERT DATABASE, Converting Datafiles on the Source Platform

When the RMAN CONVERT DATABASE step is to be performed on the source platform, the process for transporting databases across platforms works as follows:

• In preparation for transporting the database, the source database must be opened read-only.

  SQL> STARTUP MOUNT;
  SQL> ALTER DATABASE OPEN READ ONLY;
  

• Use the CHECK_DB function in the DBMS_TDB package as described in "Preparing for CONVERT DATABASE: Using the DBMS_TDB Package" to ensure that no conditions exist that 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 the desired destination platform.

  set serveroutput on
  declare
      db_ready boolean;
    begin
      /* db_ready is ignored, but with SERVEROUTPUT set to ON any 
       * conditions preventing transport will be output to console */
      db_ready := dbms_tdb.check_db('Microsoft Windows IA (32-bit)',
          dbms_tdb.skip_none);
    end;
  

• DBMS_TDB.CHECK_EXTERNAL must be used to identify any external objects:

  SQL> set serveroutput on
  SQL> 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;
  

  Typical output of DBMS_TDB.CHECK_EXTERNAL is shown in this example:

  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.
  

• When the database is ready for transport, the RMAN CONVERT DATABASE command is run, specifying a destination platform and how to name the output files. RMAN produces the files needed to move the database to the destination system, including the following:

  • A complete copy of the datafiles of the database, ready to be transported

  • A PFILE for use with the new database on the destination platform, containing settings used in the PFILE or SPFILE from the source database. Several entries at the top of the PFILE should be edited when the database is moved to the destination platform:

    # 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"
    

  • A transport script, which contains SQL statements used to create the new database on the destination platform

  The following example demonstrates the use of CONVERT DATABASE on the source platform, along with its outputs. Output related to the transport script and the parameter file for the new database is highlighted.

  RMAN> CONVERT DATABASE NEW DATABASE 'newdb'
          transport script '/tmp/convertdb/transportscript'
          to platform 'Microsoft Windows IA (32-bit)'
          db_file_name_convert '/disk1/oracle/dbs' '/tmp/convertdb'
          ;
   
  Starting convert at 25-JAN-05
  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 on the target platform 
    to create database
  Edit init.ora file init_00gb3vfv_1_0.ora.This PFILE will be used to 
    create the database on the target platform
  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 backup at 25-JAN-05
   
  RMAN>
  

  When CONVERT DATABASE completes, the source database may be opened read-write again. Then, all of the files produced must then be copied to the destination host.

• Place the datafiles in the desired locations on the destination host. If the path to the datafiles is different on the destination, then edit the transport script to refer to the new datafile locations. Also edit the PFILE to change any settings for the destination database.

• Then 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.

CONVERT DATABASE. Converting Datafiles on the Destination Host

You may choose to convert the datafiles of the database being transported on the destination platform instead of the source platform. Reasons for doing this include:

• Avoiding any 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.

In such a case, the preparations for the transport process are the same as in "CONVERT DATABASE, Converting Datafiles on the Source Platform". You must still open the database read-only, use DBMS_TDB.CHECK_DB to identify any conditions that prevent transport, and use DBMS_TDB.CHECK_EXTERNAL to identify external objects.

The remaining steps are:

• Run the RMAN CONVERT DATABASE command on the source platform specifying the ON TARGET PLATFORM argument. When used in this manner, the command syntax is as follows:

  CONVERT DATABASE ON TARGET PLATFORM
       CONVERT SCRIPT '/tmp/convertdb/convertscript.rman'
          TRANSPORT SCRIPT '/tmp/convertdb/transportscript.sql'
          new database 'newdb'
          FORMAT '/tmp/convertdb/%U'
  

  As with CONVERT DATABASE on the source platform, CONVERT DATABASE ON TARGET PLATFORM produces a transport script containing SQL*Plus commands to create a new database on the destination platform, and a PFILE for the new database containing the same settings as the source database.

  CONVERT DATABASE ON TARGET PLATFORM also generates a convert script containing RMAN CONVERT DATAFILE commands for each of the datafiles of the database being transported. The source datafiles must be copied unconverted to some temporary location at the destination, and then the convert script must be run at the destination to actually convert the datafiles to a format usable by the destination host. A typical convert script contains commands like the following:

  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';
   
  }
  

  One CONVERT DATAFILE command is generated for each datafile to be converted.

  Note that CONVERT DATABASE ON TARGET PLATFORM does not produce converted datafile copies.

  If the filesystem containing the datafiles of the source database is accessible from the destination system using the same path names, then you can use the convert script on the destination host without any changes. The CONVERT DATAFILE commands in the script produce datafile copies in the locations specified by FORMAT, converted for use with the new database. (Once the convert script has been run, the source database can be opened for read-write access again.)

  Otherwise, while the datafiles are still read-only, copy them to a temporary location. (As soon as copies of the datafiles are made, the source database can be opened read-write again.) If necessary, move the copies of the datafiles to a temporary location on the destination host, and then update each CONVERT DATAFILE command in the convert script to use the temporary location of each datafile as input and the FORMAT parameter of each CONVERT command to specify the desired final location of the datafiles of the transported database.

  This example shows the use of CONVERT DATABASE ON TARGET PLATFORM on the source host, with typical output:

  RMAN> convert database on target 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 target 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 target platform
  Run RMAN script /tmp/convertdb/convertscript-target on target platform to convert datafiles
  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 backup at 28-JAN-05
  

Run the convert script on the target platform to prepare the datafiles, and make any desired changes to the parameter file. Then run the transport script to create the new database, as described in "CONVERT DATABASE, Converting Datafiles on the Source Platform".

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