How to create an Oracle Database Cloud service (OCI) using the command line

The Oracle Cloud Infrastructure, also known as the second generation cloud, offers an integrated command line utility called OCI Command Line Interface (CLI) which can be used to perform almost all the activities we can do using the web console.

I have presented about this topic recently in Collaborate 19 conference (you can find slides here). Last year I did a mix including also the old Classic cloud CLI utilities in several conferences (slides here), providing an introduction to Oracle cloud concepts and examples configuring and using the CLI.

In this post, I want to show details of creating a database cloud service to better illustrate a common use case. There are several other useful examples in the documentation, as well in the Oracle oci-cli github repository.

Keep in mind when checking the documentation that you are using https://docs.cloud.oracle.com. That is the new OCI (second generation) version, not the classic which is still available at https://docs.oracle.com/en/cloud.

CLI installation and configuration

The CLI can be installed in Windows and Linux with a single command. It includes package dependencies, so using a small dedicated instance could be a good choice to avoid affecting other programs by this.

The examples shown here were using a desktop with Ubuntu 16.04. If you want to see more consideration related to the installation, see this post from my colleague Jose.

To use the CLI you need to provide details of your cloud account and use an RSA key pair. Both are explained clearly in the public documentation here and here.

Below are the steps to perform that:

$ openssl genrsa -out ./my-oci-key.pem 2048
$ chmod go-rwx ./my-oci-key.pem
$ openssl rsa -pubout -in ./my-oci-key.pem -out ./my-oci-key-pub.pem
$ ls -lrt 
total 4
-rwxrwxrwx 1 ncalero ncalero 451 Apr 4 23:28 my-oci-key-pub.pem
-rwxrwxrwx 1 ncalero ncalero 1679 Apr 4 23:28 my-oci-key.pem

The file my-oci-key-pub.pem should be uploaded to your user in your cloud account using the web console. If your user is part of a domain, then you need to use a local user just to hold that key (you don’t log in with that user, just use it to keep your key).

Back in your desktop, create a configuration file that looks like this:

$ vi .oci/config
$ cat .oci/config
[DEFAULT]
# my personal local OCID
user=ocid1.user.oc1..aaaaaaaaxotqff7cznh76yknkyqau2l2ysw72l65l4tb3ojto7rhfiyhrscq
fingerprint=a4:c2:1d:57:91:ba:a8:da:41:26:fd:08:f8:65:46:56
key_file=/home/ncalero/my-oci-key.pem
tenancy=ocid1.tenancy.oc1..aaaaaaaalqia3yg7kmfvj7fvmgt4j4kokziowmjkjmymyo62bwkw5hodl4pa
region=us-ashburn-1

And you are ready to work. A simple test is to query the available regions in the cloud that will use your account details to authenticate:

$ oci iam region list --output table
+-----+----------------+
| key | name           |
+-----+----------------+
| FRA | eu-frankfurt-1 |
| IAD | us-ashburn-1   |
| LHR | uk-london-1    |
| PHX | us-phoenix-1   |
| YYZ | ca-toronto-1   |
+-----+----------------+

If you did not perform the previous steps well, you will get an error like this:

$ oci iam region list --output table
ServiceError:
{
"code": "NotAuthenticated",
"message": "The required information to complete authentication was not provided or was incorrect.",
"opc-request-id": "C6A15A6E74B741C89704721C843E002A/00990538D26560F13A71509835BE3DF7/7CAC362441627E09812C81DCDD4491D5",
"status": 401
}

 

Which Oracle databases can I create?

Oracle Cloud Services offers three flavors: Exadata, Bare Metal and Virtual Machine, each of them having different combinations of memory, CPU power and storage. And of course the different options from features and license perspective: Standard, Enterprise, High performance and Extreme performance. For details about those please check the FAQ https://cloud.oracle.com/database/faq

A simple way to see the database versions we can create is to use this command:

