Create and use an Oracle Autonomous Cloud Database from the Command Line
Tags: autonomous, database, cloud, tools
Published: 2020-06-24 13:28:00

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.
<h3>Prerequisites</h3>
<ul>
 	<li><a href="https://learncodeshare.net/2020/03/06/install-and-configure-the-oracle-cloud-command-line-interface/" target="_blank" rel="noopener noreferrer">OCI-CLI installed and configured</a></li>
 	<li><a href="https://www.oracle.com/database/technologies/appdev/sqlcl.html" target="_blank" rel="noopener noreferrer">SQLcl</a> installed</li>
</ul>
<h3 id="GetCompartment">Get the Compartment OCID</h3>
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.
<pre class="lang:sh decode:true">oci iam compartment list</pre>
Assuming that you already know which compartment you want to work with you can use the <span class="lang:default decode:true crayon-inline ">--query</span>  parameter to retrieve the ID of that compartment.

The above command returns an array called data that I will use to query an object.
<pre class="lang:sh decode:true ">--query "data[]"</pre>
I'd like to retrieve only the object with a name of 'Demo'.
<pre class="lang:sh decode:true">--query "data[?name=='Demo']"</pre>
<pre class="lang:sh decode:true">[
  {
    "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"
  }
]</pre>
Now that I have the full object, I can get the id value.
<pre class="lang:default decode:true">--query "data[?name=='Demo'].id"</pre>
<pre class="lang:sh decode:true">[
"ocid1.compartment.oc1..aaaaaaaa7a6biglongguid"
]</pre>
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.
<pre class="lang:sh decode:true">--query "data[?name=='Demo'].id | [0]"</pre>
<pre class="lang:sh decode:true">"ocid1.compartment.oc1..aaaaaaaa7a6biglongguid"</pre>
Using the <span class="lang:default decode:true crayon-inline ">--raw-output</span>  parameter, I can get the raw value without the double quotes.
<pre class="lang:sh decode:true ">oci iam compartment list --query "data[?name=='Demo'].id | [0]" --raw-output</pre>
<pre class="lang:sh decode:true ">ocid1.compartment.oc1..aaaaaaaa7a6biglongguid</pre>
I can use this command to set an environment variable.
<pre class="lang:sh decode:true">export COMPARTMENT_ID=$(oci iam compartment list --query "data[?name=='Demo'].id | [0]" --raw-output)</pre>
The OCI-CLI query parameter uses the <a href="https://jmespath.org/" target="_blank" rel="noopener noreferrer">JMESPath query language</a>.
<h5>Create Compartment</h5>
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:
<ul>
 	<li>Your OCI Config file <span class="lang:sh decode:true crayon-inline">cat ~/.oci/config</span>.</li>
 	<li>The OCID of an existing compartments parent.
This time I'm using the <span class="lang:default decode:true crayon-inline ">?contains()</span>  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.  <span class="lang:sh decode:true crayon-inline">\"compartment-id\"</span>
<span class="lang:sh decode:true crayon-inline">oci iam compartment list --query "data[?contains(\"compartment-id\",'.tenancy.')].\"compartment-id\" | [0]" --raw-output</span></li>
</ul>
Once you have the parent compartment OCID the command to create a new compartment is:
<div>
<div>
<pre class="lang:sh decode:true">oci iam compartment create \
   --compartment-id ocid1.tenancy.oc1..aaaaaaaauqbiglongguid \
   --name "Demo" \
   --description "For Demonstrations"</pre>
<h3>Database</h3>
I can check to see if the database already exists by using a query similar to the one I used for compartments.
<pre class="lang:default decode:true">export DB_ID=$(oci db autonomous-database list -c $COMPARTMENT_ID --query "data[?\"db-name\"=='demo'].id | [0]" --raw-output)</pre>
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 <span class="lang:default decode:true crayon-inline ">--is-free-tier True</span>  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 <a href="https://www.oracle.com/cloud/pricing.html" target="_blank" rel="noopener noreferrer">Cost Estimator</a> to ensure that you're OK with the cost.

Setting <span class="lang:default decode:true crayon-inline ">--db-workload "OLTP"</span>  will create an Autonomous Transaction Processing database, using "DW" will create a Data Warehouse.

Make sure you use a strong <span class="lang:default decode:true crayon-inline ">--admin-password</span> , this will be the admin password for the new database.

</div>
</div>
<pre class="lang:sh decode:true">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</pre>
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.
<pre class="lang:sh decode:true">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)</pre>
In order to connect to my new database I will need to
<h3>Download the Wallet</h3>
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.
<pre class="lang:sh decode:true">oci db autonomous-database generate-wallet --autonomous-database-id $DB_ID --password Pw4ZipFile --file /home/bcarter/wallets/Wallet_demo.zip</pre>
<pre class="lang:default decode:true ">Downloading file  [####################################]  100%</pre>
Now that everything is in place I can
<h3>Test the Connection</h3>
<ul>
 	<li>I'll start SQLcl without making a connection (/nolog).</li>
 	<li>Set cloudconfig to the location of the wallet credentials.</li>
 	<li>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 <span class="lang:default decode:true crayon-inline">&lt;name of the database&gt;_&lt;performance level&gt;</span> .  You can find more <a href="https://docs.oracle.com/en/cloud/paas/atp-cloud/atpug/connect-predefined.html#GUID-9747539B-FD46-44F1-8FF8-F5AC650F15BE" target="_blank" rel="noopener noreferrer">information here</a>.</li>
 	<li>Run a test query.</li>
</ul>
<pre class="lang:plsql decode:true">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;</pre>
<h3>Use a Shell Script to Automate</h3>
The following is an example of a setup script I use for my demos.
<pre class="lang:sh decode:true">#!/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;" &gt; testConnection.sql

# Test the connection
sql /nolog @testConnection.sql

# Delete the test script
rm testConnection.sql</pre>
Notice that in the create database method I added a new parameter to the OCI call <span class="lang:default decode:true crayon-inline">--wait-for-state AVAILABLE</span>.  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
<pre class="lang:sh decode:true">@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</pre>
<h3>Explore</h3>
This is just a small taste of what you can do with the OCI-CLI.  Check out the <a href="https://docs.cloud.oracle.com/en-us/iaas/tools/oci-cli/2.9.8/oci_cli_docs/index.html" target="_blank" rel="noopener noreferrer">documentation</a> 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.