Category Archives: Autonomous

Create an Always Free Autonomous Database on the Oracle Cloud

You’ve signed up for the Oracle Cloud Free Tier at https://www.oracle.com/cloud/free/ and now you want to create an Always Free Autonomous Database.

Create a new Database

These instructions should work for both (ATP) Autonomous Transaction Processing and (ADW) Autonomous Data Warehouse Databases.

After you log into your Oracle Cloud Dashboard.

  1. Click on the “Create an ATP database” box. (Or the “Create an ADW database” box if you want a data warehouse)
  2. Choose the compartment you want to use.
  3. Fill in the “Display Name” field.  This name will be displayed in the GUI lists and drop down selection boxes in your Oracle Cloud dashboard.  You can change this later.
  4. Fill in the “Database Name” field.  This name will be used as the permanent ID for this database.  It will show up in places such as the auto-generated TNSNAMES.ORA file that you’ll download in another step.  You can not change this later, so choose wisely.
  5. In the “Choose a workload type” section, make sure the correct workload type is selected..
  6. Since we want to create an Always Free Database, leave “Shared Infrastructure” selected in the “Choose a deployment type” section.
  7. Make sure the “Always Free” selector is turned on.  This will lock in the options for an Always Free instance.  (20 GB of storage and 1 OCPU)
  8. Scroll down to the “Create administrator credentials” section and enter a Strong password for your Admin account.
    You will use the Admin account to connect to your Autonomous Database instead of SYS or System.
  9. Under the “Choose a license type” section make sure “License Included” is selected.
  10. Click the “Create Autonomous Database” button.

Your new ATP instance will be provisioned.  The orange box will turn green once it has been provisioned and is available to use.

While you’re waiting, check that you see the Always Free tag next to your database name.  If you do not see the tag, you may want to check to see what license you created the instance under and make sure you are OK with those options or terminate the instance and create a new one with the Always Free options selected.

Once the orange box turns green and your database is available, you will want to connect to it.

The Oracle Autonomous Cloud Databases are setup with a little extra security.  In order to connect you will need to use a wallet credentials file.

Download the Oracle Credentials file

There are a couple different ways to get the wallet file through the Cloud Dashboard.

From the Details page
    1. Click on the “DB Connection” button.
    2. On this form you will find a list of the auto-generated TNSNAMES entries and connection strings that you can use to connect to your database.  There is a link to the documentation explaining the difference between the entries.  For typical transactions I like to use the one ending in _TP.
    3. Click the “Download Wallet” button.
    4. Create a password that will be used to access the .zip file.
    5. Click the “Download” button.

      Remember the location where you save your wallet file, you will need it when we test the connection.
    6. Save the .zip file to a secure location.  This file is used to access your database so keep it secure.
From the Service Console

You can get to the Service Console for your Database instance either from the Details page

or from the menu for the instance in the Autonomous Transaction Processing Database list.

Once your in the service Console

  1. Click on “Administration”.
  2. Click on “Download Client Credentials (Wallet)”.
  3. Create a password that will be used to access the .zip file.
  4. Click the “Download” button.
Important Wallet Security Information

All wallets downloaded with either method will create a copy of the *SAME WALLET* even if you use different passwords for the .zip file.

You can rotate the wallet from your database instance details page.  Read the docs.

  1. Click the “DB Connection” button.
  2. Press the “Rotate Wallet” button and follow the instructions.

Connect to the Database

SQLcl is a great tool for making a quick and easy connection to the new database.

  1. Start SQLcl in ‘nolog’ mode
  2. Set the location of your .zip file in the cloudconfig variable.
  3. Connect to your database using the password you set for the Admin account.
  4. Run a test query

Enjoy

Your new Always Free Oracle Autonomous Database is running and you are able to connect.  Now you can start building your application.

If you have an questions please post them in the comments and I’ll do my best to help you find an answer.

How To Connect your Node.js app in Docker to your Oracle Database

In this post I’ll cover how to make a connection from a Node.js application in a Docker container to your Oracle Database in three different scenarios.

  • Typical Database connection.
  • Database inside a different Docker container.
  • Oracle Autonomous Transaction Processing Cloud Database.

Simple Test App

When attempting something new, I find it’s best to make sure all of the pieces that are not part of the new thing, are as simple as possible.

For my examples I will be using a small Node.js application that:

  • Connects to the Oracle Database.
  • Runs a simple query.
  • Prints the results to the console.

Create a new file named dbConfig.js to set the database connection information.

Create a new file named server.js used to test the connection.

Install the node-oracledb driver. (If you have any questions about the node-oracledb driver, you can find the answers here.)

Create environment variables for the connection information.  (Replace my values with yours)

Test the Node.js application.  You should see something similar to the following.

Create a Docker Container for the Node.js app

Create a Dockerfile

