X

Migrating an Oracle Database to an Oracle Cloud Infrastructure Database Service Virtual Machine

By: Rajeev Thottathil | Solution Architect

This blog post outlines the process of migrating a single-instance, version 12.2 Oracle Database from on-premises, Amazon Web Services (AWS), or an instance in Oracle Cloud Infrastructure to an Oracle Cloud Infrastructure Database service virtual machine (VM) instance. If your source database is on a Linux operating system, you can take a backup of the source database by using Oracle Recovery Manager (RMAN) and restore it to the Database service VM instance on Oracle Cloud Infrastructure.

Before You Start

Before you perform the migration, consider the following:

  • Identify the CPU, memory, storage, IOPS, and I/O throughput requirements for your database instance, and provision a Database service VM instance on Oracle Cloud Infrastructure that is large enough to handle those requirements.
  • The Database service VM instance that you provision should have the same database name as your source database (for example, prddb). The DB_UNIQUE_NAME and the SERVICE_NAME can be different from the target. During the migration, the DB_UNIQUE_NAME and SERVICE_NAME of the target database will be retained on the Database service VM.
  • This example assumes that the datafiles are created as Oracle Managed files.
  • If the tablespaces in the source database are not encrypted, consider encrypting them before moving the database to Oracle Cloud Infrastructure. Alternatively, you can convert the unencrypted tablespaces to Transparent Data Encryption (TDE) after the migration.
    • The procedures outlined in this post assume that your source database is not using TDE.
  • Identify the patch level of the source database.
    • Identify the bundle patch levels (CPU, PSU, RU, RUR) of the source database, and any critical one-off patches that you might need.
    • The database home on the Database service instance is usually patched up to the latest version of the bundle patches.
    • If possible, apply the bundle patch that matches the Database service instance on the source database. Alternatively, you can run datapatch.sql to patch the database objects after the database migration is complete.
    • Validate the list of one-off patches with Oracle support and identify the ones you need, and get one-off patches issued that can coexist with the bundle patch.
  • Determine the destination for the RMAN backups taken during the migration. You can place the RMAN backup files either on a file system or directly in Oracle Cloud Infrastructure Object Storage. In this example, the files are placed in Object Storage.
  • Ensure that the source database is in ARCHIVELOG mode.

Install the Oracle Database Cloud Backup Module

You can use the Oracle Database Cloud Backup Module, with RMAN, to back up the source database directly to Oracle Cloud Infrastructure Object Storage. After the database is backed up to Object Storage, you can use RMAN on the target database instance to restore the backup from Object Storage directly to the target host on Oracle Cloud Infrastructure.

Using the Oracle Cloud Infrastructure Console, create a new Object Storage bucket to hold the backup. In this example, this bucket is called prdbkup.

In the Console, navigate to the user settings and create a new authorization token. Note this token string; you use it when configuring the backup module.

Download the Oracle Database Cloud Backup Module, and upload it to the /tmp directory of both the source and target database instances.

Log in as the oracle user, change the directory to /tmp, unzip opc_installer.zip, and run the following command to install the backup module:

$ORACLE_HOME/jdk/bin/java -jar opc_install.jar -opcId <user_id> -opcPass '<auth_token>' -container <bucket_name> -walletDir ~/hsbtwallet/ -libDir ~/lib/ -configfile ~/config -host https://swiftobjectstorage.<region>.oraclecloud.com/v1/<tenant>

Perform a Backup

In this example, you take a full backup of the database and ARCHIVELOG files, and transfer the backup set to the target database instance.

Note the DBID of the source database. You need this to perform the restore on the target database instance.

sqlplus / as sysdba
SQL> select dbid from v$database;

Note the file names for the online redo logs. You will rename these redo log files on the Database service instance to use Oracle Automatic Storage Management (ASM) disk groups.

sqlplus / as sysdba
SQL> select member from v$logfile;

In the Oracle Cloud Infrastructure Console, go to the target database instance and note the database's unique name. Also note the host domain name of the target database system.

Set up a few initialization parameters in the source database's server parameter file (spfile) so that you don't have to change these on the target after the migration.

sqlplus / as sysdba
SQL> alter system set audit_file_dest='/u01/app/oracle/admin//adump' scope=spfile;
SQL> alter system set service_names='.' scope=spfile;
SQL> alter system set db_unique_name= scope=spfile;

Use RMAN to take a full backup of the database and ARCHIVELOG files. This example uses encryptit as the password to encrypt the backup, but you can change it to any string you want.

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/home/oracle/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/config)'
FORMAT "BACKUP_%U";
CONFIGURE DEFAULT DEVICE TYPE TO SBT_TAPE;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F';
CONFIGURE ENCRYPTION FOR DATABASE ON;

