Managing Autonomous Datawarehouse using oci-curl

oci-curl

oci-curl is a bash function provided in the documentation that makes it easy to get started with the REST APIs. You will need to complete a few setup operations before you can start calling it.

Start by copying the function code from the documentation into a shell script on your machine. I saved it into a file named oci-curl.sh, for example.

You will see the following section at the top of the file. You need to replace these four values with your own.

TODO: update these values to your own
  local tenancyId= "ocid1.tenancy.oc1..aaaaaaaaba3pv6wjqae5f15p2b2m2yt2j6rx32uzr4h25vqstifsfdsq";
  local authUserId= "ocid1.user.oc1..aaaaaaaatvwc5j6aqzjcaty5eqbb6qt2jvpkanghtgdaqedqw3rynjq";
  local keyFingerprint="20:3b:97:13:1c:5b:0d:d3:50:4e:c5:3a:34";
  local privateKeyPath="/Users/someuser/.oci/oci_api_key.pem";

To get all id that are used in the script notice the following instructions:

Tenancy ID

While connected to the OCI Console, navigate from the Menu – Governance and Administration > Administration > Tenancy Details

Tenancy Id

By selecting Copy the Tenancy ID gets copied to the Clipboard

You should use this value to assign to the tenancyID argument in the oci-curl script

User ID

While connected to the OCI Console, navigate from the Menu – Governance and Administration > Identity > Users

Select the user you want to use

User ID

By selecting Copy the User ID gets copied to the Clipboard

You should use this value to assign to the authUserId argument in the oci-curl script

Key Fingerprint

Next, we need to create an API signing key

On your desktop complete the following actions:

mkdir .oci
openssl genpkey -out ~/.oci/oci_api_key.pem -algorithm RSA -pkeyopt rsa_keygen_bits:2048
..........+++
......................................+++

openssl rsa -pubout -in ~/.oci/oci_api_key.pem -out ~/.oci/oci_api_key_public.pem
writing RSA key
ls -al ~/.oci
total 16
drwxr-xr-x 4 rzoeteweij staff 128 Dec 10 13:48 .
drwxr-xr-x+ 34 rzoeteweij staff 1088 Dec 10 13:47 ..
-rw-r--r-- 1 rzoeteweij staff 1708 Dec 10 13:47 oci_api_key.pem
-rw-r--r-- 1 rzoeteweij staff 451 Dec 10 13:48 oci_api_key_public.pem

To support the authentication during the execution of API calls we need to upload the public key file.

For this we again select the user that is going to execute the API calls and select API Keys

API Key

Select API Keys and press [Add Public Key]

Cut and paste the contents of oci_api_key_public.pem file and press [Add]

After the addition of the Public Key is finalised the User Fingerprint is shown

Fingerprint

We can now copy this Fingerprint and use it to assign to the keyFingerprint argument in the oci-curl script

Finally, we need to set the Private Key Path. Within the oci-curl script set the following:

local privateKeyPath="/Users/<Your username>/.oci/oci_api_key.pem";

Create an ADW instance using the oci-curl script

We first create a Jason file to supply the ADW details, in this example we call the file request.json

{
  "compartmentId" : "ocid1.tenancy.oc1..aaaaaaaaro2vctz2hlgq77hguo6jzcs6ezyheouqfsalx3nubpwr2a",
  "dbName" : "adwdb1",
  "displayName" : "adwdb1",
  "adminPassword" : "WelcomePMADWC18",
  "cpuCoreCount" : 1,
  "dataStorageSizeInTBs" : 1,
  "licenseModel" : "LICENSE_INCLUDED"
}

We can now use this Jason file to create the ADW instance as follows:

. ./oci-curl.sh

oci-curl database.us-phoenix-1.oraclecloud.com post ./request.json "/20160918/autonomousDataWarehouses"

Notice that we source the oci-curl script so the environment is fully prepared to finally call the CreateAutonomousDataWarehouse REST API to create the database

Our example uses us-phoenix-1 as data center, replace this with the data center specification as needed.

Also notice that in the request.json file we use argument compartmentId to identify the Compartment the database will be created in

We get the compartmentId as follows

While connected to the OCI Console, navigate from the Menu – Governance and Administration > Identity > Compartments

Select the Compartment the database needs to be created

Compartment ID

By selecting Copy the Compartment ID gets copied to the Clipboard

. ./oci-curl.sh

