Restoring OCI object store backups onto Exadata Cloud Service

 This blog post covers the steps necessary to restore backups made using the Oracle Database Cloud backup Service, onto Exadata Cloud Service in the event of DR situation.  

In this post, I am going to assume that you have already configured an ExaCS environment and have a VM defined to restore the database into.

The database I am going to use for testing has the characteristics below.

DBNAME:    bgrenndb

DB version:    19.19

DB_UNIQUE_NAME: BGRENNDB_HS7_IAD/

NOTE:  have been creating "KEEP" backups for this database and I want to use one of them to restore from in OCI.  This may not be case, you might be sending a weekly full backup, and a daily incremental backup.

Prerequisites:

There are some prerequisites that I found are important to make the restoration go smoothly

• Backup your TDE encryption wallet - It is important to make sure you have the encryption keys for your database.  When using the Oracle Database backup service, ALL backup pieces are encrypted, including the backups of the spfile, and controlfile. It is critical to have the encryption wallet to restore the backups.  You want to backup just the "ewallet.p12" file. I recommend you DO NOT backup the cwallet.sso file, as this is the autologin wallet.  Best MSA (Maximum Security Architecture) practice is to backup the wallet stored separate from the database backups, and recreate the autologin wallet using the password. This is much more secure than backing up the autologin wallet.
• Store the backup logs in a bucket - When restoring from a database backup you need to determine the backup pieces that are needed, especially when restoring the controlfile.  If you store the log files, it will make it much easier to restore the database without an RMAN catalog.
• Create a bucket for DB backups and Metadata - This is where the database backups will be stored, and I recommend adding a retention lock to the bucket.  Instructions on creating the retention lock can be found here.

PRO TIP : The easiest way to upload the RMAN backup log files, and backups of the wallets is to use Pre-Authenticated URLs (PARS). These make it secure (because they can only be used to drop the backup into a bucket), and they also make it easier to deal with authentication.

Steps to restore a database from object storage.

1) Create a stub database 

Because I want to use the tooling in OCI to manage my database, I am starting with a "stub" database with the same name as my backed up database, and it should be the same DB release  or higher. 

NOTE: When creating the stub database, you should use the same password as you are using for the original database.  In my case the SYS password, and the wallet password are the same.  If your wallet password is different from the SYS password, you can create the stub database with different passwords.

Stub database

DBNAME:    bgrenndb

DB version:    19.22

DB_UNIQUE_NAME: BGRENNDB_S39_IAD

PRO TIP  - In hindsight, I should have named the DB_UNIQUE_NAME the same as my production database to make it easier to restore.

2) Backup a copy of the stub SPFILE

In sqlplus I backed up the SPFILE to a PFILE that I will use later to ensure my parameters which are local to this VM are correct when I restore my database.

SQL> create pfile='/tmp/bgrenndb.origpfile' from spfile;

3) Shutdown the database and delete all files.

I shut down the database in srvctl since this is a RAC instance

#> srvctl stop database -d bgrenndb_s39_iad

I deleted all the files on ASM from both +DataC1 and +RecoC1 for this database

4) Download and configure the Oracle Database Backup Service

You need to download the Oracle Database backup service installation jar file.  Once this is downloaded, you need to run the installation which will download the library, create a wallet file, and create the configuration file used by the library.

Instructions on how to do this are documented in my last blog post you can find here.

Pro Tip : Since the I am restoring the database to a RAC cluster it would be easier if I install the Database Service configuration to a shared locations across all nodes.  In my environment, I am going to install the Backup Service configuration in "/acfs01/dbaas_acfs/bgrenndb" in a directory called opc.

Once I go through the installation, I will have the following directories

/acfs01/dbaas_acfs/bgrenndb/opc/lib        --> contains libopc.so used during restore

/acfs01/dbaas_acfs/bgrenndb/opc/config    --> backupconfig.ora containg the library parameters

/acfs01/dbaas_acfs/bgrenndb/opc/wallet     --> contains the authentication information

5) Download and configure the TDE Wallet from my backup

The easiest way to to download the most current wallet from OCI object storage is by using a Pre-authenticated URL (PAR).  I created a PAR on the object and then used curl to download my wallet file.

curl -o {name of the restored file } {PAR which is a long URL pointing to the object}

Once I download the wallet, I am going to :

