Disaster Recovery

Restore DBCS VM Databases from Automatic Backup across Availability Domains

Introduction

Oracle Cloud VM DB Systems provide fully automated backups that can be enabled by the click of a button. The backups are stored in an Oracle-managed bucket. The Cloud Tooling enables you to use these backups for an in-place restore or create a new database in the same Availability Domain.

In case you want to restore the database into another Availability Domain (AD), some manual work is needed as of today. This blog post will take you through the steps needed to achieve this goal.

The Environment

  • Source: VM DB System in AD1. The hostname is host01. Database version 19.12, using ASM, and automatic backups enabled. DB Unique name is CDB01_fra1t2.
  • Target: VM DB System in AD2. The hostname is host02. Database version 19.12, using ASM. DB Unique name is CDB01_fra2vs.

As VM DB Systems support only one CDB per VM, we need to provision a placeholder VM DB System first, delete the database files, and finally restore the database into that VM. The database name must be the same as the source database, in this case, CDB01.

Preparing

Step 0: Delete the Target Database created by Cloud Tooling

Remove the database files from the ASM disk group manually and keep the /etc/oratab entries and the srvctl registration.

Create a script to remove all unnecessary database files as follows:

[oracle@host02 ~]$ sqlplus / as sysdba

SQL> set heading off linesize 999 pagesize 0 feedback off trimspool on
SQL> spool /tmp/remove_files.sh
SQL> 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;
SQL> spool off

[oracle@host02 ~]$ chmod 777 /tmp/remove_files.sh

Stop the target database:

[oracle@host02 ~]$ srvctl stop database -db CDB01_fra2vs -o immediate

Switch to the grid user and set the environment:

[opc@host01 ~]$ sudo su - grid
[grid@host01 ~]$ . oraenv
ORACLE_SID = [grid] ? +ASM1
ORACLE_HOME = [/home/oracle] ? /u01/app/19.0.0.0/grid/
The Oracle base has been set to /u01/app/grid

Remove the existing data files, temp files, and online redo log files. As grid user:

[grid@host02 ~]$ /tmp/remove_files.sh

Step 1: Get the Source Database ID

Connect to your source database and query the DBID:

SQL> select dbid from v$database;

             DBID
------------------
1627153490

Step 2: Get the RMAN Channel Configuration of the Source Database

On the source host, connect to RMAN, and show the channel configuration:

[oracle@clouddb ~]$ rman target /

RMAN> show CHANNEL;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name CDB01_FRA1T2 are:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' FORMAT   '%d_%I_%U_%T_%t' PARMS  'SBT_LIBRARY=/opt/oracle/dcs/commonstore/oss/CDB01_fra1t2/libopc.so ENV=(OPC_PFILE=/opt/oracle/dcs/commonstore/oss/CDB01_fra1t2/187540e2-f699-4527-be88-e2c351100b85/opc_CDB01_fra1t2.ora)';

Step 3: Copy the RMAN Configuration File from Source to Target

Copy the RMAN configuration file for the Backup Module to the target host where you want to restore the database:

[oracle@host01 ~]$ scp -p /opt/oracle/dcs/commonstore/oss/CDB01_fra1t2/187540e2-f699-4527-be88-e2c351100b85/opc_CDB01_fra1t2.ora oracle@host02:/home/oracle/object_storage/
opc_CDB01_fra1t2.ora                                                                                               100%  253   122.1KB/s   00:00

This file contains the information about the Object Storage endpoint, wallet location where the Object Storage credentials are stored, and the Object Storage bucket name:

[oracle@host01 ~]$ cat /opt/oracle/dcs/commonstore/oss/CDB01_fra1t2/187540e2-f699-4527-be88-e2c351100b85/opc_CDB01_fra1t2.ora
OPC_HOST=https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/dbbackupfra
OPC_WALLET='LOCATION=file:/opt/oracle/dcs/commonstore/oss/CDB01_fra1t2/187540e2-f699-4527-be88-e2c351100b85 CREDENTIAL_ALIAS=alias_opc'
OPC_CONTAINER=bAp5FD7wqEI9g8C7CXTv

Step 4: Copy the Wallet containing the Object Storage Credentials from Source to Target

Copy the wallet file to the target host where you want to restore the database:

[oracle@host01 ~]$ scp -p /opt/oracle/dcs/commonstore/oss/CDB01_fra1t2/187540e2-f699-4527-be88-e2c351100b85/cwallet.sso oracle@host02:/home/oracle/object_storage/
cwallet.sso                                                                                               100%  116KB  38.3MB/s   00:00

This wallet file contains the Object Storage Credentials (OCI user and password) to authenticate against the Object Storage service and get access to the Object Storage bucket:

[oracle@host01 ~]$ mkstore -wrl /opt/oracle/dcs/commonstore/oss/CDB01_fra1t2/187540e2-f699-4527-be88-e2c351100b85/cwallet.sso -list
Oracle Secret Store Tool Release 23.0.0.0.0 - Production
Version 23.0.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Oracle Secret Store entries:
oracle.security.client.connect_string1
oracle.security.client.password1
oracle.security.client.username1

Attention! The Object Storage credentials are rotated every time a full backup is executed and in regular intervals (approximately every 4 days). So make sure to have the current wallet before restoring.

Step 5: Copy the library for the Backup Module from Source to Target

Optional: copy the Backup Module library to the target host:

[oracle@host01 ~]$ scp -p /opt/oracle/dcs/commonstore/oss/CDB01_fra1t2/libopc.so oracle@host02:/home/oracle/object_storage/
libopc.so                                                                                               100%   93MB  78.0MB/s   00:01

Alternatively, use the libopc library already existing on the target host.

Step 6: Copy the TDE wallet containing the TDE master encryption keys

