V4 Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Doc ID 2471245.1)

To BottomTo Bottom

In this Document

  Purpose
  Scope
  Details
    
  NOTE:  Before proceeding with this procedure, review the following notes to understand all the restrictions and issues associated with the transportable tablespace feature (TTS). All such restrictions/limitations of TTS apply.
  Overview
  Cross Platform Incremental Backup Supporting Scripts
  Prerequisites
  Troubleshooting
  Known Issues
  V4 Transport Tablespaces with Reduced Downtime using Cross Platform Incremental Backup
  Conventions Used in This Document
  Phase 1 - Initial Setup
  Step 1.1 - Install the destination database software and create the destination database
  Step 1.2 - Identify tablespaces to be transported
  Step 1.3 - Install xttconvert scripts on the source system
  Step 1.4 - Create necessary directories
  Step 1.5 - Configure xtt.properties on the source system
  Step 1.6 - Copy xttconvert scripts and xtt.properties to the destination system<
  Step 1.7 - Set TMPDIR environment variable
  Phase 2 - Prepare Phase
  Step 2.1 - Run the backup on the source system
  Step 2.2 - Transfer the following files to the destination system:
  Step 2.3 - Restore the datafiles on the destination system
  Phase 3 - Roll Forward Phase
  Step 3.1 - Create an incremental backup of the tablespaces being transported on the source system
  Step 3.2 - Transfer incremental backups and res.txt to the destination system
  Step 3.3 - Apply the incremental backup to the datafile copies on the destination system
  Step 3.4 - Repeat the roll forward phase 3 (3.1 - 3.3) or proceed to phase 4, final incremental backup
  Phase 4 - Final Incremental Backup  -- If you are running 12c, this step can be replaced by Phase 4 in Note 2005729.1:
  Step 4.1 - Alter source tablespace(s) to READ ONLY in the source database
  Step 4.2 - Create the final incremental backup of the tablespaces being transported on the source system:
  Step 4.3- Transfer incremental backups and res.txt to the destination system
  Step 4.4 - Apply last incremental backup to destination datafiles
  Phase 5 - Transport Phase:  Import Object Metadata into Destination Database
  OPTION #1.  Manually running export and import command.
  Option1.A:  Run datapump export on source database:
  Option1.B:  Transfer the export file to destination directory used by datapump

Option1.C:  Run datapump import on destination to plug in the tablespaces.  For example:
  OPTION #2.  Import across sqlnet. 
  Option2.A:  Create datapump directory and grant privilege:
  Option2.B:  Generate new xttplugin.txt for network import
  Option2.C:  Create a database link on destination database:
  Option2.D:   Modify and execute Impdp command:
  Step 6.1 Check tablespaces for corruption
  Step 6.2 - Alter the tablespace(s) READ WRITE in the destination database
  Phase 7 - Cleanup
  Appendix
  Description of Perl Script xttdriver.pl Options
  Description of Parameters in Configuration File xtt.properties
  Change History
  References

 

APPLIES TO: 

Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Oracle Database Cloud Schema Service - Version N/A and later
Linux x86-64
Updated 09-Nov-2018 -- Version 4

 

PURPOSE

This article covers the steps needed to use V4 Cross Platform Transportable Tablespaces (XTTS) with RMAN incremental backups to migrate data between systems that have different endian formats, with the least amount of application down time.

The first step will be to copy a full backup from the source to the destination. Then, by using a series of incremental backups, each smaller than the last, the data at the destination system can be brought nearly current with the source system, before any downtime is required. This procedure requires down time only during the final incremental backup, and the meta-data export/import.

This document describes the V4 procedures for Cross Platform Incremental Backup which can be used with 11.2.0.4 and higher.  This new procedure is simplified version of previous XTTs versions.  This version has the following differences:
 

  • this procure uses simplified commands.  One command (--backup) for the source and one command (--restore) for the destination.
  • this procedure requires only one file to be copied between the source's and destination's $TMPDIR (res.txt). 
  • this procedure will automatically resolve added datafiles with no additional intervention.  
  • this procedure allows for multiple incremental backups taken off the source without running the recovery.  After which, recovery will be of all the incremental backups in the destination at once.
NOTE:  There are a reported issues with multiple incremental backup recovery and large number of datafiles.  The recover command creation may cause backups to not be found (ORA-19625) and/or the recovery attempts to apply the backups in the wrong order resulting in:

