Migrate Oracle Database to OCI Using M5 Cross-Endian XTTS with RMAN Incremental Backups

Introduction

This tutorial describes how to migrate an Oracle Database to Oracle Cloud Infrastructure (OCI) using M5 Cross Endian Platform Migration with Full Transportable Data Pump and RMAN incremental backups.

This method enables migration between systems with different endian formats while minimizing application downtime.

The process begins with a full backup copy from the source to the destination. Then, incremental backups are applied to keep the destination nearly synchronized with the source. Downtime is required only during the final incremental backup and metadata export/import.

Environment Details

Objectives

Prerequisites

Task 1: Prepare the Source Database

  1. Run the following query to check the endian format on source database.

    col PLATFORM_NAME for a50
select platform_name, platform_id, endian_format from v$transportable_platform;

  2. Identify the tablespace(s) in the source database that will be transported. Run the following query if you are migrating all the tablespaces.

    select tablespace_name from dba_tablespaces where contents='PERMANENT' and tablespace_name not in ('SYSTEM','SYSAUX');

  3. Check transport set violations:

    exec dbms_tts.transport_set_check('<comma-separated tablespace list>');
SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;

  4. (Optional) Enable the block change tracking on source database to improve the performance of incremental backups.

  5. Verify that all tablespaces are online on the source database.

Task 2: Prepare the Target Database

  1. Create a new users tablespace.

    select TABLESPACE_NAME from dba_tablespaces where TABLESPACE_NAME='USERS_OCI';
select TABLESPACE_NAME from dba_tablespaces order by 1;
set long 99999
SELECT DBMS_METADATA.GET_DDL ('TABLESPACE','USERS') from dual;

    Use the above Data Definition Language (DDL) output and replace the diskgroup name as below and create new users tablespace.

    CREATE BIGFILE TABLESPACE USERS_OCI DATAFILE '+DATAC2'
SIZE 1073741824
AUTOEXTEND ON NEXT 17179869184 MAXSIZE 33554431M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
ENCRYPTION USING 'AES256' ENCRYPT DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;

    Assign the new users tablespace USERS_OCI to all the database users who have default tablespace USERS using the following query.

    select 'alter user '||username||' default tablespace USERS_OCI;' from dba_users where default_tablespace='USERS';

    The output for above select query must return 0 after re-assigning new users tablespace.

    select count(*) from dba_users where default_tablespace='USERS';
ALTER DATABASE DEFAULT TABLESPACE USERS_OCI;
drop tablespace users including contents and datafiles;
select TABLESPACE_NAME from dba_tablespaces where TABLESPACE_NAME='USERS';

  2. Create temporary tablespaces on the target database. Run the following query on source and gather the tablespace details.

    Run the following query on the source:

    select TABLESPACE_NAME from dba_tablespaces where CONTENTS='TEMPORARY';

    Then create temporary tablespaces on the target database. For example:

    create bigfile temporary tablespace USER_TEMP tempfile size 672m autoextend on next 672m extent management local;

Task 3: Incremental M5 XTTS Steps

Task 3.1: Initial Phase

  1. Download the latest M5 scripts and unzip from here: M5 Cross Endian Platform Migration using Full Transportable Data Pump Export/Import and RMAN Incremental Backups (Doc ID 2999157.1). Create a working directory on an NFS share that is shared between source and destination. The path to the NFS share should be identical on both source and destination.

    If an NFS share is not possible, use any local storage. Create the directory on the source host and copy it to the destination host. This needs to be repeated multiple times throughout the process. The path on the source and destination host should be identical. If not, various changes are required to the scripts which is beyond the scope of this procedure.

    mkdir -p <Shared_location>/XTTS_Migration
mkdir -p <Shared_location>/XTTS_Migration/Backup
mkdir -p <Shared_location>/XTTS_Migration/DPPUMP
cd <Shared_location>
unzip DBMIG.zip

    The file contains:

    • dbmig_driver_m5.sh: Migration driver script.
    • impdp.sh: Import driver script.
    • log directory: Created at runtime. Stores the RMAN backup log, migration logs, and additional logs.
      • rman_mig_bkp.log: Centralized migration logfile.
      • rman_mig_bkp.lck: Lock file created at the beginning of every backup to prevent concurrent driver execution. If a driver execution aborts the lock will not be removed. The problem should be investigated and resolved prior to removing the lock.
    • cmd directory: Created at runtime. Stores the RMAN backup and restore scripts.
      • dbmig_driver.properties: Environment variables.
      • dbmig_ts_list.txt: Tablespace list with comma separated.

  2. Modify the variables in dbmig_driver.properties (in cmd directory) to reflect the environment you are using, see the description of parameters in configuration file dbmig_driver.properties section here: M5 Cross Endian Platform Migration using Full Transportable Data Pump Export/Import and RMAN Incremental Backups.

    dbmig_driver.properties sample file

    ############################################################
