RMAN DUPLICATE from an Active Database

This topic explains how to migrate an entire, active container database (CDB) or non-CDB database to Oracle Cloud Infrastructure by using RMAN Active Duplication. The database to be migrated can reside on-premises or in Oracle Cloud Infrastructure Classic. This topic does not cover duplicating a pluggable database, or migrating a pluggable database or non-CDB to a CDB in the cloud.

The following terms are used throughout this topic:

  • Source database: The active database to be migrated.
  • Target database: The new database (duplicated from the source database) on a DB system in the Oracle Cloud Infrastructure.
Note

Prerequisites

For the source database to be migrated, you'll need:

  • The source database name, database unique name, listener port, service name, database home patch level, and the password for SYS.
  • A copy of the sqlpatch directory from the source database home. This is required for rollback in case the target DB system does not include these patches.
  • If the source database is configured with Transparent Data Encryption (TDE), you'll need a backup of the wallet and the wallet password to allow duplication of a database with encrypted data.

When migrating a source database to an existing target database, Oracle recommends that you patch the source environment to the same database bundle patch level as the target database home. If the source environment has an interim patch (previously known as a "one-off" patch) that includes a sqlpatch component, and that sqlpatch is missing from the target environment (or a different cumulative patch is applied), the interim patch should be rolled back in the source environment before the migration, if possible.

Tip

To check for interim patches installed on the source or target database, use the $ORACLE_HOME/OPatch/opatch lspatches command. To roll back SQL changes in the target database, copy the $ORACLE_HOME/sqlpatch/<patch#>/postdeinstall.sql script from the source environment to the cloud environment and execute the postdeinstall.sql script.

For the target database, you'll need:

  • A target DB system that supports the same database edition as the source database edition. When you launch a DB system, an initial database is created on it. If necessary, you can delete that database and create a new one by using the dbcli command line interface. For more information on creating a DB system, see Overview of Creating a DB System. For information about creating a database with the DBCLI, see Database Commands.
  • The target database name, database unique name, auxiliary service name, and database home patch level.
  • A free TCP port in the target database to setup the auxiliary instance.

If you need to roll back interim patches in the target environment so that the patch level matches that of the source environment, copy the source DB $ORACLE_HOME/sqlpatch/<patch_number> directory to the target database home.

Migrating Source Databases That Include Patch Set Updates (PSUs)

In Oracle Cloud Infrastructure DB systems, the database home includes an installation of Database Proactive Bundle Patches. If the source DB uses Patch Set Updates (PSUs), follow the instructions in MOS Note:1962125.1 (Oracle Database - Overview of Database Patch Delivery Methods) for migrating the DB into Oracle Cloud Infrastructure.

Verifying the Environment

Perform the following steps before you begin the migration:

  1. Make sure the source DB system is reachable from the target DB system. You should be able to SSH between the two hosts.
  2. On the target host, use the TNSPING  utility to make sure the source host  listener port works. For example:

    
    tnsping <source_host>:1521
  3. On the target host, use Easy Connect to verify the connection to the source database:

    <host>:<port>/<service_name>

    For example:

    
    sqlplus system@129.145.0.164:1521/proddb 

    Make sure the connection string does not exceed 64 characters.

  4. Copy the required sqlpatch files (for rollback) from the source database home to the target database.
  5. Make sure at least one archivelog has been created on the source database, otherwise, the RMAN duplication will fail with an error.
  6. If the source database uses wallets, back up the password-based wallet and copy it to the standard location in the DB system:

    /opt/oracle/dcs/commonstore/wallets/tde/<db_unique_name>/
  7. Make sure the compatibility parameters in the source database are set to at least:

    • 18.0.0.0.0 for an 18.1.0.0 database
    • 12.1.0.2.0 for a 12.1.0.2 or a 12.2.0.1 database
    • 11.2.0.4.0 for an 11.2.0.4 database

