Restore a TDE encrypted Cloud Database Backup to another Availability Domain, OCI Region, or On-Premises

Introduction

Oracle Cloud databases provide fully automated backups that can be enabled by the click of a button. However, the backups are stored in an Oracle-managed bucket. Hence, the automatic backups can only be used to restore on the same database host or create a new database in the same availability domain.

If you want to restore the database into another availability domain, OCI region, or on-premises, you need access to the Object Storage bucket where the backup files reside. To do so, you can create your own RMAN backup into a user-defined Object Storage bucket using:

In this blog post, we will create a manual backup using the Backup Module and restore it into a new host. The same procedure applies if you want to restore into another availability domain, OCI region, or on-premises. One key aspect to consider is copying the TDE master encryption keys from the Cloud to the target host.

The Environment

  • In Oracle Cloud I’m using Database Cloud Service on Virtual Machines (IP=130.61.131.90), Enterprise Edition, version 19.11.
  • For the new host I’m using a compute VM in Oracle Cloud (IP=130.61.43.198).
  • User defined standard Object Storage bucket named backup1911_bucket.
  • Oracle Cloud user (sinan.petrus.toma@oracle.com) and authentication token (GoC-yt:YF18BkeoCe3).
  • The target host must have network access to the Object Storage in Oracle Cloud region where the backup resides. For on-premises use VPN or FastConnect. For other OCI regions use Remote VCN Peering.

Backup the Cloud Database

Step 1: Install Oracle Database Cloud Backup Module in Oracle Cloud

Download the opc_installer.zip file and unzip it into a user-defined directory. On DBCS VMs, the module is already available by default in the /opt/oracle/oak/pkgrepos/oss/odbcs/ directory.

ssh ope@130.61.131.90
sudo su - oracle
mkdir /home/oracle/lib/
cd /opt/oracle/oak/pkgrepos/oss/odbcs/
#replace the values according to your environment
java -jar opc_install.jar -opcId 'sinan.petrus.toma@oracle.com' -opcPass 'GoC-yt:YF18BkeoCe3' -container backup1911_bucket -walletDir ~/hsbtwallet/ -libDir ~/lib/ -configfile ~/config -host https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/oci_core_emea_od_kasher

Oracle Database Cloud Backup Module wallet created in directory /home/oracle/hsbtwallet.
Oracle Database Cloud Backup Module initialization file /home/oracle/config created.
Downloading Oracle Database Cloud Backup Module Software Library from file opc_linux64.zip.
Download complete.

Check the configuration file:

OPC_HOST=https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/oci_core_emea_od_kasher
OPC_WALLET='LOCATION=file:/home/oracle/hsbtwallet CREDENTIAL_ALIAS=alias_opc'
OPC_CONTAINER=backup1911_bucket

Step 2: Create a manual RMAN backup

Create an RMAN backup into the user-defined Object Storage bucket using the backup module from step 1:

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

-- to backup all datafiles including the ones for pdb$seed
select file# from v$datafile
RMAN> run {
allocate channel c1 device type sbt PARMS 'SBT_LIBRARY=/home/oracle/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/config)';
backup datafile 1,3,4,5,6,7,8,9,10,11,12;
}

RMAN> run {
allocate channel c1 device type sbt PARMS 'SBT_LIBRARY=/home/oracle/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/config)';
backup archivelog all;
}
...
Starting Control File and SPFILE Autobackup at 05-JUL-21
piece handle=c-470444325-20210705-1e comment=API Version 2.0,MMS Version 19.0.0.1
Finished Control File and SPFILE Autobackup at 05-JUL-21
released channel: c1

Step 3: Copy the TDE wallet file from the Cloud to the target host

Copy the wallet files to the target host:

#target host: ip=130.61.43.198
mkdir -p /home/oracle/onpremhost/tdewallet/tde/

