DEV Community: Blaine Carter

Create and use an Oracle Autonomous Cloud Database from the Command Line

Blaine Carter — Wed, 24 Jun 2020 13:28:00 +0000

In this post I’ll cover how to create an Oracle Autonomous Cloud Database download the Wallet credentials and connect to the database all from the command line using the OCI-CLI.

After that I'll include an example of a shell script that I use to setup my Demo environment.

Prerequisites

OCI-CLI installed and configured
SQLcl installed

Get the Compartment OCID

I like to keep all of my work compartmentalized so that I don't run into conflicts between my (and potentially other people's) projects. This means I'll need to get the OCID of the compartment I want to work in.

Rather than use the Web Console you can run this command to get a list of your available compartments.

oci iam compartment list

Assuming that you already know which compartment you want to work with you can use the --query parameter to retrieve the ID of that compartment.

The above command returns an array called data that I will use to query an object.

--query "data[]"

I'd like to retrieve only the object with a name of 'Demo'.

--query "data[?name=='Demo']"

[
  {
    "compartment-id": "ocid1.tenancy.oc1..aaaaaaaauqbiglongguid",
    "defined-tags": {},
    "description": "For Demonstrations",
    "freeform-tags": {},
    "id": "ocid1.compartment.oc1..aaaaaaaa7a6biglongguid",
    "inactive-status": null,
    "is-accessible": null,
    "lifecycle-state": "ACTIVE",
    "name": "Demo",
    "time-created": "2019-09-26T21:14:01.870000+00:00"
  }
]

Now that I have the full object, I can get the id value.

--query "data[?name=='Demo'].id"

[
"ocid1.compartment.oc1..aaaaaaaa7a6biglongguid"
]

The list command will return all objects that match the query criteria in an array. Even when there is only a single object it will be returned in an array.

Next, I pipe out the first (and only) value from the array.

--query "data[?name=='Demo'].id | [0]"

"ocid1.compartment.oc1..aaaaaaaa7a6biglongguid"

Using the --raw-output parameter, I can get the raw value without the double quotes.

oci iam compartment list --query "data[?name=='Demo'].id | [0]" --raw-output

ocid1.compartment.oc1..aaaaaaaa7a6biglongguid

I can use this command to set an environment variable.

export COMPARTMENT_ID=$(oci iam compartment list --query "data[?name=='Demo'].id | [0]" --raw-output)

The OCI-CLI query parameter uses the JMESPath query language.

Create Compartment

If the compartment doesn't exist you can use OCI to create one. For this command you will need the OCID of an existing compartment. This will be the parent compartment.

If you want to use an existing compartment as a parent, you can use the above command to get the OCID. Or, if you want to add the new compartment to the ROOT compartment, you can use the Tenancy OCID.

You can get the Tenancy OCID from:

Your OCI Config file cat ~/.oci/config.
The OCID of an existing compartments parent. This time I'm using the ?contains() function to check compartment-id for the string '.tenancy.'. Notice that the compartment-id must be double quoted because it contains a '-' and those double quotes are escaped. \"compartment-id\" oci iam compartment list --query "data[?contains(\"compartment-id\",'.tenancy.')].\"compartment-id\" | [0]" --raw-output

Once you have the parent compartment OCID the command to create a new compartment is:

oci iam compartment create \
   --compartment-id ocid1.tenancy.oc1..aaaaaaaauqbiglongguid \
   --name "Demo" \
   --description "For Demonstrations"

Database

I can check to see if the database already exists by using a query similar to the one I used for compartments.

export DB_ID=$(oci db autonomous-database list -c $COMPARTMENT_ID --query "data[?\"db-name\"=='demo'].id | [0]" --raw-output)

If the demo database doesn't exist I can create a new Always-Free Autonomous Cloud Database with the OCI-CLI.

The data-storage-size-in-tbs is set to 1TB which is larger than the free tier supports. Setting --is-free-tier True will cause the system to automatically scale it to the correct size.