The TDE wallet location is shown in the sqlnet.ora file:

[oracle@host01 ~]$ cat $ORACLE_HOME/network/admin/sqlnet.ora | grep ENCRYPTION_WALLET_LOCATION
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/dcs/commonstore/wallets/tde/$ORACLE_UNQNAME)))

[oracle@host01 ~]$ scp -p /opt/oracle/dcs/commonstore/wallets/tde/CDB01_fra1t2/*wallet.* oracle@host02:/opt/oracle/dcs/commonstore/wallets/tde/CDB01_fra2vs
cwallet.sso                                                                                               100% 5880     4.5MB/s   00:00
ewallet.p12                                                                                               100% 5819     5.1MB/s   00:00

Prepare the Target Host

Step 7: Change the Wallet Location in the Wallet File for the Backup Module

Change the OPC_WALLET entry in the opc_CDB01_fra1t2.ora file to point to the location where the wallet is stored on the target host, which is /home/oracle/object_storage/:

[oracle@host02 ~]$ vi /home/oracle/object_storage/opc_CDB01_fra1t2.ora
OPC_HOST=https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/dbbackupfra
OPC_WALLET='LOCATION=file:/home/oracle/object_storage/ CREDENTIAL_ALIAS=alias_opc'
OPC_CONTAINER=bAp5FD7wqEI9g8C7CXTv

Step 8: Check the Copied Files

On the target host, you should now have the following files:

The RMAN configuration file, wallet, and library for the Backup Module:

[oracle@host02 ~]$ ls -l /home/oracle/object_storage/
total 95260
-rw------- 1 oracle oinstall   118863 Dec  2 13:34 cwallet.sso
-rw-r--r-- 1 oracle oinstall 97416624 Dec  2 13:33 libopc.so
-rw-r--r-- 1 oracle oinstall      200 Dec  2 20:12 opc_CDB01_fra1t2.ora

The TDE password and auto-login wallets:

[oracle@host02 ~]$ ls -ltr /opt/oracle/dcs/commonstore/wallets/tde/CDB01_fra2vs/
total 68
-rw------- 1 oracle oinstall  5819 Dec  1 16:48 ewallet.p12
-rw------- 1 oracle oinstall  5880 Dec  1 16:48 cwallet.sso

Restore into the Target Host

Step 9: Configure your RMAN Channel

In your RMAN commands, use the following RMAN Channel configuration pointing to the Backup Module files on the target host:

allocate channel c1 device type sbt PARMS 'SBT_LIBRARY=/home/oracle/object_storage/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/object_storage/opc_CDB01_fra1t2.ora)';

Step 10: Use the existing SPFILE

We are going to use the existing SPFILE on the target host instead of restoring the SPFILE of the source database. It already contains all parameters needed. In case you need to adjust some parameters to match the source database configuration, feel free to do so now or afterward.

Step 11: Restore the Control File

It’s essential to set the DBID before running the restore command for the backups to be found:

[oracle@host02 ~]$ sqlplus / as sysdba
SQL> startup nomount;
ORACLE instance started.

[oracle@host02 ~]$ rman target /
connected to target database: CDB01 (not mounted)

RMAN> set dbid 1627153490;
executing command: SET DBID

RMAN> run {
  allocate channel c1 device type sbt PARMS 'SBT_LIBRARY=/home/oracle/object_storage/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/object_storage/opc_CDB01_fra1t2.ora)';
  restore controlfile from autobackup;
}
...
output file name=+RECO/CDB01_FRA2VS/CONTROLFILE/current.256.1090245019
Finished restore at 02-DEC-21
released channel: c1

Step 12: Restore and Recover the Database

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

RMAN> alter database mount;
Statement processed

RMAN> run {
  allocate channel c1 device type sbt PARMS 'SBT_LIBRARY=/home/oracle/object_storage/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/object_storage/opc_CDB01_fra1t2.ora)';
  restore database;
}
...
Finished restore at 02-DEC-21
released channel: c1

RMAN> run {
  allocate channel c1 device type sbt PARMS 'SBT_LIBRARY=/home/oracle/object_storage/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/object_storage/opc_CDB01_fra1t2.ora)';
  recover database until available redo;
}
...
Finished recover at 02-DEC-21
released channel: c1

RMAN> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Statement processed

RMAN> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Statement processed

RMAN> alter database open resetlogs;
 
Statement processed

Step 13: Change the DBID of the Target Database

Your target database has the same DBID as the source now.

Change the DBID using the DBNEWID (nid) Utility:

[oracle@host02 ~]$ srvctl stop database -db CDB01_fra2vs -o immediate
[oracle@host02 ~]$ srvctl start database -db CDB01_fra2vs -o mount

[oracle@host02 ~]$ nid TARGET=SYS/<your_SYS_password>@CDB01_FRA2VS
DBNEWID: Release 19.0.0.0.0 - Production on Thu Dec 2 22:32:19 2021
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to database CDB01 (DBID=1627153490)
Connected to server version 19.12.0
Control Files in database:
    +RECO/CDB01_FRA2VS/CONTROLFILE/current.256.1090245019
Change database ID of database CDB01? (Y/[N]) => y
...
Database ID for database CDB01 changed to 1627285172.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

[oracle@host02 ~]$ sqlplus / as sysdba
SQL> startup mount;
SQL> alter database open resetlogs;
SQL> select dbid from v$database;

             DBID
------------------
1627285172

Step 14: Enable Automatic Backups for the Target Database

Enable Automatic Backups for the new database from the database detail’s page in the OCI Console.

Conclusion

Automatic backups are great and all work is done for you automatically. Even though the Object Storage bucket is managed by Oracle, you can access it from another Availability Domain (AD) manually and restore your database from the automatic backups across ADs.

Further Reading

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