Intro
Whatβs the point of moving a database to the Cloud if we canβt automatically deploy it. After blogging about web compute provisioning on AWS,OCI, Azure, & GCP using terraform. The least I could do as a (future-ex) DBA is to terraform database provisioning in Oracle Cloud. On top of that, I also wanted to include a bastion service session to connect to the DBCS instance in the private subnet .
Hereβs a link to my GitHub repo linked to this lab: brokedba/terraform-provider-oci/database-system
Where do I find a good OCI Database deployment sample?
I explored the official Oracle Cloud GitHub repository, but I couldnβt find a simple stack with no frills. I mean, when you want to deploy a database for the first time, you donβt want to spin 10 other app components that have nothing to do with your DB. I then decided to gather the bare minimum using a function called terraform import that will retro-engineer the code from an existing database and carried on from there.
Overview & topology
The above illustration shows the different components involved in this OCI Terraform Database stack.
-
VCN
-
Database Cloud system 21c with 1 PDB
-
Bastion service + Bastion session using port forwarding SSH targeting the DB instance
-
2x Subnets
-
DB Subnet: private / linked to default route table
-
App Subnet: public / linked to app route table
-
2x route tables
-
Default route table > routes to NAT Gateway &Service gateway
-
App route table > routes to internet gateway
-
2x security lists
-
db sec list ports : ingress 22/1521 from app subnet
-
App sec list ports “: ingress 22/80/443 , egress 1521
-
I.Terraform setup
Windows: Download and run the installer from their website (32-bit ,64-bit)
Linux : Download, unzip and move the binary to the local bin directory
$ wget https://releases.hashicorp.com/terraform/1.0.3/terraform_1.0.3_linux_amd64.zip
$ unzip terraform_1.0.3_linux_amd64.zip
$ mv terraform /usr/local/bin/
$ terraform --version
Terraform v1.0.3
II. Clone the repository
-
Pick an empty directory on your file system and issue the following command
$ git clone https://github.com/brokedba/terraform-examples.gitYou will find a sub-directory called database-system in the repository where the DBCS stack is located:
-
Cd Into
terraform-provider-oci/database-system/where our configuration resides$ cd ~/terraform-examples/terraform-provider-oci/database-system
-
Run terraform init that will install OCI provider plugin automatically$ terraform init $ terraform -v | grep provider + provider registry.terraform.io/hashicorp/oci v3.83.1
III. terraform config content
-
Let’s see what’s in the
database-systemdirectory. Here, only*.tffiles matter along with tfvars$ tree βββ bastion.tf---> OCI Bastion terraform declaration codeβββ database.tf---> OCI DBCS terraform declaration codeβββ datasources.tf---> data source declaration code (i.e to fetch shape ocids)βββ terraform.tfvars---> TF_environment_variables needed to authenticate to OCIβββ outputs.tf ---> displays the DBCS/Bastion resources detail after the deployβββ variables.tf---> Resource variables needed for the deployβββ vcn.tf---> Our Networking terraform declaration code -
Adjust the required authentication parameters in terraform.tfvars according to your tenancy and DB info
# Adapt the below variables to your own tenancy authentication configuration$ vi terraform.tfvars export TF_VAR_tenancy_ocid="ocid1.tenancy.oc1..aaaaaaaa" # change me export TF_VAR_user_ocid="ocid1.user.oc1..aaaaaaaa" # change me export TF_VAR_compartment_ocid="ocid1.tenancy.oc1..aaaaaaaa" # change me export TF_VAR_fingerprint=$(cat PATH_To_Fing/oci_api_key_fingerprint)# change me export TF_VAR_private_key_path=PATH_To_APIKEY/oci_api_key.pem # change me export TF_VAR_ssh_public_key=$(cat PATH_To_PublicSSH/id_rsa.pub) # change me export TF_VAR_region="ca-toronto-1" # change me export TF_VAR_db_admin_password="DBwelcome2022##" $ . terraform.tfvars
Terraform files OVERVIEW:
I will only show excerpts from database.tf /output.tf to have an idea but all *.tf files are accessible on my Repo.
database.tf
-
As shown in the below declaration, highlighted in green are variables and the grey ones are data source based
resource "oci_database_db_system" "MYDBSYS" { availability_domain = data.oci_identity_availability_domains.ad1.availability_domains[0].name compartment_id = var.compartment_ocid database_edition = var.db_edition db_home { database { admin_password = var.db_admin_password db_name = var.db_name pdb_name = var.pdb_name character_set = var.character_set ncharacter_set = var.n_character_set db_workload = var.db_workload db_backup_config { auto_backup_enabled = var.db_auto_backup_enabled auto_backup_window = var.db_auto_backup_window recovery_window_in_days = var.db_recovery_window_in_days } } db_version = var.db_version } shape = var.db_system_shape license_model = var.license_model subnet_id = oci_core_subnet.terraDB.id private_ip = var.db_system_private_ip ssh_public_keys = ["${var.ssh_public_key}"] hostname = var.hostname data_storage_size_in_gb = var.data_storage_size_in_gb node_count = data.oci_database_db_system_shapes.db_system_shapes.db_system_shapes[0]["minimum_node_count"] display_name = var.db_system_display_name }
All variables can of course be changed to your liking in the variables.tf
output.tf
-
Here we just grab any relevant information related to the DBCS and the Bastion service like the ssh command
######################### ## DBCS INSTANCE OUTPUT ######################### output "hostname" { description = " id of created instances." value = oci_database_db_system.MYDBSYS.hostname } output "private_ip" { description = "Private IPs of created instances." value = oci_database_db_system.MYDBSYS.private_ip } output "DB_STATE" { value = oci_database_db_system.MYDBSYS.state } output "DB_Version" { value = oci_database_db_system.MYDBSYS.version } output "db_system_options" { value = oci_database_db_system.MYDBSYS.db_system_options } ######### # BASTION ######### output "bastion_name" { value = oci_bastion_session.mybastion_session.bastion_name } output "bastion_session_name" { value = oci_bastion_session.mybastion_session.display_name } output "bastion_session_state" { value = oci_bastion_session.mybastion_session.state } output "bastion_session_target_resource_details" { value = oci_bastion_session.mybastion_session.target_resource_details } output "bastion_session_ssh_connection" { value = oci_bastion_session.mybastion_session.ssh_metadata.command }
IV. DBCS Stack deployment
Make sure you copied the adjusted terraform-tfvars file and sourced it. You can then run the plan command (output is truncated for more readability)
$ terraform plan
------------------------------------------------------------------------
Terraform will perform the following actions:
... # VCN declaration
# oci_bastion_bastion.mybastion will be created
+ resource "oci_bastion_bastion" "mybastion" { ...
# oci_bastion_session.mybastion_session will be created
+ resource "oci_bastion_session" "mybastion_session" {
...
+ target_resource_details {
+ session_type = "PORT_FORWARDING"
+ target_resource_display_name = (known after apply)
+ target_resource_operating_system_user_name = (known after apply)
+ target_resource_port = 22
+ target_resource_private_ip_address = "192.168.78.10"
}
}
# oci_core_subnet.terraApp will be created
+ resource "oci_core_subnet" "terraApp" { ...
# oci_core_subnet.terraDB will be created
+ resource "oci_core_subnet" "terraDB" {
+ availability_domain = "gwmA:CA-TORONTO-1-AD-1"
+ cidr_block = "192.168.78.0/24"
...
# oci_core_virtual_network.vcnterra will be created
+ resource "oci_core_virtual_network" "vcnterra" {
+ cidr_block = "192.168.64.0/20"
+ display_name = "db-vcn"
+ dns_label = "terravcn"
...}
..
# oci_database_db_system.MYDBSYS will be created
+ resource "oci_database_db_system" "MYDBSYS" {
+ availability_domain = "gwmA:CA-TORONTO-1-AD-1"
+ database_edition = "STANDARD_EDITION"
+ data_storage_size_in_gb = 256
+ display_name = "DBCSDEMO"
+ hostname = "hopsdb-oci"
+ license_model = "LICENSE_INCLUDED"
+ private_ip = "192.168.78.10"
+ shape = "VM.Standard2.4"
+ ssh_public_keys =[...
+ db_home {
+ db_version = "21.0.0.0"
...
+ database {
+ db_workload = "OLTP"
+ db_name = "MYCDB"
+ pdb_name = "PDB1"
...
+ db_backup_config { β¦
+ backup_destination_details {
}
}
}
}
+ db_system_options {
+ storage_management = (known after apply) }