OCI DBCS Standalone backups

One of the resources I really like in Oracle Cloud (OCI) is DBCS so the Oracle Database Cloud Service. It makes life of a DBA much simpler when it comes to provisioning and maintaining a database.

The DBCS for instance comes with per-configured automatic backups so you don’t have to spend your time with preparing storage, configuring RMAN or any doing any other related task. You simply select the retention period and that’s it. All other is done by the service.

What is also worth of mentioning is that Oracle’s price for Database backup storage is pretty cheap if you consider that you don’t have to bother with anything about storage itself.

There is one prerequisite that you must do to allow backups to be stored on Object storage. That is to update your routing table and security lists where your DB System resides. You need to add an Egress rule to allow traffic to All Services or Object storage service. And you need to create a Service Gateway and add a route to All Services or Object storage over the Service Gateway. If you are not familiar with this setup you can have a look here: https://docs.oracle.com/en-us/iaas/Content/Network/Tasks/servicegateway.htm

Automatic backups

To enable automatic backup you just need to do following steps:

1. Click Oracle Database, then click Bare Metal, VM, and Exadata.
2. Click on the DB System where you want to enable backups. Make sure you are in correct compartment.
3. Click on the Database and then click on the button Configure Automatic Backups.
4. A configuration window will appear where you simply check Enable automatic backup checkbox and define retention period along with the time frame where you want the backup to be created.
5. Click Save changes and you are done. The DB will create initial backup and the will continue with automatic incremental backups.

By enabling automatic backups the service will do following:

1. Prepare an Object Storage for storing the backups.
2. Perform relevant configuration in RMAN.
3. Do daily incremental level 1 backup.
4. Do weekly incremental level 0 backup (basically full backup).
5. Do backup of archive logs on hourly bases.

You can then use the automatic backup to either restore your DB or to create a new DB System (so new DB server with the database).

But there is also one downside of this feature. If you accidentally terminate the DB system all of the automatic backups will be deleted as well. To avoid this unpleasant situation you can do multiple prevention steps (restrict user access to the Oracle Database view, use Terraform or other similar tool to maintain the DB and define restriction on termination or many more). One of the easy steps is to create a Standalone backup.

Standalone backups

Standalone backup is on demand full backup initiated by an user. There are several advantages of Standalone backups. To name a few it is a full backup executed exactly at the time when you want. And it is a persistent backup hence retention period didn’t apply on this sort of backup. It will last for many years or until you will manually initiate a deletion of the backup.

To create a Standalone or on demand backup from console you need to do following:

1. Click Oracle Database, then click Bare Metal, VM, and Exadata.
2. Click on the DB System where you want to enable backups. Make sure you are in correct compartment.
3. Click on the Database and then in the left panel click on Backups.
4. Click on Create Backup button and enter the name of the backup.
5. Finally click on Create Backup and the Standalone/on demand backup will be created.

It is very easy to create a Standalone backup. But there is one tricky part. Where can one find the backup if the DB System has been terminated? The answer is under Bare Metal, VM, and Exadata in section Resources. There you can find Standalone Backups.

But how to automate this process?

This time I was playing a bit with OCI CLI together with Powershell on one of our Windows servers. The goal is to keep full DB backup from last 3 days. Standalone backups are marked as FULL backups in OCI CLI. OCI CLI query is using JMESPath (jmespath.org) but we need to struggle a bit with windows escape characters so be careful how you write the query.

1. First you need to get a list of FULL DB backups with their timestamps (obviously at the beginning there will be none as we are just creating some)

oci db backup list --database-id $databaseId --query "data[?contains(type,'FULL')].{\`"time-ended\`":\`"time-ended\`",id:id}" --all

2. Then you want to create a new FULL DB backup with some name (wait-for-state parameter just waits until the command is completed and it is optional).

oci db backup create --database-id $databaseId --display-name $BKPname --wait-for-state "ACTIVE"

3. You need to wait until the backup is completed or deleted when you’ll be deleting old backups.

oci db database get --database-id $databaseId --query "data.\`"lifecycle-state\`""

4. The last step is to get rid of old backups.

oci db backup delete --backup-id $i.id --force --wait-for-state "SUCCEEDED" --wait-for-state "FAILED"

And as always I’ll provide the full script with all the necessary bits.

$databaseId="ocid1.database.oc1...."
$retention_days=3
$today=(Get-Date).ToShortDateString().ToString() -replace '[/]','-'
$BKPname="PROD_Full_BKP_"+$today

$backupIDs=oci db backup list --database-id $databaseId --query "data[?contains(type,'FULL')].{\`"time-ended\`":\`"time-ended\`",id:id}" --all
$data = ConvertFrom-Json –InputObject "$backupIDs"

$createBackup=oci db backup create --database-id $databaseId --display-name $BKPname --wait-for-state "ACTIVE" 2> $null
Write-Host $createBackup


if ($data.Count -gt $retention_days)
{
    foreach ($i in $data)
    {        
        $LastDate = (Get-Date).AddDays(-$retention_days).ToString('yyyy-MM-dd')   
        
        if ($i."time-ended".Substring(0,$i."time-ended".IndexOf('T')) -lt $LastDate)
        {
            $databaseStatus=oci db database get --database-id $databaseId --query "data.\`"lifecycle-state\`""
            $databaseStatus=$databaseStatus -replace '["]',''

            while ($databaseStatus -ne "AVAILABLE")
            {
                Start-Sleep -Seconds 10
                $databaseStatus=oci db database get --database-id $databaseId --query "data.\`"lifecycle-state\`""
                $databaseStatus=$databaseStatus -replace '["]',''

            }
                        
            Write-Host $i.id $i."time-ended".Substring(0,$i."time-ended".IndexOf('T'))
            $deleteBackup=oci db backup delete --backup-id $i.id --force --wait-for-state "SUCCEEDED" --wait-for-state "FAILED" 2> $null
            Write-Host $deleteBackup
        }
    }
}

The same script but as image for better readability

When you have the script you just setup a task in the Task Scheduler and let it runs once a day. The script will keep three Full backups from last three days.