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:
Here are the steps to provision a fresh Autonomous Transaction Processing Database ad connect to it via SQLcl.
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:
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
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.
$ 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
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>
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.
SQL> set cloudconfig /home/opc/Downloads/Wallet_xxxxx.zip
SP2-0735: unknown SET option beginning "cloudconfi..."
$ /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>