Create DB system to Oracle Cloud Infrastructure with Terraform

One of the most important areas with Oracle Cloud Infrastructure is that you can utilize Oracle databases easily as part of your infrastructure. There are different options how you can provision the databases but as Terraform is the supported orchestration tool with OCI you should investigate if using it is feasible for your project.

This way all the databases created in your tenancy will be done through Terraform in the same way specially if you utilize Terraform modules which are sort of pre-build parts which are written for standard operations. When different “projects” utilize those modules they always call them in the same way and there is no need to write them again each time.

In this post I’ll share simple way of creating the module and using it to create a database.

Basics of DB systems in OCI

In OCI you have different options for your DB system. It can run either on VM, BM or Exadata instance. For each there are variety of different shapes available in terms of CPU, Memory, storage available etc. (And different Exadata shapes obviously)

For VMs you can create 2-node RAC configuration.

Database editions which are available:

• • Standard Edition

• • Enterprise Edition

• • Enterprise Edition – High Performance

• Enterprise Edition – Extreme Performance (required for 2-node RAC DB systems)

And currently supported DB versions:

• • Oracle Database 18c Release 1 (18.1)

• • Oracle Database 12c Release 2 (12.2)

• • Oracle Database 12c Release 1 (12.1)

• Oracle Database 11g Release 2 (11.2)

For licensing you have the option either to bring your own license (BYOL) or have the license included. This reflects in the cost of your system.

One important thing to remember is that the database is deployed into public subnet. You don’t need to define Internet Gateway but the database system requires public subnet. I would imagine some people don’t like this idea so much even though you can limit the access completely from outside.

Whole DB service documentation can be found from here.

Terraform setup

I’ve placed necessary Terraform files inside two folders. The module folder is named“database” and inside it I have two files:

• main.tf – this one has code which calls resource oci_database_db_system to create the DB system
• outputs.tf – this one sends variables out that I want

The project folder is named “create_db_system”  and it has three files inside.

• main.tf – this one gets necessary data from different sources and calls database module by passing different variables which are specific to this case
• variables.tf – all the custom variables defined in this file
• outputs.tf – outputs which I want to print after successfully running Terraform

Terraform module

Setting up the database module is fairly straightforward. Good example case can be found from OCI Terraform documentation here.

Note that the required parameter for resource oci_database_db_system data_storage_size_in_gb is named elsewhere as data_storage_size_in_gbs but seems correct way of calling it is without the s.

I’ve used the module so that I don’t do any data gathering inside the module but rather pass only “ready” variables into it.

My resource looks like this:

resource "oci_database_db_system" "CreateDBSystem" {

#Required

availability_domain = "${var.db_system_availability_domain}"

compartment_id = "${var.compartment_id}"

cpu_core_count = "${var.db_system_cpu_core_count}"

database_edition = "${var.db_system_database_edition}"

db_home {

#Required

database {

#Required

admin_password = "${var.db_system_db_home_database_admin_password}"

db_name = "${var.db_system_db_home_database_db_name}"

character_set = "${var.db_system_db_home_database_character_set}"

db_backup_config {

ncharacter_set = "${var.db_system_db_home_database_ncharacter_set}"

pdb_name = "${var.db_system_db_home_database_pdb_name}"

}

db_version = "${var.db_system_db_home_db_version}"

#Optional

display_name = "${var.db_system_db_home_display_name}"

}

hostname="${var.db_system_hostname}"

shape = "${var.db_system_shape}"

ssh_public_keys = ["${var.ssh_public_key}"]

subnet_id = "${var.db_subnet_id}"

data_storage_percentage = "${var.db_system_data_storage_percentage}"

data_storage_size_in_gb = "${var.db_system_data_storage_size_in_gbs}" #For VMs only

license_model = "${var.db_system_license_model}"

node_count = "${var.db_system_node_count}"

}

Terraform project
For the project itself I’ve defined all the necessary variables inside variables.tf – either these variables are passed to database module or I use them to get specific OCID’s what I require further on.

You define default variable like this:
variable “compartment_display_name” {default = “PrivDBCompartment”}
In the main.tf I fetch data using variables like here to get the compartment data:



data "oci_identity_compartments" "GetCompartments" {

#Required - tenancy OCID

compartment_id = "${var.tenancy_ocid}"

filter{

name="name"

values= ["${var.compartment_display_name}"]

}

}





This one is bit misleading as I pass tenancy OCID as compartment_id to get filtered list of available compartments.
The compartment is then used to get specific DB subnet I want to use for the DB system:



data "oci_core_subnets" "GetDBSubnet" {

#Required

compartment_id = "${lookup(data.oci_identity_compartments.GetCompartments.compartments[0],"id")}"

vcn_id="${lookup(data.oci_core_vcns.GetVcns.virtual_networks[0],"id")}"

display_name="${var.db_subnet_name}"

}





To understand what variables you need to pass to get list of subnets you can see the documentation example. Same applies to all other list requests, check the documentation!

To use the data you then use lookups to get specific values you want. I didn’t understand this in the start how it works but all available variables can be seen from documentation and then you can just use it like above to get required variables for availability domains etc.
Another good example how to use lookup is to have the necessary db_system_availability_domain variable defined from above GetDBSubnet:



db_system_availability_domain =
"${lookup(data.oci_core_subnets.GetDBSubnet.subnets[0],"availability_domain")}"




I’ve defined the GetDBSubnet earlier and now I just fetch the availability_domain the subnet belongs to. This way you can get all the variables related to some specific resource.
From the variables.tf still few settings to point out to this exercise:
“db_system_database_edition” {default = “ENTERPRISE_EDITION”}

“db_system_db_home_db_version” {default = “12.1.0.2”}

“db_system_shape” {default = “VM.Standard2.1”}

“db_system_license_model” {default = “LICENSE_INCLUDED”}
My database will be a VM 2.1 shape with 12.1.0.2 Enterprise Edition database with license included option.
Creating the Database system
Now I’m ready to create the DB system. As usual I will run first terraform init to initialize the modules and the OCI provider for terraform.
After that I will run terraform plan to see what changes will be applied and this time I will have only 1 change to add. I’ve created compartment with network already earlier using different terraform project.
Plan: 1 to add, 0 to change, 0 to destroy.

After that I will run terraform apply to do the actual changes. Creating the database took around 70 minutes in total with my configuration.
module.cr_private_db.oci_database_db_system.CreateDBSystem: Creation complete after 1h12m42s (ID: ocid1.dbsystem.oc1.eu-frankfurt-1.abthe…34qsdaozzgyb254hnr7w6nlkpaz7tshpfdy2oa)
Since I’ve set my ssh keys I can login to the DB system by providing my private key with opc user. If I look pmon processes:
[oracle@hostsimo dbhome_1]$ ps -ef|grep pmon|grep oracle

oracle 65038 47622 0 08:38 pts/0 00:00:00 grep pmon

oracle 76130 1 0 08:13 ? 00:00:00 ora_pmon_SIMO1
Besides grid ASM there is my database now also running.





Thing to remember is that if you want to allow connections to listener port modify your security lists to allow traffic! This should be part of your network setup.
After the test I’ll run terraform destroy to remove the database and that completes in bit over 7 minutes.
module.cr_private_db.oci_database_db_system.CreateDBSystem: Destruction complete after 7m19s

Summary
You can easily automatize your database creation through Terraform modules. Initial module setup takes some time so that you figure out the parameters you need but once that is done you get all your databases created in standardized way!
This was a simple example but for real world cases modules will be more complex and will require more work obviously.