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"
    }
}