Migrate database using cross-platform incremental backups with transportable tablespace

The transportable tablespace feature enables the user to move a set of applications from one Oracle database to another desired Oracle database.

By using Transportable tablespace with cross-platform incremental backup, the downtime required for the migration significantly reduces; It also provides the benefits of transportable tablespace by supporting cross-platform migrations.

Overview

The Transportable tablespace (XTTS) with Cross-Platform Incremental Backups procedure can be implemented by completing the following stages:

STAGE 1- TARGET DATABASE INSTALLATION AND SETUP

Step 1.1 **Install the desired Oracle Database software 19c on the destination system with the database and default tablespace e.g.: SYS, SYSTEM, USERS (Database versions can be our desired version based on requirement starting from 12c or later)

Step 1.2 Create the schemas required for the desired transportable tablespace in the destination database without objects. (If using multitenant architecture, ensure desired Pluggable database is created, and schema is created under this database.) Note: Initially Schemas need to be created on default tablespace USERS as the desired tablespace need to be transported should not be present at the target during metadata import at stage 5.

Step1.3 Identify application-specific tablespace to be transported in the source system (i.e tablespace required for migration).

Step1.4 Download scripts required for backup database on the source system. (i.e. rman_xttconvert scripts are oracle provided automated scripts to proceed with this operation)

On the source database server, from the operating system owner Oracle, download and extract the Oracle provided scripts rman_xttconvert_VER4.zip this can be downloaded from oracle Doc ID (Doc ID 2471245.1)

Step 1.5 Create necessary backup locations

a) On source:

• Location of the source database backups created from xttdriver.pl script during stage 2 as defined by in the xtt.properties file, refer step 1.6.

Note: xttconvert scripts won’t support compressed backup so kindly ensure the directory we create has enough space to hold space equal to source database size.

b) On destination:

• Location of backups moved from a source as defined in the xtt.properties file.
• Location for restored transportable tablespace data files on the target database, as mentioned in the xtt.properties file. (If target destination is ASM ensure the desired diskgroup is mounted)

Step 1.6 Configure xtt.properties. This file can be found under path xtt on the source system as shown in the following snapshot and ensure platformid is same on both source and target else this operation will fail.

Step 1.7 Copy xttconvert scripts including xtt.properties to the target database server.

Step 1.8 Set TMPDIR environment variable

STAGE 2 - INITIATE LEVEL 0 BACKUP

During this stage, Level 0 Backup initiated for the source tablespaces mentioned in xtt. Properties config file, and Level 0 backup files are copied to the destination database server and restored using Oracle provided scripts refer step 2.1.

Step 2.1 Initiate Level 0 Backup using xttdriver

At the source database server, from the database Operating System Owner Oracle set the environment referring to the source database. Execute the following statement to backup the database to the src_scratch_location.

(If it is a Multitenant database ensure xtt.properties is updated with desired Container connection string refer step 1.6)

Step 2.2 Transfer the Level 0 Backup files and Configuration files (xtt.properties,res.txt)to the destination system:

• Copy Backups created from Level 0 backup (location defined in src_scratch_location) to destination database server (location defined in *dest_scratch_location)
• Copy res.txt file from source system $TMPDIR to destination system $TMPDIR: Note: res.txt need to be transferred to target whenever backup is taken using xttdriver.pl

Step 2.3 Restore data files using copied Level 0 Backup on the target database.

On the destination server, from the database Operating System Owner Oracle, set the environment referring to the destination database, run the roll forward datafiles step as follows:

STAGE 3 - ROLL FORWARD USING INCREMENTAL BACKUP

During this stage an incremental backup based on SCN from the last backup is created on the source 12c database, are copied to the target database server 19c, and data files are converted to the destination endian format, then incremental backups are applied to the restored datafile copies to roll forward on the destination database server. This operation will repeat to make data sync with the source database until the date of the actual migration.

Step 3.1 Create an incremental backup at the source database for the planned tablespace.

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

Note: We can ignore backups coping to the target system if both source and target backup locations are mounted on the same NFS.

Step 3.3 Apply the backup created on step 3.1 to the restored data files on the target server.

On the target database server, from the operating system owner Oracle has set the environment referring to the destination database, complete the following step:

Step 3.4 - Repeat the roll forward phase 3 (3.1 - 3.3) until the date of migration, and then proceed to stage 4, for the final incremental backup

STAGE 4 - FINAL INCREMENTAL BACKUP

During this stage, the desired source database tablespaces are changed to READ ONLY for making destination data files to consistent with the source database incremental backups by applying a final incremental backup.

Step 4.1 Modify source tablespace(s) to READ ONLY:

Step 4.2 Create the one last incremental backup of the desired tablespaces being transported on the source system:

Note: As tablespace is in read-only mode, you will receive warnings in the backup logs. You can ignore the same.

Step 4.3 Transfer the final backups and res.txt to the destination system

Step 4.4 Apply final incremental backup to the target transported tablespace data files.

STAGE 5 - IMPORT METADATA OBJECTS INTO DESTINATION DATABASE

Import across sqlnet.

Step 5.1 Create the datapump directory on the target database and grant privilege on the Source system.

Step 5.2 Generate new xttplugin.txt for network import On the source database server, log in as the database Operating system owner to set the environment referring to the source database. Execute the following statement.

This will generate xttplugin.txt with sample import script including datafiles of all transportable tablespace transfer this to the target database.

Step 5.3 Create a network link on the target database for the metadata import.

Step:5.4 Modify xttplugin.txt for the import script with the directories and database links we created at step 5.3 and step 5.1.

(This step restores metadata and tablespace won’t take much longer)

Step 5.5 Import metadata using impdp utility script generated from modified xttplugin.txt for the final data restore.

STAGE 6: VALIDATE THE TRANSPORTED TABLESPACE

Step 6.1 Check tablespace for corruption:

At this stage, the migrated tablespace is READ ONLY in the destination database. Perform validation specific to the application by comparing objects to verify the transported data.

Also, run RMAN for physical and logical block corruption of transported tablespace of a specific pluggable database by running VALIDATE TABLESPACE as follows:

Step 6.2 Modify the tablespace(s) to READ WRITE mode in the destination database

Step 6.3 Compare source and target objects and recreate manually if any missing objects to validate the application, if everything works, the migration process is complete.

STAGE 7 REMOVE BACKUP FILES

Files created by this process are huge and can be deleted post migration. These files are available in the following path Source (src_scratch_location) location files on the source system

• dest_scratch_location location files on the destination system
• $TMPDIR location files in both source and destination systems

Conclusion

By using cross platform incremental backups with transportable tablespace migration can be done with reduced downtime across platforms. However, datatype restriction and limitation apply to transportable tablespace please refer to oracle article (Doc ID 1454872.1) for the complete supported platform and datatype.