Create and use an Oracle Autonomous Cloud Database from the Command Line

In this post I’ll cover how to create an Oracle Autonomous Cloud Database download the Wallet credentials and connect to the database all from the command line using the OCI-CLI.

After that I’ll include an example of a shell script that I use to setup my Demo environment.

Prerequisites

Get the Compartment OCID

I like to keep all of my work compartmentalized so that I don’t run into conflicts between my (and potentially other people’s) projects.  This means I’ll need to get the OCID of the compartment I want to work in.

Rather than use the Web Console you can run this command to get a list of your available compartments.

Assuming that you already know which compartment you want to work with you can use the --query  parameter to retrieve the ID of that compartment.

The above command returns an array called data that I will use to query an object.

I’d like to retrieve only the object with a name of ‘Demo’.

Now that I have the full object, I can get the id value.

The list command will return all objects that match the query criteria in an array.  Even when there is only a single object it will be returned in an array.

Next, I pipe out the first (and only) value from the array.

Using the --raw-output  parameter, I can get the raw value without the double quotes.

I can use this command to set an environment variable.

The OCI-CLI query parameter uses the JMESPath query language.

Create Compartment

If the compartment doesn’t exist you can use OCI to create one.  For this command you will need the OCID of an existing compartment.  This will be the parent compartment.

If you want to use an existing compartment as a parent, you can use the above command to get the OCID.  Or, if you want to add the new compartment to the ROOT compartment, you can use the Tenancy OCID.

You can get the Tenancy OCID from:

  • Your OCI Config file cat ~/.oci/config.
  • The OCID of an existing compartments parent.
    This time I’m using the ?contains()  function to check compartment-id for the string ‘.tenancy.’.  Notice that the compartment-id must be double quoted because it contains a ‘-‘ and those double quotes are escaped.  \"compartment-id\" oci iam compartment list --query "data[?contains(\"compartment-id\",'.tenancy.')].\"compartment-id\" | [0]" --raw-output

Once you have the parent compartment OCID the command to create a new compartment is:

Database

I can check to see if the database already exists by using a query similar to the one I used for compartments.

If the demo database doesn’t exist I can create a new Always-Free Autonomous Cloud Database with the OCI-CLI.

The data-storage-size-in-tbs is set to 1TB which is larger than the free tier supports.  Setting --is-free-tier True  will cause the system to automatically scale it to the correct size.

The default value for ‘is-free-tier’ is False, if you do not include this parameter you will create a standard Autonomous Cloud Database.  You should check the Cost Estimator to ensure that you’re OK with the cost.

Setting --db-workload "OLTP"  will create an Autonomous Transaction Processing database, using “DW” will create a Data Warehouse.

Make sure you use a strong --admin-password , this will be the admin password for the new database.

This command will return a JSON object with the properties of the new database.

I’ll add the query and raw-output parameters to extract the ID and assign it to an environment variable.

In order to connect to my new database I will need to

Download the Wallet

Wait for your database to be in an AVAILABLE state before attempting to download the wallet.

This command will download your wallet credentials in a .zip file, just like you’d get from the web console.

The $DB_ID variable was set above for the demo database.  The –file parameter accepts the location and file name where you want to download the .zip file.  The new file will have the password set by –password.

Now that everything is in place I can

Test the Connection

  • I’ll start SQLcl without making a connection (/nolog).
  • Set cloudconfig to the location of the wallet credentials.
  • Connect with the admin password and one of the service names contained in the tnsnames.ora file included in the wallet zip file.
    The predefined service names will be in the form of <name of the database>_<performance level> .  You can find more information here.
  • Run a test query.

Use a Shell Script to Automate

The following is an example of a setup script I use for my demos.

Notice that in the create database method I added a new parameter to the OCI call --wait-for-state AVAILABLE.  Since I won’t be able to download the wallet until the database is available, I use this parameter to pause at the create step until the new Database is fully up and running.

When I run the script I get

Explore

This is just a small taste of what you can do with the OCI-CLI.  Check out the documentation for a look at the possibilities.

Leave a comment if you have a question and I will do my best to find you an answer.

3 thoughts on “Create and use an Oracle Autonomous Cloud Database from the Command Line”

  1. I setup an Autonomous Database in Oracle Cloud with admin user. From there I go to “Tools” and open “SQL Developer Web”. SQL Worksheet came up.
    I created a simple table.
    >> Create table xx_test (id number, name varchar2(2000));

    Inserted a row.
    >>insert into xx_test values (1, ‘The Test User’);

    I was able to query it.
    >> select * from xx_test;

    I fired rollback command.
    >> rollback

    Output came:
    Rollback complete.
    Elapsed: 00:00:00.001

    When, I query the table again, I found rollback did not take place and record available in table. Even though I did not apply Commit;
    >> select * from xx_test;

    My quesiton is in Oracle Cloud Autonomous Database, commit and rollback has no role. Even without Commit a record permanently inserted in table and I can’t fire the rollback for that session.

    Kindly share your thoughts.

    1. Commit/Rollback both function the same on an Oracle Autonomous Database as any other Oracle Database.

      SQL Developer Web runs as a ‘stateless’ web application, it does not maintain an open connection. So, basically it’s performing an auto-commit each time you execute a statement.

      Tools such as SQLcl or SQL Developer (installed), maintain a ‘statefull’ connection giving you control over commit/rollback.

Leave a Reply