The default value for 'is-free-tier' is False, if you do not include this parameter you will create a standard Autonomous Cloud Database. You should check the Cost Estimator to ensure that you're OK with the cost.

Setting --db-workload "OLTP" will create an Autonomous Transaction Processing database, using "DW" will create a Data Warehouse.

Make sure you use a strong --admin-password , this will be the admin password for the new database.

oci db autonomous-database create \
    --compartment-id $COMPARTMENT_ID \
    --cpu-core-count 1 \
    --data-storage-size-in-tbs 1 \
    --db-name "$DB_NAME" \
    --display-name "$DB_DISPLAY_NAME" \
    --db-workload "OLTP" \
    --admin-password "T3stertester" \
    --is-free-tier True

This command will return a JSON object with the properties of the new database.

I'll add the query and raw-output parameters to extract the ID and assign it to an environment variable.

export DB_ID=$(oci db autonomous-database create \
    --compartment-id $COMPARTMENT_ID \
    --cpu-core-count 1 \
    --data-storage-size-in-tbs 1 \
    --db-name "$DB_NAME" \
    --display-name "$DB_DISPLAY_NAME" \
    --db-workload "OLTP" \
    --admin-password "T3stertester" \
    --is-free-tier  True \
    --query "data.id" --raw-output)

In order to connect to my new database I will need to

Download the Wallet

Wait for your database to be in an AVAILABLE state before attempting to download the wallet.

This command will download your wallet credentials in a .zip file, just like you'd get from the web console.

The $DB_ID variable was set above for the demo database. The --file parameter accepts the location and file name where you want to download the .zip file. The new file will have the password set by --password.

oci db autonomous-database generate-wallet --autonomous-database-id $DB_ID --password Pw4ZipFile --file /home/bcarter/wallets/Wallet_demo.zip

Downloading file  [####################################]  100%

Now that everything is in place I can

Test the Connection

I'll start SQLcl without making a connection (/nolog).
Set cloudconfig to the location of the wallet credentials.
Connect with the admin password and one of the service names contained in the tnsnames.ora file included in the wallet zip file. The predefined service names will be in the form of <name of the database>_<performance level> . You can find more information here.
Run a test query.

sql /nolog
set cloudconfig $PRESENTATION_DIRECTORY/Active/LiquibaseAlwaysFree/code/wallet/Wallet_demo.zip
connect admin/T3stertester@demo_TP
select 'Yes! I connected to my Always Free ATP database!' did_it_work from dual;
exit;

Use a Shell Script to Automate

The following is an example of a setup script I use for my demos.

#!/bin/bash

# Set Variables
COMPARTMENT_NAME="Demo"
DB_NAME="demo"
DB_DISPLAY_NAME="DemoDb"
DB_PW="T3stertester"
WALLET_PW="Pw4ZipFile"
WALLET_ZIP="/home/bcarter/tempWallet/Wallet_${DB_NAME}.zip"

# Create an Free-Tier Autonomous Database
create_db() {
    DB_ID=$(oci db autonomous-database create \
        --compartment-id ${COMPARTMENT_ID} \
        --cpu-core-count 1 \
        --data-storage-size-in-tbs 1 \
        --db-name "${DB_NAME}" \
        --display-name "${DB_DISPLAY_NAME}" \
        --db-workload "OLTP" \
        --admin-password "${DB_PW}" \
        --is-free-tier  True \
        --wait-for-state AVAILABLE \
        --query "data.id" --raw-output)
}

# Download the Wallet .zip file
download_wallet() {
    oci db autonomous-database generate-wallet --autonomous-database-id ${DB_ID} --password ${WALLET_PW} --file ${WALLET_ZIP}
}

# Get the Compartment OCID
COMPARTMENT_ID=$(oci iam compartment list --query "data[?name=='${COMPARTMENT_NAME}'].id | [0]" --raw-output)

# Get the Database OCID
DB_ID=$(oci db autonomous-database list -c ${COMPARTMENT_ID} --query "data[?\"db-name\"=='${DB_NAME}'].id | [0]" --raw-output)

# If the Database does not exist ask to create it.
if [[ -z "${DB_ID}" ]]; then
    echo "No ${DB_NAME} Database found."
    while true; do
        read -p "Do you wish to create the ${DB_NAME} Database? " yn
        case $yn in
            [Yy]* ) create_db; break;;
            [Nn]* ) exit;;
            * ) echo "Please answer y or n.";;
        esac
    done
