Cross Platform Migration to Exadata Database Service on Dedicated Infrastructure with Cloud Native DR

Modernisation is an on going process and I am sure some of you might have dealt with it in the past and some of you might deal with it in future as well.

There are lot of ways to Modernise your Oracle databases and one of them is to move to Exadata Database Service on Dedicated Infrastructure in OCI.

You may need to tackle lot of things as part of database migration and one of them will be to choose right method or tools for migration.

Choosing the right method gets tougher when there is a requirement to have a physical standby immediately after the migration.

Things get more complex when you are dealing with cross platform and cross Endian migration.

Can Cross Platform Transportable Tablespace handle this scenario ?

Yes , it is possible when you take care of certain things.

Tip : For Same Endian migration please refer to my previous blog

Let’s explore.

Below is high level summary of how we are planning to get this done.

Perform level 0 backup on the source database.
Restore the level 0 backup on target primary and standby database.
Perform level 1 backup on source database and apply on the target primary and standby . Repeat this procedure until final incremental is taken.
Downtime begins
Source database data tablespaces converted to READ ONLY.
Final incremental backup on source database and metadata export.
Apply incremental backup on target primary and standby database.
Metadata import on target primary.
Downtime ends.

Below is my environment.

Source primary database: xpa_dbcsp ( Oracle Base Database in Frankfurt )

Source standby database : xpa_jp4_fra ( Oracle Base Database in Frankfurt)

Source DB Version : 19.26

Target primary database : xpa_exap (Oracle ExaDB-D in Frankfurt VM cluster 2)

Target standby database : xpa_exas (Oracle ExaDB-D in Frankfurt VM Cluster 3)

Target DB version : 19.26

ExaDB-D stands for Exadata Database Service on Dedicated Infrastructure.

My source database is on Linux Platform , however this article is will work for source databases on big endian platform.

I have divided the steps in to multiple sections as below so that it is easy to navigate.

Step 0 – Prerequisites

Step 1 – Prepare M5 scripts

Step 2 – Level 0 Backup and Restore

Step 3 – Level 1 Backup and Apply

Step 4 – Final Level 1 Backup and Apply

Step 5 – Metadata Import

Step 6 – Post Migration

Let’s explore one step at a time.

Step 0 – Prerequisites

a. General prerequisites

We will be using M5 Cross Platform Transportable Tablespace using Full Transportable Data Pump Export/Import procedure for this article with some additional steps to handle the physical standby in the target.

Please refer MOS note : 2999157.1 which lists down all the prerequisites for using Cross Platform Transportable Tablespace using Full Transportable Data Pump Export/Import .

b. Target database provisioning

I have created source databases (primary and one physical standby) in Oracle Base Database in OCI for this article . You may already have a database which you need to migrate.

Please create a target primary database in ExaDB-D and use console to create a physical standby database in another VM Cluster (recommended on a different ExaDB-D Infrastructure ). Ensure to select same database and national character set as the source database (This is recommended).

Please note that these target databases are brand new and doesn’t have any data. As part of migration using M5 XTTS we will be bringing the tablespaces from source and will plug them into the target primary and standby database.

c. Configure file system for RMAN XTTS backup

We would require a file system to keep the RMAN backups and this can be local file system or a NFS.

I would recommend to use NFS so that you don’t have to copy the backups as wells as M5 scripts from source database host to target primary and target standby host.

I have used FSS in OCI for this article and I have mounted FSS on source primary , target primary and target standby.

d. Apply DataPump proactive patches on both source and target databases.

This step is already part of the MOS note mentioned earlier , however I would like to highlight it once again.

We have to apply DataPump Bundle patch based on the database version.

I have applied 19.26 DataPump Bundle patch.

Step 1 – Prepare M5 scripts

We are going to use M5 scripts for this migration.

Please download the latest scripts from MOS note : 2999157.1

We have to make modification to below scripts based on our environment.

dbmig_driver.properties : This has details about our source database home , PDB Name etc.

dbmig_ts_list.txt : This file has details about the tablespaces that are going to be migrated.

I have created this file with below contents.

USERS,SALES_TS,MARKETING_TS

Step 2 – Level 0 Backup and Restore

a. Initiate level 0 backup

Let us initiate level 0 backup of source database using below command.

sh dbmig_driver_m5.sh L0

This will initiate level 0 backup on source database and prepare RMAN command for restore (on target).

Below is sample output.

Please check the backup log file for any errors or warnings.

b. Restore level 0 backup on target primary

We will restore the level 0 backup of source database on the target primary database.

Backup pieces are already available for the target database since we are using NFS.

I have issued below command for restore.

rman target sys@XPA_PDB1 cmdfile=/mnt/backup/xtts/cmd/restore_L0_xpa_250821145337.cmd

Below is sample output.

Check the restore log file to ensure it is successful.

Rename the RMAN restore logfile to some other name (to identify it is related to target primary) since next step (restore on target standby) will create restore logfile with same name.