ORA-19638: file /<path>/<datafile name> is not current enough to apply this incremental backup
ORA-19642: start SCN of incremental backup is <scn>
ORA-19641: backup datafile checkpoint is SCN <scn> time MM/DD/YYYY HH:MM:SS
ORA-19640: datafile checkpoint is SCN <scn> time MM/DD/YYYY HH:MM:SS

Review the following for details and workaround:
V4 XTTs: Restore Returns Errors (ORA-19625 or ORA-19641) With Large Number of Datafiles (Note 2689397.1)


The earlier versions of this procedure are still available in:

11G - Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Note 1389592.1)
12C - Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Note 2005729.1)

 

The Cross Platform Incremental Backup feature does not affect the amount of time it takes to perform other actions for XTTS, such as metadata export and import.  Hence, databases that have very large amounts of metadata (DDL) will see limited benefit from Cross Platform Incremental Backup because migration in these environments is typically dominated by metadata operations, not datafile transfer and conversion.

 

NOTE:  Only those database objects that are physically located in the tablespace(s) being transported will be copied to the destination system.   Other objects,  such as users, pl/sql objects, sequences, views etc., located in the SYSTEM tablespace will not be transported.  You will need to pre-create the users and copy such objects to the destination system, possibly using data pump.  

The following may help:

Oracle Database 12c: Full Transportable Export/Import

and/or

MAA paper Platform Migration Using Transportable Tablespaces: Oracle Database.

The high-level steps for Cross Platform Incremental Backup are:

1.  Initial setup

2.  Prepare phase (source data remains online)

  1. Backup (level=0) of tablespaces to be transported
  2. Transfer backup and other necessary setup files to destination system
  3. Restore datafiles on destination system endian format

3.  Roll Forward phase (source data remains online - Repeat this phase as many times as necessary to catch destination datafile copies up to source database)

  1. Create incremental backup on source system
  2. Transfer incremental backup and other necessary setup files to destination system
  3. Convert incremental backup to destination system endian format and apply the backup to the destination datafile copies
  4. Repeat steps until ready to transport the tablespace.  
NOTE:  In Version 4, added files will automatically be added in the destination with no additional intervention required.  I.e., if a datafile is added to the tablespace OR a new tablespace name is added to the xtt.properties file.

 4.  Transport phase (source data is READ ONLY)

    1. Alter the tablespaces in the source database to READ ONLY
    2. Repeat the Roll Forward phase one final time
      • This step makes destination datafile copies consistent with source database and generates necessary export.
      • Time for this step is significantly shorter than traditional XTTS method when dealing with large data because the incremental backup size is smaller.
    3. Import metadata of objects in the tablespaces into destination database using Data Pump
    4. Alter the tablespaces in the destination database to READ WRITE 

SCOPE

The source system may be any platform provided the prerequisites referenced and listed below for both platform and database are met. 

If you are migrating from a little endian platform to Oracle Linux, then the migration method that should receive first consideration is Data Guard.  See Note 413484.1 for details about heterogeneous platform support for Data Guard between your current little endian platform and Oracle Linux.

DETAILS

  

NOTE:  Before proceeding with this procedure, review the following notes to understand all the restrictions and issues associated with the transportable tablespace feature (TTS). All such restrictions/limitations of TTS apply.

Transportable Tablespace (TTS) Restrictions and Limitations: Details, Reference, and Version Where Applicable (Note 1454872.1)
Master Note for Transportable Tablespaces (TTS) -- Common Questions and Issues (Note 1166564.1)

Overview

This document provides a procedural example of transporting two tablespaces called TS1 and TS2 from an Oracle Solaris SPARC system to Oracle Linux, incorporating Oracle's Cross Platform Incremental Backup capability to reduce downtime.

After performing the initial setup, moving the data is performed as follows:

Prepare
During the prepare phase, a level =0 backup of the tablespaces' datafiles is taken on the source.  The backups are transferred to the destination, datafiles are restored and converted to the destination endian format.    

Roll Forward
During the roll forward phase, the datafiles restored during the prepare phase are rolled forward using incremental backups taken from the source database.  By performing this phase multiple times, each successive incremental backup becomes smaller and faster to apply, allowing the data at the destination system to be brought almost current with the source system.  The application being migrated is fully accessible during the Roll Forward phase.