oci-curl database.us-ashburn-1.oraclecloud.com post /Users/rzoeteweij/oci/request.json "/20160918/autonomousDataWarehouses"
{
"additionalDatabaseStatus" : null,
"compartmentId" : "ocid1.tenancy.oc1..aaaaaaaakuvh37w2btnqlu4hf6a4zgfx4mwgqwlrx4vuepp64cyv7gfq",
"connectionStrings" : null,
"cpuCoreCount" : 1,
"dataStorageSizeInTBs" : 1,
"dbName" : "adwdb1ROBZTW",
"dbVersion" : null,
"definedTags" : { },
"displayName" : "adwdb1 - Rob Zoeteweij",
"freeformTags" : { },
"id" : "ocid1.autonomousdwdatabase.oc1.iad.abuwcljrdd6nx5myspn354ecn7m4kkyduepu6alrrwf6ns4suyeat6dq",
"licenseModel" : "LICENSE_INCLUDED",
"lifecycleDetails" : null,
"lifecycleState" : "PROVISIONING",
"serviceConsoleUrl" : null,
"timeCreated" : "2018-12-11T09:03:53.395Z"
}

At first, we will see the Database lifecycle shown as “Provisioning”

Provisioning

After some time, it will change to “Available”

Available

List ADW instances

Using the oci-curl script we could also ask for a list of ADW instances, as the following example shows:

. ./oci-curl.sh