fi

# Download the wallet
download_wallet

# Create an SQL script to test the connection
echo "set cloudconfig ${WALLET_ZIP}
connect admin/${DB_PW}@${DB_NAME}_TP
select 'Yes! I connected to my Always Free ATP database!' did_it_work from dual;
exit;" > testConnection.sql

# Test the connection
sql /nolog @testConnection.sql

# Delete the test script
rm testConnection.sql

Notice that in the create database method I added a new parameter to the OCI call --wait-for-state AVAILABLE. Since I won't be able to download the wallet until the database is available, I use this parameter to pause at the create step until the new Database is fully up and running.

When I run the script I get

@orablaineos
:OCI-CLI$ ./setup.sh
Query returned empty result, no output to show.
No demo Database found.
Do you wish to create the demo Database? y
Action completed. Waiting until the resource has entered state: ('AVAILABLE',)
Downloading file  [####################################]  100%

SQLcl: Release 18.3 Production on Fri Apr 03 14:28:11 2020

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Operation is successfully completed.
Operation is successfully completed.
Using temp directory:/tmp/oracle_cloud_config3687856744011425249
Connected.

DID_IT_WORK
------------------------------------------------
Yes! I connected to my Always Free ATP database!


Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

Explore

This is just a small taste of what you can do with the OCI-CLI. Check out the documentation for a look at the possibilities.

Leave a comment if you have a question and I will do my best to find you an answer.

Install and configure the Oracle Cloud Command Line Interface

Blaine Carter — Tue, 23 Jun 2020 19:00:03 +0000

I love working with cloud resources. I don't have to bog down my laptop and I don't have to maintain all of the back-end stuff. I don't mean to make it sound as if I think the "back-end stuff" is easy. It's actually, I understand just enough of that "stuff" to make it go (most of the time) so it's nice to have experts in the cloud taking care of it for me.

Some of you may be thinking "but cloud resources are expensive." That's true sometimes, but it depends on who's cloud you're using. If you've been following me you should already know how much I like Oracle's Always Free Services and you should know that I love to automate anything I can. If you'd like to follow along but you don't have an Oracle Cloud account, click that link, create an Always Free account then come back.

In this post I'll cover how to use the Oracle Cloud Command Line Interface (OCI-CLI) to access and control your Oracle Cloud resources.

CLI Overview

Oracle's cloud CLI is a small application you can use to control your Oracle Cloud resources. It gives you the same core functionality as you'd get using the Web Console, and some extra commands. It allows you to control your cloud account from your local console application so you can easily automate the control of your resources.

Pre-requisits

Python 3.5+

The CLI is built with Python so make sure you have Python version 3.5 or higher installed.

RSA Key Pair

You will need an RSA key attached to your cloud user in order to remotely access your account. This must be an RSA key pair in PEM format (minimum 2048 bits).

The easiest way to generate this key par is with openssl. The following commands work in Linux/Mac environments. For Windows you can use your favorite tool or execute the commands in GitBash, WSL or some other Linux shell environment.

openssl genrsa -out myPrivateKey.pem 2048
openssl rsa -pubout -in myPrivateKey.pem -out myPublicKey.pem

The first command creates a PRIVATE KEY called 'myPrivateKey.pem' (name yours whatever you'd like). This is the key you will use to access remote systems. DO NOT share this key, whoever has this key can connect to those systems as you. Think of it as your admin password.

The second command uses your private key to create a PUBLIC KEY called 'myPublicKey.pem' (name yours whatever you'd like). This is the key you will share with remote systems. Those systems will add your PUBLIC KEY to their authorized keys list, allowing you to access their system using your private key.

Store these keys in a secure location. On Linux, the default location is usually in the ~/.ssh directory. But, if you're creating separate keys for your projects, you can store them wherever you'd like. Just remember the location for later.

Cloud Account

You need to have access to an Oracle Cloud account with a user that is authorized to preform the tasks you intend to automate. A good way to tell if your user has the correct permissions is to log onto your account through the Web Console and create an Always Free database then terminate it. While you're logged into the Web Console collect some information.

Tenancy
1. In the menu, under Administration, click Tenancy Details.
2. Locate the OCID and click Copy.
3. Save this value for later.
User
1. In the menu under Identity click Users.
2. Select your user.
3. Near the bottom click 'API Keys' under the resource menu.
4. Click the 'Add Public Key' button.
5. Choose the PUBLIC key file you generated earlier.
6. Click the 'Add' button.
7. Your key should now show up in the 'API Keys' list.
8. Copy the fingerprint of your key and save it for later.
9. Near the top of the user page, locate the OCID and click Copy. Save this value for later.

Quickstart Install

You can download an execute a script that will ask you typical installation configuration questions, after which it will install and configure the OCI-CLI.

The following is current as of the publish date for this post, but you may want to review the instructions in case things change.

Linux / Mac

Open a terminal and run the following command.

bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)"

Windows

Open PowerShell as Administrator.

Set-ExecutionPolicy RemoteSigned

powershell -NoProfile -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).DownloadString('https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.ps1'))"