set encryption identified by encryptit only;

run {
backup as compressed backupset incremental level 0 SECTION SIZE=512M DATABASE PLUS ARCHIVELOG TAG='$tag';
}

Restore and Recover the Database

When you created the Database service VM instance, a database was created for you. Before you restore the files from the source database, you need to delete the files that pertain to the database that was created for you. You do not have to re-create all the configurations (such as the cluster registry, storage locations, or dbcli metadata).

This section describes the following steps:

  • Restore the server parameter file (spfile).
  • Restore the database controlfiles.
  • Restore the database files and ARCHIVELOG files, and recover the database.
  • Implement TDE and encrypt the tablespaces.
  • Clean up.

Note: If the source database is encrypted using TDE, copy the wallet file from the source database environment and create an autologin wallet before proceeding with the restore.

Restore the Server Parameter File

Before you start this step, shut down the database and delete all the files belonging to this database. Log in as the oracle user and shut down the database. Then, log in as the grid user and use asmcmd to locate and delete all the files under the +DATA/<db_unique_name> and +RECO/<db_unique_name> directories.

In this step, you configure the RMAN parameters to restore the spfile from a tape device, which will be configured to point to Oracle Cloud Infrastructure Object Storage. Use the DBID that you noted from the source database, and use the same password (in set DECRYPTION) that you used to encrypt the backup on the source instance during the restore.

rman target /
set dbid 3114159043;
startup force nomount;
set DECRYPTION identified by encryptit;

run {
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT TO '%F';
allocate channel c1 device type sbt PARMS 'SBT_LIBRARY=/home/oracle/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/config)';
RESTORE SPFILE to '/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/spfileprddb.ora' FROM AUTOBACKUP;
}

Set up the database initialization file to point to the newly restored spfile, so that all subsequent database startups use this spfile restored from the source database.

echo SPFILE=/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/spfileprddb.ora > $ORACLE_HOME/dbs/initprddb.ora
sqlplus / as sysdba
SQL>shutdown immediate;
SQL>startup nomount;

Restore the Database Control Files

Before restoring the controlfiles, set a few initialization parameters in the spfile so that the controlfiles and the database files are restored to the correct new locations.

On the source database, the controlfiles and database files were on a file system. In the Oracle Cloud Infrastructure Database service, the controlfiles and database files are placed on ASM disk groups.

Set the db_create_file_dest parameter to +DATA so that the database files will be restored to the +DATA disk group, and set up the control_files parameter to place one copy of the controlfile in the +DATA disk group and one copy in the +RECO disk group.

sqlplus / as sysdba
SQL> alter system set db_create_file_dest='+DATA' scope=spfile;
SQL> alter system set db_recovery_file_dest='+RECO' scope=spfile;
SQL> alter system set db_recovery_file_dest_size=4385144832 scope=spfile;
SQL> alter system set control_files='+RECO' scope=spfile;
shutdown immediate;
startup nomount;

-- Restore the controlfile

rman target /
set dbid 3114159043;
set DECRYPTION identified by encryptit;

run {
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT TO '%F';
allocate channel c1 device type sbt PARMS 'SBT_LIBRARY=/home/oracle/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/config)';
RESTORE CONTROLFILE FROM AUTOBACKUP;
}

alter database mount;

Restore and Recover the Database Files

Now you are ready to restore and recover the database files.

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/home/oracle/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/config)';
CONFIGURE DEFAULT DEVICE TYPE TO SBT_TAPE;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F';
CONFIGURE ENCRYPTION FOR DATABASE ON;

run
{ set ARCHIVELOG DESTINATION to '+RECO';
set NEWNAME for database to '+DATA';
allocate channel c1 device type sbt PARMS 'SBT_LIBRARY=/home/oracle/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/config)' FORMAT "BACKUP_%U";
SQL "ALTER DATABASE RENAME FILE ''/u01/oradata/prddb/redo01.log'' TO ''+RECO/<db_unique_name>/ONLINELOG/redo01.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u01/oradata/prddb/redo02.log'' TO ''+RECO/<db_unique_name>/ONLINELOG/redo02.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u01/oradata/prddb/redo03.log'' TO ''+RECO/<db_unique_name>/ONLINELOG/redo03.log'' ";
restore database;
switch datafile all;
restore archivelog all;
recover database;
}
alter database open resetlogs;

Convert Tablespaces to use TDE

Often the on-premises databases that are migrated to the Database service don’t have their tablespaces encrypted. If your tablespaces aren’t already encrypted, after the migration is complete, use the following high-level steps to encrypt the tablespaces with TDE:

  • Add the master key for the Container Database (CDB) and the Pluggable Databases (PDBs) to the wallet.
  • Encrypt the tablespaces.

When the original Database service instance was created using the Oracle Cloud Infrastructure Console, the Oracle wallet location was already set up in the sqlnet.ora file and the master keys for the original database were already present in the wallet. A password-based wallet and autologin wallet were created in the /opt/oracle/dcs/commonstore/wallets/tde/<db_unique_name>/ directory. You will use this same wallet and add your new keys to this wallet, using the following process.

If the source database was already configured to use TDE, and you copied the wallet before performing the restore, you can skip this step.

Remove the Autologin Wallet

To add the master keys for the database to the wallet, you need to use the password-based wallet. Remove the autologin wallet:

cd /opt/oracle/dcs/commonstore/wallets/tde/<db_unique_name>/
mv cwallet.sso cwallet.sso-orig

Add the Master Key for the CDB to the Wallet

export ORACLE_UNQNAME=<db_unique_name>
sqlplus / as sysdba
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <walletpassword>;
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY <walletpassword> WITH BACKUP;

The wallet_password is the same password that you specified as the admin password during the target instance creation on Oracle Cloud Infrastructure via the Console.

Now you can encrypt the user-created tablespaces in the CDB. The following command performs an online conversion of the USERS tablespace:

SQL>ALTER TABLESPACE users ENCRYPTION ONLINE USING 'AES192' encrypt;

Add the Master Key for the PDBs to the Wallet

Run the following processes for each of the PDBs in the CDB:

SQL>alter pluggable database <pdbname> open;
SQL>alter session set container=<pdbname>;
sqlplus / as sysdba SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/opt/oracle/dcs/commonstore/wallets/tde/<db_unique_name>' IDENTIFIED BY ; The command above, will create the single signon wallet file cwallet.sso in the /opt/oracle/dcs/commonstore/wallets/tde/<db_unique_name> directory. This single signon wallet will now have the new Master keys we have created. The commands we used above, for opening the wallet and adding the Master key to the wallet will work for both 12.1 and 12.2 databases. For commands to do this operation for 11.2.0.4 databases, please refer to the white paper http://www.oracle.com/technetwork/database/availability/tde-conversion-dg-3045460.pdf.
SQL>ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY WelCome#_12;
SQL>ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY WelCome#_12 WITH BACKUP;

SQL>ALTER TABLESPACE users ENCRYPTION ONLINE USING 'AES192' encrypt;
SQL>ALTER TABLESPACE hrts ENCRYPTION ONLINE USING 'AES192' encrypt;
SQL>ALTER TABLESPACE oets ENCRYPTION ONLINE USING 'AES192' encrypt;
Re-Create the autologin wallet
sqlplus / as sysdba SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/opt/oracle/dcs/commonstore/wallets/tde/<db_unique_name>' IDENTIFIED BY <WalletPasword>;

The command above, will create the single signon wallet file cwallet.sso in the /opt/oracle/dcs/commonstore/wallets/tde/<db_unique_name> directory. This single signon wallet will now have the new Master keys we have created. The commands we used above, for opening the wallet and adding the Master key to the wallet will work for both 12.1 and 12.2 databases. For commands to do this operation for 11.2.0.4 databases, please refer to the white paper titled Convert to Transparent Database Encryption.

Cleanup Steps

Perform these steps to finalize the migration of the database.

Reset the spfile Location

During the database restore process, the database spfile was temporarily restored to the $ORACLE_HOME/dbs directory. Now you need to move this spfile to the ASM disk group and update the location for the spfile in the cluster registry.

Log in as the grid user and run the following commands to copy the spfile to ASM:

export ORACLE_HOME=/u01/app/12.2.0.1/grid
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export ORACLE_SID=+ASM1

asmcmd
ASMCMD> cp '/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/spfileprddb.ora' '+DATA/<db_unique_name>/PARAMETERFILE/spfileprddb.ora'

Log in as the oracle user and run the following command to set the spfile for the database to point to the location of the file on ASM:

srvctl modify database -d <db_unique_name> -p +DATA/<db_unique_name>/PARAMETERFILE/spfileprddb.ora

Reset RMAN Configuration Parameters

Run the following commands to reset the configuration parameters that would have been reset during the database restore phase. You perform this step to ensure that changed configuration parameters don't negatively impact the automatic backups scheduled for the database.

rman target /
RMAN> configure controlfile autobackup clear;
RMAN> CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' clear;
RMAN> configure backup optimization clear;
RMAN> configure default device type clear;
RMAN> configure encryption for database clear;

You have now completed the migration of the database to an Oracle Cloud Infrastructure Database service VM instance, and you are now ready to connect to this database and use it.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha