Long-Term Retention of Database Backups on Exadata Cloud Service

*** The solution featured in this article is now deprecated. Refer the latest blog post on getting this done on Exadata Cloud Service ***
Many of our customers require their database backups to be stored on Oracle Cloud Infrastructure (OCI) for long term retention due to regulatory compliance reasons in their industries. The most frequent question I get from our Enterprise customers is: How can I archive my backups on OCI for seven years or ten years to meet our regulators requirement?

Unsplash Image

Currently the automated backups provided by default on Exadata Cloud Service (ExaCS) is limited to a maximum of 60 days. So, how do we do customized backups and retain them long-term while keeping security and cost factors into consideration, as well as meet the customers regulatory requirement?

In this article we’ll show you how you can take an on-demand database backups from ExaCS to a customer managed bucket on Oracle Object Storage.

Solution

The Exadata Cloud Service provides an API specifically to create on-demand customer managed backups and provides the capability to either create one-off backups or schedule it automatically in crontab. The API is called bkup_api and it can be used to create both on-demand backups and scheduled backups.

Before proceeding we assume the following pre-requisites are in place :

1. A database exists on the ExaCS VM Cluster. In this example we will use a database called ‘primary’

2. You have enabled Automatic backups on the database. These backups go to an Oracle-managed bucket

3. You have the required IAM permissions on your OCI user for Oracle Object Storage to create bucket and write objects to buckets in any region *[1]

4. You have SSH access to the ExaCS VM CLUSTER

Execution

1. Create a Bucket in your OCI Tenancy

2. Login to the the first ExaCS Node and Check for Node 1 and Create a Backup Configuration file

Login as grid user on any of the ExaCS nodes to determine Node 1

$ $ORACLE_HOME/bin/olsnodes -n

[grid@cust-5qy9z1 ~]$ $ORACLE_HOME/bin/olsnodes -n
cust-5qy9z1 1
cust-5qy9z2 2

As we can see cust-5qy9z1 is the Node 1. SSH into Node 1 of the ExaCS VM Cluster

ssh -i <private_key_path> opc@<node_1_ip_address>
ssh -i mypriv.key opc@cust-5qy9z1

Switch to root user

$ sudo su -

Use the bkup_api get config command to generate a file containing the current backup settings for the database deployment

# /var/opt/oracle/bkup_api/bkup_api get config --file=/tmp/primary.cfg --dbname=primary
$ chmod 600 /tmp/primary.cfg
$ vi /tmp/primary.cfg

Add the details of your Object storage URL, OCI user and Auth token for your user. Please note that in here we will use the Swift storage API’s and the password is an auth token generated from OCI console and not the actual user’s password

EDIT BELOW PARAMETERS AS PER YOUR TENANCY

#### This section is applicable for configuring backups to OSS  ####
# Enable backup to oss container. Can be set to yes or no
bkup_oss=yes
## Below parameters are required if backups to OSS are configured
# Recovery window of oss. Must be between 1 and 90
bkup_oss_recovery_window=90
# OSS url. Example:  https://storage.oraclecorp.com/v1/Storage-test/test
bkup_oss_url=https://swiftobjectstorage.<region>.oraclecloud.com/v1/<tenancy-namespace>/<bucket-name>
# OSS username
bkup_oss_user=oracleidentitycloudservice/youremail@companydomain.com
# OSS password
bkup_oss_passwd=<swift-token>
#*(Will be deprecated, please use bkup_l0_day) Default day on which level0 backups are taken for OSS
bkup_oss_l0_day=
#Default day on which level0 backups are taken
bkup_l0_day=Sun

3. Load the newly created configuration file to the Database ‘primary’

Continuing with root user on Node 1 ExaCS VM cluster

sudo su -
cd /var/opt/oracle/ocde/assistants/bkup/
-- Load the config file to the database
./bkup -cfg /tmp/primary.cfg --dbname=primary
Starting BKUP
Logfile is /var/opt/oracle/log/primary/bkup/bkup_2022-04-21_23:16:56.610267306549.log
Config file is /tmp/primary.config
Running on the local host... gefin-5qy9z1
dbname: primary
Logfile is /var/opt/oracle/log/primary/bkup/bkup_2022-04-21_23:17:01.640534306549.log
ERROR: db: chk_passwd_in_dbagent_wallet: not a valid UUID
INFO: skipping cfg files bkup for exadata
INFO: Saving SPFILE location for backup and restore.
Dataguard configuration
Enabled: 0
Mode: PRIMARY
INFO: OSS connection using supplied creds passed.
 -> Moving previous configuration to /var/opt/oracle/dbaas_acfs/primary/bkup_20220421231710
Looking for previous configuration:
Directory : /var/opt/oracle/dbaas_acfs/primary/bkup
 -> the system is clean
