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:
- dbcli utility for Database Cloud Service virtual and bare metal machines, bkup_api utility for Exadata Cloud Service. Or,
- Database Cloud Backup Module for virtual, bare metal, and Exadata machines. And for on-premises Oracle databases as well.
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, e.g. /home/oracle:
mkdir /home/oracle/lib
cd /home/oracle/
unzip opc_installer.zip
cd /home/oracle/opc_installer/opc_installer/
#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:
mkdir /home/oracle/lib
cd /home/oracle/
unzip opc_installer.zip
cd /home/oracle/opc_installer/opc_installer/
#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.
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
- DBCS VM and BM: Backing Up a Container Database to Object Storage Using RMAN
- ExaCS: Managing Exadata Database Backups by Using bkup_api
- Recovering a Container Database from Object Storage
- Recovering an Exadata Database by Using RMAN
- Restoring to a New Database Host
- Administer OCI Vault encryption keys for ExaCS
- About Migrating Back from an External Keystore
Database Heartbeat 