X

Break New Ground

Sign up for Developer Newsletter

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

  1. Launch the Cloud Developer Image from the Console
  2. Set up OCI CLI
  3. Create Autonomous Transaction Processing Database using CLI
  4. Download Wallet using CLI
  5. 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:

  1. From the Console, choose Create a VM Instance
  2. Click Change Image Source
  3. Click Oracle Images, next to Platform Image
  4. Check Oracle Cloud Developer Image and review and accept the terms and conditions
  5. Click Select Image
  6. Paste in your ssh public key
  7. 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>
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.

Recent Content