X

Database Migration to Oracle Cloud Infrastructure: Migrating Oracle Database from On-Premises or Other Cloud Providers to Oracle DBaaS Using RMAN Backup Sets

Rohit Chandok
Cloud Architect at OCI Development, Inbound Product Management

 


This post is part of the “Database Migration to Oracle Cloud Infrastructure blog series", which includes the posts related to database migration. Use these posts as building blocks for various migration approaches.

For more information on Oracle Database and Exadata Cloud Services, review the details at Oracle Cloud Infrastructure - Database


 

This post provides reference steps to help you migrate Oracle Database from on premises or from other cloud providers or from Oracle Cloud Infrastructure Classic to Oracle Cloud Infrastructure Database (DBaaS) by using RMAN backup sets. This post covers the use of incremental backups. The idea is that as long as backups are complete and consistent, you can stagger the level 0 and level 1 backups over a period of time for the final restore.

 

This post assumes that Transparent Data Encryption (TDE) is enabled at the source, so you should determine whether your source database uses TDE. TDE is mandatory for all Oracle Cloud Infrastructure databases. If TDE not used at the source, enable it either at the source or at the target. Be sure to back up and restore the required TDE wallets from the source to the target.

This sample migration workflow covers the following tasks:

  1. Evaluate and Plan
  2. Back Up the Source Database
  3. Perform Incremental Backups of the Source Database
  4. Prepare the Target Database for the Restore
  5. Restore and Recover the Database at the Target

Evaluate and Plan

Use the Evaluation and Planning Checklist to help you evaluate and plan for the migration of your databases to Oracle Cloud Infrastructure, based on the unique requirements of your source and target databases.

Back Up the Source Database

  1. Connect to the source database, enable backup encryption, and set the compression to medium.

    rman target /
    set encryption on;
    set compression algorithm 'medium';

    For example (click image for larger view):

  2. Perform a level 0 backup, which is equivalent to taking a full backup.

    run
    {
        configure controlfile autobackup off;
        backup as compressed backupset
        device type disk
        tag dta_level0
        cumulative incremental level 0
        format '/u01/nfs/l0_%T_%d_set%s_piece%p_%U'
        section size 24g
        database
        include current controlfile spfile
        plus archivelog
        format '/u01/nfs/l0_%T_%d_set%s_piece%p_%U';
    }

    For example (click image for larger view):

    .......

    .......

    .......

    .......

    Note: Record the backup piece name of the control file backup. You will need this to restore the control file at the target.

  3. Copy the password file and TDE wallet files.

    cp $ORACLE_HOME/dbs/orapwrohitdb /u01/nfs/.
    zip -rj /u01/nfs/tde_wallet.zip /u01/app/oracle/admin/rohitdb/tde_wallet
    

    For example (click image for larger view):

  4. Transfer the backups to Oracle Cloud Infrastructure Object Storage. Use the information in Data Transfer Guidance and Transfer Options.

Perform Incremental Backups of the Source Database

  1. Perform optional incremental backups, as needed.

    set encryption on;
    set compression algorithm 'medium';
    run
    {
        backup as compressed backupset
        device type disk
        tag dta_level1
        cumulative incremental level 1
        format '/u01/nfs/l1_%T_%d_set%s_piece%p_%U'
        section size 24g
        database
        include current controlfile spfile
        plus archivelog
        format '/u01/nfs/l1_%T_%d_set%s_piece%p_%U';
    }

    For example (click image for larger view):

    .....

    .....

    .....

    .....

    Note: Record the backup piece name of the control file backup. You will need this to restore the control file at the target.

  2. Transfer the incremental backups to Object Storage. Use the information in Data Transfer Guidance and Transfer Options.