Build the image

Now that you have an image you’ll run a container and to connect it to:

Typical Oracle Database
The container will run, execute the query and stop.  You should see something similar to this output.
An Oracle Database inside a Docker Container

In a previous post I walked through how to setup both an ORDS instance and an Oracle XE instance in Docker Containers.  Follow through the section showing how to create an Oracle XE Database in a Docker container.

When you followed the steps in that post you should have also created a Docker Network.

You’ll use that same network to connect from the Node.js container to the Oracle XE container.

First, change your NODE_ORACLEDB_CONNECTIONSTRING environment variable to use the Oracle XE container name.

Now when you run a new docker container you will attach it to the same Docker Network as the Oracle XE container.

The container will run, execute the query and stop.  You should see something similar to this output.

Since both containers are using the same Docker network, you do not need to open the port when you run the Oracle XE container.  This is useful if you’d like to keep your database private inside the Docker environment.

Oracle Autonomous Transaction Processing Cloud Database

After you create an Oracle ATP Cloud Database you’ll need to download the credentials file and extract it into a secure location.

For this database, you will be using an entry in the tnsnames.ora file to make the database connection.  Change the NODE_ORACLEDB_CONNECTIONSTRING environment variable to use the tnsnames entry:

Before you run the container, you need to modify the sqlnet.ora file found in the credentials directory.  Change the wallet location to the value that will be mapped inside the Docker container.

When you run the new container for the node app you will map a volume from the directory where you extracted your credentials file to the internal container directory.  Mapping a volume makes sure that there is not a copy of the credential files inside the Docker container.

Finally, when you run the new container, you will add an additional environment variable defining where the tns admin information is located.

The full command looks like this:

The container will run, execute the query and stop.  You should see something similar to this output.

I used the same docker image for all of the examples

You may have noticed that you only built the Docker image once at the beginning of the post.  The application in the Docker image uses environment variables to connect to the database.  This allows you to run the same application in one or more containers and each container can connect to different Oracle Databases without having to rebuild the image.

Oracle REST Data Services on Autonomous Transaction Processing Database

Whenever I give a presentation, write a blog post or make a video I like to run all of my examples and demos locally and on an Oracle Cloud instance.  I keep a Virtual Box machine or Docker Container ready on my laptop, just in case I can’t get to the internet, but for speed and dependability, you just can’t beat a cloud instance.

I like to have some automation scripts handy to create and destroy my environments and depending on what I’m doing that can sometimes take a while.

So when Oracle’s Autonomous Transaction Processing Database was released I was excited by how fast I could spin up a new instance.  It takes a few minutes instead of around an hour for other options.  Oracle wanted to make sure ATP was solid, so they initially launched with a limited feature set and new features have been making their way into Oracle’s Autonomous Transaction Processing Database almost every month.

At KScope19 it was announced that Oracle REST Data Services is now available on ATP.

I was scheduled to give my ‘Creating REST APIs Is Fast, Easy, and Secure with Oracle REST Data Services‘ talk on Wednesday and I like to live dangerously, so I decide (Tuesday afternoon) to convert my demos over to ORDS on ATP.  I figured worst case, if I couldn’t figure it out I’d just use my Docker container.

It’s going to be hard… right?

I already had a couple of ATP instances that I use for stuff and things.  Since I wanted to do a live demo, I decided to spin up a new instance just in case I expose anything that could be used to get in.  It took under 5 minutes to fully create and start up a brand new ATP database.  After that, I downloaded my Client Credentials (Wallet) and connected with SQL Developer.  There are lots of examples for this part so I’ll move on to the new stuff.

New User

I try to do as little as possible as the Admin user so I created a rest_demo user with a couple of tables and a view.

Create a test API

Then I used the SQL Developer ‘Auto REST’ feature to REST enable the schema and a table.   (PL/SQL exported below in case you want to try it.)

Get the URI

Now I just needed the URI to test my new service.  Here are the steps:

  1. Log into your Oracle Cloud Dashboard.
  2. From the menu, select ‘Autonomous Transaction Processing’.
  3. From the instance menu, select ‘Service Console’.

    Or, Click on your Instance Name then Click the ‘Service Console’ button.
  4. Click ‘Development’.
  5. In the Rest Data Services box, click the “Copy URL” button.
Test it

For a simple fast test, I just used an internet browser with the following URI:
https://someIdstuff-blaineords.adb.my-region-1.oraclecloudapps.com/ords/api/cool_data

Well, that was easy.

To be honest I expected this to be a lot harder and that I’d have problems.  I ran through all of the demos for my presentation and they just worked, first try.

The thing I spent the most time on was re-checking everything.  Since it was so easy, I thought I had connected to one of my pre-existing cloud databases.

Go try it!

As always, don’t take my word for it, go try it yourself.

Here are some links to help get you going: