{"id":770,"date":"2024-10-02T18:12:16","date_gmt":"2024-10-02T16:12:16","guid":{"rendered":"https:\/\/ilmarkerm.eu\/blog\/?p=770"},"modified":"2024-10-06T09:38:55","modified_gmt":"2024-10-06T07:38:55","slug":"creating-oracle-base-database-in-oci-using-terraform","status":"publish","type":"post","link":"https:\/\/ilmarkerm.eu\/blog\/2024\/10\/creating-oracle-base-database-in-oci-using-terraform\/","title":{"rendered":"Creating Oracle Base database in OCI using Terraform"},"content":{"rendered":"\n<p>In the <a href=\"https:\/\/ilmarkerm.eu\/blog\/2024\/09\/creating-database-software-images-in-oci-using-terraform\/\" data-type=\"post\" data-id=\"768\">previous post<\/a> 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.<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># List all availability domainsdata \"oci_identity_availability_domains\" \"ads\" {\n    compartment_id = oci_identity_compartment.compartment.id\n}\n\n# Create the Oracle Base database\nresource \"oci_database_db_system\" \"test23ai\" {\n    # Waits until DB system is provisioned\n    # It can take a few hours\n    availability_domain = data.oci_identity_availability_domains.ads.availability_domains&#91;0].name\n    compartment_id = oci_identity_compartment.compartment.id\n    db_home {\n        database {\n            admin_password = \"correct#HorseBatt5eryS1-_ple\"\n            character_set = \"AL32UTF8\"\n            #database_software_image_id = oci_database_database_software_image.db_23051.id\n            db_name = \"test23ai\"\n            pdb_name = \"PDB1\"\n            db_backup_config {\n                auto_backup_enabled = false\n            }\n        }\n        db_version = oci_database_database_software_image.db_23051.patch_set\n        database_software_image_id = oci_database_database_software_image.db_23051.id\n    }\n    hostname = \"test23ai1\"\n    shape = \"VM.Standard.E5.Flex\"\n    ssh_public_keys = &#91;\"ssh-rsa AAAAB3NzaC1yc2EAAA paste your own ssh public key here\"]\n    subnet_id = oci_core_subnet.subnet.id\n\n    cpu_core_count = 2\n    data_storage_size_in_gb = 256\n    database_edition = \"ENTERPRISE_EDITION\"\n    db_system_options {\n        storage_management = \"ASM\" # LVM did not work for me, provisioning stuck at 42% for many many hours until it times out\n    }\n    display_name = \"Test 23ai\"\n    domain = \"dev.se1\"\n    #fault_domains = var.db_system_fault_domains\n    license_model = \"LICENSE_INCLUDED\"\n    node_count = 1\n    # Network Security Groups\n    #nsg_ids = var.db_system_nsg_ids\n    source = \"NONE\"\n    storage_volume_performance_mode = \"BALANCED\"\n    time_zone = \"UTC\"\n}\n<\/code><\/pre>\n\n\n\n<p>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 &#8211; so your terraform code could now take these values and store it in some service discovery\/parameter store.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ilmar_kerm@codeeditor:oci-terraform-example (eu-stockholm-1)$ terraform state show oci_database_db_system.test23ai\n\n# oci_database_db_system.test23ai:\nresource \"oci_database_db_system\" \"test23ai\" {\n    availability_domain             = \"AfjF:EU-STOCKHOLM-1-AD-1\"\n    compartment_id                  = \"ocid1.compartment.oc1..aaaa\"\n    cpu_core_count                  = 2\n    data_storage_percentage         = 80\n    data_storage_size_in_gb         = 256\n    database_edition                = \"ENTERPRISE_EDITION\"\n    defined_tags                    = {\n        \"Oracle-Tags.CreatedBy\" = \"default\/ilmar.kerm@gmail.com\"\n        \"Oracle-Tags.CreatedOn\" = \"2024-10-02T14:57:03.606Z\"\n    }\n    disk_redundancy                 = \"HIGH\"\n    display_name                    = \"Test 23ai\"\n    domain                          = \"dev.se1\"\n    fault_domains                   = &#91;\n        \"FAULT-DOMAIN-2\",\n    ]\n    freeform_tags                   = {}\n    hostname                        = \"test23ai1\"\n    id                              = \"ocid1.dbsystem.oc1.eu-stockholm-1.anqxeljr4ebxpbqanr42p2zebku5hdk5nci2\"\n    iorm_config_cache               = &#91;]\n    license_model                   = \"LICENSE_INCLUDED\"\n    listener_port                   = 1521\n    maintenance_window              = &#91;]\n    memory_size_in_gbs              = 32\n    node_count                      = 1\n    reco_storage_size_in_gb         = 256\n    scan_ip_ids                     = &#91;]\n    shape                           = \"VM.Standard.E5.Flex\"\n    source                          = \"NONE\"\n    ssh_public_keys                 = &#91;\n        \"ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQCvccb2GOc+VU6V0lw367a5sgKqn0epAok9vCVboK6WvQid6byo7hkWUSixIuB6ZPGG89n3ig4r my ssh public key\",\n    ]\n    state                           = \"AVAILABLE\"\n    storage_volume_performance_mode = \"BALANCED\"\n    subnet_id                       = \"ocid1.subnet.oc1.eu-stockholm-1.aaaaaaaal6ru\"\n    time_created                    = \"2024-10-02 14:57:04.037 +0000 UTC\"\n    time_zone                       = \"UTC\"\n    version                         = \"23.5.0.24.07\"\n    vip_ids                         = &#91;]\n\n    data_collection_options {\n        is_diagnostics_events_enabled = false\n        is_health_monitoring_enabled  = false\n        is_incident_logs_enabled      = false\n    }\n\n    db_home {\n        create_async               = false\n        database_software_image_id = \"ocid1.databasesoftwareimage.oc1.eu-stockholm-1.anqxeljr4ebxpbqa3h6dawz\"\n        db_home_location           = \"\/u01\/app\/oracle\/product\/23.0.0.0\/dbhome_1\"\n        db_version                 = \"23.5.0.24.07\"\n        defined_tags               = {}\n        display_name               = \"dbhome20241002145704\"\n        freeform_tags              = {}\n        id                         = \"ocid1.dbhome.oc1.eu-stockholm-1.anqxeljrb3hetziaby6hwudav\"\n        state                      = \"AVAILABLE\"\n        time_created               = \"2024-10-02 14:57:04.042 +0000 UTC\"\n\n        database {\n            admin_password             = (sensitive value)\n            character_set              = \"AL32UTF8\"\n            connection_strings         = &#91;\n                {\n                    all_connection_strings = {\n                        \"cdbDefault\"   = \"test23ai1.dev.se1:1521\/test23ai_rf6_arn.dev.se1\"\n                        \"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)))\"\n                    }\n                    cdb_default            = \"test23ai1.dev.se1:1521\/test23ai_rf6_arn.dev.se1\"\n                    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)))\"\n                },\n            ]\n            database_software_image_id = \"ocid1.databasesoftwareimage.oc1.eu-stockholm-1.anqxeljr4ebxpbqa3h6\"\n            db_name                    = \"test23ai\"\n            db_unique_name             = \"test23ai_rf6_arn\"\n            db_workload                = \"OLTP\"\n            defined_tags               = {\n                \"Oracle-Tags.CreatedBy\" = \"default\/ilmar.kerm@gmail.com\"\n                \"Oracle-Tags.CreatedOn\" = \"2024-10-02T14:57:03.740Z\"\n            }\n            freeform_tags              = {}\n            id                         = \"ocid1.database.oc1.eu-stockholm-1.anqxeljr4ebxpbqac3v4lrr\"\n            ncharacter_set             = \"AL16UTF16\"\n            pdb_name                   = \"PDB1\"\n            pluggable_databases        = &#91;]\n            state                      = \"AVAILABLE\"\n            time_created               = \"2024-10-02 14:57:04.043 +0000 UTC\"\n\n            db_backup_config {\n                auto_backup_enabled       = false\n                auto_full_backup_day      = \"SUNDAY\"\n                recovery_window_in_days   = 0\n                run_immediate_full_backup = false\n            }\n        }\n    }\n\n    db_system_options {\n        storage_management = \"ASM\"\n    }\n}<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>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, [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[68,67],"class_list":["post-770","post","type-post","status-publish","format-standard","hentry","category-blog-entry","tag-oci","tag-terraform"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/770","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/comments?post=770"}],"version-history":[{"count":2,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/770\/revisions"}],"predecessor-version":[{"id":772,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/770\/revisions\/772"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=770"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/categories?post=770"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/tags?post=770"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}