In either system, answer the questions to complete the install.

Install with Python

If you don't have the rights on your computer or you'd rather not execute the above scripts, you can install the OCI-CLI with Python.

I recommend using a virtual environment when working with python. It helps keep all of your projects clean and isolated.

python3 -m venv oci-cli-env

# Linux / Mac
source oci-cli-env/bin/activate

# Windows
oci-cli-env\Scripts\activate.bat

Once your virtual environment is active, install the OCI-CLI with pip.

pip install oci-cli

You can see if the install was successful by checking the version.

oci -v

2.9.5

Configure the OCI-CLI

Using the values you saved from above, you can create a config file with this command.

oci setup config

You will be prompted for the following information.

Enter a location for your config [/home/bcarter/.oci/config]:
Enter a user OCID: <User OCID from above>
Enter a tenancy OCID: <Tenancy OCID from above>
Enter a region (e.g. us-ashburn-1, us-phoenix-1): us-ashburn-1
Do you want to generate a new RSA key pair? [Y/n]: n
Enter the location of your private key file: /home/bcarter/.ssh/myPrivateKey.pem
Fingerprint: <saved value>

The default location for the config file is typically '~/.oci/config'. If you use a different location you will need to remember where it is.
Enter the user and tenancy OCIDs saved from your account.
Enter the region you want to work in. The system will display some examples you can choose from.
You already generated an RSA key pair so enter 'n'.
Enter the location of your PRIVATE key. This will not be uploaded, the CLI will use your PRIVATE key to make the connection to the cloud.
Enter the Fingerprint you saved when you uploaded your PUBLIC key.

Once it's complete, you should see a response similar to this.

Config written to /home/bcarter/.oci/config

Your OCI-CLI should now be configured. If you open the config file it should look similar to this.

[DEFAULT]
user=ocid1.user.oc1..aaaaaaaat5nvwcna5j6aqzjcaty5eqbb6qt2jvpkanghtgdaqedqw3rynjq
fingerprint=20:3b:97:13:55:1c:5b:0d:d3:37:d8:50:4e:c5:3a:34
key_file=~/.oci/oci_api_key.pem
tenancy=ocid1.tenancy.oc1..aaaaaaaaba3pv6wkcr4jqae5f15p2b2m2yt2j6rx32uzr5vqstifsfdsq
region=us-ashburn-1

You can add other connection profiles manually by following this format or you can use the same command to add a new profile. If you re-run the command, it will ask you for a name to use for the new profile which will be added to the config file.