Setting Up Storage on the DB System

  1. SSH to the DB System.

    ssh -i <private_key_path> opc@<db_system_ip_address> 
  2. Log in as opc and then sudo to the root user. Use sudo su - with a hyphen to invoke the root user's profile, which will set the PATH to the dbcli directory (/opt/oracle/dcs/bin).

    
    login as: opc
    			
    [opc@dbsys ~]$ sudo su - 
  3. Use the Dbstorage Commands to set up directories for DATA, RECO, and REDO storage. The following example creates 10GB of ACFS storage for the tdetest database.

    [root@dbsys ~]# dbcli create-dbstorage --dbname tdetest --dataSize 10 --dbstorage ACFS 
    Note

    When migrating a version 11.2 database, ACFS storage must be specified.

  4. Use the Dbstorage Commands command to list the storage ID. You'll need the ID for the next step.

    [root@dbsys ~]# dbcli list-dbstorages
    ID                                       Type   DBUnique Name        Status
    ---------------------------------------- ------ -------------------- ----------
    9dcdfb8e-e589-4d5f-861a-e5ba981616ed     Acfs   tdetest              Configured
  5. Use the Dbstorage Commands command with the storage ID from the previous step to list the DATA, RECO and REDO locations.

    [root@dbsys ~]# dbcli describe-dbstorage --id 9dcdfb8e-e589-4d5f-861a-e5ba981616ed
    DBStorage details
    ----------------------------------------------------------------
                         ID: 9dcdfb8e-e589-4d5f-861a-e5ba981616ed
                    DB Name: tdetest
              DBUnique Name: tdetest
             DB Resource ID:
               Storage Type: Acfs
              DATA Location: /u02/app/oracle/oradata/tdetest
              RECO Location: /u03/app/oracle/fast_recovery_area/
              REDO Location: /u03/app/oracle/redo/
                      State: ResourceState(status=Configured)
                    Created: August 24, 2016 5:25:38 PM UTC
                UpdatedTime: August 24, 2016 5:25:53 PM UTC

    Note the locations. You'll use them later to set the db_create_file_dest, db_create_online_log_dest, and db_recovery_file_dest parameters for the database.

Choosing an ORACLE_HOME

Decide which ORACLE_HOME to use for the database restore and then switch to that home with the correct ORACLE_BASE, ORACLE_HOME, and PATH settings.

To get a list of existing ORACLE_HOMEs, or to create a new ORACLE_HOME, use the Dbhome command.

Copying the Source Database Wallets

