No results found

Your search did not match any results.

We suggest you try the following to help find what you're looking for:

  • Check the spelling of your keyword search.
  • Use synonyms for the keyword you typed, for example, try "application" instead of "software."
  • Try one of the popular searches shown below.
  • Start a new search.
Trending Questions
 

Database Infrastructure

The Oracle Database Cloud Service offers autonomous and co-managed Oracle Database cloud solutions. Autonomous databases are preconfigured, fully-managed environments that are suitable for either transaction processing or for data warehouse workloads.

Author: Malte Menkhoff

Updated:

Database System Overview
Database System Overview

The Oracle Database Cloud Service offers autonomous and co-managed Oracle Database cloud solutions. Autonomous databases are preconfigured, fully-managed environments that are suitable for either transaction processing or for data warehouse workloads. Co-managed solutions are virtual machine and Exadata DB systems that you can customize with the resources and settings that meet your needs.

You can quickly provision an Autonomous Database or co-managed DB system. You have full access to the features and operations available with the database, but Oracle owns and manages the infrastructure.

You can also extend co-managed database services into your data center by using Exadata Cloud@Customer, which applies the combined power of Exadata and Oracle Cloud Infrastructure while enabling you to meet your organization’s data-residency requirements.

For details about each offering, start with the following overview topics:

Autonomous Databases

The Database service offers Oracle’s Autonomous Databases with transaction processing and data warehouse workload types.

Co-managed Systems

Database Cloud Service on Virtual Machine

Database Cloud Service offers full-featured Oracle Database cloud instances:

In addition to our Oracle Database Cloud Solution (DBCS) we also offer managed MySQL Cloud Services and other Data Management Cloud Services.

Cloud automation under customer control - provisioning, patching, backup, disaster recovery

 
DBCS Features
DBCS Features

Database Cloud Service Provisioning using Terraform and OCI Resource Manager

Database Cloud Service on VM is an exceptional entry into the world of cloud-supported database services. First, you keep full control of your database servers and databases, plus the service includes many convenience functions, which simplify and accelerate the creation and configuration of database systems. Furthermore, with a few clicks your database can be enhanced with more features to accommodate growing demands or required adjustments. This includes CPU and storage scaling, or the creation of a standby database through Data Guard.

When creating a Database Cloud Service on VM all the cloud resources you need to get a fully operational database instance going are provisioned:

Note: All DBCS resources are only accessible through the Database System resource, not through the individual categories as Compute Instances or Object Store.

Note: All DBCS components are created as part of the Database System resource. Therefore you won’t find them under the Compute Instance or Object Storage Categories in the OCI console.

Prepare the OCloud Landing Zone

Before we create a DBCS on VM resource, we’ll set up a Compartment, a Virtual Cloud Network (VCN), and a Subnet. You can do this through the OCI console or Terraform, using OCI’s Rest API. The latter method is used for this session.

Instructions on how to deploy OCloud Landing Zone can be found here.

 
DBCS Architecture
DBCS Architecture

Policies for the Database Compartment allow members of the dbops group to create database subnets and to manage database-family resources:


-	ALLOW GROUP <label>_dbops manage database-family in compartment <label>_database_compartment
-	ALLOW GROUP <label>_dbops read all-resources in compartment <label>_database_compartment
-	ALLOW GROUP <label>_dbops manage subnets in compartment <label>_database_compartment

OCloud Remote Stack

Now, to create the database system resource OCI Rest API requires a target compartment and a target subnet. There are three different ways to collect information in OCI:


# In this example a list of all compartments within a Tenant is returned which is filtered by the database compartment name
data "oci_identity_compartments" "db_compartment" {
   compartment_id = <tenancy_ocid>
   compartment_id_in_subtree = true
   state          = "ACTIVE"
   filter {
      name   = "name"
      values = [ <Datenbank Compartment Name> ]
   }
}

data "oci_resourcemanager_stack_tf_state" "stack1_tf_state" {
   stack_id   = <stack id>
   local_path = "stack1.tfstate"
}

# Load the pulled state file into a remote state data source
data "terraform_remote_state" "external_stack_remote_state" {
   backend = "local"
   config = {
      path = "${data.oci_resourcemanager_stack_tf_state.stack1_tf_state.local_path}"
   }
}

In real-life, several of these methods will likely be used in combination.

Creating the Database Subnet

After validating the prerequisites for a DBCS on VM deployment, now we’ll provision the resources for the DBCS on VM stack.

For this use case all members of the dbops group should be able to create subnets and database resources within the Tenant’s Database Compartment’s limits. For creating subnets, we use a dedicated Terraform module that is provided by the terraform-oci-ocloud-landing-zone repository.

https://github.com/oracle-devrel/terraform-oci-ocloud-landing-zone

The network_domain module, which is also used as part of the landing zone provisioning, creates a private subnet and all required Security List Policies to communicate with the database system.

When sizing the subnet, you should pay attention to the minimum required IP addresses for a certain deployment type (single node vs RAC). The OCloud landing zone defines the bigger subnets for each service, but if the database architecture demands more as one big subnet, the Terraform function cidrsubnet(prefix,newbits,netum) allows you to split the address space into smaller chunks

service_segment_subnets key subnet
app 10.0.0.0/26
db 10.0.0.64/26
pres 10.0.1.0/26
k8s 10.0.0.128/25

Below you can see an example where CIDIR block 10.0.0.64/26 is split into four subnets by adding two additional bits (newbits).


> cidrsubnets("10.0.0.64/26",2,2,2,2)
 tolist([
   "10.0.0.64/28",  --->  cidrsubnet("10.0.0.64/26",2,0)
   "10.0.0.80/28",  --->  cidrsubnet("10.0.0.64/26",2,1)
    "10.0.0.96/28",  --->  cidrsubnet("10.0.0.64/26",2,2)
   "10.0.0.112/28",  --->  cidrsubnet("10.0.0.64/26",2,3)
])

The Terraform module network_domain creates a private subnet for a given database compartment and VNC. It also sets all ingress rules to allow ssh to the Database nodes and to communicate with the database itself. Last but not least, it sets all egress rules to access Object Storage and YUM Repository on the Service Network.

The db_domain module doesn’t define its own Bastion Service as this is available through the application subnet however, after provisioning the database, a couple of Bastion Sessions are created (ssh, sqlnet) aiming to validate database system connectivity. Once the Time-to-Live has exceeded the Bastion Sessions it will be terminated automatically.

Resource Schema


module "db_domain" {
  
  source = "github.com/oracle-devrel/terraform-oci-ocloud-landing-zone/component/network_domain"
  config  = {
    service_id     = <Container Compartment ID>
    compartment_id = <Database Compartment ID>
    vcn_id         = <VNC ID>
    anywhere       = 0.0.0.0/0
    defined_tags   = null
    freeform_tags  = {"framework" = "ocloud"}
  }
  # Subnet Requirements
  # DB System Type, # Required IP Addresses, Minimum Subnet Size
  # 1-node virtual machine, 1 + 3 reserved in subnet = 4, /30 (4 IP addresses)
  # 2-node RAC virtual machine, (2 addresses * 2 nodes) + 3 for SCANs + 3 reserved in subnet = 10, /28 (16 IP addresses)
  subnet  = {
    # Select the predefined name per index
    domain                      = <predefined subnet postfix (See OCloud Landing Zone, module service_segment)>
    # Select the predefined range per index
    cidr_block                  = <predefined subnet cidr block (See OCloud Landing Zone, module service_segment)>>
    # Create subnet as private
    prohibit_public_ip_on_vnic  = true # Creates a private subnet
    dhcp_options_id             = null
    route_table_id              = <Routing Table ID for Oracle Service Network connectivity which is created by the OCloud Landing Zone>
  }
  bastion  = {
    create            = false # Determine whether a bastion service will be deployed and attached
    client_allow_cidr = []
    max_session_ttl   = null
  }
  # Security List Policies
  tcp_ports = {
    // [protocol, source_cidr, destination port min, max]
    ingress = [
      ["ssh", 0.0.0.0/0, 22, 22], # DBnode access
      ["http", 0.0.0.0/0, 80, 80], # APEX access
      ["https", 0.0.0.0/0, 443, 443], # APEX access
      ["tcp", 0.0.0.0/0, 1521, 1522], # DB Access where 1521 is used for DBCS and 1522 for Autonomous DB
      ["tcp", 0.0.0.0/0, 5500, 5500], # Enterprise Manager Express access
      ["tcp", 0.0.0.0/0, 6200, 6200] # Enables the Oracle Notification Services (ONS) to communicate about Fast Application Notification (FAN) events
    ]
  }

Database System Provisioning

Now that all our prerequisite resources are created, we’re ready to set up the final components. The OCI resource oci_database_db_system provisions a database system, database nodes, and an initial CDB and PDB all in one step. Convenient, no? Note that oci_database_db_system comes with many additional parameters to support other flavors of provisioning a database, i.e., to create a database instance from a backup or as clone from an existing database system. For this scenario we’ll focus on a fresh database install. Refer to oci_database_db_system resource documentation for further details.


resource "oci_database_db_system" "dbaas_db_system" {
  availability_domain = <Availibility Domain>
  compartment_id      = <Database Compartment>
  database_edition    = <Database Edition, i.e. ENTERPRISE EDITION>

  db_home {
    database {
      admin_password = <SYS PASSWORD>
      db_name        = <CDB Name>
      character_set  = <Character Set>
      ncharacter_set = <International Character Set>
      db_workload    = <Workload Type, OLTP or DW>
      pdb_name       = <PDB Name>
      tde_wallet_password = <TDE Wallet Password if it is different to the admin_password>

      db_backup_config {
        auto_backup_enabled = <Is automated backup to Object Storage enabled?>
        auto_backup_window = <Two hour time slot within 24 hour when the backup can take place>
        recovery_window_in_days = <Retention Period>
      }
    }

    db_version   = <Database Version, whereas 19.0.0.0.0 corresponds to the latest available version, i.e. 19.12.0.0.0
    display_name = <>
  shape           = <Database Node Shape which defines the number of OCPUs and Memory>
  subnet_id       = <Target Database Subnet>
  ssh_public_keys = [<Public Key for ssh access>]
  display_name    = <OCI Display Name>
  hostname                = <DB Node Hostname Prefix>
  data_storage_size_in_gb = <Initial Database Storage>
  license_model           = <Database License is either included or transferred from On Premise >
  node_count              = <Database Node Count, 1 or 2 for a Real Application Cluster>
  cluster_name            = <RAC Cluster name>
  nsg_ids                 = <Optional Network Security Group>
  db_system_options {
   	storage_management = <LVM or ASM>
  }
}

OCI Resource Manager

To use DBCS on VM stack with Resource Manager you need to either download the code as zip-file from https://github.com/oracle-devrel/terraform-oci-ocloud-db/tree/main, click “Deploy to Oracle Cloud” within the Github repository’s Readme or create your own cloned repository on Gitlab or Github. Note that DBCS stack references so if you are using a customized version of the landing zone stack you might have to update the “source” parameter of the db_domain module.

For this session we’ll pick “Deploy to Cloud” from the stack’s Readme. It automatically redirects you to OCI Console login and after authentication, opens Resource Manager. “Package URL” points to the stack artifact which is stored within the Github repository. Confirm the “Terms of Use.”

Now, let’s enter a meaningful name for your DBCS stack and choose a Compartment where your stack resource will be created. Note that the stack compartment may differ from the Compartment where the actual infrastructure resources reside.

On the next page you’ll finalize the database system configuration. Enter the landing zone’s Stack OCID, which can be found in Resource Manager next to the landing zone stack. This avoids the need to re-enter a lot of the parameters and enables access to Output artifacts from the landing zone.

Enter a Database System display name. Accept the shown default values, or adjust them to you needs. For a default deployment the following values are used:

Configuration Database Version Oracle Database Software Edition Shape OCPUs Storage(GB)
Small 19c Oracle Enterprise Edition VM.Standard2.2 2 512
Deployment Type Storage Management Software Auto Backup enabled? Node Count
Fast Provisioning LVM false 1

Finally enter the admin or sys password and the public part of your ssh key. Make sure you update the admin password after installation as the initial password will show up in the Terraform tfstate file.

Another way to preconfigure parameters is to manually download the Terraform code from repository and add a terraform.tfvars file. The respective values are shown as read-only parameters in the stack configuration.

Example:


db_system_display_name = "OCI Database System Display Name"
db_system_ssh_public_keys = "ssh-rsa …"
db_system_db_home_database_admin_password = "Password"
stack_id = "ocid1.ormstack.oc1.eu-frankfurt-1.aaaaaaaaqyekvuodrozodmn23zxi…"

OCloud DBCS on VM Stack supports other ways to deploy a database system, but they’re not the subject of this session:

For further information refer to the OCloud DBCS on VM stack’s Readme.

Plan but don’t apply

Create the stack but don’t check “Run Apply” because you should plan your deployment first. This step is very important as it validates to see if the stack code contains any syntax errors, and exactly which OCI resources are going to be added, updated, or destroyed. OCI resources contain updateable and non-updateable parameters, therefore it is recommended to make the planning task a routine as updating a non-updateable value may result in destruction of the resource:


Plan: 9 to add, 0 to change, 0 to destroy.

After verifying the expected results, the stack can be applied. For a “Small” configuration and “Fast Provisioning” it can take between 20 to 25 minutes until the database is available.

Verify DB Node and Database access

Once Database System, Container Database, and Pluggable Database are available you can verify connectivity. Our target resource is in a private subnet; therefore, the Apps Compartments Bastion Service needs to be used as the Bastion Sessions were created as part of the DBCS stack.

Protocol Session type IP Address Port Maximum session time-to-live (min)  
ssh SSH Port Forwarding Session Host IP 22 1800 ssh access to database node(s)
sqlnet SSH Port Forwarding Session Host IP 1521 1800 sqlplus, sqlcl or sqldeveloper

SSH:

Copy the SSH command from OCI console (Bastion Session) to create an SSH tunnel from localhost:localport to <database node ip address>:22.


ssh -i <private key file path> -N -L <local port>:<database node ip address>:22 -p 22 <bastion session OCID>@host.bastion.eu-frankfurt-1.oci.oraclecloud.com

To ssh to the DB Node use the following command:


ssh -i <private key file> opc@localhost -p <local port>

Oracle SQL Developer:

For an Oracle SQL Developer connection to either the CDB or PDB, copy the SSH command from OCI console (Bastion Session) to create an ssh tunnel from localhost:localport to <database node ip address>:1521.


ssh -i <private key file> -N -L <local port>:<database node ip address>:1521 -p 22 <bastion session OCID>@host.bastion.eu-frankfurt-1.oci.oraclecloud.com

Open sqldeveloper and create a new database connection:

Parameter Value
Username sys
Password admin password
Role SYSDBA
Connection Type Basic
Hostname localhost
Port [local sql port]
Service Name CDB or PDB SERVICE_NAME from DBCS output

SQLcl and SQLplus

For a sqlcl or sqlplus connection, copy the SSH command from OCI console (Bastion Session) to create a SSH tunnel from localhost:localport to <database node ip address>:1521.


ssh -i <private key file> -N -L <local port>:<database node ip address>:1521 -p 22 <bastion session OCID>@host.bastion.eu-frankfurt-1.oci.oraclecloud.com

Execute


sql sys@localhost:1523/<FQ Database Name> AS sysdba
or
sqlplus sys@<FQ Database Name> AS sysdba

And now you’ve logged into the database, congratulations!

Conclusion

We successfully provisioned a Database Cloud Service on VM Database instance. From here, you can apply further adjustments or add additional stacks to your OCI Infrastructure in your Tenancy.

Happy building!