At this point I have created everything else apart from the database. For the database we have few options starting if we would like to have Baremetal, Virtual Machine or Exadata.
In this demo I’m going to create VM single-node database but I could create it as RAC database as well with two nodes. Similar to compute instance I need to select shape for my database which will in turn determine available memory and cpu. I’m going to use VM with 2.8 shape which means it’s second generation VM with 8 OCPU’s available.
More details on shapes can be found from here.
I’ve tested creating DB with 2.1 shape earlier and compared to 2.8 the creation time is almost the same. In addition I will use 18.0.0.0 version (other options available for me 11.2.0.4, 12.1.0.2 and 12.2.0.1).
I’m going to use minimum amout of storage (256GB) allocated for this DB as there will be no need to load any data into it.
Overall I’m going with a minimal setup to show deployment with Terraform and display I can access the database.
I’m going to add following to my main.tf.
resource "oci_database_db_system" "CreateDBSystem" {
availability_domain = "${lookup(data.oci_identity_availability_domains.ADs.availability_domains[0],"name")}"
compartment_id = "${oci_identity_compartment.CreateCompartment.id}"
database_edition = "${var.db_system_database_edition}"
db_home {
database {
admin_password = "${var.db_system_db_home_database_admin_password}"
character_set = "${var.db_system_db_home_database_character_set}"
ncharacter_set = "${var.db_system_db_home_database_ncharacter_set}"
pdb_name = "${var.db_system_db_home_database_pdb_name}"
db_name = "${var.db_system_db_home_database_db_name}"
}
db_version = "${var.db_system_db_home_db_version}"
display_name = "${var.db_system_db_home_display_name}"
}
hostname = "${var.db_system_hostname}"
shape = "${var.db_system_shape}"
ssh_public_keys = ["${var.ssh_public_key}"] // Needs to be passed as a list
subnet_id = "${oci_core_subnet.CreatePrivateSubnet.id}"
// Optional parameters start from here
// cpu_core_count = "${var.db_system_cpu_core_count}" // Ignored when VM shape is used
// data_storage_percentage = "${var.db_system_data_storage_percentage}" // Ignored when VM shape is used
license_model = "${var.db_system_license_model}"
node_count = "${var.db_system_node_count}"
data_storage_size_in_gb = "${var.db_system_data_storage_size_in_gbs}"
}
On line five and six you can see that for db_home and database we will specify specific variables. I’m hoping that for example with Exadata deployments we can utilize that in the future to create additional databases via Terraform.
Note that ssh key for login must be passed as a list or otherwise it will throw an error and will not work.
In the variables.tf I have following defined.
// Database system variables
variable "db_system_database_edition" {
default = "ENTERPRISE_EDITION"
} // Using Standard Edition here but can be changed to ENTERPRISE_EDITION if needed
variable "db_system_db_home_database_admin_password" {
default = "First1_Database2_"
} // 9-30 characters, two uppercase, two numbers, two special
variable "db_system_db_home_database_db_name" {
default = "TEST"
}
variable "db_system_db_home_database_character_set" {
default = "AL32UTF8"
}
variable "db_system_db_home_database_ncharacter_set" {
default = "AL16UTF16"
}
variable "db_system_db_home_database_pdb_name" {
default = "TESTPDB"
}
variable "db_system_db_home_db_version" {
default = "18.0.0.0"
}
variable "db_system_db_home_display_name" {
default = "HOME1"
} #Optional
variable "db_system_hostname" {
default = "testhost"
}
variable "db_system_shape" {
default = "VM.Standard2.8"
} // Adjust when needed with 2.2, 2.4 etc..
// Optional DB system variables
variable "db_system_data_storage_percentage" {
default = "80"
}
/*
variable "db_system_display_name" {
default = "TEST DB"
}
*/ // Not applicable for virtual DB systems
variable "db_system_license_model" {
default = "LICENSE_INCLUDED"
}
variable "db_system_node_count" {
default = "1"
}
variable "db_system_data_storage_size_in_gbs" {
default = "256"
}
Nothing strange there, I’ve just defined variables which make sense in this case (18c database, license included etc) and I’m ready to execute my Terraform script.
I was investigating is to display connection_strings and private IP what gets assigned to the database instance in the outputs.tf but using data sources I was unable to do it yet.
Running Terraform apply I see following:
oci_database_db_system.CreateDBSystem: Creating...
availability_domain: "" => "xknJ:EU-FRANKFURT-1-AD-1"
backup_subnet_id: "" => "<computed>"
cluster_name: "" => "<computed>"
compartment_id: "" => "ocid1.compartment.oc1..xxxxx"
cpu_core_count: "" => "<computed>"
data_storage_percentage: "" => "<computed>"
data_storage_size_in_gb: "" => "256"
database_edition: "" => "ENTERPRISE_EDITION"
db_home.#: "" => "1"
db_home.0.database.#: "" => "1"
db_home.0.database.0.admin_password: "<sensitive>" => "<sensitive>"
db_home.0.database.0.backup_id: "" => "<computed>"
db_home.0.database.0.backup_tde_password: "<sensitive>" => "<sensitive>"
db_home.0.database.0.character_set: "" => "AL32UTF8"
db_home.0.database.0.db_backup_config.#: "" => "<computed>"
db_home.0.database.0.db_name: "" => "TEST"
db_home.0.database.0.db_workload: "" => "<computed>"
db_home.0.database.0.ncharacter_set: "" => "AL16UTF16"
db_home.0.database.0.pdb_name: "" => "TESTPDB"
db_home.0.db_version: "" => "18.0.0.0"
db_home.0.display_name: "" => "HOME1"
disk_redundancy: "" => "<computed>"
display_name: "" => "<computed>"
domain: "" => "<computed>"
freeform_tags.%: "" => "<computed>"
hostname: "" => "testhost"
last_patch_history_entry_id: "" => "<computed>"
license_model: "" => "LICENSE_INCLUDED"
lifecycle_details: "" => "<computed>"
listener_port: "" => "<computed>"
node_count: "" => "1"
reco_storage_size_in_gb: "" => "<computed>"
scan_dns_record_id: "" => "<computed>"
scan_ip_ids.#: "" => "<computed>"
shape: "" => "VM.Standard2.8"
sparse_diskgroup: "" => "<computed>"
ssh_public_keys.#: "" => "1"
ssh_public_keys.0: "" => "ssh-rsa xxxxx"
state: "" => "<computed>"
subnet_id: "" => "ocid1.subnet.oc1.eu-frankfurt-1.xxxxx"
time_created: "" => "<computed>"
version: "" => "<computed>"
vip_ids.#: "" => "<computed>"
oci_database_db_system.CreateDBSystem: Creation complete after 1h14m19s (ID: ocid1.dbsystem.oc1.eu-frankfurt-1.xxxxx)
Creating database on VM shape usually seems to take around one hour at least with 2.1, 2.2 and 2.8 shapes. I still need to test if RAC has any impact on creation time.
Now when system is running I can login to my jump server and further log on to my database instance (remember I allowed port 22 only from public subnet’s private CIDR block) after I have found out the private IP which was assigned to it. Looking from the console I see it has IP 172.27.1.2 in the private subnet I created earlier.
[opc@public-1 ~]$ ssh -i mykey.ppk opc@172.16.1.2
Last login: Mon Mar 4 18:00:30 2019 from 172.16.0.2
[opc@testhost ~]$ ps -ef|grep pmon
opc 1786 98997 0 20:51 pts/0 00:00:00 grep pmon
oracle 2335 1 0 15:44 ? 00:00:01 ora_pmon_TEST
grid 81332 1 0 15:22 ? 00:00:01 asm_pmon_+ASM1
grid 99813 1 0 15:24 ? 00:00:01 apx_pmon_+APX1
I can see user “oracle” is running my TEST database and using sudo I can access that user and perform database related operations I would need to do.
Basic environment variables for Oracle database should be set once you sudo to the user (ORACLE_HOME, PATH etc).
[opc@testhost ~]$ sudo su - oracle
[oracle@testhost ~]$ cd $ORACLE_HOME
[oracle@testhost dbhome_1]$ pwd
/u01/app/oracle/product/18.0.0.0/dbhome_1
[oracle@testhost dbhome_1]$ sqlplus sys as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Mon Mar 4 20:54:05 2019
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 6.4425E+10 bytes
Fixed Size 19534392 bytes
Variable Size 8858370048 bytes
Database Buffers 5.5432E+10 bytes
Redo Buffers 114679808 bytes
Database mounted.
Database opened.
SQL>
I could modify init parameters or do any server side installations depending on requirements. Or even automate deployment of my own init.ora with Ansible for example!
I still want to verify I can access the database from public subnet as I opened port 1521 for sql*net. This could be a case for application server accessing your database from the public subnet.
I’ve created user in the database and installed instant client on the public server. I can login to console and copy the required connection string for accessing the database. More on connection strings and accessing database can be found from documentation.
Connection string could be retrieved using Terraform data sources but like mentioned previously I had some challenges with it which I can hopefully address in the future.
[opc@public-1 ~]$ sqlplus c##simo/xxxxx@//testhost.pri.oci.oraclevcn.com:1521/TEST_fra1v3.pri.oci.oraclevcn.com
SQL*Plus: Release 18.0.0.0.0 - Production on Mon Mar 4 21:12:26 2019
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL>
This completes the deployment of required resources which included compartment, network components with specific rules, jump server and finally an Oracle database.
On the last post of the series I will summarize everything and provide the source code to either run everything via Terraform or utilizing the new just released OCI Resource Manager.
Last time I showed how to provision Autonomous Database Serverless (ADB-S) on Google Cloud. This…
I bet few years back folks didn't expect that by 2024 we would be able…
This will NOT be a technical walkthrough on Oracle Database@Azure but rather my opinions and…
Recently OCI announced small but VERY useful service, IP Address Insights. Why this matters? I've…