• Go to the wallet directory (under WALLET_ROOT/tde and delete the original wallet files (ewallet.p12 and cwallet.sso).
• Replace the ewallet.p12 with my downloaded wallet from my source database.

Now that I have the wallet downloaded, I need to create the autologin wallet.

NOTE: it is not recommended to backup the autologin wallet, just the passworded wallet

To create the autologin wallet from the passworded wallet I execute

>mkstore -wrl {wallet_location} -createSSO

I enter the password for the wallet, and it creates the autologin wallet for me.

6) Startup the database nomount and validate wallet

Now that I have the wallet in the correct location, I created a basic pfile.  I only need the following parameters.  You can look at the backup of the stub spfile to get the appropriate setting for the "control_files", "db_unque_name", and proper disk groups for DATA and RECO.

*.control_files='+DATAC1/BGRENNDB_S39_IAD/CONTROLFILE/current.327.1166013711'
*.db_name='bgrenndb'
*.enable_pluggable_database=true
*.db_recovery_file_dest='+RECOC1'
*.db_recovery_file_dest_size=6538932518912
*.db_unique_name='bgrenndb_s39_iad'
*.diagnostic_dest='/u02/app/oracle'
*.pga_aggregate_target=5000m
*.processes=2048
*.sga_target=7600m
*.tde_configuration='keystore_configuration=FILE'
*.wallet_root='/var/opt/oracle/dbaas_acfs/bgrenndb/wallet_root'

NOTE: I am going to restore the spfile, so this is only temporary.

I started the database nomount with this small pfile

SQL> startup nomount pfile=bgrenndb.ora;

Once the database started, I used the first TDE query from my blog to check the status of the wallet.  You want to make sure the encryption wallet is OPEN before proceeding.

 INST_ID PDB Name   Type       WRL_PARAMETER                                      Status                         WALLET_TYPE          KEYSTORE Backed Up
---------- ---------- ---------- -------------------------------------------------- ------------------------------ -------------------- -------- ----------
         1            FILE       /var/opt/oracle/dbaas_acfs/bgrenndb/wallet_root/td OPEN                           UNKNOWN              NONE     NO
                                 e/

7) Locate the name of the SPFILE and Controlfile backup pieces

As part of my backup script, I also uploaded the log file associated with the backup. This gave me

• The DBID
• The name of the spfile backup piece associated with the backup I am going to restore
• The name of the controlfile backup piece associated with the backup I am going to restore

8) Restore the spfile and update it.

Using the backup piece name, I restored my spfile to the file system, and created a pfile copy of it so that I can make a few changes.

RMAN>
 run {
 allocate CHANNEL c1 TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/acfs01/dbaas_acfs/bgrenndb/opc/lib/libopc.so,SBT_PARMS=(OPC_PFILE=/acfs01/dbaas_acfs/bgrenndb/opc/config/backupconfig.ora)';
 set dbid=367184428;
 restore spfile to '/tmp/bgrenndb.spfile' from 'BGRENNDB_KEEP_20240227_3776_1' ;
}

RMAN> 2> 3> 4> 5>
allocated channel: c1
channel c1: SID=2142 device type=SBT_TAPE
channel c1: Oracle Database Backup Service Library VER=19.0.0.1

executing command: SET DBID

Starting restore at 12-APR-24

channel c1: restoring spfile from AUTOBACKUP BGRENNDB_KEEP_20240227_3776_1
channel c1: SPFILE restore from AUTOBACKUP complete
Finished restore at 12-APR-24
released channel: c1

RMAN> create pfile='/tmp/bgrenndb.pfile' from spfile='/tmp/bgrenndb.spfile';

Statement processed

I then edited my pfile, "/tmp/bgrenndb.pfile" and made the following changes.

• I changed custer_interconnects to match the entries in the original spfile from the stub.
• I changed entries that were pointing to DATAC6 and RECOC6 to DATAC1 and RECOC1 to match the VM I am restoring to.
• I changed the REMOTE_LISTENER to match the original spfile.
• I changed the bgrenndb_hs7_iad to bgrenndb_s39_iad since that will be new db_unique_name.

I then bounced the database and started it up NOMOUNT again with the new pfile

9) Restore the controlfile

Now I am going to identify the backup location of the controlfile I want, and restore the control file 

