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

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.