#Source database properties
#my_M5_prop_version=2
# -
# - ORACLE_HOME Path to Oracle Home
# - ORACLE_SID SID of the source database
# - SRC_SCAN Connect string to source database via SCAN.
# If no SCAN, specify source database network name.
# Enclose in single quotes
# Example: '@myhost-scan/db1'
# Example: '@localhost/pdb1'
# - MIG_PDB Accepted values: 0, 1
# Choose 0 if source is non-CDB
# Choose 1 if source is a PDB
# - PDB_NAME If source is a PDB, specify PDB name.
# Else leave blank
# Example: PDB1
# - BKP_FROM_STDBY Accepted values: 0, 1
# Choose 0 to back up from primary database,
# or if Data Guard is not in use.
# Choose 1 to back up from standby database.
############################################################
export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=soldb1918
export SRC_SCAN='@scan1/soldb1918'
export MIG_PDB=0
export PDB_NAME=
export BKP_FROM_STDBY=0
############################################################
#Source Data Pump settings
# - SOURCE_DPDMP Directory path of the directory DATA_PUMP_DIR
# Example: /u01/app/oracle/m5/data_pump_dir
# - SOURCE_DPIR Data Pump Directory, typically DATA_PUMP_DIR
# - SYSTEM_USR Username for Data Pump export.
# Do not use SYS AS SYSDBA
# Example: SYSTEM
# - DP_TRACE Data Pump trace level.
# Use 0 to disable trace.
# Use 3FF0300 to full transportable tracing
# See MOS Doc ID 286496.1 for details.
# - DP_PARALLEL Data Pump parallel setting.
# Accepted values: 1 to 999
# Example: 16
############################################################
export SOURCE_DPDMP=<Shared_location>/XTTS_Migration/DPPUMP
export SOURCE_DPDIR=DATA_PUMP_DIR
export SYSTEM_USR=SYSTEM
export DP_TRACE=0
export DP_PARALLEL=1
export DP_ENC_PROMPT=N
############################################################
#Source RMAN settings
# - BKP_DEST_TYPE Accepted values: DISK, SBT_TAPE
# Choose DISK to backup up to local storage
# Choose SBT_TAPE to use ZDLRA
# - BKP_DEST_PARM If BKP_DEST_TYPE=DISK, enter location for backup:
# Example: /u01/app/oracle/m5/rman
# If BKP_DEST_TYPE=SBT_TAPE, enter channel configuration:
# Example: "'%d_%U' PARMS \"SBT_LIBRARY=<oracle_home>/lib/libra.so,SBT_PARMS=(RA_WALLET='location=file:<oracle_home>/dbs/zdlra credential_alias=<zdlra-connect-string>')\""
# - CAT_CRED If you use RMAN catalog or ZDLRA, specify connect string to catalog database
# Example: <scan-name>:<port>/<service>
# - SECTION_SIZE Section size used in RMAN backups
# - CHN Number of RMAN channels allocated
############################################################
export BKP_DEST_TYPE=DISK
export BKP_DEST_PARM=<Shared_location>/XTTS_Migration/Backup
export CAT_CRED=
export SECTION_SIZE=64G
export CHN=8
############################################################
#Destination host settings
#If specified, the script transfers the RMAN backups and
#Data Pump dump file to the destination via over SSH.
#SSH equivalence is required.
# - DEST_SERVER Network name of the destination server.
# Leave blank if you manually transfer
# backups and dump files
# - DEST_USER User for SSH connection
# Example: oracle
# - DEST_WORKDIR The script working directory on destination
# Example: /u01/app/oracle/m5
# - DEST_DPDMP The directory path used by DATA_PUMP_DIR
# in destination database
# Example: /u01/app/oracle/m5/data_pump_dir
############################################################
export DEST_SERVER=srvadm01
export DEST_USER=oracle
export DEST_WORKDIR=<Shared_location>/XTTS_Migration
export DEST_DPDMP=<Shared_location>/XTTS_Migration/DPDUMP
############################################################
#Advanced settings
#Normally, you don't need to edit this section
############################################################
export WORKDIR=$PWD
export LOG_DIR=${WORKDIR}/log
export CMD_DIR=${WORKDIR}/cmd
export PATH=$PATH:$ORACLE_HOME/bin
export DT='date +%y%m%d%H%M%S'
export CMD_MKDIR='which mkdir'
export CMD_TOUCH='which touch'
export CMD_CAT='which cat'
export CMD_RM='which rm'
export CMD_AWK='which awk'
export CMD_SCP='which scp'
export CMD_CUT='which cut'
export CMD_PLATFORM='uname'
if [[ "$CMD_PLATFORM" = "Linux" ]]; then
export CMD_GREP="/usr/bin/grep"
else
if [[ "$CMD_PLATFORM" = "AIX" ]]; then
export CMD_GREP="/usr/bin/grep"
else
if [[ "$CMD_PLATFORM" = "HPUX" ]]; then
export CMD_GREP="/usr/bin/grep"
else
export CMD_GREP='which ggrep'
fi
fi
fi
export my_M5_prop_version=2

    Create directories for datapump dumpfiles on source and target databases.

    create directory M5_XTTS_MIG as '<Shared_Location>/XTTS_Migration/DPDUMP';