RMAN>

 run {
  allocate CHANNEL c1 TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/acfs01/dbaas_acfs/bgrenndb/opc/lib/libopc.so,SBT_PARMS=(OPC_PFILE=/acfs01/dbaas_acfs/bgrenndb/opc/config/backupconfig.ora)';
  set dbid=367184428;
 restore controlfile from 'BGRENNDB_KEEP_20240227_3777_1' ;
}
4> 5>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=9 instance=bgrenndb1 device type=SBT_TAPE
channel c1: Oracle Database Backup Service Library VER=19.0.0.1

executing command: SET DBID

Starting restore at 12-APR-24

channel c1: restoring control file
channel c1: restore complete, elapsed time: 00:00:04
output file name=+DATAC1/BGRENNDB_S39_IAD/CONTROLFILE/current.332.1166124375
Finished restore at 12-APR-24
released channel: c1

Once restored the controlfile, I updated the pfile to the location the controlfile was restored to.

Then I created the spfile from pfile.

SQL> create spfile from pfile='/tmp/bgrenndb.pfile';

I then shutdown the instance and started it mount and ensured the parameters were correct, and once again ensured the wallet was open.

10) Change the channel configuration in RMAN and restore

I changed the channel configuration to match the backup service settings, and restored the database using the TAG

 restore database from tag=KEEP_BGRENNDB_HS7_IAD_20240227;

 recover database from tag=KEEP_BGRENNDB_HS7_IAD_20240227;

11) I opened the database reset logs

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 04/12/2024 19:59:12
ORA-19751: could not create the change tracking file
ORA-19750: change tracking file: '+DATAC6/BGRENNDB_HS7_IAD/CHANGETRACKING/ctf.898.1160234109'
ORA-17502: ksfdcre:4 Failed to create file +DATAC6/BGRENNDB_HS7_IAD/CHANGETRACKING/ctf.898.1160234109
ORA-15046: ASM file name '+DATAC6/BGRENNDB_HS7_IAD/CHANGETRACKING/ctf.898.1160234109' is not in single-file creation form
ORA-17503: ksfdopn:2 Failed to open file +DATAC6/BGRENNDB_HS7_IAD/CHANGETRACKING/ctf.898.1160234109
ORA-15001: diskgroup "DATAC6" does not ex

Oops, I then disabled block change tracking.

RMAN> alter database disable block change tracking;

RMAN> alter database open resetlogs;

Statement processed
PL/SQL package SYS.DBMS_BACKUP_RESTORE version 19.19.00.00 in TARGET database is not current
PL/SQL package SYS.DBMS_RCVMAN version 19.19.00.00 in TARGET database is not current

Now it was successful, and I see I have to upgrade the database.

12) Patch the database from 19.19 to 19.22

I ran through the patch upgrade process 

> cd $ORACLE_HOME/OPatch

>./datapatch -verbose

Summary :

Once I patched the database, I turned on automatic backups which was successful. This was a great sign that I had everything correct and my new database ready to go !

Autonomous Recovery Service Checklist

 Utilizing the Autonomous Recovery Service (ARS) for your Oracle Databases in OCI is the best method for backing up your Oracle databases.  In this post I will go through the steps required to successfully implement this service.  To learn more about this service you can find the documentation here.

1. Ensure your tenancy's resource limits are adequate

Before implementing the ARS, you first must see what the resource settings are in your tenancy. You want to make sure that the "Space Used for Recovery Window (GB)" and "Protected Database Count" allow for the number of databases, and backup size of the databases you want to utilize the service for.

Below is what you would see for the ARS. This is a screen shot from my free tenancy.  In your tenancy you should see what the current limits are.  When looking at the root compartment, this will show you the Limits and usage for the whole tenancy.

If you need to increase the limits for your tenancy click on the 3 dots to the right of the limit you want to increase. It will bring up a choice to "Open Support Request".  After choosing "Open Support Request" you will see a window that allows you to request a limit increase for your tenancy.

NOTE: There is a second choice when clicking on the 3 dots to "Create Quota Policy Stub". Using the stub displayed you can limit the quota of specific compartments.  This can be used to limit the usage for your "dev" compartment for example, ensuring there is space within your limits for production

2. Verify the policies for the tenancy

A) Set root compartment policies for service

Tenancy Polices for ARS