Transport
During the transport phase, the tablespaces being transported are put into READ ONLY mode, a final incremental backup is taken from the source database, backups are transferred to destination and applied to the destination datafiles.  At this point, the destination datafile copies consistent with source database and the application being migrated cannot receive any further updates.  The tablespaces then are TTS-exported from the source database and TTS-imported into the destination database.  Finally, the tablespaces are made READ WRITE for full access on the destination database. 

Cross Platform Incremental Backup Supporting Scripts

The Cross Platform Incremental Backup core functionality was delivered in Oracle Database 11.2.0.4 and later.  Some of the features in the prior versions (such as using DBMS_FILE_TRANSFER) are not available in this Version 4.  If you need such functionality, use the standard procedure for 11g outlined in Note 1389592.1. The procedures outlined in this note applies to both Oracle 11.2.0.4 , 12c and later.  See the Requirements and Recommendations section for details. In addition, a set of supporting scripts in the file rman_xttconvert_VER4.zip is attached to this document that are used to manage the procedure required to perform XTTS with Cross Platform Incremental Backup using Version 4.

The two primary supporting scripts files are the following:

  • Perl script xttdriver.pl script that is run to perform the main steps of the XTTS with Cross Platform Incremental Backup procedure.
  • Parameter file xtt.properties: the file which stores site-specific configuration.

Prerequisites

The following prerequisites must be met before starting this procedure:

  • The limitations and considerations for transportable tablespaces must still be followed.  They are defined in the following manuals:
    • Oracle Database Administrator's Guide
    • Oracle Database Utilities
  • In addition to the limitations and considerations for transportable tablespaces, the following conditions must be met:
    • The current version does NOT support Windows as either source or destination.
    • The source database's COMPATIBLE parameter must not be greater than the destination database's COMPATIBLE parameter.
    • The source database must be in ARCHIVELOG mode.
    • RMAN on the source system must not have DEVICE TYPE DISK configured with COMPRESSED.
    • RMAN on the source system must not have BACKUP TYPE TO COPY.  The source must have BACKUP TYPE TO BACKUPSET.
    • RMAN on the source system must not have default channel configured to type SBT.   I.e., this procedure can only be used with DISK channels.  
    • RMAN on the source system must not have ANY channel configuration limitations.  For example, MAXSETSIZE, MAXPIECESIZE, etc.
    • The set of tablespaces being moved must all be online, and contain no offline data files.  Tablespaces must be READ WRITE. 
    • Tablespaces that are READ ONLY may be moved with the normal XTTS method.  There is no need to incorporate Cross Platform Incremental Backups to move tablespaces that are always READ ONLY.
    • Although preferred destination system is Linux (either 64-bit Oracle Linux or a certified version of RedHat Linux), this procedure can be used with other Unix based operating systems. However, any non-Linux operating system must be running 12.1.0.1 or higher in both destination and source.
    • The Oracle version of source must be lower or equal to destination.  Therefore, this procedure can be used as an upgrade method.  Transportable tablespace restrictions WILL apply. 
    • Minimum version for source and destination is 11.2.0.4. 
    • ASM can only be used for final location of datafiles in destination, backups cannot be placed on ASM with this version.  
    • The backup location of destination MUST be a device with read/write privileges.  I.e., cannot be a READONLY device.  This can cause ORA-19624 on the backupset conversion. 
  • All steps in this procedure are run as the oracle user that is a member of the OSDBA group. OS authentication is used to connect to both the source and destination databases.
  • Although NOT recommended, a standby database can be used for ONLY the backup portion of this procedure.    This must be done under the direction of Oracle support.  
  • A Snapshot Standby database is NOT supported for this procedure.  

Whole Database Migration

If Cross Platform Incremental Backups will be used to reduce downtime for a whole database migration, then the steps in this document can be combined with the XTTS guidance provided in the MAA paper Platform Migration Using Transportable Tablespaces: Oracle Database. 

Troubleshooting

Debug mode enables additional screen output and causes all RMAN executions to be performed with the debug command line option.  To enable debug mode, set the environment variable XTTDEBUG=1 before running xttdriver.pl OR
run xttdriver.pl with the --debug flag.  This flag allows for 3 levels, -d [1/2/3] with 3 generating the most information.  

