Avoid Datafile Conversion during Transportable Database

Purpose

When migrating to a new platform with Transportable Database (RMAN CONVERT DATABASE command), the default behavior is to perform datafile conversion on all datafiles in the database. However, only datafiles that contain undo data require conversion, including all datafiles contained within the SYSTEM tablespace, all datafiles contained within any other tablespace that contains any ROLLBACK segments, and all UNDO tablespaces. Depending on the storage infrastructure and the conversion method used, the time required for a platform migration can be reduced significantly by skipping the conversion process on datafiles that do not require it.

Scope and Application

In Oracle Database 10g Release 2 and Oracle Database 11g Release 1, skipping the conversion process on selected datafiles can only be accomplished when performing a target platform conversion and following the steps shown below.  It is not possible to skip conversion for any datafile when performing a source platform conversion. 

NOTE: When converting FROM/TO HP Tru64 UNIX, in addition to files with UNDO segments, all files with ASSM headers need conversion.

Avoid Datafile Conversion during Transportable Database

To skip datafile conversion for datafiles that do not contain undo data, you must perform a target platform conversion, which is done by running the CONVERT DATABASE ON TARGET PLATFORM command in RMAN.  This command creates three output files, one of which is called the convert script.

For details of the remaining output files and of the transportable database process in general, see Oracle Database Backup and Recovery Advanced User’s Guide for Oracle Database 10g, or Oracle Database Backup and Recovery User's Guide for Oracle Database 11g.

Normally, the convert script contains RMAN CONVERT DATAFILE commands for all datafiles in the database. The script is transferred to the target system, modified to fix file path names, and run to perform the datafile conversions. In order to skip conversion for datafiles that do not contain undo data, when the convert script is modified to fix file path names, it must also be modified to remove CONVERT DATAFILE commands for datafiles that do not contain undo data.

To skip conversion for datafiles that do not contain undo data, perform the following steps:

1. Perform the transportable database procedure as documented in the Oracle Database Backup and Recovery User's Guide up to the point where the convert script is modified to fix file path names.

During a normal target system conversion, the datafiles are copied from the source system and placed in a staging directory.  The convert script reads the datafiles from the staging directory and places the converted datafiles in their final location.

When implementing this optimization to skip conversion of certain datafiles, only the datafiles that will be converted (i.e. those that contain undo data) are copied to the staging area.  Datafiles that are not converted (i.e. those that are removed from the convert script) must be made accessible to the target system via NFS/shared storage or copied to the target system by other methods. 

2. Identify datafiles that contain undo data by running the following query:

select distinct(file_name)
from dba_data_files a, dba_rollback_segs b
where a.tablespace_name=b.tablespace_name;

3. If NOT converting FROM/TO HP Tru64 UNIX, skip this step and go to step #4.
   Identify datafiles with ASSM headers as they also need conversion:

select distinct header_file
from dba_segments
where segment_subtype = 'ASSM';

4. Edit the convert script.  Remove CONVERT DATAFILE commands for all datafiles except for those that contain undo data, as identified in the previous step.

Note that additional edits will be required to fix file path names, as indicated in the documentation.

5. Complete the transportable database procedure as documented in the Oracle Database Backup and Recovery User's Guide.  In general:
• Startup no mount new database
• Run modified convert script
• Run transport script, the transport script will:
• create the controlfile
• open the database
• recompile the plsql packages

Example

Datafiles with undo data are identified as follows:

SQL> select distinct(file_name)
     from dba_data_files a, dba_rollback_segs b
     where a.tablespace_name=b.tablespace_name;

FILE_NAME
---------------------------------------------
+ENG/tdb/datafile/undotbs1.6901.662718943
+ENG/tdb/datafile/system.14703.662718941

If the generated convert script has the following contents (Note that the necessary file path name corrections, as referenced in the documentation, are not reflected in this example.):

RUN {

CONVERT DATAFILE '+ENG/tdb/datafile/system.14703.662718941'
FROM PLATFORM 'Linux IA (32-bit)'
FORMAT '/tmp/system.14703.662718941';

CONVERT DATAFILE '+ENG/tdb/datafile/sysaux.13732.662718941'
FROM PLATFORM 'Linux IA (32-bit)'
FORMAT '/tmp/sysaux.13732.662718941';

CONVERT DATAFILE '+ENG/tdb/datafile/example.14713.662719027'
FROM PLATFORM 'Linux IA (32-bit)'
FORMAT '/tmp/example.14713.662719027';

CONVERT DATAFILE '+ENG/tdb/datafile/undotbs1.6901.662718943'
FROM PLATFORM 'Linux IA (32-bit)'
FORMAT '/tmp/undotbs1.6901.662718943';

CONVERT DATAFILE '+ENG/tdb/datafile/users.7185.662718943'
FROM PLATFORM 'Linux IA (32-bit)'
FORMAT '/tmp/users.7185.662718943';

}

Then edit the convert script and leave the CONVERT DATAFILE commands for only the datafiles that contain undo data (Note that the necessary file path name corrections, as referenced in the documentation, are not reflected in this example).

RUN {

CONVERT DATAFILE '+ENG/tdb/datafile/system.14703.662718941'
FROM PLATFORM 'Linux IA (32-bit)'
FORMAT '/tmp/system.14703.662718941';

CONVERT DATAFILE '+ENG/tdb/datafile/undotbs1.6901.662718943'
FROM PLATFORM 'Linux IA (32-bit)'
FORMAT '/tmp/undotbs1.6901.662718943';

}

When you transfer the source system datafiles to the target system, transfer the following datafiles directly to their final destination (i.e. the location where they will reside during normal database use) because they do not contain undo data:

• +ENG/tdb/datafile/sysaux.13732.662718941
• +ENG/tdb/datafile/example.14713.662719027
• +ENG/tdb/datafile/users.7185.662718943

Transfer the following datafiles to the staging area referenced in the convert script because they contain undo data and, therefore, require conversion:

• +ENG/tdb/datafile/system.14703.662718941
• +ENG/tdb/datafile/undotbs1.6901.662718943

These datafiles will be converted when the convert script is run, which places a converted copy of each datafile in its final location.

Note that for some platform combination (such as Linux to Windows and vice-versa in 11g), you need not run any CONVERT command in RMAN. You can directly backup and restore RMAN backupsets/imagecopes across platforms. Check below note for details:
Note 1079563.1 RMAN DUPLICATE/RESTORE/RECOVER Mixed Platform Support

References

BUG:8398158 - RMAN CONVERT ORA-600 KCBZ_CHECK_OBJD_TYP_3
NOTE:417455.1 - Datafiles are not converted in parallel for transportable database
NOTE:1079563.1 - RMAN DUPLICATE/RESTORE/RECOVER Mixed Platform Support