Policy Statement	Purpose
Allow service database to manage recovery-service-family in tenancy	Enables the OCI Database Service to access protected databases, protection policies, and Recovery Service subnets within your tenancy.
Allow service database to manage tagnamespace in tenancy	Enables the OCI Database Service to access the tag namespace in a tenancy.
Allow service rcs to manage recovery-service-family in tenancy	Enables Recovery Service to access and manage protected databases, Recovery Service subnets, and protection policies within your tenancy.
Allow service rcs to manage virtual-network-family in tenancy	Enables Recovery Service to access and manage the private subnet in each database VCN within your tenancy. The private subnet defines the network path for backups between a database and Recovery Service.
Allow group admin to manage recovery-service-family in tenancy	Enables users in a specified group to access all Recovery Service resources. Users belonging to the specified group can manage protected databases, protection policies, and Recovery Service subnets.

B) Allow users (in group) to manage the Recovery Service

Group Policy Statement by Compartment

Policy Statement	Create In	Purpose
Allow group {group name} to manage recovery-service-policy in compartment {location}	Compartment that owns the protection policies.	Enables all users in a specified group to create, update, and delete protection policies in Recovery Service.

C) Allow users (in group) to manage the required subnet for the Recovery Service

Group Policy Statement by Compartment

Policy Statement	Create In	Purpose
Allow Group {group name} to manage recovery-service-subnet in compartment {location}	Compartment that owns the Recovery Service subnets.	Enables all users in a specified group to create, update, and delete Recovery Service subnets.

3. Configure Network Resources for Recovery Service

The Recovery Service uses Private endpoints to control backup traffic between your database and the recovery service.  Below is the architecture.

Each Recovery service subnet needs to be created within the VNC where your database resides.  

The minimum size of the subnet is /24 (256 IP addresses).  You can create a new subnet, or use an preexisting subnet in you database VCN.  This subnet must be IPv4.

Security rules (implemented through Security Lists, or Network Security Groups) for the private subnet must include stateful ingress rules to allow destination ports 8005 and 2484.

NOTE: You can use a public subnet, but it is not recommended for security reasons.

This private subnet must be registered as a Recovery Service Subnet.

Checklist for Security rules (Security List or NSG)

1. Rule 1 - Ingress. Allow HTTPS traffic from anywhere

• Stateless: No (All rules must be stateful)
• Source Type: CIDR
• Source CIDR : CIDR of the VCN where the database resides
• IP Protocol: TCP
• Source Port Range: All
• Destination Port Range: 8005

2. Rule 2 - Ingress. Allow SQLNet traffic from anywhere

• Stateless: No (All rules must be stateful)
• Source Type: CIDR
• Source CIDR : CIDR of the VCN where the database resides
• IP Protocol: TCP
• Source Port Range: All
• Destination Port Range: 2484

NOTE: If your VCN restricts network traffic between subnets, ensure to add an egress rule for ports 2484, and 8005 from the database subnet to the Recovery Service subnet that you create.

3. Register the subnet with recovery service

Under Oracle Database --> Database Backups you need to click on "Recovery Service Subnets" and register the Recovery Service Subnet.

4. Ensure the Recovery Service Subnet can communicate with Oracle services.

The Recovery Service Subnet that you registered needs to communicate with the Recovery Service. In order to access the service, the routing table for this subnets needs to include "All IAD Services In Oracle Services Network".

If all these pieces are in place you should be ready to successfully configure your database backups to go the Recover Service for backup.

Short checklist

1. Check your limits and quotas for the recovery service
2. Create the policies for the Recovery Service, and groups (users) to manage the recovery service
3. Create the subnet for the Recovery Service making sure you have the correct security settings, and the subnet has access to Oracle services
4. Register the subnet as the Recovery Service Subnet.

DB Script management through pre-authenticated URLs

 Pre-authenticated URLs in OCI are fast becoming one of my favorite features of using object storage.  In this blog post I will explain how I am using them for both:

• Storing the current copy of my backup scripts and dynamically pulling it from my central repository
• uploading all my logs files to a central location

PROBLEM:

The problem I was trying to solve, is that I wanted to create a script to run on all my database nodes to create a weekly archival backup.

Since I have databases that are both Base DB databases, and ExaCS I found that I was continuously making changes to my backup script.  Sometimes it was checking something different in my environment, and sometimes it was improving the error checking.