Known Issues

  1. If the roll forward phase (xttdriver.pl -r) fails with the following errors, then verify RMAN DEVICE TYPE DISK is not configured COMPRESSED.

    Entering RollForward
    After applySetDataFile
    Done: applyDataFileTo
    Done: RestoreSetPiece
    DECLARE
    *
    ERROR at line 1:
    ORA-19624: operation failed, retry possible
    ORA-19870: error while restoring backup piece
    /<path>/xtts_incr_backup
    ORA-19608: /<path>/xtts_incr_backup is not a backup
    piece
    ORA-19837: invalid blocksize 0 in backup piece header
    ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 2338
    ORA-06512: at line 40

     
  2. If using ASM in both source and destination, see XTTS Creates Alias on Destination when Source and Destination use ASM (Note 2351123.1)
  3. If using ASM for datafiles, an error deleting file system file using ASMCMD can be ignored.  (Bug 29268792, currently open)
  4. The existence of a GLOGIN.sql, in either source or destination, can cause syntax errors. 
  5. For other known issues, see other issues within Known Issues for Cross Platform Transportable Tablespaces XTTS Document 2311677.1.
  6. You cannot use this procedure for a TDE tablespace.
  7. This procedure has only been tested with English language.
  8. Be aware of open bug 30777480 which can cause an issue if the CDB's undo datafile id (file#) is the same as the datafile id (file#) belonging to a tablespaces being transported.  
    You will receive an error RMAN-20201 'datafile not found in the recovery catalog' on the undo datafile number.
  9. Incremental backup creation parallelism is defined by RMAN configuration for DEVICE TYPE DISK PARALLELISM.  

 

 


V4 Transport Tablespaces with Reduced Downtime using Cross Platform Incremental Backup

The XTTS with Cross Platform Incremental Backups procedure is divided into the following phases:

  • Phase 1 - Initial Setup phase
  • Phase 2 - Prepare phase
  • Phase 3 - Roll Forward phase
  • Phase 4 - Transport Phase: Import Metadata
  • Phase 5 - Validate the Transported Data
  • Phase 6 - Cleanup

Conventions Used in This Document

  • All command examples use bash shell syntax.
  • Commands prefaced by the shell prompt string [oracle@source]$ indicate commands run as the oracle user on the source system.
  • Commands prefaced by the shell prompt string [oracle@dest]$ indicate commands run as the oracle user on the destination system.

Phase 1 - Initial Setup

Perform the following steps to configure the environment to use Cross Platform Incremental Backups:

Step 1.1 - Install the destination database software and create the destination database

Install the desired Oracle Database software on the destination system that will run the destination database.  

Identify (or create) a database on the destination system to transport the tablespace(s) into and create the schema users required for the tablespace transport.  I.e., users who own the objects within the tablespaces being transported.  

Per generic TTS requirement, ensure that the schema users required for the tablespace transport exist in the destination database.
Step 1.2 - Identify tablespaces to be transported

Identify the tablespace(s) in the source database that will be transported. Tablespaces TS1 and TS2 will be used in the examples in this document.  As indicated above, the limitations and considerations for transportable tablespaces must still be followed.

Step 1.3 - Install xttconvert scripts on the source system

On the source system, as the oracle software owner, download and extract the supporting scripts attached as rman_xttconvert_VER4.zip to this document.

[oracle@source]$ pwd
/home/oracle/xtt

[oracle@source]$ unzip rman_xttconvert_VER4.zip
Archive: rman_xttconvert_v3.zip
inflating: xtt.properties
inflating: xttcnvrtbkupdest.sql
inflating: xttdbopen.sql
inflating: xttdriver.pl
inflating: xttprep.tmpl
extracting: xttstartupnomount.sql
Step 1.4 - Create necessary directories
  1. On source:
    • Location of backups as defined by src_scratch_location parameter in the xtt.properties file.  
       
  2. On destination:
    • Location of backups as defined by the dest_scratch_location parameter in the xtt.properties file.  
    • Location for datafiles on destination, as defined by dest_datafile_location parameter in the xtt.properties file.  
Step 1.5 - Configure xtt.properties on the source system

