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
- OCI-CLI installed and configured
- SQLcl installed
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.
|
1 |
oci iam compartment list |
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.
|
1 |
--query "data[]" |
I’d like to retrieve only the object with a name of ‘Demo’.
|
1 |
--query "data[?name=='Demo']" |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
[ { "compartment-id": "ocid1.tenancy.oc1..aaaaaaaauqbiglongguid", "defined-tags": {}, "description": "For Demonstrations", "freeform-tags": {}, "id": "ocid1.compartment.oc1..aaaaaaaa7a6biglongguid", "inactive-status": null, "is-accessible": null, "lifecycle-state": "ACTIVE", "name": "Demo", "time-created": "2019-09-26T21:14:01.870000+00:00" } ] |
Now that I have the full object, I can get the id value.
|
1 |
--query "data[?name=='Demo'].id" |
|
1 2 3 |
[ "ocid1.compartment.oc1..aaaaaaaa7a6biglongguid" ] |
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.
|
1 |
--query "data[?name=='Demo'].id | [0]" |
|
1 |
"ocid1.compartment.oc1..aaaaaaaa7a6biglongguid" |
Using the --raw-output parameter, I can get the raw value without the double quotes.
|
1 |
oci iam compartment list --query "data[?name=='Demo'].id | [0]" --raw-output |
|
1 |
ocid1.compartment.oc1..aaaaaaaa7a6biglongguid |
I can use this command to set an environment variable.
|
1 |
export COMPARTMENT_ID=$(oci iam compartment list --query "data[?name=='Demo'].id | [0]" --raw-output) |
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:
|
1 2 3 4 |
oci iam compartment create \ --compartment-id ocid1.tenancy.oc1..aaaaaaaauqbiglongguid \ --name "Demo" \ --description "For Demonstrations" |
Database
I can check to see if the database already exists by using a query similar to the one I used for compartments.
|
1 |
export DB_ID=$(oci db autonomous-database list -c $COMPARTMENT_ID --query "data[?\"db-name\"=='demo'].id | [0]" --raw-output) |
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.
|
1 2 3 4 5 6 7 8 9 |
oci db autonomous-database create \ --compartment-id $COMPARTMENT_ID \ --cpu-core-count 1 \ --data-storage-size-in-tbs 1 \ --db-name "$DB_NAME" \ --display-name "$DB_DISPLAY_NAME" \ --db-workload "OLTP" \ --admin-password "T3stertester" \ --is-free-tier True |
I’ll add the query and raw-output parameters to extract the ID and assign it to an environment variable.
|
1 2 3 4 5 6 7 8 9 10 |
export DB_ID=$(oci db autonomous-database create \ --compartment-id $COMPARTMENT_ID \ --cpu-core-count 1 \ --data-storage-size-in-tbs 1 \ --db-name "$DB_NAME" \ --display-name "$DB_DISPLAY_NAME" \ --db-workload "OLTP" \ --admin-password "T3stertester" \ --is-free-tier True \ --query "data.id" --raw-output) |
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.
|
1 |
oci db autonomous-database generate-wallet --autonomous-database-id $DB_ID --password Pw4ZipFile --file /home/bcarter/wallets/Wallet_demo.zip |
|
1 |
Downloading file [####################################] 100% |
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.
|
1 2 3 4 5 |
sql /nolog set cloudconfig $PRESENTATION_DIRECTORY/Active/LiquibaseAlwaysFree/code/wallet/Wallet_demo.zip connect admin/T3stertester@demo_TP select 'Yes! I connected to my Always Free ATP database!' did_it_work from dual; exit; |
Use a Shell Script to Automate
The following is an example of a setup script I use for my demos.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
#!/bin/bash # Set Variables COMPARTMENT_NAME="Demo" DB_NAME="demo" DB_DISPLAY_NAME="DemoDb" DB_PW="T3stertester" WALLET_PW="Pw4ZipFile" WALLET_ZIP="/home/bcarter/tempWallet/Wallet_${DB_NAME}.zip" # Create an Free-Tier Autonomous Database create_db() { DB_ID=$(oci db autonomous-database create \ --compartment-id ${COMPARTMENT_ID} \ --cpu-core-count 1 \ --data-storage-size-in-tbs 1 \ --db-name "${DB_NAME}" \ --display-name "${DB_DISPLAY_NAME}" \ --db-workload "OLTP" \ --admin-password "${DB_PW}" \ --is-free-tier True \ --wait-for-state AVAILABLE \ --query "data.id" --raw-output) } # Download the Wallet .zip file download_wallet() { oci db autonomous-database generate-wallet --autonomous-database-id ${DB_ID} --password ${WALLET_PW} --file ${WALLET_ZIP} } # Get the Compartment OCID COMPARTMENT_ID=$(oci iam compartment list --query "data[?name=='${COMPARTMENT_NAME}'].id | [0]" --raw-output) # Get the Database OCID DB_ID=$(oci db autonomous-database list -c ${COMPARTMENT_ID} --query "data[?\"db-name\"=='${DB_NAME}'].id | [0]" --raw-output) # If the Database does not exist ask to create it. if [[ -z "${DB_ID}" ]]; then echo "No ${DB_NAME} Database found." while true; do read -p "Do you wish to create the ${DB_NAME} Database? " yn case $yn in [Yy]* ) create_db; break;; [Nn]* ) exit;; * ) echo "Please answer y or n.";; esac done fi # Download the wallet download_wallet # Create an SQL script to test the connection echo "set cloudconfig ${WALLET_ZIP} connect admin/${DB_PW}@${DB_NAME}_TP select 'Yes! I connected to my Always Free ATP database!' did_it_work from dual; exit;" > testConnection.sql # Test the connection sql /nolog @testConnection.sql # Delete the test script rm testConnection.sql |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
@OraBlaineOS:OCI-CLI$ ./setup.sh Query returned empty result, no output to show. No demo Database found. Do you wish to create the demo Database? y Action completed. Waiting until the resource has entered state: ('AVAILABLE',) Downloading file [####################################] 100% SQLcl: Release 18.3 Production on Fri Apr 03 14:28:11 2020 Copyright (c) 1982, 2020, Oracle. All rights reserved. Operation is successfully completed. Operation is successfully completed. Using temp directory:/tmp/oracle_cloud_config3687856744011425249 Connected. DID_IT_WORK ------------------------------------------------ Yes! I connected to my Always Free ATP database! Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0 |
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.
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.
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.
Awesome. Appreciated. Thanks a ton for the explanation.