oci-curl database.us-ashburn-1.oraclecloud.com get "/20160918/autonomousDataWarehouses?compartmentId=ocid1.tenancy.oc1..aaaaaakteuvh37w2h2btnqlu4hf6a4zgfx4mwgqwlrx4vuepcyv7gfq"
[{"additionalDatabaseStatus":null,"compartmentId":"ocid1.tenancy.oc1..aaaaaaaakteuvh37w2h2btnqlu4hf6a4zgfxqwlrx4vue4cyv7gfq","connectionStrings":{"allConnectionStrings":null,"high":"adb.us-ashburn-1.oraclecloud.com:1522/kbrq5ikoxqxy_adwdb1robztw_high.adwc.oraclecloud.com","low":"adb.us-ashburn-1.oraclecloud.com:1522/kbrq5ikjoy4z_adwdb1robztw_low.adwc.oraclecloud.com","medium":"adb.us-ashburn-1.oraclecloud.com:1522/kbrqkjoxqy4z_adwdb1robztw_medium.adwc.oraclecloud.com"},"cpuCoreCount":1,"dataStorageSizeInTBs":1,"dbName":"adwdb1ROBZTW","dbVersion":"18.0.3.3","definedTags":{},"displayName":"adwdb1 - Rob Zoeteweij","freeformTags":{},"id":"ocid1.autonomousdwdatabase.oc1.iad.abuwcljrbbxbdd6nx5myspn354ecn7m4kkydu6alrwf6ns4suyeat6dq","licenseModel":"LICENSE_INCLUDED","lifecycleDetails":null,"lifecycleState":"AVAILABLE","serviceConsoleUrl":"https://adb.us-ashburn-1.oraclecloud.com/console/index.html?tenant_name=OCID1.TENANCY.OC1..AAAAAAAAKTEUVH37H2BTNQLU4HF6A4ZGFX4MWGQWX4VUEPP64CYGFQ&database_name=ADWDB1ROBZTW&service_type=ADW","timeCreated":"2018-12-11T09:03:53.395Z"},{"additionalDatabaseStatus":null,"compartmentId":"ocid1.tenancy.oc1..aaaaaaaakteuvhw2h2btnqlu4hf6a4zmwgqwlrx4vuepp64cyv7gfq","connectionStrings":{"allConnectionStrings":{"HIGH":"adb.us-ashburn-1.oraclecloud.com:1522/kbrq5ikjq_gpmigrds_high.adwc.oraclecloud.com","MEDIUM":"adb.us-ashburn-1.oraclecloud.com:1522/kbrq5ikz_gpmigrds_medium.adwc.oraclecloud.com","LOW":"adb.us-ashburn-1.oraclecloud.com:1522/kbrq5ikjqxypmigrds_low.adwc.oraclecloud.com"},"high":"adb.us-ashburn-1.oraclecloud.com:1522/kbrq5ikjqxy_gpmigrds_high.adwc.oraclecloud.com","low":"adb.us-ashburn-1.oraclecloud.com:1522/kbrq5iqxy4z_gpmigrds_low.adwc.oraclecloud.com","medium":"adb.us-ashburn-1.oraclecloud.com:1522/kbrq5ikjoxz_gpmigrds_medium.adwc.oraclecloud.com"},"cpuCoreCount":1,"dataStorageSizeInTBs":1,"dbName":"gpmigrds","dbVersion":"18.0.3.3","definedTags":{},"displayName":"gpmigrds","freeformTags":{},"id":"ocid1.autonomousdwdatabase.oc1.iad.abuwcljremopou5riehljbawjcozpk5vlxnx74vlzq22uo27bynzay7l3q","licenseModel":"BRING_YOUR_OWN_LICENSE","lifecycleDetails":null,"lifecycleState":"AVAILABLE","serviceConsoleUrl":"https://adb.us-ashburn-1.oraclecloud.com/console/index.html?tenant_name=OCID1.TENANCY.OC1..AAAAAAAAKTEUVH37W2H2BTNHF6A4ZGFX4MWGQWLRX4VUEPP64CYV7GFQ&database_name=GPMIGRDS&service_type=ADW","timeCreated":"2018-

In order to get the output in a more formatted way we could do the following:

. ./oci-curl.sh

oci-curl database.us-ashburn-1.oraclecloud.com get "/20160918/autonomousDataWarehouses?compartmentId=ocid1.tenancy.oc1..aaaaaaaakteuvh37w2h2btnqlu4hf6a4zgfx4mwgqwlrx4vuepp64cyv7gfq" | python -m json.tool
[
    {
        "additionalDatabaseStatus": null,
        "compartmentId": "ocid1.tenancy.oc1..aaaaaaeu37w2htnqlu4hf6a4zgfx4mwgqwlrx4vuepp64cyv7gfq",
        "connectionStrings": {
            "allConnectionStrings": {
                "HIGH": "adb.us-ashburn-1.oraclecloud.com:1522/kbrq5ikjqxy4z_adwdb1robztw_high.adwc.oraclecloud.com",
                "LOW": "adb.us-ashburn-1.oraclecloud.com:1522/kbrq5ikjqxy4z_adwdb1robztw_low.adwc.oraclecloud.com",
                "MEDIUM": "adb.us-ashburn-1.oraclecloud.com:1522/kbrq5ikjqxy4z_adwdb1robztw_medium.adwc.oraclecloud.com"
            },
            "high": "adb.us-ashburn-1.oraclecloud.com:1522/kbrq5ikjqxy4z_adwdb1robztw_high.adwc.oraclecloud.com",
            "low": "adb.us-ashburn-1.oraclecloud.com:1522/kbrq5ikjqxy4z_adwdb1robztw_low.adwc.oraclecloud.com",
            "medium": "adb.us-ashburn-1.oraclecloud.com:1522/kbrq5ikjqxy4z_adwdb1robztw_medium.adwc.oraclecloud.com"
        },
        "cpuCoreCount": 1,
        "dataStorageSizeInTBs": 1,
        "dbName": "adwdb1ROBZTW",
        "dbVersion": "18.0.3.3",
        "definedTags": {},
        "displayName": "adwdb1 - Rob Zoeteweij",
        "freeformTags": {},
        "id": "ocid1.autonomousdwdatabase.oc1.iad.abuwcljrbbxb6nx5myspn354ecn7m4kkyduepu6alrrwf6ns4suyeat6dq",
        "licenseModel": "LICENSE_INCLUDED",
        "lifecycleDetails": null,
        "lifecycleState": "AVAILABLE",
        "serviceConsoleUrl": "https://adb.us-ashburn-1.oraclecloud.com/console/index.html?tenant_name=OCID1.TENANCY.OC1..AAAAAAAAKTEUVH37W2H2BTNQLU4HF6A4ZGFX4MWGQWLRX4VUEPP64CYV7GFQ&database_name=ADWDB1ROBZTW&service_type=ADW",
        "timeCreated": "2018-12-11T09:03:53.395Z"
    },
    {
        "additionalDatabaseStatus": null,
        "compartmentId": "ocid1.tenancy.oc1..aaaaaaaakteuvh32btnqlu4hf6a4zgfx4mwgqwlrx4vup64cyv7gfq",
        "connectionStrings": {
            "allConnectionStrings": {
                "HIGH": "adb.us-ashburn-1.oraclecloud.com:1522/kbrq5ikjqxy4z_gpmigrds_high.adwc.oraclecloud.com",
                "LOW": "adb.us-ashburn-1.oraclecloud.com:1522/kbrq5ikjqxy4z_gpmigrds_low.adwc.oraclecloud.com",
                "MEDIUM": "adb.us-ashburn-1.oraclecloud.com:1522/kbrq5ikjqxy4z_gpmigrds_medium.adwc.oraclecloud.com"
            },
            "high": "adb.us-ashburn-1.oraclecloud.com:1522/kbrq5ikjqxy4z_gpmigrds_high.adwc.oraclecloud.com",
            "low": "adb.us-ashburn-1.oraclecloud.com:1522/kbrq5ikjqxy4z_gpmigrds_low.adwc.oraclecloud.com",
            "medium": "adb.us-ashburn-1.oraclecloud.com:1522/kbrq5ikjqxy4z_gpmigrds_medium.adwc.oraclecloud.com"
        },
        "cpuCoreCount": 1,
        "dataStorageSizeInTBs": 1,
        "dbName": "gpmigrds",
        "dbVersion": "18.0.3.3",
        "definedTags": {},
        "displayName": "gpmigrds",
        "freeformTags": {},
        "id": "ocid1.autonomousdwdatabase.oc1.iad.abuwcljremopou5riehljbawjcpjmk5vlxnx74vlzq27bynzay7l3q",
        "licenseModel": "BRING_YOUR_OWN_LICENSE",
        "lifecycleDetails": null,
        "lifecycleState": "AVAILABLE",
        "serviceConsoleUrl": "https://adb.us-ashburn-1.oraclecloud.com/console/index.html?tenant_name=OCID1.TENANCY.OC1..AAAAAAAAKTEUVHBTNQLU4HF6A4ZGFX4MWGQWLRX4VUEPP64CYV7GFQ&database_name=GPMIGRDS&service_type=ADW",
        "timeCreated": "2018-11-28T14:21:09.070Z"
    }
]

Get Autonomous Database details

. ./oci-curl.sh

oci-curl database.us-ashburn-1.oraclecloud.com get "/20160918/autonomousDataWarehouses/ocid1.autonomousdwdatabase.oc1.iad.abuwcljrbbxbdd6nx5myspn354ecn7m4kkyduepu6alrrwf6ns4suyeat6dq"
{
"additionalDatabaseStatus" : null,
"compartmentId" : "ocid1.tenancy.oc1..aaaaaaaakteuvhw2htnqlu4hf6a4zgfx4mwgqwlrx4vuepp64cyv7gfq",
"connectionStrings" : {
"allConnectionStrings" : {
"HIGH" : "adb.us-ashburn-1.oraclecloud.com:1522/kbrq5ikxy4z_adwdb1robztw_high.adwc.oraclecloud.com",
"MEDIUM" : "adb.us-ashburn-1.oraclecloud.com:1522/kbrq5iqxy4z_adwdb1robztw_medium.adwc.oraclecloud.com",
"LOW" : "adb.us-ashburn-1.oraclecloud.com:1522/kbrq5xqxy4z_adwdb1robztw_low.adwc.oraclecloud.com"
},
"high" : "adb.us-ashburn-1.oraclecloud.com:1522/kbrq5ikxq4z_adwdb1robztw_high.adwc.oraclecloud.com",
"low" : "adb.us-ashburn-1.oraclecloud.com:1522/kbrq5ikxq4z_adwdb1robztw_low.adwc.oraclecloud.com",
"medium" : "adb.us-ashburn-1.oraclecloud.com:1522/kbrq5iqxy4z_adwdb1robztw_medium.adwc.oraclecloud.com"
},
"cpuCoreCount" : 1,
"dataStorageSizeInTBs" : 1,
"dbName" : "adwdb1ROBZTW",
"dbVersion" : "18.0.3.3",
"definedTags" : { },
"displayName" : "adwdb1 - Rob Zoeteweij",
"freeformTags" : { },
"id" : "ocid1.autonomousdwdatabase.oc1.iad.abuwcljrbbxdx5myspn354ecn7m4kkyduepu6alrrwf6ns4suat6dq",
"licenseModel" : "LICENSE_INCLUDED",
"lifecycleDetails" : null,
"lifecycleState" : "AVAILABLE",
"serviceConsoleUrl" : "https://adb.us-ashburn-1.oraclecloud.com/console/index.html?tenant_name=OCID1.TENANCY.OC1..AAAAAAAAKTEUVH37BTNQLU4HF6A4ZGFX4MWGQWLRX4VUEPP64CYV7GFQ&database_name=ADWDB1ROBZTW&service_type=ADW",
"timeCreated" : "2018-12-11T09:03:53.395Z"
}

Credits
https://blogs.oracle.com/datawarehousing/managing-autonomous-data-warehouse-using-oci-curl

Disclaimer
Please notice that all statements made by me and information provided on this blog are mine and not necessarily those of Oracle Corporation.

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Google photo

You are commenting using your Google account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s