An Oracle Autonomous Database workshop: here is what I learnt

Pop Quiznos

Oracle Cloud Free Tier

Always Free

On Databases

Credits can be used on many items (see above). And that includes various databases. In the workshop you get to provision an Autonomous Data Warehouse (we’re still Always Free).  

The Autonomous Database (ADB)

Overview

The ADB calls OCI home. It’s a fully managed, pre-configured database environment. Four workload types are available:

• Autonomous Transaction Processing
• Autonomous Data Warehouse
• Autonomous JSON Database
• Oracle APEX Application Development

NOTE: Always Free entitles you to two of the above databases; each with 1 OCPU* and 20 GB storage. There is also an option to choose a noSQL Database with 133 million reads/writes per month, 25 GB storage per table (up to 3 tables).

ALSO: An “OCPU” is an Oracle Compute Unit. 1 OCPU on the x86 (AMD/Intel) architecture is equivalent to 2 virtual processor cores (vCPUs). Whereas on the ARM architecture, the ratio is 1:1 (i.e., 1 OCPU = 1 vCPU).

Automation

Zero hardware configuration/management or software installation/configuration is required! Once your database is created, nearly everything is automated:  

• Backups
• Patching
• Upgrading
• Performance tuning

You’re a Database Administrator (DBA) and you didn’t even know it!

Scaling

The ADB shines here. CPU and storage can be scaled automatically. Auto-scaling can automatically add more CPU cores to the base number of cores during periods of high demand, and then automatically reduce the number of cores back to the base number as demand decreases (auto-scaling also allows up to three times the current base number of CPU cores at any time).

To the Workshop!

The FREE workshop consists of five labs (and an optional sixth):

• Lab 1: Provision an Autonomous Database
• Lab 2: Work with Free Sample Data Sets
• Lab 3: Load Data
• Lab 4: Query External Data
• Lab 5: Visualize Your Data

Once you’ve requested an Oracle Cloud account (everything can be done with the Free Tier option), you’ll then create an Autonomous Database. The instructions for requesting your account are simple and can be found here (but also on the workshop’s landing page).

Lab 1

Just two steps here, simple. Everything is done through the Oracle Cloud dashboard. My two takeaways:

1. WRITE DOWN all your login/credential information! I hadn’t considered that as an Oracle employee I’d have more than one login (it can be confusing). Not to mention later, you’ll be creating an ADMIN password for the actual database (separate from your tenancy credentials).
2. Initial Database configuration. I really appreciated that when you first configure your Autonomous Database, you are automatically provided 1 CPU and 20 GB of storage. But should you require more CPU or storage, everything can be adjusted with simple drop-down menus. Is nice that a user (like a developer or non-System/Database Administrator role) can provision their own free playground in a matter of minutes.

Lab 2

1. Things seemed fast. And that is saying something, because for part of this workshop (Lab 3 actually), I was in the air flying from Denver, Colorado to Raleigh, North Carolina using the airline wi-fi and objects were actually loading into their OCI buckets (I write about this later).
2. Second takeaway was caching. When possible, the autonomous database caches query results. Meaning, for subsequent queries, results are just…there. That is a big deal to me, because in my previous role I was a Product Manager for an application performance tool. And there are a couple of things that just absolutely kill an application’s response time and/or throughput:

• CPU intensive tasks (often repetitive ones)
• I/O processes

TL;DR – we like fast. Fast is good 🙂

Lab 3

I should remind that this might not be your typical workflow. You might not create a Bucket and then load Objects every time. But you will still need a URI, authorization token, and credentials to load this data into the specified database.

This lab was still a good exercise even for the non-Systems or Database Administrator. Maybe you’re a Developer or Business Intelligence Analyst. I don’t suspect many will perform all these tasks. But its important to understand how teams/roles/individual members are connected (from end-to-end).

Lab 4

Up until this point I felt like I was an active participant. But here I felt like I was cheating. he code snippets you are provided allow you to create an external table directly from your cloud object store (excerpt of the code; click the image to go to our GitHub). 

/* Specify the URL that you copied from your files in OCI Object Storage in the define base_URL line below*/

set define on
/* Specify the URL that you copied from your files in OCI Object Storage in the define base_URL line below*/
 
set define on
define &file_uri_base = '<bucket URI>'
 
begin
 dbms_cloud.create_external_table(
    table_name =>'CHANNELS_EXT',
    credential_name =>'OBJ_STORE_CRED',
    file_uri_list =>'&file_uri_base/chan_v3.dat',
    format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true'),
    column_list => 'CHANNEL_ID NUMBER,
        CHANNEL_DESC VARCHAR2(20),
        CHANNEL_CLASS VARCHAR2(20),
        CHANNEL_CLASS_ID NUMBER,
        CHANNEL_TOTAL VARCHAR2(13),
        CHANNEL_TOTAL_ID NUMBER'
 );
 
 dbms_cloud.create_external_table(
    table_name =>'COUNTRIES_EXT',
    credential_name =>'OBJ_STORE_CRED',
    file_uri_list =>'&file_uri_base/coun_v3.dat',
    format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true'),
    column_list => 'COUNTRY_ID NUMBER ,
        COUNTRY_ISO_CODE CHAR(2) ,
        COUNTRY_NAME VARCHAR2(40) ,
        COUNTRY_SUBREGION VARCHAR2(30) ,
        COUNTRY_SUBREGION_ID NUMBER ,
        COUNTRY_REGION VARCHAR2(20) ,
        COUNTRY_REGION_ID NUMBER ,
        COUNTRY_TOTAL VARCHAR2(11) ,
        COUNTRY_TOTAL_ID NUMBER ,
        COUNTRY_NAME_HIST VARCHAR2(40)'
 );

The code snippets you are provided allow you to create an external table directly from your cloud object store. Now you can then query from that external table, outside of the database. Without that code snippet, I would have struggled to do this on my own (or at least, not without some research). That is more of a “me” problem though.

I appreciate that this method of querying is available, especially if you are only concerned with a small subset of your data. Keeping this data closer to you can certainly make subsequent requests quicker.

Lab 5

Here we try our hand at business intelligence. You begin by downloading Oracle Analytics Desktop. The tool reminded me of Tableau or one of the SAS data visualization products that are available. 

Consider this: Biases aside, Oracle Analytics Desktop is free. And you can connect to your database within a matter of minutes. 

If its forecasting and visualizations you are looking for, this thing looks plenty powerful.

Much like before, everything is GUI-based. But you can also do much of the set-up in Oracle’s SQL Developer, or from within the browser. With some provided SQL, I defined a table from the data I imported in a previous Lab. From there you get some practice establishing a secure connection to your database, and then import that table into Oracle Analytics Desktop.

The End

There is a totally optional Lab 6, where you export your newly created project to a .DVA file. But I skimmed that and got the idea.

Like I mentioned before, this content will become increasingly more technical. I’ll be doing some exploring with the APIs in ORDS. And I’ll see what I can do with flask + python + ORDS. I’ve found some good references on Github and of course I’ll be share the [heavily commented] code here and on Github too!

And as always, you can find me here:

• Twitter
• LinkedIn
• YouTube
• GitHub
• Discord
• Stack Overflow
• Medium

Until next time. Love, peace, and chicken grease.