DB edition: enterprise
INFO : Cfg files recovery window set to 90
No initial bkup of PFILE needed.
Configuring Backup to OSS
Preparing OPC library.
 Changing directory ownership of /acfs01/dbaas_acfs/primary
 Changing directory ownership of /acfs01/dbaas_acfs/primary
INFO : OPC opc install
Running OPC installer...
INFO : libopc found at /var/opt/oracle/dbaas_acfs/primary/opc. Move to /tmp directory.
Installing OPC installer local copy.
Running OPC installer.....
using file : /var/opt/oracle/log/primary/bkup/temp.sh
Oracle Database Cloud Backup Module Install Tool, build 12.2.0.1.0DBBKPCSBP_2018-06-12
Oracle Database Cloud Backup Module credentials are valid.
Oracle Database Cloud Backup Module wallet created in directory /acfs01/dbaas_acfs/primary/opc/opc_wallet.
Oracle Database Cloud Backup Module initialization file /acfs01/dbaas_acfs/primary/opc/opcprimary.ora created.
Skipping library download because option -libDir is not specified.
OPC installer completed successfully.
OSS specific RMAN config for libopc
Validating Scan connection....
run {  allocate channel ch1 device type disk connect '@primary as SYSBACKUP';
 allocate channel ch2 device type disk connect '@primary as SYSBACKUP';
 allocate channel ch3 device type disk connect '@primary as SYSBACKUP';
 allocate channel ch4 device type disk connect '@primary as SYSBACKUP';
 allocate channel ch5 device type disk connect '@primary as SYSBACKUP';
 allocate channel ch6 device type disk connect '@primary as SYSBACKUP';
 allocate channel ch7 device type disk connect '@primary as SYSBACKUP';
 allocate channel ch8 device type disk connect '@primary as SYSBACKUP';
 allocate channel sbt_1 device type 'SBT_TAPE' PARMS='SBT_LIBRARY=/var/opt/oracle/dbaas_acfs/primary/opc/libopc.so ENV=(OPC_PFILE=/var/opt/oracle/dbaas_acfs/primary/opc/opcprimary.ora)' connect '@primary as SYSBACKUP';
 }Scan connection status : SUCCESS
Connect string used... : export ORACLE_HOME=/u02/app/oracle/product/19.0.0.0/dbhome_1; export ORACLE_BASE=/u02/app/oracle; export ORACLE_SID=primary1; export PATH=/u02/app/oracle/product/19.0.0.0/dbhome_1/bin:$PATH; export NLS_DATE_FORMAT="DD-MON-RRRR HH24:MI:SS"; export TNS_ADMIN=/u02/app/oracle/product/19.0.0.0/dbhome_1/network/admin/primary; su oracle -c "rman msgno  "
Validating Scan connection....
run {  allocate channel ch1 device type disk connect '@primary as SYSBACKUP';
 allocate channel ch2 device type disk connect '@primary as SYSBACKUP';
 allocate channel ch3 device type disk connect '@primary as SYSBACKUP';
 allocate channel ch4 device type disk connect '@primary as SYSBACKUP';
 allocate channel ch5 device type disk connect '@primary as SYSBACKUP';
 allocate channel ch6 device type disk connect '@primary as SYSBACKUP';
 allocate channel ch7 device type disk connect '@primary as SYSBACKUP';
 allocate channel ch8 device type disk connect '@primary as SYSBACKUP';
 allocate channel sbt_1 device type 'SBT_TAPE' PARMS='SBT_LIBRARY=/var/opt/oracle/dbaas_acfs/primary/opc/libopc.so ENV=(OPC_PFILE=/var/opt/oracle/dbaas_acfs/primary/opc/opcprimary.ora)' connect '@primary as SYSBACKUP';
 }Scan connection status : SUCCESS