Edit the xtt.properties file on the source system with your site-specific configuration.   For more information about the parameters in the xtt.properties file, refer to the Description of Parameters in Configuration File xtt.properties section in the Appendix below.   For this procedure, only the following parameters are mandatory.  Others are optional and/or available for use.

    • tablespaces
    • platformid
    • src_scratch_location
    • dest_scratch_location
    • dest_datafile_location
    • (if using 12c) -- usermantransport=1
Step 1.6 - Copy xttconvert scripts and xtt.properties to the destination system<

As the oracle software owner copy all xttconvert scripts and the modified xtt.properties file to the destination system.
 

[oracle@source]$ scp -r /home/oracle/xtt oracle@dest:/home/oracle/xtt
Step 1.7 - Set TMPDIR environment variable

In the shell environment on both source and destination systems, set environment variable TMPDIR to the location where the supporting scripts exist.  Use this shell to run the Perl script xttdriver.pl as shown in the steps below.  If TMPDIR is not set, output files are created in and input files are expected to be in /tmp.

[oracle@source]$ export TMPDIR=/home/oracle/xtt

[oracle@dest]$ export TMPDIR=/home/oracle/xtt
 

Phase 2 - Prepare Phase

During the Prepare phase, datafiles of the tablespaces to be transported are backed up on source, backups transferred to the destination system and restored by the xttdriver.pl script. 

NOTE: For large number of files, if you wish to use dbms_file_transfer you will not be able to use V4, you will have to use 11G - Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Note 1389592.1). It has been found to be the fastest method for transferring datafiles to destination. This method can be used by ALL Oracle version migrations wanting to use dbms_file_transfer.
Step 2.1 - Run the backup on the source system

On the source system, logged in as the oracle user with the environment (ORACLE_HOME and ORACLE_SID environment variables) pointing to the source database, run the backup as follows:

[oracle@source]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup
Step 2.2 - Transfer the following files to the destination system:
  • Backups created from source src_scratch_location to destination dest_scratch_location
  • The res.txt file from source $TMPDIR to destination $TMPDIR:

   In the example below, scp is used to transfer the level=0 backup created by the previous step from the source system to the destination system.

[oracle@source]$ scp /src_scratch/* oracle@dest:/dest_scratch
[oracle@source]$ scp res.txt oracle@dest:/home/oracle/xtt
Step 2.3 - Restore the datafiles on the destination system

On the destination system, logged in as the oracle user with the environment (ORACLE_HOME and ORACLE_SID environment variables) pointing to the destination database, run the restore as follows:

[oracle@dest]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore
 

Datafiles will be placed on the destination system in the defined dest_datafile_location. 

Phase 3 - Roll Forward Phase

During this phase an incremental backup is created from the source database, transferred to the destination system, converted to the destination system endian format, then applied to the converted destination datafile copies to roll them forward.  This phase may be run multiple times. Each successive incremental backup should take less time than the prior incremental backup, and will bring the destination datafile copies more current with the source database.  The data being transported (source) is fully accessible during this phase.  

NOTE:  Multiple backups can be executed against the source without applying them to the destination.  The backup files and the res.txt must be copied before the '--restore' is executed at the destination.   

NOTE:  The script will shutdown and startup, in NOMOUNT, the destination database before the --restore.  
Step 3.1 - Create an incremental backup of the tablespaces being transported on the source system

On the source system, logged in as the oracle user with the environment (ORACLE_HOME and ORACLE_SID environment variables) pointing to the source database, run the create incremental step as follows:

[oracle@source]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup
 

This step will create an incremental backup for all tablespaces listed in xtt.properties.  

Step 3.2 - Transfer incremental backups and res.txt to the destination system

Transfer the incremental backup(s) (between src_scratch_location and dest_scratch_location) and the res.txt (between the $TMPDIRs) from the source to the destination.  The list of incremental backup files from current backup can be 
found in the incrbackups.txt file on the source system.

[oracle@source]$ scp `cat incrbackups.txt` oracle@dest:/dest_scratch_location
[oracle@source]$ scp res.txt oracle@dest:/home/oracle/xtt
 
If the src_scratch_location on the source system and the dest_scratch_location on the destination system refer to the same NFS storage location, then the backups do not need to be copied as they are available in the expected location on the destination system.  

However, the res.txt file MUST be copied after the LAST incremental backup before it can be applied on destination (step 3.3).
Step 3.3 - Apply the incremental backup to the datafile copies on the destination system

On the destination system, logged in as the oracle user with the environment (ORACLE_HOME and ORACLE_SID environment variables) pointing to the destination database, run the roll forward datafiles step as follows:

[oracle@dest]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore

The roll forward step connects to destination database and applies the incremental backups on the tablespaces' datafiles for each tablespace being transported.

 
NOTE:  Although multiple backups can be executed against the source without being applied on the destination, the res.txt must be copied after the last backup and before the '--restore' is executed at the destination. 
Step 3.4 - Repeat the roll forward phase 3 (3.1 - 3.3) or proceed to phase 4, final incremental backup

At this point there are two choices:

  1. If you need to bring the files at the destination database closer in sync with the production system, then repeat the Roll Forward phase, starting with step 3.1.
  2. If the files at the destination database are as close as desired to the source database, then proceed to the Transport phase. 

Phase 4 - Final Incremental Backup  -- If you are running 12c, this step can be replaced by Phase 4 in Note 2005729.1:

During this phase the source data is made READ ONLY and the destination datafiles are made consistent with the source database by creating and applying a final incremental backup. After the destination datafiles are made consistent, the normal transportable tablespace steps are performed to export object metadata from the source database and import it into the destination database.  The data being transported is accessible only in READ ONLY mode until the end of this phase.

Step 4.1 - Alter source tablespace(s) to READ ONLY in the source database
On the source system, logged in as the oracle user with the environment (ORACLE_HOME and ORACLE_SID environment variables) pointing to the source database, alter the tablespaces being transported to READ ONLY.
 
system@source/prod SQL> alter tablespace TS1 read only;

Tablespace altered.

system@source/prod SQL> alter tablespace TS2 read only;

Tablespace altered.
Step 4.2 - Create the final incremental backup of the tablespaces being transported on the source system:

On the source system, logged in as the oracle user with the environment (ORACLE_HOME and ORACLE_SID environment variables) pointing to the source database, run the backup as follows:

[oracle@source]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup

 

NOTE:  As the tablespaces are in READ ONLY mode, the following warning received can be ignored:

####################################################################
Warning:
------
Warnings found in executing /home/oracle/convert_source/backup_Nov9_Fri_09_08_26_213//xttpreparenextiter.sql
####################################################################
Prepare newscn for Tablespaces: 'SECOND'
DECLARE*
ERROR at line 1:
ORA-20001: TABLESPACE(S) IS READONLY OR,
OFFLINE JUST CONVERT, COPY
ORA-06512: at line 284 
Step 4.3- Transfer incremental backups and res.txt to the destination system

[oracle@source]$ scp 'cat incrbackups.txt' oracle@dest:/dest_scratch_location
[oracle@source]$ scp res.txt oracle@dest:/home/oracle/xtt

Step 4.4 - Apply last incremental backup to destination datafiles

The final incremental backup must be applied to the destination datafiles:

[oracle@dest]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore

 This step will apply the last incremental backup to the datafiles on the destination. 

Phase 5 - Transport Phase:  Import Object Metadata into Destination Database

NOTE: Be sure the destination database has the necessary objects to allow the import to succeed. This includes pre-creating the owners of the tables in the tablespace being plugged in. See information on Transportable Tablespace and the guidance provided in the MAA paper Platform Migration Using Transportable Tablespaces: Oracle Database 11g.

During this phase, you need an export of the metadata of the tablespaces from the source and plug in the tablespace(s) into the destination.   

NOTE:  This would be skipped if you are running 12c and chose to use "Phase 4 "in Note 2005729.1. 

There are two options, running manually or running across sqlnet:  

OPTION #1.  Manually running export and import command.

Option1.A:  Run datapump export on source database:

Perform the tablespace transport by running transportable mode Data Pump export on the source database to export the object metadata being transported into a dump file.  The below example assumes a directory (DATA_PUMP_DIR) already exists in the source.  For example: 

[oracle@source]$ cat exp.par

dumpfile=xttdump.dmp
directory=DATA_PUMP_DIR
statistics=NONE
transport_tablespaces=TS1,TS2
transport_full_check=y
logfile=tts_export.log 

[oracle@source]$ expdp system/manager parfile=exp.par

Refer to the following manuals for details:

Oracle Database Administrator's Guide
Oracle Database Utilities

Option1.B:  Transfer the export file to destination directory used by datapump

Option1.C:  Run datapump import on destination to plug in the tablespaces.  For example:
[oracle@dest]$ cat manual_imp.par
dumpfile= xttdump.dmp
directory=DATAPUMP
transport_datafiles='/dest_datafile_location/TS1.dbf','/dest_datafile_location/TS2.dbf'

[oracle@dest]$ impdp system/oracle parfile=manual_imp.par 

OPTION #2.  Import across sqlnet. 

Option2.A:  Create datapump directory and grant privilege:

Datapump will look for the export file in the specified directory.  Either copy the '.dmp' file in your existing data pump directory or create a new directory pointing to the  or copy the '.dmp' in an existing datapump directory

SYS@DESTDB> create directory dpump_tts as '/home/oracle/destination/convert';

The directory must be granted to use who will do the import:

SYS@DESTDB> GRANT READ, WRITE ON DIRECTORY dpump_tts TO system;
Option2.B:  Generate new xttplugin.txt for network import

On the destination system, logged in as the oracle user with the environment (ORACLE_HOME and ORACLE_SID environment variables) pointing to the destination database, run the generate Data Pump TTS command step as follows:

[oracle@dest]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -e

This will generate a sample Data Pump network_link transportable import command in the file xttplugin.txt with the transportable tablespaces parameters TRANSPORT_TABLESPACES and TRANSPORT_DATAFILES correctly set. In addition, a datapump export file will also be created.

NOTE:  This command will overwrite the previous xttplugin.txt which is needed by Step5A. 
Option2.C:  Create a database link on destination database:

Connecting to the destination database, create a database link connecting to the source database. For example:

SQL@dest> create public database link ttslink connect to system identified by <password> using '<tns_to_source>';

Verify the database link can properly access the source system:

SQL@dest> select name from v$database@ttslink;
Option2.D:   Modify and execute Impdp command:

A separate export or dump file is not required.  To perform the tablespace transport with this command, then you must edit the import command file xttplugin.txt (generated in step 5B.1) and replace import parameters DIRECTORY, LOGFILE, and NETWORK_LINK with site-specific values.

The following is an example network mode transportable import command:

 

[oracle@dest]$ impdp directory=DATA_PUMP_DIR logfile=tts_imp.log network_link=ttslink \ 
transport_full_check=no \ 
transport_tablespaces=TS1,TS2 \ 
transport_datafiles='+DATA/prod/datafile/ts1.285.771686721', \ 
'+DATA/prod/datafile/ts2.286.771686723', \ 
'+DATA/prod/datafile/ts2.287.771686743' 
NOTE:  Per generic TTS requirement, database users that own objects being transported must exist in the destination database before performing the transportable import.  

Resources:

Phase 6 - Validate the Transported Data

Step 6.1 Check tablespaces for corruption

At this step, the transported data is READ ONLY in the destination database.  Perform application specific validation to verify the transported data.

Also, run RMAN to check for physical and logical block corruption by running VALIDATE TABLESPACE as follows:

 
RMAN> validate tablespace TS1, TS2 check logical;

Step 6.2 - Alter the tablespace(s) READ WRITE in the destination database

The final step is to make the destination tablespace(s) READ WRITE in the destination database.

system@dest/prod SQL> alter tablespace TS1 read write; 

Tablespace altered. 

system@dest/prod SQL> alter tablespace TS2 read write; 

Tablespace altered.

Phase 7 - Cleanup

If a separate incremental convert home and instance were created for the migration, then the instance may be shutdown and the software removed.
Files created by this process are no longer required and may now be removed.  They include the following:
  • src_scratch location on the source system
  • dest_scratch location on the destination system
  • $TMPDIR location in both source and destination systems

Appendix

Description of Perl Script xttdriver.pl Options

 The following table describes the options available for the main supporting script xttdriver.pl.

Option Description

--backup 

Creates a level 0 or level 1 backup of the datafiles belonging to the selected tablespaces. These backups will be written into the location pointed to by the xtt.properties variable “src_scratch_location”.  These backups  need to be copied over to the destination to the location pointed to by the variable “dest_scratch_location”.                                                                                                                 


--restore 

Restores and converts the datafiles from the backups copied to the location “dest_scratch_location”  in destination.   The restored files will be placed in the path defined by the variable “dest_datafile_location”.

-e  Generates Data Pump TTS command step is run once on the destination system with the environment (ORACLE_HOME and ORACLE_SID) set to the destination database.

This step creates the template of a Data Pump Import command that uses a network_link to import metadata of objects that are in the tablespaces being transported.
--debug Enables debug mode for xttdriver.pl and RMAN commands it executes.  Debug mode can also be enabled by setting environment variable XTTDEBUG=1.  Debug allows for 1,2,3 levels of debug.  I.e., xttdriver.pl -debug 3

Description of Parameters in Configuration File xtt.properties

The following table describes the parameters used by xttdriver.pl which must be defined in the xtt.properties file that are needed for this 12c procedure.  Other parameters may exist which are needed for backward compatibility.   

Parameter Description Example Setting
tablespaces Comma-separated list of tablespaces to transport from source database to destination database. Must be a single line, any subsequent lines will not be read. tablespaces=TS1,TS2
platformid Source database platform id, obtained from V$DATABASE.PLATFORM_ID. platformid=13
dest_datafile_location

Directory object in the destination database that defines where the destination datafiles will be created.

dest_datafile_location=/u01/oradata/V122
i.e.:
dest_datafile_location=+DATA
src_scratch_location Location on the source system where backups are created.  

This location must have sufficient free space to hold the level=0 backup of the tablespace datafiles and all subsequent incremental backups created for one iteration through the process documented above.

This location may be an NFS-mounted filesystem that is shared with the destination system, in which case it should reference the same NFS location as the dest_scratch_location parameter for the destination system.
scr_scratch_location=/stage_source
dest_scratch_location Location on the destination system where backups are placed by the user when they are transferred manually from the source system. 

This location must have sufficient free space to hold level=0 backup and all subsequent incremental backups transferred from the source. 

This location may be a DBFS-mounted filesystem.

This location may be an NFS-mounted filesystem that is shared with the source system, in which case it should reference the same NFS location as the src_scratch_location parameters for the source system.  See Note 359515.1 for mount option guidelines.
dest_scratch_location=/stage_dest
 asm_home  ORACLE_HOME for the ASM instance that runs on the destination system.
 
asm_home=/u01/app/12.2.0.1/grid
asm_sid ORACLE_SID for the ASM instance that runs on the destination system. asm_sid=+ASM1
parallel

Defines the degree of parallelism used in copying (prepare phase), converting.    Incremental backup creation parallelism is defined by RMAN configuration for DEVICE TYPE DISK PARALLELISM.  

 


parallel=3
srcconnstr

In CDB environment, specifies connect string of the source pluggable database.


srcconnstr=sys/passwd@sourc_cdb_PDB1
destconnstr

In CDB environment, specifies connect string of the destination pluggable database.


destconnstr=sys/passwd@dest_cdb_PDB1
usermantransport

Set this parameter to 1 if using 12c to use the 12c functionality. 

usermantransport=1

Change History

 

Change Date

rman_xttconvert_VER4.zip released

29-Nov-2018

REFERENCES


NOTE:2311677.1 - Known Issues for Cross Platform Transportable Tablespaces XTTS
NOTE:2013271.1 - 12c How Perform Cross-Platform Database Transport to different Endian Platform with RMAN Backup Sets
NOTE:2005729.1 - 12C - Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup 
BUG:18596700 - CHANGE TRACKING IS NOT USED DURING INCREMENTAL BACKUP FOR READ ONLY TBS


NOTE:25890056.8 - Bug 25890056 - Fix for Bug 25670970 on 12.2 - ORA-600 [25027] on Securefile Lob Segment after XTTS
BUG:28309542 - LFI-00005 LFI-01007 XTTDRIVER.PL --RESTORE

BUG:29268792 - V4 XTTS WILL ATTEMPT TO DELETE FILE SYSTEM FILE WITH ASMCMD

NOTE:2689397.1 - V4 XTTs: Restore Returns Errors (ORA-19625 or ORA-19641) With Large Number of Datafiles 
NOTE:1389592.1 - 11G - Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup


NOTE:25670970.8 - Bug 25670970 - ORA-600 [25027] or ORA-600 [kcfrbd_3] on Securefile Lob Segment after XTTS