$ oci db version list -c $COMPID --db-system-shape "VM.Standard1.1" --all --output table
+-----------------------------+--------------+-----------------+
| is-latest-for-major-version | supports-pdb | version         |
+-----------------------------+--------------+-----------------+
| True                        | False        | 11.2.0.4        |
| False                       | False        | 11.2.0.4.181016 |
| False                       | False        | 11.2.0.4.190115 |
| False                       | False        | 11.2.0.4.190416 |
| True                        | True         | 12.1.0.2        |
| False                       | True         | 12.1.0.2.181016 |
| False                       | True         | 12.1.0.2.190115 |
| False                       | True         | 12.1.0.2.190416 |
| True                        | True         | 12.2.0.1        |
| False                       | True         | 12.2.0.1.181016 |
| False                       | True         | 12.2.0.1.190115 |
| False                       | True         | 12.2.0.1.190416 |
| True                        | True         | 18.0.0.0        |
| False                       | True         | 18.4.0.0        |
| False                       | True         | 18.5.0.0        |
| False                       | True         | 18.6.0.0        |
+-----------------------------+--------------+-----------------+

It can be used without providing a particular shape, but that will only list the latest versions for each major release (the same as if we don’t use ‘-all’ parameter).

Note that only the latest three PSU are available. That list is updated each time a new PSU is released, pushing old ones out of the list for all versions. This is the output of that same command executed a couple of months ago:

+-----------------------------+--------------+-----------------+
| is-latest-for-major-version | supports-pdb | version         |
+-----------------------------+--------------+-----------------+
| True                        | False        | 11.2.0.4        |
| False                       | False        | 11.2.0.4.180417 |
| False                       | False        | 11.2.0.4.180717 |
| False                       | False        | 11.2.0.4.181016 |
| True                        | True         | 12.1.0.2        |
| False                       | True         | 12.1.0.2.180417 |
| False                       | True         | 12.1.0.2.180717 |
| False                       | True         | 12.1.0.2.181016 |
| True                        | True         | 12.2.0.1        |
| False                       | True         | 12.2.0.1.180417 |
| False                       | True         | 12.2.0.1.180717 |
| False                       | True         | 12.2.0.1.181016 |
| True                        | True         | 18.0.0.0        |
| False                       | True         | 18.2.0.0        |
| False                       | True         | 18.3.0.0        |
| False                       | True         | 18.4.0.0        |
+-----------------------------+--------------+-----------------+

In the following example, I am going to create a Database System VM. These systems allow only one database and will create a compute node to host the Oracle Database (two if RAC) that we can connect using ssh. Those nodes are not listed as compute instances in our account, despite us thinking they are. Database nodes part of DB Systems should be managed using the DB node API and not the one available for compute instances.

 

Gathering parameters required to launch our instance

The command to use for launching our database is oci db system launch, and you need to provide several parameters. The good thing is you can use a JSON file with those parameters instead of passing all of them in the command line. And the oci client gives you a template for that:

$ oci db system launch --generate-full-command-json-input
{
"adminPassword": "string",
"availabilityDomain": "string",
"backupSubnetId": "string",
"characterSet": "string",
"clusterName": "string",
"compartmentId": "string",
"cpuCoreCount": 0,
"dataStoragePercentage": 0,
"databaseEdition": "STANDARD_EDITION|ENTERPRISE_EDITION|ENTERPRISE_EDITION_HIGH_PERFORMANCE|ENTERPRISE_EDITION_EXTREME_PERFORMANCE",
"dbName": "string",
"dbVersion": "string",
"dbWorkload": "string",
"definedTags": {
"string1": {
"string1": {
"string1": "string",
"string2": "string"
},
"string2": {
"string1": "string",
"string2": "string"
}
},
"string2": {
"string1": {
"string1": "string",
"string2": "string"
},
"string2": {
"string1": "string",
"string2": "string"
}
}
},
"diskRedundancy": "HIGH|NORMAL",
"displayName": "string",
"domain": "string",
"faultDomains": [
"string",
"string"
],
"freeformTags": {
"string1": "string",
"string2": "string"
},
"hostname": "string",
"initialDataStorageSizeInGb": 0,
"licenseModel": "LICENSE_INCLUDED|BRING_YOUR_OWN_LICENSE",
"maxWaitSeconds": 0,
"ncharacterSet": "string",
"nodeCount": 0,
"pdbName": "string",
"shape": "string",
"sparseDiskgroup": true,
"sshAuthorizedKeysFile": "/path/to/file",
"subnetId": "string",
"timeZone": "string",
"waitForState": "PROVISIONING|AVAILABLE|UPDATING|TERMINATING|TERMINATED|FAILED",
"waitIntervalSeconds": 0
}