[DEFAULT]
user=ocid1.user.oc1..aaaaaaaat5nvwcna5j6aqzjcaty5eqbb6qt2jvpkanghtgdaqedqw3rynjq
fingerprint=20:3b:97:13:55:1c:5b:0d:d3:37:d8:50:4e:c5:3a:34
key_file=~/.oci/oci_api_key.pem
tenancy=ocid1.tenancy.oc1..aaaaaaaaba3pv6wkcr4jqae5f15p2b2m2yt2j6rx32uzr5vqstifsfdsq
region=us-ashburn-1

[ACCOUNT2]
user=ocid1.user.oc1..aaaaaaaa65vwl7zut55hiavppn4nbfwyccuecuch5tewwm32rgqvm6i34unq
fingerprint=72:00:22:7f:d3:8b:47:a4:58:05:b8:95:84:31:dd:0e
key_file=~/.ssh/other_key.pem
tenancy=ocid1.tenancy.oc1..aaaaaaaaba3pv6wkcr4jqae5f15p2b2m2yt2j6rx32uzr5vqstifsfdsq
region=us-ashburn-1

Quick Test

Enter the following command.

oci iam availability-domain list

If everything is working, you should receive a response similar to this.

{
  "data": [
    {
      "compartment-id": "ocid1.tenancy.oc1..biglongguid",
      "id": "ocid1.availabilitydomain.oc1..biglongguid",
      "name": "qVbG:US-ASHBURN-AD-1"
    },
    {
      "compartment-id": "ocid1.tenancy.oc1..biglongguid",
      "id": "ocid1.availabilitydomain.oc1..biglongguid",
      "name": "qVbG:US-ASHBURN-AD-2"
    }
  ]
}

Now that your OCI-CLI is installed and configured, you should familiarize yourself with the OCI-CLI documentation to learn about the many, many commands you can use to automate the control of your Oracle Cloud resources.

You can use these same commands from any system with the OCI-CLI installed, including Oracle Cloud Compute instances.

Create and use an Oracle Cloud Compute instance from the Command Line

Blaine Carter — Thu, 18 Jun 2020 21:44:56 +0000

In a previous post, I walked through how to create an Autonomous Database on the Oracle Cloud using the OCI-CLI. In this post you’ll learn how to create a compute instance.

You can use these commands in your Oracle Cloud Shell from your Cloud Dashboard where the OCI-CLI is already setup and ready to go.

If you’d rather use your own environment you can follow these instructions to install and configure the OCI-CLI.

Environment Variables

There are some pieces of information you’ll need in order to create the compute instance. Of course you can look this information up manually, but it’s more fun to automate as much as possible.

Preset Values

Create environment variables for the following:

The name of the Compartment you want to create your new Compute instance in.
The name for your new Compute instance.
The shape you want to use. VM.Standard.E2.1.Micro is used for an Always-Fee Compute instance.
The absolute path for your user’s home directory.

export COMPARTMENT\_NAME='Test'export COMPUTE\_NAME='TestCompute'export COMPUTE\_SHAPE='VM.Standard.E2.1.Micro'export USER\_HOME=$(eval echo ~)

The following commands will create the given object and use the returned OCID to create an environment variable to be used in the other steps.

For example:

export NEW\_OCID=$(the OCI command you would run to create the object and return the OCID)

Compartment OCID

The previous post demonstrates how to use the --query parameter to get the OCID for the Compartment.

export COMPARTMENT\_ID=$(oci iam compartment list --query "data[?name=='${COMPARTMENT\_NAME}'].id | [0]" --raw-output)

Availability Domain

You’ll need to define which Availability Domain you want to use. The above Compute Shape is typically available in your third sub-domain, ‘xxxx:US-ASHBURN-AD-3’.

The following --query parameter for the list command will search for the name of a sub-domain ending in ‘-3’, if one is not found it will chose the first sub-domain in the array.

