Oracle Database ADB

OCI360 works over Oracle Autonomous Database (even the Always Free option). This section will show how to prepare the ADB for OCI360.

Creating a Object Storage Bucket

First, you will need to have a bucket where your OCI exported JSON and CSV files will be kept.

You can easily do it via the OCI web-console. Instructions are available here: https://docs.cloud.oracle.com/en-us/iaas/Content/Object/Tasks/managingbuckets.htm#usingconsole

Or you can also do it using oci-cli:

 $ oci os bucket create --name oci360_bucket \
 --namespace-name idgimbpbaoa7 \
 --compartment-id ocid1.compartment.oc1..aaaaaaaaysvxrbvyht4goajycwzxiulxvrqygtmed3ugzbpamrydot6xjskq
 {
  "data": {
    "approximate-count": null,
    "approximate-size": null,
    "compartment-id": "ocid1.compartment.oc1..aaaaaaaaysvxrbvyht4goajycwzxiulxvrqygtmed3ugzbpamrydot6xjskq",
    "created-by": "ocid1.user.oc1..aaaaaaaax52wpgowsyg4og4lgtmxtndlhk24vyygszwvajxfgi4lewpfptmq",
    "defined-tags": {
      ...
    },
    "etag": "284e0ea9-906d-44c4-ae81-7de90130e989",
    "freeform-tags": {},
    "id": "ocid1.bucket.oc1.iad.aaaaaaaauyhynnuzlmqsxz4r6pbm4vlzwnpaykxrj2w5fbtxouwnos2nhofa",
    "is-read-only": false,
    "kms-key-id": null,
    "metadata": {},
    "name": "oci360_bucket",
    "namespace": "idgimbpbaoa7",
    "object-events-enabled": false,
    "object-lifecycle-policy-etag": null,
    "public-access-type": "NoPublicAccess",
    "replication-enabled": false,
    "storage-tier": "Standard",
    "time-created": "2020-05-20T14:04:10.729000+00:00"
  },
  "etag": "284e0ea9-906d-44c4-ae81-7de90130e989"
}

PS: If you don't know your namespace-name, you can get it running: oci os ns get

Creating a API user with access to the bucket

Next step is creating a new user account which will be used by your Autonomous Database to connect on the bucket and read the objects that you will upload.

For doing those steps below using the OCI webconsole, please refer to: https://docs.cloud.oracle.com/en-us/iaas/Content/Identity/Tasks/managingusers.htm#three

Create the User

$ oci iam user create --name oci360_user --description "Account used for OCI360"
{
  "data": {
    "capabilities": {
      ...
    },
    "compartment-id": "ocid1.tenancy.oc1..aaaaaaaaunn73emggesayznwlqeunvmbsmbtgzbigd67mtjwbu2doq44igna",
    "defined-tags": {
      ...
    },
    "description": "Account used for OCI360",
    "email": null,
    "external-identifier": null,
    "freeform-tags": {},
    "id": "ocid1.user.oc1..aaaaaaaabo3vcsiqj5qk2nxf6pewgiqgf3ty5j5csxsf2ppmfvkfta5fe2yq",
    "identity-provider-id": null,
    "inactive-status": null,
    "is-mfa-activated": false,
    "lifecycle-state": "ACTIVE",
    "name": "oci360_user",
    "time-created": "2020-05-20T13:55:23.535000+00:00"
  },
  "etag": "ad235e46aef7c703869f7e5ebe1068b52238e1c2"
}

Create the Group

Next, create a group and associate to this user.

$ oci iam group create --name oci360_group --description "Group used for OCI360"
{
  "data": {
    "compartment-id": "ocid1.tenancy.oc1..aaaaaaaaunn73emggesayznwlqeunvmbsmbtgzbigd67mtjwbu2doq44igna",
    "defined-tags": {
      ...
    },
    "description": "Group used for OCI360",
    "freeform-tags": {},
    "id": "ocid1.group.oc1..aaaaaaaazhppyhtdyqodhvd6364jsgkhzoxwaluvquomdw43lfltnco2nlyq",
    "inactive-status": null,
    "lifecycle-state": "ACTIVE",
    "name": "oci360_group",
    "time-created": "2020-05-20T14:00:09.031000+00:00"
  },
  "etag": "1e1be279494e685ae6d3f555d899c9c9dbbd3192"
}

Grant Group to User

Put the oci360_user inside the oci360_group:

$ oci iam group add-user \
--user-id ocid1.user.oc1..aaaaaaaabo3vcsiqj5qk2nxf6pewgiqgf3ty5j5csxsf2ppmfvkfta5fe2yq \
--group-id ocid1.group.oc1..aaaaaaaazhppyhtdyqodhvd6364jsgkhzoxwaluvquomdw43lfltnco2nlyq
{
  "data": {
    "compartment-id": "ocid1.tenancy.oc1..aaaaaaaaunn73emggesayznwlqeunvmbsmbtgzbigd67mtjwbu2doq44igna",
    "group-id": "ocid1.group.oc1..aaaaaaaazhppyhtdyqodhvd6364jsgkhzoxwaluvquomdw43lfltnco2nlyq",
    "id": "ocid1.groupmembership.oc1..aaaaaaaavhdj4oa3ph4emlcmvwip7z2q2iqcwccz7r62f5tccw2hopx5n33a",
    "inactive-status": null,
    "lifecycle-state": "ACTIVE",
    "time-created": "2020-05-20T14:01:57.493000+00:00",
    "user-id": "ocid1.user.oc1..aaaaaaaabo3vcsiqj5qk2nxf6pewgiqgf3ty5j5csxsf2ppmfvkfta5fe2yq"
  },
  "etag": "da67c5f6b1930e551d346c15e99db84085aac39e"
}