c. Restore level 0 backup on target standby

We will restore the level 0 backup of source satabase on the target standby database as well.

Backup pieces are already available for the target standby database since we are using NFS.

I have issued below command for restore .

rman target / cmdfile=/mnt/backup/xtts/cmd/restore_L0_xpa_250821145337.cmd

As you might have noticed I have used RMAN connection to CDB (as opposed to PDB in previous step ) since there is no PDB service available for standby database . I haven’t observed any issues due to this.

Below is sample output.

Check the restore logs to ensure restore is successful.

Rename the RMAN restore logfile to some other name to identify that it belongs to standby database.

Step 3 – Level 1 Backup and Apply

a. Initiate level 1 backup

Let us initiate level 1 backup of source database using below command.

sh dbmig_driver_m5.sh L1

This will initiate level 1 backup on source database and prepare RMAN command for restore.

Below is sample output.

Please check the backup log files for any errors or warnings.

b. Apply level 1 backup on target primary

We will apply the level 1 backup of source database on the target primary database.

I have issued below command for level 1 backup apply.

rman target sys@XPA_PDB1 cmdfile=/mnt/backup/xtts/cmd/restore_L1_xpa_250821153353.cmd

Below is sample output.

Check the restore logfile for any issues.

Rename the RMAN restore logfile to some other name (to identify it is related to target primary) since next step (Apply on target standby) will create restore logfile with same name.

c. Apply level 1 backup on target standby.

We will apply the level 1 backup of source database on the target standby database.

I have issued below command for level 1 backup apply.

rman target / cmdfile=/mnt/backup/xtts/cmd/restore_L1_xpa_250821153353.cmd

Below is sample output.

Rename the RMAN restore logfile to some other name to identify that it belongs to standby database.

We have to repeat all the tasks in this step (Step 3) as many times as we need until we are ready for downtime window.

Step 4 – Final Incremental Backup and Apply

Once we are in downtime window we can proceed with final backup and restore.

Bring down the application before proceeding with next steps.

a. Initiate final level 1 backup and metadata export

Let us initiate final level 1 backup of source database using below command.

sh dbmig_driver_m5.sh L1F

Above command will place the tablespaces in read only and proceed with final level 1 backup followed by metadata export.

It will also create the restore command to be used in next steps.

Below is sample output.

Please check the backup log file and export log file for any errors or warnings.

b. Apply final level 1 backup on target primary

We will restore the final level 1 backup of source database on the target primary database.

Steps are similar to the previous restore steps (level 0 and level 1) except we will be using command file generated by final level 1 backup.

I have used below command.

rman target sys@XPA_PDB1 cmdfile=/mnt/backup/xtts/cmd/restore_L1F_xpa_250822085028.cmd

Below is sample output.

Rename the RMAN restore logfile to some other name (to identify it is related to target primary) since next step (Apply on target standby) will create restore logfile with same name.

c.Apply final level 1 backup on target standby

We will apply the final level 1 backup of source database on the target standby database.

I have issued below command for final level 1 backup apply.

rman target / cmdfile=/mnt/backup/xtts/cmd/restore_L1F_xpa_250822085028.cmd

Below is sample output.

Check restore log file for any errors or warnings.

I have also renamed the logfile to reflect it is related to standby.

Step 5 – Metadata Import Preparation

This step is a slightly different from the step for metadata import mentioned as part of M5 procedure.

Regular M5 procedure will plug in tablespaces with their actual restored datafile names which is not suitable for target database with physical standby since restored data file names are different on both databases due to OMF and ASM . This will result in managed recovery unable to find correct files on the standby database. You can definitely resolve this manually after migration , however doing it for a database with hundreds of datafiles will be time consuming and your standby will be out of sync until you fix the issue.

To avoid standby database recovery failing with incorrect file names , We will create alias for each datafile for primary and standby database and use those alias for metadata import.

We will tackle this with multiple steps.

a. Prepare DataPump Import parameter file.

Let us prepare import parameter file using the impdp.sh script from M5.

Please modify below details in the impdp.sh to reflect target primary details.

ORACLE_HOME: The path to the Oracle home of the destination database.

ORACLE_SID: SID of the destination database.

ORACLE_CONNECT_STRING: A connect string directly into the destination non-CDB database or destination PDB.

DATA_PUMP_PARALLEL: Data Pump parallel level for the import. Use if the destination database is Oracle Database 21c or later.

DATA_PUMP_TRACE: Data Pump trace level. Set to 0 to disable trace.

I have executed below command to generate the import parameter file.

sh impdp.sh exp_xpa_250822085028.dmp log/restore_L1F_xpa_250822085028.log.primary test Y

Below are contents of parameter file.

We can’t use the above parameter file directly since physical standby will look for exact file name in standby.

Let us create ASM alias for each datafile and use that alias for metadata import.

b. Prepare ASM alias for target primary and standby

We can prepare ASM alias manually when we have limited number of data files , however when you have hundreds of datafiles it may not be efficient.

