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

Written by: ilmarkerm

Category: Blog entry

Published: September 29, 2024

I’ll continue exploring using OCI services with Terraform. Now it is time to start looking into databases. High Oracle PM-s have been lobbying for a database image creation service, where you just supply patch numbers and Oracle will return you the fully built database home. I see that this service is now available in the cloud (for cloud databases only).

I’ll try it out, using terraform.

resource "oci_database_database_software_image" "db_23051" {
    # NB! Waits until image is provisioned
    # This took 10m47s to provision
    compartment_id = oci_identity_compartment.compartment.id
    display_name = "23-db-23051"

    image_shape_family = "VM_BM_SHAPE" # For use in Database Base service
    # oci db version list
    # NB! 23.0.0.0 seems to be behind on patches, 23.0.0.0.0 seems to be current    
    database_version = "23.0.0.0.0"
    image_type = "DATABASE_IMAGE"
    # Can't find how to query that list - but the format seems quite self-explanatory
    # Exadata Cloud Service Software Versions (Doc ID 2333222.1)
    patch_set = "23.5.0.24.07"
}

I had hard time finding out the allowed values for parameter patch_set, but they seem to be described in Doc ID 2333222.1 (and what the contents are).

Examining the state of the created resource

ilmar_kerm@codeeditor:oci-terraform-example (eu-stockholm-1)$ terraform state show oci_database_database_software_image.db_23051

# oci_database_database_software_image.db_23051:
resource "oci_database_database_software_image" "db_23051" {
    compartment_id                           = "ocid1.compartment.oc1..aaaaaaaasbzzr7i54kpv6oc5s7i23isiij6n2tyentd5udc34ptzagovrgqa"
    database_software_image_included_patches = [
        "35221462",
        "36741532",
        "36744688",
    ]
    database_software_image_one_off_patches  = [
        "35221462",
        "36741532",
        "36744688",
    ]
    database_version                         = "23.0.0.0.0"
    defined_tags                             = {
        "Oracle-Tags.CreatedBy" = "default/ilmar.kerm@gmail.com"
        "Oracle-Tags.CreatedOn" = "2024-09-29T12:36:02.119Z"
    }
    display_name                             = "23-db-23051"
    freeform_tags                            = {}
    id                                       = "ocid1.databasesoftwareimage.oc1.eu-stockholm-1.anqxeljr4ebxpbqadhgioquzxv6qtrui72e3sn3c7iwxcljncmdq7fx5jdbq"
    image_shape_family                       = "VM_BM_SHAPE"
    image_type                               = "DATABASE_IMAGE"
    is_upgrade_supported                     = false
    patch_set                                = "23.5.0.24.07"
    state                                    = "AVAILABLE"
    time_created                             = "2024-09-29 12:36:02.123 +0000 UTC"
}

One thing I notice here (verified with testing), that the parameter database_software_image_one_off_patches gets pre-populated with included patches after the image is created – so you have to include the included patches also the the parameter value.

With 19c version process is similar

resource "oci_database_database_software_image" "db_19241" {
    # NB! Waits until image is provisioned
    # This took 16m4s to provision
    compartment_id = oci_identity_compartment.compartment.id
    display_name = "19-db-19241"

    image_shape_family = "VM_BM_SHAPE" # For use in Database Base service
    # oci db version list
    database_version = "19.0.0.0"
    image_type = "DATABASE_IMAGE"
    patch_set = "19.24.0.0"
}

I did try to apply MRP on top of it, but maybe the cloud patch numbers are different, since the usual MRP patch number did not apply on top of it.

In the next post I’ll try to spin up an actual database using the image.