export AVAILABILITY\_DOMAIN=$(oci iam availability-domain list --query "(data[?ends\_with(name, '-3')] | [0].name) || data[0].name" --raw-output)

Create a Virtual Cloud Network

Your Compute instance will need a VCN in order to connect to the outside world. If you have already created a compute instance in this compartment you can re-use the existing VCN and subnet or follow these instructions to create a new one.

Create a new VCN

export VCN\_ID=$(oci network vcn create -c ${COMPARTMENT\_ID} --cidr-block "10.0.0.0/16" --query "data.id" --raw-output)

Add a Subnet to the VCN

Create a new subnet that your compute instance will use for connections.

export SUBNET\_ID=$(oci network subnet create --vcn-id ${VCN\_ID} -c ${COMPARTMENT\_ID} --cidr-block "10.0.0.0/24" --query "data.id" --raw-output)

Add an Internet Gateway

Create an Internet Gateway that will be used to connect from your VCN to the internet.

export IG\_ID=$(oci network internet-gateway create -c ${COMPARTMENT\_ID} --is-enabled true --vcn-id ${VCN\_ID} --query "data.id" --raw-output)

Add a Route Table

Create a Route Table which is a collection of rules used to route packets to the correct network entity.

export RT\_ID=$(oci network route-table list -c ${COMPARTMENT\_ID} --vcn-id ${VCN\_ID} --query "data[0].id" --raw-output)

Add a Route Rule for the Internet Gateway

Update your Route Table and add a rule granting internet access to your compute instance through your Internet Gateway.

oci network route-table update --rt-id ${RT\_ID} --route-rules '[{"cidrBlock":"0.0.0.0/0","networkEntityId":"'${IG\_ID}'"}]' --force

RSA Key

In order to connect to your compute instance you’ll need an RSA key pair.

If you don’t already have a key pair, use the following command to generate two new files, your “key pair”. id_rsa is your private key, do not share this. id_rsa.pub is your public key that you will share. The below command will create these files in the .ssh directory inside your home directory. You can change the directory or name if you wish.

ssh-keygen -t rsa -N "" -b 2048 -C "CiCd-Compute-Instance" -f ${USER\_HOME}/.ssh/id\_rsa

Now that you have created a network and a key pair you can

Create the Compute Instance

If you created your key pair with a different name or in a different location than ${USER_HOME}/.ssh/id_rsa.pub , you will need to modify the --ssh-authorized-keys-filevalue below when you launch your new instance.

export COMPUTE\_OCID=$(oci compute instance launch \ -c ${COMPARTMENT\_ID} \ --shape "${COMPUTE\_SHAPE}" \ --display-name "${COMPUTE\_NAME}" \ --image-id ocid1.image.oc1.iad.aaaaaaaahjkmmew2pjrcpylaf6zdddtom6xjnazwptervti35keqd4fdylca \ --ssh-authorized-keys-file "${USER\_HOME}/.ssh/id\_rsa.pub" \ --subnet-id ${SUBNET\_ID} \ --availability-domain "${AVAILABILITY\_DOMAIN}" \ --wait-for-state RUNNING \ --query "data.id" \ --raw-output)

Most of the parameters use the values we defined above.

The parameter --image-id is the OCID for the Oracle Linux 7.8 image.

The parameter --wait-for-state RUNNING will pause at the launch command until your instance is fully up and running.

Get the Public IP

You will need the public ip for your instance in order to establish an ssh connection.

export COMPUTE\_IP=$(oci compute instance list-vnics \ --instance-id "${COMPUTE\_OCID}" \ --query 'data[0]."public-ip"' \ --raw-output)echo $COMPUTE\_IP

Connect

Use ssh to connect to the new instance.

ssh opc@${COMPUTE\_IP}

If you changed the location or the name of your private key you may need to include the private key.

ssh -i /path/to/your/key/privateKeyName opc@${COMPUTE\_IP}

Enjoy

At this point you can start configuring your new Compute instance however you’d like.

Leave a comment if something goes wrong or if you have any questions.