Note:

This tutorial requires access to Oracle Cloud. To sign up for a free account, see Get started with Oracle Cloud Infrastructure Free Tier.
It uses example values for Oracle Cloud Infrastructure credentials, tenancy, and compartments. When completing your lab, substitute these values with ones specific to your cloud environment.

Enable Database Management for Oracle Cloud databases Using Terraform

Introduction

Oracle Cloud Infrastructure (OCI) Database Management is an useful performance diagnostics tool which can help you in monitoring and managing your Oracle database. When you create an Oracle Cloud Database within a database system, the Database Management option is not enabled by default because it is based on supplementary resources and actions, independent of the database creation process.

This tutorial describes an example about how to create Terraform code to enable or disable Database Management at code level, as part of a Terraform automation.

The Database Management option for Oracle Cloud Databases is available on the Database Information page, under Associated Services and you can also enable it from the UI. There are several steps and prerequisites for this process to succeed and have all the needed information. For more information about the steps to enable database management using the UI, see the links in the Related Links section at the end of this tutorial.

Objectives

Assuming you have a code that creates an Oracle Cloud database, single instance or RAC, in a private subnet, at this stage the database will not have Database Management enabled and there is no exposed parameter which will do this directly. You will need to create extra resources, grant privileges at OCI level and database level and obtain information from the existing database to be used in the Database Management enabling. All this should be done as an addition to the same code that creates the database, without hardcoded values. The following steps need to be done:

Set the DBSNMP user password and privileges in the database.
Save the monitoring user password as a Secret in OCI Vault
Assign IAM permissions to Database Management service
Assign IAM permissions to your OCI user
Create a Private Endpoint in your VCN
Allow network traffic between the Private Endpoint and your Cloud databases
Enable Database Management for your Cloud database

Prerequisites

Knowledge of Terraform and Oracle Database.
User privileges to be able to create policies, vault, Oracle Cloud database. If the user does not have these privileges, then a tenancy admin can run these actions.
Terraform installed on the machine.

Task 1: Get the private IP of the database node (or of one of the RAC nodes)

There is no attribute that exposes the private IP of the database node(s), so you need to find it indirectly. One way is to use the following 2 steps:

Use the Terraform data source oci_database_db_nodes which will expose the attribute vnic_id.
Use this VNIC OCID with the data source oci_core_private_ips which will return several private IPs. You need to isolate the one for which the attribute is_primary = true, so the primary private IP of the VNIC. For example create a local variable like this:
```
locals {
  node_ip = join(",",[for idx in data.oci_core_private_ips.this.private_ips: idx.ip_address if idx.is_primary == true])
}
```

In order to connect to the database using its private IP over SSH, without any client, you need to use a bastion host. The connection will be as opc user. You can use a null resource to define a connection through the bastion host with SSH. By setting bastion_host in the connection block, the provisioner will connect to bastion_host first, and then connect from there to host(DB node).
After this, use the provisioner remote-exec to execute a script which will switch opc user to oracle and connect to sqlplus as sysdba. The shell script can then invoke a sql secondary script to run the grant statements needed, and also unlock the DBSNMP account and change its password. You can find examples of shell and sql scripts in files.zip.

Here is an example of what the code can look for this step:

resource "null_resource" "set_dbsnmp_pw_and_privs" {

  # connect to RAC node using the bastion
  connection {
      bastion_host        = var.bastion_public_ip  
      type                = "ssh"
      user                = "opc"
      host                = local.node_ip
      private_key         = file(var.ssh_private_key)
      agent               = "false"
      timeout             = "5m"
    }

  # copy sql scrip to RAC node
  provisioner "file" {
    source = "${path.root}/userdata/dbsnmp.sql"
    destination = "/tmp/dbsnmp.sql"
  }

  # copy shell scrip to RAC node
  provisioner "file" {
    source = "${path.root}/userdata/dbsnmp.sql"
    destination = "/tmp/dbsnmp.sh"
  }

  # run sqlplus script as oracle OS user from shell script
  provisioner "remote-exec" {
    inline = [
      "chmod 777 /tmp/dbsnmp.sql",
      "chmod 777 /tmp/dbsnmp.sh",
      "sudo su -c '/tmp/dbsnmp.sh' oracle"
    ]
  }
}

Task 3: Create vault, master key and secret for the DBSNMP password

Use the Terraform resource oci_kms_vault to create a vault.
Use the Terraform resource oci_kms_key to create a new master encryption key in this vault.
Use the Terraform resource oci_vault_secret to create a secret for the DBSNMP password which was set with the above script.

Task 4: Create policy for Database Management service and the OCI user

Create policies using the resource oci_identity_policy:

Create a policy and include these statements to grant IAM permissions to the Database Management service.

statements = [
  "Allow service dpd to manage objects in compartment <compartment>",      
  "Allow service dpd to read secret-family in compartment <compartment>"
]

Create a policy for the OCI user or include these statements in an already existing one.

statements = [
  "Allow group <user_group> to manage dbmgmt-family in compartment <compartment>"
  "Allow group <user_group> to read database-family in compartment <compartment>"
  "Allow group <user_group> to manage vnics in compartment <compartment>"
  "Allow group <user_group> to use subnets in compartment <compartment>"
  "Allow group <user_group> to use network-security-groups in compartment <compartment>"
  "Allow group <user_group> to use security-lists in compartment <compartment>"
  "Allow group <user_group> to manage secret-family in compartment <compartment>"
  "Allow group <user_group> to read buckets in compartment <compartment>"
]

Task 5: Create private endpoint in your VCN

To create a Database Management private endpoint, use the Terraform resource oci_database_management_db_management_private_endpoint.

Note: If you create it in the same private subnet as the one of the Oracle Cloud database then there is no need to further configure the network, so you can skip Task 7.

Task 6: Configure network to allow traffic between the private endpoint and the Cloud database

If the private endpoint is created in a different subnet than the one on the database, add rules to an existing network security group using oci_core_network_security_group_security_rule.

Task 7: Enable Database Management on the Cloud database

To enable database management, you need to use the Terraform resource oci_database_cloud_database_management. It needs to be populated with some of the information created in the previous steps.

The private endpoint should be obtained from the resource created in Task 5.
There is no data source to directly obtain the service name as an attribute. The service name format is Database Unique Name.Host Domain Name. Since the Database Unique Name format is Database Name_Region Key Auto generated string, you need to obtain the service in an indirect manner.
First of all create a data source oci_database_databases. This datasource returns the attribute connection_strings which lists all the connection strings.
Apply different Terraform functions on this result to eventually isolate the service name as a string.
After these steps are executed as part of the code, and the Database Management is enabled for the respective Oracle Cloud database, the Performance Hub button will be available in the OCI Console and the Metrics will be populated on the Metrics page.
Using the same Terraform code with destroy option the Database Management option can be disabled.

Acknowledgments

Author - Adina Nicolescu (Senior Cloud Engineer)

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.

Title and Copyright Information

Enable Database Management for Oracle Cloud databases using Terraform

F79854-01

March 2023