grant read,write on directory M5_XTTS_MIG  to sys,system;

Task 3.2: Level 0 Backup & Restore

  1. On the source system, log in as the Oracle user with the environment variables (ORACLE_HOME and ORACLE_SID) set to the source database., then run the backup:

    cd <Shared_location>
nohup sh dbmig_driver_m5.sh L0 &

  2. If not using a shared NFS, copy the working directory to the destination host.

  3. Restore the data files on the destination system.

    On destination, set the environment to the destination database. The driver script creates a restore script. It is stored in the cmd directory:

    cd <Shared_location>/XTTS_Migration
cp nohup.out nohup.out_L0
ls -ltra cmd/restore_L0_*
rman target / cmdfile=<restore_cmdfile_name>

    Check the RMAN log file:

    egrep "WARN-|ORA-" log/restore_*.log

Task 3.3: Roll Forward Phase

During this phase, an incremental backup is created on the source database and transferred to the destination system.

The backup is converted to the destination endian format and applied to the destination datafiles. This process can be repeated multiple times to keep the destination database synchronized.

Each incremental backup is smaller than the previous one and reduces the final downtime window.

Note: Multiple backups can be executed against the source without applying them to the destination.

  1. On source, execute a level 1 (L1) incremental backup of the source database:
   cd <Shared_location>/XTTS_Migration
   cp nohup.out nohup.out_1
   nohup sh dbmig_driver_m5.sh L1 &

  1. If not using a shared NFS, copy the working directory to the destination host.

  2. Apply the incremental backup to the datafile copies on the destination system. On the destination system, log in as the Oracle user with the appropriate environment variables set, run the roll forward datafiles step as follows.

On the destination system, log in as the Oracle user with the appropriate environment variables set. The driver script creates a restore script. It is stored in the cmd directory: Restore the level 1 (L1) backup on the destination database using the latest restore script.

   cd <Shared_location>/XTTS_Migration
   ls -ltra cmd/restore_L1_*
   cp nohup.out nohup.out_3
   rman target / cmdfile=<restore_cmdfile_name>

Check the RMAN log file:

   egrep "WARN-|ORA-" log/restore_*.log

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

Repeat the roll forward phase until the database cutover window.

Task 3.4: Final Incremental Backup Phase

  1. Disable the following audit related parameters on source and target until the completion of import. Re-enable them after import is completed:
   show parameter audit_sys_operations
   show parameter audit_trail
   alter system set audit_trail=none scope=spfile sid='*';
   alter system set audit_sys_operations=FALSE scope=spfile sid='*';
   srvctl stop database -d ${ORACLE_UNQNAME}
   srvctl start database -d ${ORACLE_UNQNAME}
   show parameter audit_sys_operations
   show parameter audit_trail
  1. On source, execute the final level 1 (L1) incremental backup of the source database:

The script performs the following: - Prompts for the system password - Sets tablespaces to read-only mode - Executes the final incremental backup - Runs Data Pump export

   cd <Shared_location>/XTTS_Migration
   cp nohup.out nohup.out_1
   nohup sh dbmig_driver_m5.sh L1F &

  1. If not using a shared NFS, copy the working directory to the destination host.

  2. Apply last incremental backup to destination datafiles.

On destination, set the environment to the destination database. The driver script creates a restore script. It is stored in the cmd directory:

   cd <Shared_location>/XTTS_Migration
   ls -ltra cmd/restore_L1F_*

Restore the final level 1 (L1) backup on the destination database using the final restore script.

   cd <Shared_location>/XTTS_Migration
   cp nohup.out nohup.out_5
   nohup rman target / cmdfile=<restore_cmdfile_name> &

Check the RMAN log file:

   egrep "WARN-|ORA-" log/restore_*.log

Task 3.5: Data Pump Import

  1. On destination, edit the import driver script (impdp.sh). Use information about the destination database to fill in the variables.

    • ORACLE_HOME: Path to Oracle home
    • ORACLE_SID: Destination database SID
    • ORACLE_CONNECT_STRING: Connection string
    • DATA_PUMP_PARALLEL: Parallel level
    • DATA_PUMP_TRACE: Trace level

The import driver script takes four parameters:

expdp_dumpfile: Name of the Data Pump dump file, e.g., exp_UP19_240206134155.dmp
rman_last_restore_log: Relative path to the last RMAN restore log
run-mode: One of the below:
    test: Generates the Data Pump parameter file. Does not start Data Pump.
    test-readonly. Generates the Data Pump parameter file and adds TRANSPORTABLE=KEEP_READ_ONLY to the Data Pump parameter file. Review the documentation for the usage of this parameter. Does not start Data Pump.
    run: Generates the Data Pump parameter file. Starts Data Pump to perform the import.
    run-readonly: Generates the Data Pump parameter file and adds TRANSPORTABLE=KEEP_READ_ONLY to the Data Pump parameter file. Review the documentation for the usage of this parameter. Starts Data Pump to perform the import.

  DP_ENC_PROMPT: 
     One of the below
        Y: Will prompt the user for an encryption password in case one was used for the export
        N: Will not prompt the user for an encryption password.

Start the import driver script in test mode to verify the correctness of the generated Data Pump parameter file:

   cd <Shared_location>/XTTS_Migration
   sh impdp.sh <expdp_dumpfile> <rman_last_restore_log> test N

The driver script generates the parameter file in the current directory. Examine it.

$ vi imp_<oracle_sid>_<timestamp>_xtts.par

Do not change the generated Data Pump parameter file. If you need to change the parameter file, change the code in the driver script, so it generates the desired parameter file.

Start the import driver script in run mode to perform the Data Pump import:

   cd <Shared_location>/XTTS_Migration
   sh impdp.sh <expdp_dumpfile> <rman_last_restore_log> run N

Always review the Data Pump log file. Even if the Data Pump is listed as successful, you should always review the Data Pump log file for any critical errors.

  1. Re-enable the audit parameters on source and target databases after import is completed.
   show parameter audit_sys_operations
   show parameter audit_trail
   alter system set audit_trail=<value before migration taken above> scope=spfile sid='*';
   alter system set audit_sys_operations=<value before migration taken above> scope=spfile sid='*';
   srvctl stop database -d ${ORACLE_UNQNAME}
   srvctl start database -d ${ORACLE_UNQNAME}
   show parameter audit_sys_operations
   show parameter audit_trail
  1. Validate the transported data.
   rman target sys/<password>@<target_db_name> trace=rman_trace.trc log=<backup_location>/tablespace_validate.log
   validate tablespace <comma-separated tablespace list> check logical;
  1. Assign users tablespace to revert back the changes made in the target database preparation.
   select 'alter user '||username||' default tablespace users;' from dba_users where default_tablespace='USERS_OCI';

  Execute the alter commands generated by above select query.

   select count(*) from dba_users where default_tablespace='USERS_OCI';
   ALTER DATABASE DEFAULT TABLESPACE USERS;
   drop tablespace USERS_OCI including contents and datafiles;
   select TABLESPACE_NAME from dba_tablespaces where TABLESPACE_NAME='USERS';

Validate the data on source and target databases.

Related Links

Acknowledgments

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.