#cloud
scp -p /opt/oracle/dcs/commonstore/wallets/tde/CDB1911_fra16m/*wallet.* oracle@130.61.43.198:/home/oracle/onpremhost/tdewallet/tde/

Restore the Database to the Target Host

Step 4: Install Oracle Database Cloud Backup Module on the target host

Download the opc_installer.zip file and unzip it into a user-defined directory, e.g. /home/oracle:

unzip opc_installer.zip
cd /home/oracle/opc_installer/opc_installer/
mkdir /home/oracle/lib
java -jar opc_install.jar -opcId 'sinan.petrus.toma@oracle.com' -opcPass 'GoC-yt:YF18BkeoCe3' -container backup1911_bucket -walletDir ~/hsbtwallet/ -libDir ~/lib/ -configfile ~/config -host https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/oci_core_emea_od_kasher

Oracle Database Cloud Backup Module wallet created in directory /home/oracle/hsbtwallet.
Oracle Database Cloud Backup Module initialization file /home/oracle/config created.
Downloading Oracle Database Cloud Backup Module Software Library from file opc_linux64.zip.
Download complete.

Step 5: Get the DBID of the cloud database

If your cloud database is still accessible:

-- cloud database
SQL> select dbid from v$database;

      DBID
----------
 470444325

If your database in the cloud is completely lost, then, from the target host:

#check connectivity to Cloud Object Storage
curl -u 'sinan.petrus.toma@oracle.com:GoC-yt:YF18BkeoCe3' -v https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/oci_core_emea_od_kasher
...
Connection #0 to host swiftobjectstorage.eu-frankfurt-1.oraclecloud.com left intact

#get the DBID from the control file name
curl -u 'sinan.petrus.toma@oracle.com:GoC-yt:YF18BkeoCe3' -v https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/oci_core_emea_od_kasher/backup1911_bucket?prefix=sbt_catalog/c-

#in the output, look for the following string
...sbt_catalog/c-470444325-20210705-19/metadata.xml...

The DBID is 470444325.

Step 6: Define the TDE Wallet Location on the target host

Set the TDE wallet location:

#set the environment variables
export ORACLE_SID=CDB1911
export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1/

#create a dummy pfile with the following content:
vi /home/oracle/dummy.ora
db_name=CDB1911
wallet_root=/home/oracle/onpremhost/tdewallet/
tde_configuration='KEYSTORE_CONFIGURATION=FILE'

sqlplus / as sysdba

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/dummy.ora';

ORACLE instance started.

Copy the local TDE wallet to the /home/oracle/onpremhost/tdewallet/tde/ directory.

Step 7: Restore the spfile

Restore the spifle into a pfile:

rman target /

connected to target database: CDB1911 (not mounted)

RMAN> set dbid 470444325;

executing command: SET DBID

RMAN> run {
  allocate channel c1 device type sbt PARMS 'SBT_LIBRARY=/home/oracle/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/config)';
  RESTORE SPFILE TO PFILE '/home/oracle/onpremhost/pfile.ora' FROM AUTOBACKUP;
}
...
Finished restore at 05-JUL-21
released channel: c1

Edit the pfile.ora to reflect the target host’s directory structure, changing the locations for control files, datafiles, recovery files, online redo logs, and audit files.

#this is just an example, usually you'll use different locations
mkdir /home/oracle/onpremhost/controlfiles/
mkdir /home/oracle/onpremhost/datafiles/
mkdir /home/oracle/onpremhost/fra/
mkdir /home/oracle/onpremhost/redo/
mkdir /home/oracle/onpremhost/audit/

vi /home/oracle/onpremhost/pfile.ora
*.control_files='/home/oracle/onpremhost/controlfiles/control01.ctl','/home/oracle/onpremhost/controlfiles/control02.ctl'
*.db_create_file_dest='/home/oracle/onpremhost/datafiles/'
*.db_recovery_file_dest='/home/oracle/onpremhost/fra/'
*.db_create_online_log_dest_1='/home/oracle/onpremhost/redo/'
*.audit_file_dest='/home/oracle/onpremhost/audit/'

#add the wallet_root and tde_configuration parameter
*.wallet_root=/home/oracle/onpremhost/tdewallet/
*.tde_configuration='KEYSTORE_CONFIGURATION=FILE'

Create spfile from pfile and restart the database instance:

SQL> create spfile='/u01/app/oracle/product/19c/dbhome_1/dbs/spfileCDB1911.ora' from pfile='/home/oracle/onpremhost/pfile.ora';

File created.

SQL> startup force nomount;

Oracle instance started

Step 8: Restore the control file

Restore the controle file from the Cloud Object Storage and mount the database:

RMAN> run {
  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;
}
...
Finished restore at 05-JUL-21
released channel: c1

RMAN> alter database mount;

Statement processed

Step 9: Restore and Recover the database

Restore and recover the database files from the Cloud Object Storage and open the database:

RMAN> run {
  allocate channel c1 device type sbt PARMS 'SBT_LIBRARY=/home/oracle/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/config)';
  RESTORE datafile 1,3,4,5,6,7,8,9,10,11,12;
}

RMAN> run {
  allocate channel c1 device type sbt PARMS 'SBT_LIBRARY=/home/oracle/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/config)';
  recover database until available redo;
}

RMAN> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Statement processed

RMAN> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/home/oracle/onpremhost/rman_change_track.f';

Statement processed

RMAN> alter database open resetlogs;

Statement processed

Step 10: Create new Master Encryption Keys

Create new TDE master encryption keys for the CDB$ROOT and all PDBs using the container=all clause:

SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY MyVerySecPW__11 WITH BACKUP container=all;

keystore altered.

Conclusion

Automatic backups are great and all work is done for you automatically. The automatic backups can be used to restore the database on the same host or to create a new database in the same availability domain.

If you need more flexibility, you can easily create manual backups stored in user-defined buckets while you continue to benefit from the highly available (3-way mirrored), durable (99.999999999 %), and very cost-efficient ($0.0255 per GB/month) Object Storage.

Further Reading

Would you like to get notified when the next post is published?