Connect string used... : export ORACLE_HOME=/u02/app/oracle/product/19.0.0.0/dbhome_1; export ORACLE_BASE=/u02/app/oracle; export ORACLE_SID=primary1; export PATH=/u02/app/oracle/product/19.0.0.0/dbhome_1/bin:$PATH; export NLS_DATE_FORMAT="DD-MON-RRRR HH24:MI:SS"; export TNS_ADMIN=/u02/app/oracle/product/19.0.0.0/dbhome_1/network/admin/primary; su oracle -c "rman msgno  "
Use of uninitialized value $config in pattern match (m//) at /var/opt/oracle/ocde/assistants/bkup/../../../perl_lib/DBAAS/bkup.pm line 2560.
Use of uninitialized value $config in pattern match (m//) at /var/opt/oracle/ocde/assistants/bkup/../../../perl_lib/DBAAS/bkup.pm line 2560.
Use of uninitialized value $config in pattern match (m//) at /var/opt/oracle/ocde/assistants/bkup/../../../perl_lib/DBAAS/bkup.pm line 2560.
Use of uninitialized value $config in pattern match (m//) at /var/opt/oracle/ocde/assistants/bkup/../../../perl_lib/DBAAS/bkup.pm line 2560.
Use of uninitialized value $config in pattern match (m//) at /var/opt/oracle/ocde/assistants/bkup/../../../perl_lib/DBAAS/bkup.pm line 2560.
Use of uninitialized value $config in pattern match (m//) at /var/opt/oracle/ocde/assistants/bkup/../../../perl_lib/DBAAS/bkup.pm line 2560.
Use of uninitialized value $config in pattern match (m//) at /var/opt/oracle/ocde/assistants/bkup/../../../perl_lib/DBAAS/bkup.pm line 2560.
Use of uninitialized value $config in pattern match (m//) at /var/opt/oracle/ocde/assistants/bkup/../../../perl_lib/DBAAS/bkup.pm line 2560.
Use of uninitialized value $config in pattern match (m//) at /var/opt/oracle/ocde/assistants/bkup/../../../perl_lib/DBAAS/bkup.pm line 2560.
Common RMAN Config
Instantiating obkup
Instantiating dbcfg.spec
Instantiating dgobscfg.spec
Configuring backup of Config File
 Changing directory ownership of /acfs01/dbaas_acfs/primary/bkup
Updating Control File Record Keep Time
Updating RMAN defaults
INFO: cron.pm: cron::bkup_cron : Creating backup of crontab.
Crontab entry disabled.
INFO : Archivelog management enabled.
INFO : Adding entry to crontab
INFO: cron.pm: cron::bkup_cron : Creating backup of crontab.
Getting the Database ID ..
The DBaaS instance database id is: 1940111969
ERROR: db: chk_passwd_in_dbagent_wallet: not a valid UUID
ERROR: db: chk_passwd_in_dbagent_wallet: not a valid UUID
#### Completed Execution.

Once completed we will now create the On-demand backup for the primary database

4. Create On-demand backup with Long Retention

$ /var/opt/oracle/bkup_api/bkup_api bkup_start --keep --dbname=primary --tag=yearly
DBaaS Backup API V1.5 @2022 Multi-Oracle home
-> Action : bkup_start
-> WARNING: bkup_api bkup_start is deprecated! Use 'dbaascli' to perform this operation. Check "dbaascli database backup --help" for more details.
-> logfile: /var/opt/oracle/log/primary/bkup_api_log/bkup_api_f42101e0_20220421233838.433904.log
UUID 5a3b3f9ec17811ec9e8e02001700c06a for this backup
** process started with PID: 176463
** see log file for monitor progress
-------------------------------------

To check the progress of the backup process, enter the following command

$ /var/opt/oracle/bkup_api/bkup_api --uuid 5a3b3f9ec17811ec9e8e02001700c06a --dbname=primary
[LOG     ->  2022-04-21 23:38:44.099930 - API:: invoked with args : -keep -tag=yearly -dbname=primary -uuid=5a3b3f9ec17811ec9e8e02001700c06a -format=yearly
[LOG     ->  2022-04-21 23:38:46.551849 - API:: Oracle database state is up and running
[LOG     ->  2022-04-21 23:39:30.427805 - API:: LONG TERM BACKUP RETENTION #####
[LOG     ->  2022-04-21 23:39:30.427913 - API:: STARTING LONG TERM BACKUP
[LOG     ->  2022-04-21 23:40:45.546684 - API:: RMAN STEPS FINISHED
[LOG     ->  2022-04-21 23:41:44.314656 - API:: VALIDATING DATABASE BACKUPS:
[LOG     ->  2022-04-21 23:41:44.736707 - API:: Determining the oracle database id
[LOG     ->  2022-04-21 23:41:44.760053 - API::  DBID: 1940111969
[LOG     ->  2022-04-21 23:41:44.769677 - API:: Creating directories to store config files
[LOG     ->  2022-04-21 23:41:44.815485 - API:: Enabling RAC exclusions for config files.
[LOG     ->  2022-04-21 23:41:45.516812 - API:: Compressing config files into tar files
[LOG     ->  2022-04-21 23:41:45.554361 - API:: Skipping configuration files backup from legacy code.
[LOG     ->  2022-04-21 23:41:45.592387 - API:: Uploading config files to cloud storage
[LOG     ->  2022-04-21 23:41:49.496951 - API:: Removing temporary location /var/opt/oracle/log/primary/obkup/5a3b3f9ec17811ec9e8e02001700c06a.
[LOG     ->  2022-04-21 23:41:51.524553 - API:: : longterm, Config files backup ended successfully
[ END TICKET ]

To verify this from RMAN go to Node 1 and login with oracle user

$ source primary.env
$ rman target /
RMAN> list backup;
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
42      Full    1.25M      SBT_TAPE    00:00:02     21-APR-22
        BP Key: 42   Status: AVAILABLE  Compressed: YES  Tag: YEARLY
        Handle: 230rhojp_67_1_1-PRIMARY-yearly--XP-19-1940111969-20220421   Media: swiftobjectst../s*******y/PrimaryDB_LongTerm_Backup_Retention
        Keep: BACKUP_LOGS        Until: 29-DEC-35
  Control File Included: Ckp SCN: 2586710      Ckp time: 21-APR-22

To re-confirm the backup is created, check the customers bucket in the region. If everything went well without any errors then you should see the backup files in the bucket.

5. Restore the Long Term Backup *[6]

SSH login with root user on Node 1

$ sudo su -
$ /var/opt/oracle/bkup_api/bkup_api list — dbname=primary
DBaaS Backup API V1.5 @2022 Multi-Oracle home
-> Action : list
-> WARNING: bkup_api list is deprecated! Use ‘dbaascli’ to perform this operation. Check “dbaascli database backup — help” for more details.
-> logfile: /var/opt/oracle/log/primary/bkup_api_log/bkup_api_f6bcacd1_20220422175354.393944.log
-> Listing all backups
 Backup Tag Completion Date (UTC) Type keep
 — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — 
 TAG20220421T224615 04/21/2022 02:50:26 full False
 yearly 04/21/2022 03:42:33 keep-forever True

Restore the database using the backup Tag and check the progress of the recovery process, enter the following bkup_api commands:

$  /var/opt/oracle/bkup_api/bkup_api recover_start -b TAG20220421T224615 --dbname=primary
DBaaS Backup API V1.5 @2022 Multi-Oracle home
-> Action : recover_start
-> WARNING: bkup_api recover_start is deprecated! Use 'dbaascli' to perform this operation. Check "dbaascli database recover --help" for more details.
-> logfile: /var/opt/oracle/log/primary/bkup_api_log/bkup_api_2fac852f_20220422181206.321341.log
UUID e6d92e20c21311ecbce402001700c06a for this recovery
** process started with PID: 278280
** see log file for monitor progress
$ /var/opt/oracle/bkup_api/bkup_api recover_status --dbname=primary
or
$ /var/opt/oracle/bkup_api/bkup_api --uuid e6d92e20c21311ecbce402001700c06a --dbname=primary

In this blog post we’ve demonstrated how you can create custom backups on OCI Exadata Cloud Service in your own user managed bucket. For further data availability you can create cross-region replication on the bucket to have a copy of your DB backups stored in another OCI Region.

We can see how easy it is to create a long-term backup and restore it to meet your compliance requirements.. Some Industry requirements for eg: Banking and Insurance require database backups to be retained for 10 plus years. And this can easily be achieved using the bkup_api on Exadata Cloud Service.

Whats Next?

1. To further extend your backup strategy you can create cross-region replication on your backup bucket so that you have a copy of your backups in another region. *[5]
2. Have a restore strategy in place. Always test your backups to ensure you avoid last minute surprises when you need to restore data for business needs
3. The backup we created in this article was an on-demand one-time backup. For more regular backups ensure that you create automated user-managed backups using the bkp_api *[1]
4. Explore retention rules of Oracle object storage for copying the backups to a bucket with retention rules to avoid deletion of backups*[4]

Important Points:

[a] Backups created with bkup_api do not appear on OCI console and cannot be managed from the console.

[b] When you have created a customized backup the existing auto-backup configuration is over-written and you cannot take automatic backups or manual backups from OCI console. Please refer to this Oracle Support document [3] on how to revert back to auto-backups of OCI.

[c] It is always good practice to configure automatic backups. The same support document has the steps to configure customized scheduled backups.[3]

References:

[1] Managing Exadata Database Backups by Using bkup_api — https://docs.oracle.com/en-us/iaas/Content/Database/Tasks/exabackingupBKUPAPI.htm

[2] Backup Failures in Exadata Cloud Service — https://docs.oracle.com/en-us/iaas/Content/Database/Troubleshooting/Backup/exabackupfail.htm

[3] OCI: How To Configure & Manage Database Backups On OCI EXACS DB System (Doc ID 2708469.1)

[4] Using Retention Rules to Preserve Data — https://docs.oracle.com/en-us/iaas/Content/Object/Tasks/usingretentionrules.htm

[5] About Object Storage Replication — https://docs.oracle.com/en-us/iaas/Content/Object/Tasks/usingreplication.htm

[6] Restoring from a Specific Backup with bkup_api — https://docs.oracle.com/en/cloud/paas/exadata-cloud/csexa/recover-specific-backup.html#GUID-D8A14B80-6DA2-4327-B461-95711BBC7597