Create the Policy

Finally, let's create a policy allowing the oci360_user to read the objects on the oci360_bucket:

$ oci iam policy create \
--name oci360_policy \
--description "Policy used for OCI360" \
--compartment-id ocid1.tenancy.oc1..aaaaaaaaunn73emggesayznwlqeunvmbsmbtgzbigd67mtjwbu2doq44igna \
--statements "[\"allow group oci360_group to read objects in tenancy where target.bucket.name='oci360_bucket'\"]"
{
  "data": {
    "compartment-id": "ocid1.tenancy.oc1..aaaaaaaaunn73emggesayznwlqeunvmbsmbtgzbigd67mtjwbu2doq44igna",
    "defined-tags": {
      ...
    },
    "description": "Policy used for OCI360",
    "freeform-tags": {},
    "id": "ocid1.policy.oc1..aaaaaaaaciaavvpjuj2uwy2fbiqcp6ddoam7ip7b24mywjofgwahrlh2rp7a",
    "inactive-status": null,
    "lifecycle-state": "ACTIVE",
    "name": "oci360_policy",
    "statements": [
      "allow group oci360_group to read objects in tenancy where target.bucket.name='oci360_bucket'"
    ],
    "time-created": "2020-05-20T14:08:00.719000+00:00",
    "version-date": null
  },
  "etag": "b3ec721cb54221eadc52c0753903cd8f1bf77e4f"
}

Creating a Key-pair for ADB <> OCI API connection

Now that the account is prepared, you need to create a RSA key pair used to connect your ADB (using DBMS_CREDENTIAL) with the OCI.

First, create your private and public keys using the openssl:

$ openssl genrsa -out ./oci_api_key.pem 2048
Generating RSA private key, 2048 bit long modulus
..............+++
...........................................................................................+++
e is 65537 (0x10001)

$ openssl rsa -pubout -in ./oci_api_key.pem -out ./oci_api_key_public.pem
writing RSA key

Next, upload the generated PUBLIC KEY to you oci360_user:

$ oci iam user api-key upload \
--user-id ocid1.user.oc1..aaaaaaaabo3vcsiqj5qk2nxf6pewgiqgf3ty5j5csxsf2ppmfvkfta5fe2yq \
--key-file ./oci_api_key_public.pem
{
  "data": {
    "fingerprint": "ba:a2:5d:10:e9:61:25:57:ab:85:8d:d6:2b:df:c3:80",
    "inactive-status": null,
    "key-id": "ocid1.tenancy.oc1..aaaaaaaaunn73emggesayznwlqeunvmbsmbtgzbigd67mtjwbu2doq44igna/ocid1.user.oc1..aaaaaaaabo3vcsiqj5qk2nxf6pewgiqgf3ty5j5csxsf2ppmfvkfta5fe2yq/ba:a2:5d:10:e9:61:25:57:ab:85:8d:d6:2b:df:c3:80",
    "key-value": "-----BEGIN PUBLIC KEY-----\nMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAst8W/IMyT3ikzNRBe2gg\n871O9Gx1oqfXm0MdeKse6uDQMFz/y3TJ+7C48bqBLz9PQgqAI7j5D3tjvLKjAE9w\nUbrRgrosxg+rvY0e+A4RUa6gfMIZ5hPNDRMlebBeT+Dv+NyTYWMnIw9/DfldBVUp\nkssxui4DLPUWFj4wTwqy7/XdhWrJ+jceNcZsdYOkgnn0JfAlDC5jaxQEUU3wyUAq\n2LM67G0umu5wcsx9T3nGOSIVAq3p8FHhz5BnShAQSQaz4bhj1/1qctVC/sOZpItY\nJQBfvY9cmLi0QbN+vcDBS5PfKmN7Y3Btex5lVZXJmB4r6TpK1M1gvjPc5/xpMv6B\nWQIDAQAB\n-----END PUBLIC KEY-----",
    "lifecycle-state": "ACTIVE",
    "time-created": "2020-05-20T14:15:39.655000+00:00",
    "user-id": "ocid1.user.oc1..aaaaaaaabo3vcsiqj5qk2nxf6pewgiqgf3ty5j5csxsf2ppmfvkfta5fe2yq"
  }
}

That's it. Note down the information returned above as you will need it when creating your credential with DBMS_CLOUD.CREATE_CREDENTIAL.

Creating the ADB instance

The steps to create the ADB instance in OCI can be found here: https://docs.cloud.oracle.com/en-us/iaas/Content/Database/Tasks/adbcreating.htm