Oracle Database Cloud Service: Create a database from backup using Terraform

A common DBA task is to ensure that a development-type environment is refreshed. In a typical on-premises case a “dev refresh” involves quite a bit of scripting in various programming languages. Whilst that’s a perfectly fine approach, it can be done a lot simpler when you consider the use of the cloud. My example uses Oracle’s Database Cloud Service (DBCS).

I prefix all my cloud posts with a similar warning, and this is no exception. Using cloud services costs money, so please make sure you are authorised to make use of these services. You also need to ensure you are licensed appropriately

The Scenario

I am recreating a typical scenario: a database backup acts as the source for the “DEV” environment. To keep this post simple-ish, let’s assume I can use the backup as it is. The database backup is located in Oracle Cloud Infrastructure (OCI) Object Storage.

Implementation

Writing a piece of Terraform code with the intention of storing it in version control requires the use of variables, at least in my opinion. Otherwise, any change to the input parameters will result in git marking the file’s status as untracked. And you certrainly don’t want to store passwords in code, ever.

You’ll see variables used throughout in my example code.

Getting backup details

Backup details for my source database are provided by a database backups data source. My requirement is quite simple: just take the latest backup and use it for the restore operation.

#
# get the database backups for src_db_ocid
#
data "oci_database_backups" "src_bkp" {
  database_id    = var.src_db_ocid
} 

The database backup to grab is element 0 in the resulting list of backups provided by the data source.

Thinking about passwords

Passwords are a tricky affair in OCI. It would be great if we could lift them from (OCI) Vault, but this wasn’t possible at the time of writing. A Github issue has been raised but didn’t seem to gain much momentum. There are workarounds though, please refer to this excellent post by Yevgeniy Brikman on the topic. I’ll leave it as an exercise to the reader to work out the best strategy.

Since Terraform v0.14 it is possible to declare a variable to be “sensitive”. That sounds great:

variable "new_admin_pwd" {
  type      = string
  sensitive = true
}

variable "backup_tde_password" {
  type      = string
  sensitive = true
}

Except they aren’t quite there yet: all sensitive information still appears in the state file in plain text :(

Creating the DB System

The final step is to create the database system. In my case, I only need a single resource:

resource "oci_database_db_system" "dev_system" {

  # the AD of the new environment has to match the AD where
  # the backup is stored (a property exported by the data source)
  availability_domain = data.oci_database_backups.src_bkp.backups.0.availability_domain

  # instruction to create the database from a backup
  source = "DB_BACKUP"

  # Some of these properties are hard-coded to suit my use case. 
  # Your requirement is almost certainly different. Make sure you
  # change paramaters as required
  compartment_id          = var.compartment_ocid
  database_edition        = "ENTERPRISE_EDITION"
  data_storage_size_in_gb = 256
  hostname                = "dev"
  shape                   = "VM.Standard2.1"
  node_count              = 1
  ssh_public_keys         = [var.ssh_public_key]
  subnet_id               = var.subnet_id
  nsg_ids                 = [var.nsg_id]
  license_model           = "LICENSE_INCLUDED"

  display_name = "development DB system"

  db_home {

    database {
      # the admin password for the _new_ database
      admin_password = var.new_admin_pwd

      # this is from the source backup!
      backup_tde_password = var.backup_tde_password
      backup_id           = data.oci_database_backups.src_bkp.backups.0.id

      db_name = "DEV"
    }

  }

  db_system_options {
    storage_management = "ASM"
  }
}

This is all it takes. The majority of input parameters are provided as variables to make the script a little more portable between environments and easier to check in with version control.

A short terraform apply later a new database system is created. Happy Automating!