You can find a detailed explanation of each parameter in the CLI doc and in the OCI Database reference.

I would like to focus on a few parameters pointing to resources that should already exist in our account before launching a DB:

• Compartment: Where the database will be created. They are like folders to organize your resources and it is a good practice to create everything you need instead of using the default root. For example – test, desa, prod. Keep in mind this cannot be changed after you create the DB. As of today, there is no feature to move resources between compartments.
• VCN and Subnet: A VCN is needed in the region where you want to launch the DB, with at least one Subnet on it for BM and VM DB systems, and two for Exadata. Again, there are several considerations here: public or private subnet? Regional or AD-specific? How are you going to route the traffic? You can see more details about those considerations here for Exadata DB systems, and here for BM and VM DB Systems.
  Similarly to the above, you should design and create the proper configuration to make sure your traffic is only shared between the resources that need it, for security and performance reasons.
• Security lists: these are the rules controlling the traffic allowed in your subnet. I chose to mention this as a separate item from the VCN and Subnet above because you need to add a specific rule to the subnet default security list to be able to create a new DB. It is an ingress rule from all source ports with port 22 as the destination, like this:

source cidr: 192.168.0.0/16   --> your subnet
ip protocol: tcp
source port range: all
destination port range: 22

You can find this rule explained in MOS note 2433870.1, but as of today, it fails to clarify that this rule must be added to the default security list. You will get this error if you add it to any other security list (as if this rule is not set at all):

"code": "InvalidParameter",
"message": "Request is rejected as port 22 is not enabled in the security list for subnet ocid1.subnet.oc1.iad.aaaaaaaahzydgowtl3tim7usbpvruluadfrucyvvf6jrsrixkwhlbbzexowa",

To add this rule from the CLI, there is the command update which replaces existing rules in the security list. That means we have to get SL details to then add the new rule. For example, you need to use the below commands (here the subnet in use is 192.168.0.0):

$ oci network security-list get --security-list-id $SECLISTID 
...
$ oci network security-list update --security-list-id $SECLISTID --ingress-security-rules '[{ ... }, { ... }, {"source": "192.168.0.0/16", "protocol": "6", "isStateless": false, "tcpOptions": {"destinationPortRange": {"max": 22, "min": 22}, "sourcePortRange": null}} ]'

 

How to get all those IDs?

References to cloud resources using the CLI is done by their internal ID, called OCID. This is a unique identifier for absolutely every resource (including compartments, availability domains, and tenancies) in OCI. You should become familiar with them to handle your Oracle cloud resources.

One of the parameters required in the JSON file is the subnet OCID. To get it, we need to know the VCN OCID. And to check and fix our security rules, we need to know their OCIDs.

Let’s start from the first OCID we need to know: which compartments do we have?

$ oci iam compartment list --output table --query 'data [*].{id:id}'

The above example uses a table format instead of the default JSON output. The syntax for filtering the data to be returned is using JMESPath query language https://jmespath.org/

Remember the region was configured initially in the CLI so it is not being passed to all commands here. But you can overwrite that using –region parameter if needed to work with resources in another region.

Back to getting our OCIDs: which is the VCN OCID for the compartment we are going to use?

$ oci network vcn list -c $COMPID --output table

Which subnets are in that VCN?

$ oci network subnet list -c $COMPID --vcn-id $VCNID --output table --query 'data [*].{Name:"display-name", AD:"availability-domain", CIDR:"cidr-block", id:id}'

The security lists attached to one subnet?

$ oci network subnet get --subnet-id $SUBNETID

Finally, to see the rules in the security list (you can have several per subnet):

$ oci network security-list get --security-list-id $SECLISTID

Creating the cloud database

This is the JSON file I prepared with all required parameters for launching my DB System VM once the Compartment, VCN and subnet are created in my account:

