Break New Ground

Cloud Cloud
Developers
Linux / Oracle Cloud Infrastructure
May 11, 2019

Get Started with Autonomous Database and SQLcl in No Time Using Cloud Developer Image

Sergio Leunissen

In this blog post, I describe how to use a free trial for Oracle Cloud and the recently released, Oracle Linux-based Cloud Developer Image to provision an Autonomous Transaction Processing Database and connect to it via SQLcl, all in a matter of minutes.

Think of the Cloud Developer Image as a Swiss army knife for Cloud developers. It has a ton of tools pre-installed, including:

Languages and Oracle Database Connectors

Java Platform Standard Edition (Java SE) 8, 11 and 12
Python 3.6 and cx_Oracle 7
Node.js 10 and node-oracledb
Go 1.12
Oracle Instant Client 18.5

Oracle Cloud Infrastructure Client Tools

Oracle Cloud Infrastructure CLI
Python, Java, Go and Ruby Oracle Cloud Infrastructure SDKs
Terraform and Oracle Cloud Infrastructure Terraform Provider
Oracle Cloud Infrastructure Utilities

Other

Oracle SQL Developer and SQLcl
Oracle Container Runtime for Docker
Extra Packages for Enterprise Linux (EPEL) via Yum
GUI Desktop with access via VNC Server

Here are the steps to provision a fresh Autonomous Transaction Processing Database ad connect to it via SQLcl.

Steps

Launch the Cloud Developer Image from the Console
Set up OCI CLI
Create Autonomous Transaction Processing Database using CLI
Download Wallet using CLI
Launch SQLcl and connect to Autonomous Database

1. Launch Cloud Developer Image

Log in to the Console. If you don't already have an ssh key pair, make sure you generate those firstby following the documentation.
There are several ways to launch an Oracle-provided Marketplace image. Here's one:

From the Console, choose Create a VM Instance
Click Change Image Source
Click Oracle Images, next to Platform Image
Check Oracle Cloud Developer Image and review and accept the terms and conditions
Click Select Image
Paste in your ssh public key
Click Create

2. Set up OCI CLI

Connect to your newly launched image from your local computer via ssh:

ssh -i <path to your ssh keys> opc@<IP address>

Once logged in, run oci setup config and follow the directions, providing the necessary OCIDs as described in the documentation on Required Keys and OCIDs.

$ oci setup config

Remember to upload your API key by following the instructions in the same documentation. If you accepted all the defaults during the oci client setup, the public key to upload is the output of this:

$ cat /home/opc/.oci/oci_api_key_public.pem

3. Create Autonomous Transaction Processing Database using the OCI CLI

A few of the next commands require the compartment-id as input so it's helpful to have a shorthand ready. Get its value and store it in an environment variable by calling the metadata service via oci-metadata

$ export C=`oci-metadata -g compartmentid --value-only`

Next, create the Autonomous Database. Be sure to provide your own admin password.

$ oci db autonomous-database create --compartment-id $C --db-name myadb --cpu-core-count 1 --data-storage-size-in-tbs 1 --admin-password "<YOUR PASSWORD>"

You should see output similar to:

{
  "data": {
    "compartment-id": "ocid1.tenancy.oc1..aaaaaalskdjflsdkjflsdjflsdkflsjdflksjjfqntfkzizeeikohha4oa", 
    "connection-strings": null, 
    "cpu-core-count": 1, 
    "data-storage-size-in-tbs": 1, 
    "db-name": "myadb", 
    "db-version": null, 
    "db-workload": "OLTP", 
    "defined-tags": {}, 
    "display-name": "autonomousdatabase20190511024732", 
    "freeform-tags": {}, 
    "id": "ocid1.autonomousdatabase.oc1.iad.abuwcljrgx2kosiudoisdufoidsufoidsufodsfkdkdd3zprxjzsouzq", 
    "license-model": "BRING_YOUR_OWN_LICENSE", 
    "lifecycle-details": null, 
    "lifecycle-state": "PROVISIONING", 
    "service-console-url": null, 
    "time-created": "2019-05-11T02:47:32.745000+00:00", 
    "used-data-storage-size-in-tbs": null
  }, 
  "etag": "a133c7fa"
}

Export the Database ID in an environment variable as that will come in handy later.

export DB_ID=`oci db autonomous-database list --compartment-id $C | jq -r '.data[] | select( ."db-name" == "myadb" ).id'`

Wait for the Database to be in AVAILABLE state. You can check the database state with the following command. Initially, this command will return PROVISIONING

oci db autonomous-database get --autonomous-database-id $DB_ID | jq -r '.data["lifecycle-state"]'
AVAILABLE

For me, it took about 6 minutes from for the database to be available after executing the create command.

4. Download Wallet using CLI

$ oci db autonomous-database generate-wallet --autonomous-database-id $DB_ID --password <YOUR PASSWORD> --file wallet.zip

Set TNS_ADMIN and extract wallet.zip

$ export TNS_ADMIN="`cat /etc/ld.so.conf.d/oracle-instantclient.conf`/network/admin"
$ sudo -E unzip ~/wallet.zip -d $TNS_ADMIN

5. Launch SQLcl and connect to Autonomous Database

Start SQLcl in /nolog mode and point it to the wallet.zip you downloaded earlier using the set cloudconfig command.

$ sql /nolog

SQLcl: Release 19.1 Production on Fri May 10 00:24:29 2019

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


SQL> set cloudconfig /home/opc/wallet.zip
Operation is successfully completed.
Operation is successfully completed.
Using temp directory:/tmp/oracle_cloud_config2842421108875448254

Connect to your Autonomous database with the admin. For the service name, use one of the entries in $TNS_ADMIN/tnsnames.ora. Each ADB is created with a high, medium and low service.

SQL> connect admin/<YOUR PASSWORD>@myadb_high
Connected.
SQL> select sysdate from dual;

SYSDATE  
---------
11-MAY-19

SQL>

Conclusion

The Oracle Linux-based Cloud Developer Image comes with wealth of developer tools pre-installed, reducing the time it takes to get started with Oracle Cloud and Autonomoud Database. In this blog post, I showed how you can provision an Autonomous Database and get connected to it in a matter of minutes. The fact that the Cloud Developer Image already has the important bits pre-installed, including OCI client tools an Oracle Instant Client, makes completing this task a breeze.

Join the discussion

Comments ( 2 )

Vish Friday, April 17, 2020

set cloudconfig /home/opc/wallet.zip does not work

SQL> set cloudconfig /home/opc/Downloads/Wallet_xxxxx.zip
SP2-0735: unknown SET option beginning "cloudconfi..."
Sergio Friday, April 17, 2020

@Vish - are you sure you are using sqlcl and not sqlplus? I just ran sqlcl without a valid wallet path, but I'm not getting the error you are seeing.

$ /opt/oracle/sqlcl/bin/sql /nolog

SQLcl: Release 19.4 Production on Fri Apr 17 15:55:22 2020

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

SQL> set cloudconfig foo
Invalid path specified: foo

SQL>

Recent Content

Oracle