There are lot of methods available to migrate On-Premises oracle database to Co-Managed database in Oracle Cloud Infrastructure.
Customers typically choose a solution that is best suited to meet their requirements.
Lets’ explore a method which is very easy to accomplish.
We will see how you can perform a Physical Offline migration of your On-premises database (running on Linux) to Oracle Base Database using Oracle Cloud Backup Module and RMAN.
This is a comprehensive article with step by step instructions which will cover not just migration but also all the post steps required to make your restored database fully compliant with OCI.
You can use the same method to migrate to Oracle Exadata Database Service on Dedicated Infrastructure also.
This migration approach involves creating a backup of On-Premises database (I have used compute in OCI) to Oracle Object Storage and restoring it to Oracle Base Database.
Here is my demo environment.
Source Database : Oracle Database on Compute in OCI (19.20)
Target Database : Oracle Base Database in OCI (19.20)
I have divided the steps in to multiple sections as below so that it is easy to navigate.
Create API Keys
Create Object Storage Bucket
Prepare Source Database
Initiate Source Database Backup
Prepare Target Database
Restore and Recover Database
Enable TDE on Target Database
Cleanup old PDB from console
Enable Auto Backup
Let’s explore each step one at a time.
Create API Keys
We will use API keys to authenticate with OCI which is required to backup the on-premises database to Object Storage.
Please create API keys following documentation.
You will get below information at the end of API Key creation , Please keep it handy.
Also save the private and public API keys.
Please copy the private key file (I have saved it as “oci_api_private.pem”) to source and target database host.
Create Object Storage Bucket
Create an object storage bucket in the same tenancy as your target database .
I have created a bucket named backup for this article.
Please refer documentation in case you need help.
Prepare Source Database
We will make necessary changes to source database (On-Premises) so that we can initiate a database backup to object storage bucket in OCI.
We will use Oracle Database Cloud Backup Module to backup the On-Premises database to Object Storage.
Enable Archivelog Mode
RMAN can be used take cold backup or hot backup of an Oracle Database.
We will use hot backup in this article and for that Source Database should be in archivelog mode.
Please refer documentation to enable archivelog mode for your database.
Install Oracle Database Cloud Backup Module
Let’s install Oracle Database Cloud Backup Module which will be used along with RMAN to push the backup to Object Storage.
Let’s download the backup module from here.
Upload the downloaded zip file to the source database server and unzip to a directory.
I have unzipped backup module to /tmp.
Navigate to the directory as below.
cd /tmp/opc_installer/oci_installer
Let’s install Oracle Database Cloud Backup module using below command.
java -jar oci_install.jar \
-host https://objectstorage.eu-frankfurt-1.oraclecloud.com \
-pubFingerPrint <Public Fingerprint of API Key> \
-tOCID <OCID of tenancy> \
-uOCID <OCID of user> \
-libDir $ORACLE_HOME/lib \
-walletDir $ORACLE_HOME/dbs/opc_wallet \
-pvtKeyFile /home/oracle/oci_api_private.pem \
-bucket backup \
-configFile ~/config
-host – OCI object storage URI
-pubFingerPrint – Same as fingerprint value that you got in previous step.
-tOCID – Same as the tenancy OCID value in previous step.
-uOCID – Same as user OCID value in previous step.
-libDir – Location to keep SBT library files.
-walletDir – Location to keep the wallet.
-pvtKeyFile – Location of private API key file.
-bucket – Name of bucket used to store the backup.
-configFile – Directory where the config file for Cloud backup module is stored.
Please refer documentation for more details about the above parameters.
Below is sample output of command execution.
Below are the files created under the -libdir directory.
Wallet is created under $ORACLE_HOME/dbs/opc_wallet as seen below.
Oracle Database Cloud Backup module config file also got created under /home/oracle/config with below contents.
Configure RMAN
Configure Source database RMAN with below commands.
RMAN > CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN > CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO ‘%F’;
Initiate Source Database Backup
Let’s initiate database backup of On-Premises database.
Configure encryption for backup using below command.
RMAN > SET ENCRYPTION ON IDENTIFIED BY password only;
Let’s start the backup.
I have used below commands , you can tailor it according to your requirement.
RMAN> run
{
allocate channel c1 DEVICE TYPE ‘SBT_TAPE’ PARMS=’SBT_LIBRARY=/u01/app/oracle/product/19c/dbhome_1/lib/libopc.so SBT_PARMS=(OPC_PFILE=/home/oracle/config)’;
BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;
sql ‘alter system archive log all’;
backup archivelog all not backed up 1 times;
}
Below is sample output.
Please make a note of the DBID of the source database from the output which is required while restoring.
Also make a note of the maximum archive log sequence number backed up by RMAN at the end of backup.
For this case it is 21 as seen below.
Starting backup at 29-JUL-24
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=21 RECID=5 STAMP=1175582136
Okay , we have now made a backup of Source Database to Object Storage.
Prepare Target Database
We will now proceed to make changes in target database so that we can restore the backup that we have taken.
Install Oracle Database Cloud Backup Module
We can either use the same commands that we have used to install the Oracle Database Cloud Backup Module for Source Database or copy the files from Source Database.
I have copied below files from Source Database host to Target Database host.
$ORACLE_HOME/lib/libopc.so
$ORACLE_HOME/lib/metadata.xml
$ORACLE_HOME/lib/odbsrmt.py
$ORACLE_HOME/lib/bulkimport.pl
$ORACLE_HOME/dbs/opc_wallet/cwallet.sso
/home/oracle/config
Please modify /home/oracle/config file to reflect the correct wallet location in Target Database.
Also modify the permission of wallet file ($ORACLE_HOME/dbs/opc_wallet/cwallet.sso) to reflect same permission as the source database.
Remove Target Database files
Connect to Target Database and run the below script to get the commands to remove database files from ASM.
set heading off linesize 999 pagesize 0 feedback off trimspool on
spool /tmp/clean.sh
select ‘asmcmd rm ‘||name from v$datafile union all select ‘asmcmd rm ‘||name from v$tempfile union all select ‘asmcmd rm ‘||member from v$logfile;
spool off
You will get a file with similar contents as below.
asmcmd rm +DATA/ORCL_DBCS/DATAFILE/system.260.1175580267
asmcmd rm +DATA/ORCL_DBCS/DATAFILE/sysaux.267.1175580255
asmcmd rm +DATA/ORCL_DBCS/DATAFILE/undotbs1.259.1175580289
asmcmd rm +RECO/ORCL_DBCS/ONLINELOG/group_3.258.1175579989
asmcmd rm +RECO/ORCL_DBCS/ONLINELOG/group_2.259.1175579989
asmcmd rm +RECO/ORCL_DBCS/ONLINELOG/group_1.257.1175579989
Stop the database using srvctl command.
srvctl stop database -d <db_unique_name>
Switch to grid user and run the clean.sh script as below.
sh clean.sh
We have now removed Target Database files including datafiles, tempfiles and redolog files.
Restore and Recover Database
We are now good to start with restoration of RMAN backup to Target DB system.
Bring up the Target Database in nomount state and set the DBID using below commands from RMAN prompt.
RMAN > startup nomount
RMAN > set dbid 1703114761
RMAN > SET DECRYPTION IDENTIFIED BY password;
Restore the controlfile using below RMAN commands.
run{
allocate channel c1 device type sbt PARMS ‘SBT_LIBRARY=/u01/app/oracle/product/19.0.0.0/dbhome_1/lib/libopc.so ,SBT_PARMS=(OPC_PFILE=/home/oracle/config)’
restore controlfile from autobackup;
}
Mount the database using below command.
alter database mount;
Below is sample output.
Okay , controlfile has been restored and we are good to start with database restore.
We will recover the database until sequence 22 since highest sequence number backed up from source database was 21.
We will use the below RMAN commands for restore.
RMAN > run
{
set until sequence 22;
allocate channel c1 device type sbt PARMS ‘SBT_LIBRARY=/u01/app/oracle/product/19.0.0.0/dbhome_1/lib/libopc.so ,SBT_PARMS=(OPC_PFILE=/home/oracle/config)’;
set newname for database to ‘+DATA’;
restore database;
switch datafile all;
recover database;
}
Below is sample output.
We will see a similar output as below at the end of RMAN restore and recover.
We can now open the database with below command.
alter database open resetlogs.
I had faced below error while executing the command.
I have renamed the redo log files to RECO Disk group using below command.
alter database rename file ‘/<old_path>/redo01.log’ to ‘+RECO’;
Finally , I am able to open database.
Enable TDE on Target Database.
This step is required for source database where there was no TDE enabled.
For TDE enabled source database, you can simply copy the wallet from source to target before restore and recover and database should be able to use it.
We will first create TDE key followed by tablespace encryption.
Create TDE Key
Let’s create new TDE key by following below steps.
1. Collect information about the TDE wallet location using below query.
select WRL_PARAMETER from v$encryption_wallet;
Below is sample output.
2. Bring down the database using srvctl.
This step is actually optional since existing wallets are not used by database and there should not be any process using it.
3. Navigate to the location of wallet (refer step 1) and rename them as below.
These were the TDE wallets used by target database when it was created initially from console.
4. Bring up the database using srvctl.
5. Create keystore.
Execute the below command after modifying the keystore location (same as the encryption wallet location collected in step 1) and the TDE password.
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE ‘/opt/oracle/dcs/commonstore/wallets/ORCL_DBCS/tde/’ identified by password;
Below is sample output.
6. Open the keystore.
For a CDB environment (target database in this lab is CDB ), run the following command after updating with your password.
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password container = ALL;
Below is sample output.
For a non-CDB environment, run the following command.
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password;
7. Create and activate master key.
For a CDB environment, run the following command (ensure to update the password).
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY password with backup container = ALL;
Below is sample output.
For a non-CDB environment, run the following command.
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY password with backup;
8. Verify the wallet status.
Let’s check the wallet status using below query.
select CON_ID,WALLET_TYPE,STATUS from v$encryption_wallet;
If the query output shows STATUS as OPEN and WALLET_TYPE as PASSWORD, it means the configuration of the password-based keystore is complete at this stage.
Below is sample output.
We have now completed creation of password based keystore.
We will now create auto-login based wallet.
9. Create Auto login key store.
Execute below query after modifying keystore location (same as the keystore location specified in step 5) and TDE password.
ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE ‘/opt/oracle/dcs/commonstore/wallets/ORCL_DBCS/tde/’ IDENTIFIED BY password;
Below is sample output.
10. Close password based keystore.
Execute the below statement after replacing PASSWORD with your password.
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY password;
Below is sample output.
11. Verify the wallet status.
Execute below query to verify the wallet status.
select CON_ID,WALLET_TYPE,STATUS from v$encryption_wallet;
If the query output shows STATUS as OPEN and WALLET_TYPE as AUTOLOGIN, it means the configuration of the auto-login keystore is complete at this stage.
Below is sample output.
Okay ,we have the required TDE key now.
We will proceed with encryption of the tablespaces now.
Encrypt Tablespaces
1. Check the status of tablespaces using below command
select tablespace_name,encrypted from dba_tablespaces;
You will see that none of the tablespace are encrypted in the root container.
Sample output is shown below.
2. Encrypt tablespaces.
We will get the names of the datafiles belonging to a tablespace using below command.
select file_name from dba_data_files where tablespace_name='<Tablespace_Name>’
We will now proceed with encryption of tablespace online using below command.
alter tablespace <tablespace_name> encryption online encrypt.
We will once again collect the names of the datafiles of the tablespace that we have just encrypted.
We can notice that a new datafile has been created as part of encryption.
We can remove the old datafile from asm later.
Sample output is shown below.
Continue to encrypt all the tablespace in your root except for temporary tablespces.
We can’t encrypt an existing temporary tablespace .
We will create a new temporary tablespace which will by default gets encrypted in OCI.
We will also change the default temporary tablespace to new temp tablespace and drop the old temporary tablespace.
Below is how you can get this done.
create temporary tablespace TEMP1;
alter database default temporary tablespace TEMP1;
drop tablespace TEMP including contents and datafiles;
Below is sample output.
Repeat same procedure for all your pluggable databases except PDB$SEED.
Cleanup old PDB from Console.
Once you have restored the database , pluggable database from source will reflect in console in few hours.
You will also see that your old pluggable database which got created as part of OCI database creation will change to FAILED state. You can remove since it is no longer required.
Below is a screenshot from the console.
Enable Auto Backup
This step is optional since it depends on your policy for backup.
Navigate to Database details in OCI console and click on “Configure automatic backup” as shown below.
Configure the details as per your requirement and save changes.
You will see that a new backup is initiated as below.
Once backup has been completed , you will be able to see it as below.
That’s all , we have completed all the steps for migrating the on-premise database to Oracle Base Database in OCI.
Leave a Reply