$ cat db-ad1-18c.json
{
"adminPassword": "***",
"characterSet": "WE8MSWIN1252",
"dbWorkload": "OLTP",
"dbName": "MYCDB",
"dbVersion": "18.5.0.0",
"ncharacterSet": "AL16UTF16",
"pdbName": "MYPDB",
"waitForState": "PROVISIONING",
"waitIntervalSeconds": 30,
"availabilityDomain": "HDGG:US-ASHBURN-AD-1",
"clusterName": "MYDB18c",
"compartmentId": "ocid1.compartment.oc1..aaaaaaaazh5fmhjk4hyvvyfv7x46u6763sgv2sye7yjdheridcykdevdha7q",
"cpuCoreCount": 8,
"dataStoragePercentage": 80,
"databaseEdition": "ENTERPRISE_EDITION_EXTREME_PERFORMANCE",
"diskRedundancy": "HIGH",
"displayName": "MYDB18c",
"domain": "sub10072038530.vcn1007203853.oraclevcn.com",
"faultDomains": [
"FAULT-DOMAIN-1"
],
"freeformTags": {},
"hostname": "myserver",
"initialDataStorageSizeInGb": 256,
"licenseModel": "BRING_YOUR_OWN_LICENSE",
"nodeCount": 1,
"shape": "VM.Standard1.1",
"sparseDiskgroup": null,
"sshAuthorizedKeysFile": "./ps-key-pub",
"subnetId": "ocid1.subnet.oc1.iad.aaaaaaaavdjnitqlvfveuhlyj2x2itn2xboi227dchshcum6oqqtm7dy3wra",
"timeZone": "UTC"
}

Note the parameter sshAuthorizedKeysFile should include all the public keys that will be added to the database nodes. I can use only my public key, or create a file with all the public keys our team will need.

To create the database system:

$ oci db system launch --from-json file://db-ad1-18c.json
Action completed. Waiting until the resource has entered state: PROVISIONING
{
"data": {
"availability-domain": "HDGG:US-ASHBURN-AD-1",
"backup-subnet-id": null,
"cluster-name": "MYDB18c",
"compartment-id": "ocid1.compartment.oc1..aaaaaaaazh5fmhjk4hyvvyfv7x46u6763sgv2sye7yjdheridcykdevdha7q",
"cpu-core-count": 1,
"data-storage-percentage": 80,
"data-storage-size-in-gbs": 256,
"database-edition": "ENTERPRISE_EDITION_EXTREME_PERFORMANCE",
"defined-tags": {},
"disk-redundancy": "HIGH",
"display-name": "MYDB18c",
"domain": "sub10072038530.vcn1007203853.oraclevcn.com",
"fault-domains": [
"FAULT-DOMAIN-1"
],
"freeform-tags": {},
"hostname": "myserver",
"id": "ocid1.dbsystem.oc1.iad.abuwcljt5jvywba7go2f5wngqgnajkxmuvxxdoc5uzlv6qsp4s6vv4rsjmwq",
"iorm-config-cache": null,
"last-patch-history-entry-id": null,
"license-model": "BRING_YOUR_OWN_LICENSE",
"lifecycle-details": null,
"lifecycle-state": "PROVISIONING",
"listener-port": 1521,
"node-count": 1,
"reco-storage-size-in-gb": 256,
"scan-dns-record-id": null,
"scan-ip-ids": null,
"shape": "VM.Standard1.1",
"sparse-diskgroup": null,
"ssh-public-keys": [
"ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDRYRf9z/aH8S48bQb0NGELNU8XfW0kABqaC2AeUWm85NVTz6kCgOfjq7azeAWQS21sAa8nwaC+sgxqEyaAYFywFBuzQ4riVT7RZopYQ8nUibqIBZOaBpfXGMPJ5GdFWLM+tDjsjP5SI+GtO1CoKBYraWbRXlwCGSEuWMWE7P4LeYCY2ohkApCZnSwV7zbcEV4Xb3dPRAl6SUR65SWHw0nb25DnGsEYl7pPPFpsYc3VbGpnrF9n/kjDo2K0TEsbsWrypFunxqkYC8fsre58pvzOJO8YJT9oysUepnMoeQDpd9NCyn8nD+DGTJwqcNp2lzCYQD3gsxCDPIEuW0faBzn7 calero"
],
"subnet-id": "ocid1.subnet.oc1.iad.aaaaaaaavdjnitqlvfveuhlyj2x2itn2xboi227dchshcum6oqqtm7dy3wra",
"time-created": "2019-05-06T13:34:07.753000+00:00",
"time-zone": "UTC",
"version": null,
"vip-ids": null
},
"etag": "ad8ace3c"
}