Prepare the Target Database for Restore

  1. Create the target database in Oracle Cloud Infrastructure.

    To ensure that the target database has all the required metadata for Oracle Cloud Infrastructure tooling to work, create the target database by using one of the supported methods: Oracle Cloud Infrastructure Console, CLI, or Terraform provider. This target database will be cleaned to be used as a shell for the migration, as needed.

  2. Configure the Oracle Database Cloud Backup Module.

    Configuring the Cloud Backup Module for Existing or Fresh Backups provides an example of how to configure the Cloud Backup Module to point to the Object Storage backup bucket. For details, including variables and commands, see Installing the Oracle Database Cloud Backup Module.

  3. Stop the target database.

    . oraenv
    rohitdb
    export ORACLE_UNQNAME=rohitdb_phx3bv
    srvctl stop database -d rohitdb_phx3bv
    

    For example (click image for larger view):

  4. Manually clean up the target database.

    From Oracle:

    rm /opt/oracle/dcs/commonstore/wallets/tde/rohitdb_phx3bv/*
    rm $ORACLE_HOME/dbs/orapwrohitdb
    

    For example (click image for larger view):

    From grid:

    asmcmd ls +DATA/rohitdb_phx3bv
    asmcmd rm -rf +DATA/rohitdb_phx3bv/CHANGETRACKING
    asmcmd rm -rf +DATA/rohitdb_phx3bv/DATAFILE
    asmcmd rm -rf +DATA/rohitdb_phx3bv/TEMPFILE
    asmcmd rm -rf +DATA/rohitdb_phx3bv/69F189055AB223CEE053D62DC40ABE06
    asmcmd rm -rf +DATA/rohitdb_phx3bv/73BBBF30958C4846E0530D01000AE2B2
    asmcmd ls +RECO/rohitdb_phx3bv
    asmcmd rm -rf +RECO/rohitdb_phx3bv/* 
    

    For example (click image for larger view):

    Note: Do not delete the parameter file.

  5. Copy the source password file and TDE wallet files at the target location.

    wget https://objectstorage.us-phoenix-1.oraclecloud.com/p/jqpeWTkKbsbsGPphsjuIn0oAVkchH-4hCxuVrtsYPE8/n/sic-dbaas/b/rohit-backups/o/orapwrohitdb
    wget https://objectstorage.us-phoenix-1.oraclecloud.com/p/R7zIMbjv-bV0TldZPntONowVyLtD1ljSljEhE8xL0Ro/n/sic-dbaas/b/rohit-backups/o/tde_wallet.zip
    cp orapwrohitdb $ORACLE_HOME/dbs/.
    unzip tde_wallet.zip -d /opt/oracle/dcs/commonstore/wallets/tde/rohitdb_phx3bv/
    

    For example (click image for larger view):

  6. Ensure that sqlnet.ora has the right ENCRYPTION_WALLET_LOCATION.

    cat $ORACLE_HOME/network/admin/sqlnet.ora
    

    For example (click image for larger view):

  7. Adjust the control file location.

    sqlplus / as sysdba
    startup force nomount;
    alter system set control_files='+RECO' scope=spfile sid='*';
    startup force nomount;
    

    For example (click image for larger view):

Restore and Recover the Database at the Target

  1. Create the SBT metadata.xml file for the Object Storage backup pieces.

    run {
    allocate channel t1 device type sbt parms='SBT_LIBRARY=/home/oracle/cbm/cbm_lib/libopc.so, SBT_PARMS=(opc_pfile=/home/oracle/cbm/cbm_config)';
    send channel t1 '
    export backuppiece l0_20180823_ROHITDB_set237_piece1_7dtb9luh_1_1;
    export backuppiece l0_20180823_ROHITDB_set238_piece1_7etb9lul_1_1;
    export backuppiece l0_20180823_ROHITDB_set239_piece1_7ftb9m02_1_1;
    export backuppiece l0_20180823_ROHITDB_set240_piece1_7gtb9m15_1_1;
    export backuppiece l0_20180823_ROHITDB_set241_piece1_7htb9m29_1_1;
    export backuppiece l0_20180823_ROHITDB_set242_piece1_7itb9m2b_1_1;
    export backuppiece l0_20180823_ROHITDB_set243_piece1_7jtb9m2c_1_1;
    ';
    }
    
    run {
    allocate channel t1 device type sbt parms='SBT_LIBRARY=/home/oracle/cbm/cbm_lib/libopc.so, SBT_PARMS=(opc_pfile=/home/oracle/cbm/cbm_config)';
    send channel t1 '
    export backuppiece l1_20180823_ROHITDB_set244_piece1_7ktb9r4k_1_1;
    export backuppiece l1_20180823_ROHITDB_set245_piece1_7ltb9r4m_1_1;
    export backuppiece l1_20180823_ROHITDB_set246_piece1_7mtb9r4q_1_1;
    export backuppiece l1_20180823_ROHITDB_set248_piece1_7otb9r4t_1_1;
    export backuppiece l1_20180823_ROHITDB_set249_piece1_7ptb9r4v_1_1;
    export backuppiece l1_20180823_ROHITDB_set250_piece1_7qtb9r51_1_1;
    ';
    }
    

    For example (click image for larger view):

  2. Restore the control file from the Object Storage backups.

    Note: If you want to use incremental backups, use the level 1 control file backup piece. If only level 0 backups will be used for the final restore, then use the level 0 control file backup piece.

    run {
    allocate channel t1 device type sbt parms='SBT_LIBRARY=/home/oracle/cbm/cbm_lib/libopc.so, SBT_PARMS=(opc_pfile=/home/oracle/cbm/cbm_config)';
    restore controlfile from 'l1_20180823_ROHITDB_set248_piece1_7otb9r4t_1_1';
    alter database mount;
    }
    

    For example (click image for larger view):

  3. Catalog the Object Storage backup pieces.

    run {
    configure channel device type 'sbt_tape' parms 'SBT_LIBRARY=/home/oracle/cbm/cbm_lib/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/cbm/cbm_config)';
    crosscheck backup device type sbt;
    delete noprompt expired backup;
    catalog device type sbt backuppiece
    'l0_20180823_ROHITDB_set237_piece1_7dtb9luh_1_1',
    'l0_20180823_ROHITDB_set238_piece1_7etb9lul_1_1',
    'l0_20180823_ROHITDB_set239_piece1_7ftb9m02_1_1',
    'l0_20180823_ROHITDB_set240_piece1_7gtb9m15_1_1',
    'l0_20180823_ROHITDB_set241_piece1_7htb9m29_1_1',
    'l0_20180823_ROHITDB_set242_piece1_7itb9m2b_1_1',
    'l0_20180823_ROHITDB_set243_piece1_7jtb9m2c_1_1',
    'l1_20180823_ROHITDB_set244_piece1_7ktb9r4k_1_1',
    'l1_20180823_ROHITDB_set245_piece1_7ltb9r4m_1_1',
    'l1_20180823_ROHITDB_set246_piece1_7mtb9r4q_1_1',
    'l1_20180823_ROHITDB_set248_piece1_7otb9r4t_1_1',
    'l1_20180823_ROHITDB_set249_piece1_7ptb9r4v_1_1',
    'l1_20180823_ROHITDB_set250_piece1_7qtb9r51_1_1';
    }
    
    list backup summary;
    

    For example (click image for larger view):

    ....

    ....

  4. Restore the database from the Object Storage backups.

    run {
    set newname for database to new;
    restore device type sbt database;
    switch datafile all;
    switch tempfile all;
    }
    

    For example (click image for larger view):

    ....

    ....

    ....

    ....

  5. Recover the database from the Object Storage backups.

    list backup of archivelog all;
    run {
    set until sequence 59 thread 1;
    recover device type sbt database;
    }
    

    For example (click image for larger view):

    ....

    ....

    ....

    ....

  6. Adjust the log files and block change tracking location.

    alter database rename file '/u04/app/oracle/redo/redo03.log' to '+RECO';
    alter database rename file '/u04/app/oracle/redo/redo02.log' to '+RECO';
    alter database rename file '/u04/app/oracle/redo/redo01.log' to '+RECO';
    

    For example (click image for larger view):

    alter database disable block change tracking;
    alter database enable block change tracking using file '+DATA';
    

    For example (click image for larger view):

  7. Open the database with resetlogs.

    alter database open resetlogs;
    select open_mode from v$database;
    

    For example (click image for larger view):