Creating Oracle Base database in OCI using Terraform
- Written by: ilmarkerm
- Category: Blog entry
- Published: October 2, 2024
In the previous post I looked how to create Oracle database home in OCI, using custom patches. Here I create a Oracle Base database on top of that custom home.
I was struggling for days with this, mostly because I first wanted to create database on LVM storage, but after running for many many hours, the provisioning got stuck always at 42%, without any error messages. Finally, with tons of experimentation, I found out that if I replace LVM with ASM storage, it all just works.
# List all availability domainsdata "oci_identity_availability_domains" "ads" {
compartment_id = oci_identity_compartment.compartment.id
}
# Create the Oracle Base database
resource "oci_database_db_system" "test23ai" {
# Waits until DB system is provisioned
# It can take a few hours
availability_domain = data.oci_identity_availability_domains.ads.availability_domains[0].name
compartment_id = oci_identity_compartment.compartment.id
db_home {
database {
admin_password = "correct#HorseBatt5eryS1-_ple"
character_set = "AL32UTF8"
#database_software_image_id = oci_database_database_software_image.db_23051.id
db_name = "test23ai"
pdb_name = "PDB1"
db_backup_config {
auto_backup_enabled = false
}
}
db_version = oci_database_database_software_image.db_23051.patch_set
database_software_image_id = oci_database_database_software_image.db_23051.id
}
hostname = "test23ai1"
shape = "VM.Standard.E5.Flex"
ssh_public_keys = ["ssh-rsa AAAAB3NzaC1yc2EAAA paste your own ssh public key here"]
subnet_id = oci_core_subnet.subnet.id
cpu_core_count = 2
data_storage_size_in_gb = 256
database_edition = "ENTERPRISE_EDITION"
db_system_options {
storage_management = "ASM" # LVM did not work for me, provisioning stuck at 42% for many many hours until it times out
}
display_name = "Test 23ai"
domain = "dev.se1"
#fault_domains = var.db_system_fault_domains
license_model = "LICENSE_INCLUDED"
node_count = 1
# Network Security Groups
#nsg_ids = var.db_system_nsg_ids
source = "NONE"
storage_volume_performance_mode = "BALANCED"
time_zone = "UTC"
}
And to see the connection options, they are visible from terraform state. Here we see database connection information, donnection strings in different formats are visible – so your terraform code could now take these values and store it in some service discovery/parameter store.
ilmar_kerm@codeeditor:oci-terraform-example (eu-stockholm-1)$ terraform state show oci_database_db_system.test23ai
# oci_database_db_system.test23ai:
resource "oci_database_db_system" "test23ai" {
availability_domain = "AfjF:EU-STOCKHOLM-1-AD-1"
compartment_id = "ocid1.compartment.oc1..aaaa"
cpu_core_count = 2
data_storage_percentage = 80
data_storage_size_in_gb = 256
database_edition = "ENTERPRISE_EDITION"
defined_tags = {
"Oracle-Tags.CreatedBy" = "default/ilmar.kerm@gmail.com"
"Oracle-Tags.CreatedOn" = "2024-10-02T14:57:03.606Z"
}
disk_redundancy = "HIGH"
display_name = "Test 23ai"
domain = "dev.se1"
fault_domains = [
"FAULT-DOMAIN-2",
]
freeform_tags = {}
hostname = "test23ai1"
id = "ocid1.dbsystem.oc1.eu-stockholm-1.anqxeljr4ebxpbqanr42p2zebku5hdk5nci2"
iorm_config_cache = []
license_model = "LICENSE_INCLUDED"
listener_port = 1521
maintenance_window = []
memory_size_in_gbs = 32
node_count = 1
reco_storage_size_in_gb = 256
scan_ip_ids = []
shape = "VM.Standard.E5.Flex"
source = "NONE"
ssh_public_keys = [
"ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQCvccb2GOc+VU6V0lw367a5sgKqn0epAok9vCVboK6WvQid6byo7hkWUSixIuB6ZPGG89n3ig4r my ssh public key",
]
state = "AVAILABLE"
storage_volume_performance_mode = "BALANCED"
subnet_id = "ocid1.subnet.oc1.eu-stockholm-1.aaaaaaaal6ru"
time_created = "2024-10-02 14:57:04.037 +0000 UTC"
time_zone = "UTC"
version = "23.5.0.24.07"
vip_ids = []
data_collection_options {
is_diagnostics_events_enabled = false
is_health_monitoring_enabled = false
is_incident_logs_enabled = false
}
db_home {
create_async = false
database_software_image_id = "ocid1.databasesoftwareimage.oc1.eu-stockholm-1.anqxeljr4ebxpbqa3h6dawz"
db_home_location = "/u01/app/oracle/product/23.0.0.0/dbhome_1"
db_version = "23.5.0.24.07"
defined_tags = {}
display_name = "dbhome20241002145704"
freeform_tags = {}
id = "ocid1.dbhome.oc1.eu-stockholm-1.anqxeljrb3hetziaby6hwudav"
state = "AVAILABLE"
time_created = "2024-10-02 14:57:04.042 +0000 UTC"
database {
admin_password = (sensitive value)
character_set = "AL32UTF8"
connection_strings = [
{
all_connection_strings = {
"cdbDefault" = "test23ai1.dev.se1:1521/test23ai_rf6_arn.dev.se1"
"cdbIpDefault" = "(DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.2.80)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=test23ai_rf6_arn.dev.se1)))"
}
cdb_default = "test23ai1.dev.se1:1521/test23ai_rf6_arn.dev.se1"
cdb_ip_default = "(DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.2.80)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=test23ai_rf6_arn.dev.se1)))"
},
]
database_software_image_id = "ocid1.databasesoftwareimage.oc1.eu-stockholm-1.anqxeljr4ebxpbqa3h6"
db_name = "test23ai"
db_unique_name = "test23ai_rf6_arn"
db_workload = "OLTP"
defined_tags = {
"Oracle-Tags.CreatedBy" = "default/ilmar.kerm@gmail.com"
"Oracle-Tags.CreatedOn" = "2024-10-02T14:57:03.740Z"
}
freeform_tags = {}
id = "ocid1.database.oc1.eu-stockholm-1.anqxeljr4ebxpbqac3v4lrr"
ncharacter_set = "AL16UTF16"
pdb_name = "PDB1"
pluggable_databases = []
state = "AVAILABLE"
time_created = "2024-10-02 14:57:04.043 +0000 UTC"
db_backup_config {
auto_backup_enabled = false
auto_full_backup_day = "SUNDAY"
recovery_window_in_days = 0
run_immediate_full_backup = false
}
}
}
db_system_options {
storage_management = "ASM"
}
}