Each time I made a change, I was going out to every DB host and copying the new copy of my script.

Eventually I realized that Pre-authenticated URLs could not only help me ensure all my DB hosts are running the current copy of my backup script, they could be the central log location.

Solution:

Solution #1 - Script repository

The first problem I wanted to solve, was that I wanted to configure a script repository that I could dynamically pull the most current copy of my scripts from. Since I am running in OCI, I was looking for a "Cloud Native" solution rather than using NFS mounts that are shared across all my DB hosts.

To complicate things, I have databases that are running in different tenancies.

Step #1 - Store scripts in a bucket

The first step was to create a bucket in OCI to store both the scripts and logs.  Within that bucket, under "More Actions" I chose "Create New Folder" and I created 2 new folders, "logs" and "scripts".

Then within the scripts folder I uploaded by current scripts

• rman.sh - My executable script that will set the environment and call RMAN
• backup.rman - My RMAN script that contains the RMAN command to backup my database.

Step #2 - Create a Pre-Authenticated Request

The next step was to create a Pre-Authenticated request on the "scripts" folder.  Next to the scripts folder I clicked on the  3 dots and chose "Create Pre-Authenticated Request".

On the window that came up, I changed the expiration to be 1 year in the future (the default is 7 days).  I chose the "Objects with prefix" box so that I could download any scripts that I put in this folder to the DB hosts.  I also made sure the "Access Type" is "Permit object reads on those with specified prefix".

I did not chose "Enable Object Listing".

These settings will allow me to download the scripts from this bucket using the Pre-Authenticated URL only.  From this URL you will not be able to list the objects, or upload any changes.

Step #3 - Create wrapper script to download current scripts

Then using the Pre-Authenticated URL in a wrapper script, I download the current copies of the scripts to the host and then executed my execution script (rman.sh) with a parameter.

Below you can see that I am using curl to download my script (rman.sh) and storing it my local script directory (/home/oracle/archive_backups/scripts).  I am doing the same thing for the RMAN command file.

Once I download the current scripts, I am executing the shell script (rman.sh) .

curl -X GET https://{my tenancy}.objectstorage.us-ashburn-1.oci.customer-oci.com/p/{actual URL is removed }/n/id20skavsofo/b/bgrenn/o/scripts/rman.sh --output /home/oracle/archive_backups/scripts/rman.sh
curl -X GET https://{my tenancy}.objectstorage.us-ashburn-1.oci.customer-oci.com/p/{actual URL is removed }/n/id20skavsofo/b/bgrenn/o/scripts/backup.rman --output /home/oracle/archive_backups/scripts/backup.rman


/home/oracle/archive_backups/scripts/rman.sh $1

Solution #2 - Log repository

The second problem I wanted to solve was to make it easy review the execution of my scripts.  I don't want to go to each DB host and look at the log file.  I want to have the logs stored in a central location that I can check.  Again Pre-Authenticated URLs to the rescue !

Step #1 - Create the Pre-Authenticated URL

In the previous steps I already create a "logs" folder within the bucket. In this step I want to create a Pre-Authenticated URL like I did for the scripts, but in this case I want to use it to store the logs.

Like before I chose "Create Pre-Authenticated Request" for the "logs" folder.

This time, I am choosing "Permit object writes to those with the specified prefix". This will allow me to write my log files to this folder in the bucket, but not list the logs, or download any logs.

Step #2 - Upload the log output from my script

The nice thing was once I implemented Solution #1, and had all of my DB nodes already downloading the current script.  I updated the script to add an upload to object storage of the log file and they will all use my new script.

In my script I already had 2 variables set

• NOW - The current date in "yyyymmdd" format
• LOGFILE - The name of the output log file from my RMAN backup.

Now all I had to do was to add a curl command to upload my log file to the bucket.

Note I ma using the NOW variable to create a new folder under "logs" with the data so that my script executions are organized by date.

curl --request PUT --upload-file /home/oracle/archive_backups/logs/${LOGFILE} https://{My tenancy}.objectstorage.us-ashburn-1.oci.customer-oci.com/p/{URL removed}/n/id20skavsofo/b/bgrenn/o/logs/${NOW}/${LOGFILE}

BONUS

If I wanted to get fancy I could have put my LOGS in a new bucket, and configured  a lifecycle management rule to automatically delete logs after a period of time from the bucket.