I have prepared below shell script which will prepare SQL statement for ASM alias creation.

restore_log=$1
target_dg=$2
cat $restore_log | grep "restoring foreign file" | awk '{print $6,$8}' > datafile.lst
rm add_alias.sql
IFS=$'\n'
for i in `cat datafile.lst `
do
fno=`echo $i | awk '{print $1}'`
fname=`echo $i | awk '{print $2}'`
echo "ALTER DISKGROUP $target_dg ADD ALIAS '+$target_dg/DF_ALIAS_$fno' FOR '$fname';" >> add_alias.sql
done

Save above script as create_alias.sh.

We can execute the script as below to get the sql file.

sh create_alias.sh <RMAN final level 1 restore log file> <target data disk group>

Below is the content of the sql file generated.

ALTER DISKGROUP DATAC4 ADD ALIAS ‘+DATAC4/DF_ALIAS_13’ FOR ‘+DATAC4/XPA_EXAP/3C41E79ADA942FF8E0635B00000A57E1/DATAFILE/sales_ts.787.1209740987’;
ALTER DISKGROUP DATAC4 ADD ALIAS ‘+DATAC4/DF_ALIAS_14’ FOR ‘+DATAC4/XPA_EXAP/3C41E79ADA942FF8E0635B00000A57E1/DATAFILE/marketing_ts.371.1209740987’;
ALTER DISKGROUP DATAC4 ADD ALIAS ‘+DATAC4/DF_ALIAS_12’ FOR ‘+DATAC4/XPA_EXAP/3C41E79ADA942FF8E0635B00000A57E1/DATAFILE/users.659.1209740987’;

We need to run the command again with target standby details since SQL statement will be different for target standby.

c. Create ASM alias for target primary and standby.

We will now create ASM alias in target primary and standby using sql statement generated in previous step.

Connect to ASM instance and execute the sql file generated in previous step.

Below is sample output for target primary.

Repeat the same procedure for target standby.

d. Modify DataPump Import parameter file.

Let’s us modify the TRANSPORT_DATAFILES parameter for the import with ASM alias names.

Below is the modified parameter file.

e. Modify db_file_name_convert in target standby.

This step is only required when the data Disk Group name is different on primary and standby.

In my case primary data disk group is DATAC4 and standby data disk group is DATAC5 which means even if we use ASM alias our standby database will complain about not able to find the file since the path of file is different due to disk group name change.

I have set db_file_name_convert as below.

db_file_name_convert=’+DATAC4/DF_ALIAS’,’+DATAC5/DF_ALIAS’

f. Create Guaranteed Restore point.

Create a guaranteed restore point on target primary database pluggable database using below command.

create restore point <restore_point_name> guarantee flashback database;

g. Perform Metadata Import.

Now we are all set to do the metadata import.

Execute below.

impdp <modified_parfile from step d>

Check the import log files to ensure everything looks good.

h. Verification

We can perform multiple checks to ensure migration has successful and one critical piece will be to check that all the objects that we need has been migrated from source to target. There are various scripts that each DBA uses to perform this task , I am not explaining them here.

I am curious to understand how the datafiles looked like in primary and standby.

Below is output from primary database.

Below is output from standby database.

As you can see migrated datafiles in primary and standby are reflecting alias names rather than actual names which is expected.

We will correct this as part of post migration.

Step 6 : Post Migration

a. Monitor physical standby

We have performed multiple additional steps in addition to M5 procedure to ensure we can have a standby in place right after migration.

Check the alert logs , DG broker to ensure that our physical standby is able to apply all the changes which are part of migration.

b. Rename migrated data files.

As we have seen in earlier step , datafiles in primary and standby are still reflecting the alias instead of real names.

Let’s use online datafile move to fix this.

Execute command similar to below.

alter database move datafile ‘<current_file_name’>

Below is sample output.

Check the datafile names.

Everything looks good now!

Repeat the same procedure for standby datafiles as well.

c. Enable encryption

You may not be having TDE encryption for your tablespaces in the source database , however it is mandatory to have TDE for your tablespaces in ExaDB-D.

Perform required steps to encrypt tablespaces online . Please note that you don’t have to create any master key for TDE since it is already created as part of database provisioning.

You can refer one of my previous blog in case you need steps for encryption of tablespaces.

That’s all , we have done with the Cross Platform Migration to a Target database with existing standby and the standby is immediately available after migration.

Does this procedure work with source database with encrypted tablespaces ?

Yes , it will work . However you need export/import keys of your source PDB to target standby PDB before you can perform the metadata import on the target primary database otherwise standby database MRP will complain about not able to find the Master key for the plugged in tablespaces.

Can you perform XTTS migration to the target primary database and later plug the tablespaces to standby ?

There is separate procedure that you can use in case you don’t have a requirement to have standby PDB immediately available after the migration. I will blog about it soon!

Happy Migration!

Important Tip : Always test the procedure multiple times on a test environment before using for Production Migration.

Lessons from my journey with databases