The creation could take up to one hour. We can validate the progress by checking the DB System version – once it is reported, then our DB is ready.

$ oci db system get --db-system-id $DBSYSID | grep version
"version": null,
$ oci db system get --db-system-id $DBSYSID | grep version
"version": "18.5.0.0.190115",

To connect to the instance now it is created, we need to know its IP address. For that, we need to find out first the VNICs associated with the DB node to then get its public IP:

$ oci db node list -c $COMPID --db-system-id $DBSYSID --output table --query 'data [*].{hostname:hostname, "vnic-id":"vnic-id"}'
$ oci network vnic get --vnic-id $VNICID --query 'data [*].{"public-ip":"public-ip"}'

Now we can connect using our private ssh key and review what we have created:

$ ssh -i ps-key-priv opc@129.213.138.94
The authenticity of host '129.213.138.94 (129.213.138.94)' can't be established.'
RSA key fingerprint is SHA256:Fuyans+8L6o53p3bte2ouVIvBJoeftd1+L9jSt81MCY.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '129.213.138.94' (RSA) to the list of known hosts.
[opc@myserver ~]$ ps -eaf | grep pmon
oracle 7627 1 0 14:43 ? 00:00:00 ora_pmon_MYCDB
grid 61807 1 0 14:14 ? 00:00:00 asm_pmon_+ASM1
opc 73146 63029 0 15:02 pts/0 00:00:00 grep pmon
grid 88315 1 0 14:16 ? 00:00:00 apx_pmon_+APX1
[opc@myserver ~]$ cat /etc/oracle-release
Oracle Linux Server release 6.10
[opc@myserver ~]$ sudo su - grid
[grid@myserver ~]$ tail /etc/oratab
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
MYCDB:/u01/app/oracle/product/18.0.0.0/dbhome_1:N
[grid@myserver ~]$ crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [18.0.0.0.0]
[grid@myserver ~]$ crsctl get cluster configuration
Name : dbSysv4rsjmwq
Configuration : Cluster
Class : Standalone Cluster
Type : flex
The cluster is not extended.
--------------------------------------------------------------------------------
MEMBER CLUSTER INFORMATION

Name Version GUID Deployed Deconfigured
================================================================================
================================================================================
[grid@myserver ~]$ exit
logout
[opc@myserver ~]$ sudo su -
[root@myserver ~]# dbcli describe-component
System Version
---------------
18.3.4.0.0

Component Installed Version Available Version
---------------------------------------- -------------------- --------------------
GI 18.5.0.0.190115 18.6.0.0.190416
DB 18.5.0.0.190115 18.6.0.0.190416

[root@myserver ~]# dbcli list-dbhomes

ID Name DB Version Home Location Status
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
05b8bb02-7dab-4118-8f32-a5d75afa9d19 OraDB18000_home1 18.5.0.0.190115 /u01/app/oracle/product/18.0.0.0/dbhome_1 Configured

[root@myserver ~]#

Costs

How much will it cost you to have this setup running? An easy way to estimate this is by using the Cost estimator.

There are a lot of possible combinations based on the amount of CPUs, Database Edition and instances. A test like the one described in this post (1 CPU, BYOL, Extreme Performance, one instance) for two hours costs $5 US.

Be careful if thinking about testing Exadata Cloud Services, as there is a minimum charge of an entire month (744hs). This is clearly explained in the Pricing page.

 

Summary

We have explored how to use the Oracle Cloud Infrastructure command line utility oci to create a DB System VM, allowing for a repeatable and easy to follow procedure.

But there is a lot more to explore about this topic:

• other tools available for the same, as terraform
• other database deployments requiring different considerations, as Bare metal or Exadata Cloud services
• more ways to create databases in the cloud if we already have our DB Systems created, like dbcli