Skip this section if the source database is not configured with TDE.

  1. On the DB system, become the oracle user:

    sudo su - oracle
  2. Create the following directory if it does not already exist:

    mkdir /opt/oracle/dcs/commonstore/wallets/tde/<db_unique_name>
  3. Copy the ewallet.p12 file from the source database to the directory you created in the previous step.

  4. On the target host, make sure that $ORACLE_HOME/network/admin/sqlnet.ora contains the following line:

    ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/dcs/commonstore/wallets/tde/$ORACLE_UNQNAME)))

    Add the line if it doesn't exist in the file. (The line might not be there if this is a new home and no database has been created yet on this host.)

  5. Create the autologin wallet from the password-based wallet to allow auto-open of the wallet during restore and recovery operations.

    For version 12c, use the ADMINISTER KEY MANAGEMENT command:

    $cat create_autologin_12.sh
    
    #!/bin/sh
    if [ $# -lt 2 ]; then
            echo "Usage: $0 <db_unique_name> <remote_wallet_location>"
            exit 1;
    fi
    
    mkdir /opt/oracle/dcs/commonstore/wallets/tde/$1
    cp $2/ewallet.p12* /opt/oracle/dcs/commonstore/wallets/tde/$1
    rm -f autokey.ora
    echo "db_name=$1"  > autokey.ora
    autokeystoreLog="autologinKeystore_`date +%Y%m%d_%H%M%S_%N`.log"
    echo "Enter Keystore Password:"
    read -s keystorePassword
    echo "Creating AutoLoginKeystore -> "
    sqlplus "/as sysdba"  <<EOF
    spool $autokeystoreLog
    set echo on
    startup nomount pfile=autokey.ora
    ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE
    FROM KEYSTORE '/opt/oracle/dcs/commonstore/wallets/tde/$1' -- Keystore location
    IDENTIFIED BY "$keystorePassword";
    shutdown immediate;
    EOF

    For version 11g, use the orapki command:

    orapki wallet create -wallet wallet_location -auto_login [-pwd <password>]

Setting Up the Static Listener

Set up the static listener for the auxiliary instance for RMAN duplication.

  1. On the DB system, create $ORACLE_HOME/network/admin/listener.ora and add the following content to it.

    LISTENER_aux_<db_unique_name>=
      (DESCRIPTION=
        (ADDRESS_LIST=
          (ADDRESS=(PROTOCOL=TCP)(HOST=<hostname> or <ip_address>)(PORT=<available_TCP_port>))
        )
       )
    SID_LIST_LISTENER_aux_<db_unique_name>=
      (SID_LIST=
        (SID_DESC= 
          (GLOBAL_DBNAME=<auxServiceName_with_domain>) 
           (ORACLE_HOME=<Oracle_home_for_target_database>) 
           (SID_NAME=<database_name>) 
           (ENVS="TNS_ADMIN=<path_to_tnsnames.ora>") 
           (ENVS="ORACLE_UNQNAME=<db_unique_name(in lower case)>"))
    )
  2. Make sure the port specified in (PORT=<available_TCP_port>) is open in the DB system's iptables and in the DB system's cloud network Security List.

Using the RMAN Duplicate Command to Migrate the Database

  1. Set the following environment variables for RMAN and SQL Plus sessions for the database:

    ORACLE_HOME=<path_of_Oracle_home_where_the database_is_to_be_restored> 
    ORACLE_SID=<database_name>
    ORACLE_UNQNAME=<db_unique_name(in lower case)>
    NLS_DATE_FORMAT="mm/dd/yyyy hh24:mi:ss"
  2. Start the listener:

    lsnrctl start listener_aux_<db_unique_name>
  3. Create an init.ora file with the minimal required parameters as described in Creating an Initialization Parameter File and Starting the Auxiliary Instance and use it for the auxiliary instance.

  4. Start the auxiliary instance in nomount mode:

    startup nomount
  5. Run the following commands to duplicate the database. Note that the example below uses variables to indicate the values to be specified:

    rman target sys/$sourceSysPassword@$sourceNode:$sourceListenerPort/$sourceDb auxiliary sys/$auxSysPassword@$targetNode:$targetListenerPort/$auxService<<EOF
    
    spool log to "`date +%Y%m%d_%H%M%S_%N`_duplicate_${targetDbUniqueName}_from_${sourceDb}.log"
    set echo on
    
    duplicate target database to $targetDb from active database
    password file
    spfile
     PARAMETER_VALUE_CONVERT $sourceDb $targetDb $sourceDbUniqueNameCaps $targetDbUniqueNameCaps
     set cluster_database='false'
     set db_name='$targetDb'
     set db_unique_name='$targetDbUniqueName'
     set db_create_file_dest='$dataLoc'
     set db_create_online_log_dest_1='$redoLoc'
     set db_recovery_file_dest='$recoLoc'
     set audit_file_dest = '$auditFileDest'
     reset control_files
    nofilenamecheck
    ;
    EOF

Preparing to Register the Database

Before you register the database:

  1. Make sure the database COMPATIBLE parameter value is acceptable.

    For a 11.2 database, the minimum compatibility value is 11.2.0.4.

    For a 12c database, the minimum compatibility value is 12.1.0.2.

    If the value is less than the minimum, the database cannot be registered until you upgrade the database compatibility.

  2. Use the following command to verify that the database has registered with the local listener and service name.

    lsnrctl services
  3. Use the following command to verify that the password file was restored or created for a new database.

    ls -ltr $ORACLE_HOME/dbs/orapw<$ORACLE_SID>

    If the file does not exist, create it using the orapwd command.

    orapwd file=<$ORACLE_HOME/dbs/orapw<$ORACLE_SID>> password=<sys_password>
  4. Use the following command to verify that the restored database is open in read write mode.

    select open_mode from v$database;

    Read write mode is required to register the database later. Any PDBs must also be in read write mode.

  5. From oracle home on the migrated database host, use the following command verify the connection to SYS.

    conn sys/<password>@<service_name> as sysdba

    This connection is required to register the database later. Fix any connection issues before continuing.

  6. Copy the folder $ORACLE_HOME/sqlpatch from source database to the target database. This will enable the dbcli register-database command to rollback any conflicting patches.

    Note

    If you are migrating a version 11.2 database, additional steps are required after you register the database. For more information, see Rolling Back Patches on a Version 11.2 Database.

  7. Use the following SQL*Plus command to make sure the database is using the spfile.

    SHOW PARAMETERS SPFILE 

Registering the Database on the DB System

The dbcli register-database command registers the migrated database to the dcs-agent so it can be managed by the dcs-agent stack. See Database Commands for more information.

Note

The dbcli register-database command is not available on 2-node RAC DB Systems.

As the root user, use the dbcli register-database command to register the database on the DB system, for example:

[root@dbsys ~]# dbcli register-database --dbclass OLTP --dbshape odb1 --servicename crmdb.example.com --syspassword
Password for SYS:
{
  "jobId" : "317b430f-ad5f-42ae-bb07-13f053d266e2",
  "status" : "Created",
  "message" : null,
  "reports" : [ ],
  "createTimestamp" : "August 08, 2016 05:55:49 AM EDT",
  "description" : "Database service registration with db service name: crmdb.example.com",
  "updatedTime" : "August 08, 2016 05:55:49 AM EDT"
}

Migrating a Version 12.1 or Later Database That Includes SQL Patch Components

For a 1-node DB system at version 12.1 or higher, the dbcli register-database command automates the datapatch execution. Before executing the dbcli register-database command, open all PDBs in read-write mode. If you have already run the dbcli register-database command and did not open all PDBs, or did not copy the $ORACLE_HOME/sqlpatch directory from the source database home, manually rerun the datapatch utility to configure the SQL portion of existing interim patches. This can be done by executing the command $ORACLE_HOME/OPatch/opatch datapatch.

Tip

If the source database includes patch 23170620 and the target database is running with the October 2017 patch or a later one, the $ORACLE_HOME/sqlpatch directory does not need to be copied to the target database, because the contents of the patch are already installed in the target database.

Rolling Back Patches on a Version 11.2 Database

For version 11.2 databases, the sqlpatch application is not automated, so any interim patches (previously known as a "one-off" patches) applied to the source database that are not part of the installed PSU must be rolled back manually in the target database. After registering the database, execute the catbundle.sql script and then the postinstall.sql script with the corresponding PSU patch (or the overlay patch on top of the PSU patch), as described below.

Tip

Some interim patches may include files written to the $ORACLE_HOME/rdbms/admin directory as well as the $ORACLE_HOME/sqlpatch directory. Oracle recommends that you roll back these patches in the source database using the instructions in the patch read-me prior to migrating the database to OCI environment. Contact Oracle Support if you need assistance with rolling back these patches.
  1. On the DB System, use the dbcli list-dbhomes command to find the PSU patch number for the version 11.2 database home. In the following sample command output, the PSU patch number is the second number in the DB Version column:

    [root@dbsys ~]# dbcli  list-dbhomes
    ID                                   Name               DB Version                             Home Location                             Status 
    ------------------------------------ -----------------  -------------------------------------  ----------------------------------------- ----------
    59d9bc6f-3880-4d4f-b5a6-c140f16f8c64 OraDB11204_home1	11.2.0.4.160719 (23054319, 23054359)   /u01/app/oracle/product/11.2.0.4/dbhome_1 Configured
     

    (The first patch number, 23054319 in the example above, is for the OCW component in the database home.)

  2. Find the overlay patch, if any, by using the lsinventory command. In the following example, patch number 24460960 is the overlay patch on top of the 23054359 PSU patch.

    $ $ORACLE_HOME/OPatch/opatch lsinventory              
    ...
    Installed Top-level Products (1): 
    
    Oracle Database 11g                                                  11.2.0.4.0
    There are 1 products installed in this Oracle Home.
    
    
    Interim patches (5) :
    
    Patch  24460960     : applied on Fri Sep 02 15:28:17 UTC 2016
    Unique Patch ID:  20539912
       Created on 31 Aug 2016, 02:46:31 hrs PST8PDT
       Bugs fixed:
         23513711, 23065323, 21281607, 24006821, 23315889, 22551446, 21174504
       This patch overlays patches:
         23054359
       This patch needs patches:
         23054359
       as prerequisites
    
  3. Start SQL*Plus and execute the catbundle.sql script, for example:

    SQL> startup 
    SQL> connect / as sysdba
    SQL> @$ORACLE_HOME/rdbms/admin/catbundle.sql psu apply
    exit
    
  4. Apply the sqlpatch, using the overlay patch number from the previous step, for example:

    SQL> connect / as sysdba
    SQL> @$ORACLE_HOME/sqlpatch/24460960/postinstall.sql 
    exit

Creating a Backup Configuration (Optional)

If you would like to manage the database backup with the dbcli command line interface, you can associate a new or existing backup configuration with the migrated database when you register it or after you register it. A backup configuration defines the backup destination and recovery window for the database. As the root user, use the following Backupconfig Commands to create, list, and display backup configurations:

  • dbcli create-backupconfig
  • dbcli list-backupconfigs
  • dbcli describe-backupconfig

Post Migration Checklist

After the database is migrated and registered on the DB system, use the following checklist to verify the results of the migration and perform any post-migration customizations.

  1. Make sure the database files were restored in OMF format.
  2. Make sure the database is listed in the Database Commands command output.
  3. Check for the following external references in the database and update them if necessary:
    • External tables: If the source database uses external tables, back up that data and migrate it to the target host.
    • Directories: Customize the default directories as needed for the migrated database.
    • Database links: Make sure all the required TNS entries are updated in the tnsnames.ora file in ORACLE_HOME.
    • Email and URLs: Make sure any email addresses and URLs used in the database are still accessible from the DB system.
    • Scheduled jobs: Review the jobs scheduled in source database and schedule similar jobs as needed in the migrated database.
  4. If you associated a backup configuration when you registered the database, run a test back up using the Backup Commands command.
  5. Verify that patches have been applied to all PDBs if the migrated database contains CDB and PDBs.
  6. Validate the database performance by using Database Replay and SQL Performance Analyzer for SQL. For more information, see the Database Testing Guide.