First impression of the Oracle Database Appliance (ODA) REST API

With the release 19.8 of the ODA software Oracle released the JAVA SDK and the REST API officially for managing the ODA. (https://docs.oracle.com/en/engineered-systems/oracle-database-appliance/19.8/odapi/index.html).


Before the 19.8 release the easiest way for automating the ODA was using ansible and execute the odacli commands in a shell task with the –json, -j ( json output) parameter for json output.

But with the current (19.8) release we do now have a REST API to automate some tasks on an ODA.

In this blog post I focus on the usage of these technologies with an ODA and not on the technical details of REST and json.

The easiest way to execute a REST call is to use curl or a browser Plugin. The following shows a curl command to get all databases on this ODA:

curl -u oda-admin:<PASSWORD> -k -X GET https://<ODA_FQDN>:7070/databases

This curl command will provide the following answer.

This is a json array with an element per database. In this case I do have two databases on this ODA. One database called SDK and one database called SDKClONE. As you can see you have a lot more information here as if you would receive if you execute the odacli list-databases command.

[
   {
      "updatedTime":1597912714315,
      "id":"a4765ca4-cea9-4abf-9d81-529e8b20a71a",
      "name":"SDK",
      "createTime":1597912083382,
      "state":{
         "status":"CONFIGURED"
      },
      "dbName":"SDK",
      "databaseUniqueName":"SDK",
      "dbVersion":"19.6.0.0.200114",
      "dbHomeId":"0b07ae45-a517-448d-bce9-7a1b1e6919b6",
      "dbId":"3356023092",
      "isCdb":false,
      "pdBName":null,
      "pdbAdminUserName":null,
      "enableTDE":false,
      "tdePassword":null,
      "isBcfgInSync":null,
      "dbType":"SI",
      "dbRole":"PRIMARY",
      "dbTargetNodeNumber":"0",
      "dbClass":"OLTP",
      "dbShape":"odb1",
      "dbStorage":"ACFS",
      "dbOnFlashStorage":null,
      "level0BackupDay":"sunday",
      "instanceOnly":false,
      "registerOnly":false,
      "rmanBkupPassword":null,
      "dbEdition":"EE",
      "dbDomainName":"<DB_DOMAIN>",
      "dbRedundancy":null,
      "enableSEHA":false,
      "dbTargetNodeName":"<HOSTNAME>",
      "associatedNetworks":[
         "Public-network:b9aebe9f-69fc-4ef8-b5f1-4cc2cc837e5f"
      ],
      "dbCharacterSet":{
         "characterSet":"AL32UTF8",
         "nlsCharacterset":"AL16UTF16",
         "dbTerritory":"AMERICA",
         "dbLanguage":"AMERICAN"
      },
      "dbConsoleEnable":false,
      "backupDestination":"NONE",
      "cloudStorageContainer":null,
      "backupConfigId":null,
      "isAutoBackupDisabled":false
   },
   {
      "updatedTime":1597913436637,
      "id":"1d0e1dd1-5612-4595-89b1-c16246033b0e",
      "name":"SDKCLONE",
      "createTime":1597912954604,
      "state":{
         "status":"CONFIGURED"
      },
      "dbName":"SDKCLONE",
      "databaseUniqueName":"SDKCLONE",
      "dbVersion":"19.6.0.0.200114",
      "dbHomeId":"0b07ae45-a517-448d-bce9-7a1b1e6919b6",
      "dbId":"988801031",
      "isCdb":false,
      "pdBName":null,
      "pdbAdminUserName":null,
      "enableTDE":false,
      "tdePassword":null,
      "isBcfgInSync":null,
      "dbType":"SI",
      "dbRole":null,
      "dbTargetNodeNumber":"0",
      "dbClass":"OLTP",
      "dbShape":"odb1",
      "dbStorage":"ACFS",
      "dbOnFlashStorage":null,
      "level0BackupDay":null,
      "instanceOnly":false,
      "registerOnly":false,
      "rmanBkupPassword":null,
      "dbEdition":"EE",
      "dbDomainName":"<DB_DOMAIN>",
      "dbRedundancy":null,
      "enableSEHA":false,
      "dbTargetNodeName":"<HOSTNAME>",
      "associatedNetworks":[
         "Public-network:b9aebe9f-69fc-4ef8-b5f1-4cc2cc837e5f"
      ],
      "dbCharacterSet":{
         "characterSet":"AL32UTF8",
         "nlsCharacterset":"AL16UTF16",
         "dbTerritory":"AMERICA",
         "dbLanguage":"AMERICAN"
      },
      "dbConsoleEnable":false,
      "backupDestination":null,
      "cloudStorageContainer":null,
      "backupConfigId":null,
      "isAutoBackupDisabled":false
   }
]

The odacli output would only provide the following information:

  • ID
  • DB Name
  • DB Type
  • DB Version
  • CDB
  • Class
  • Shape
  • Storage
  • Status
  • DbHomeID

With the REST API we do get the following additional output:

  • associatedNetworks
  • backupConfigId
  • backupDestination
  • cloudStorageContainer
  • createTime
  • databaseUniqueName
  • dbCharacterSet
    • characterSet
    • nlsCharacterset
    • dbTerritory
    • dbLanguage
  • dbConsoleEnable
  • dbDomainName
  • dbEdition
  • dbHomeId
  • dbOnFlashStorage
  • dbRedundancy
  • dbRole
  • dbTargetNodeName
  • dbTargetNodeNumber
  • enableSEHA
  • enableTDE
  • instanceOnly
  • isAutoBackupDisabled
  • isBcfgInSync
  • level0BackupDay
  • name
  • pdbAdminUserName
  • pdBName
  • registerOnly
  • rmanBkupPassword
  • tdePassword
  • updatedTime

These are the exact same information as we would get with the odacli describe-database command.

Database details                                                  
---------------------------------------------------------------- 
                     ID: f8c4a149-def3-4d3c-8e16-2fbcf7af99ee
            Description: SDK
                DB Name: SDK
             DB Version: 19.6.0.0.200114
                DB Type: Si
                DB Role: PRIMARY
    DB Target Node Name: <HOSTNAME>
             DB Edition: EE
                   DBID: 3356628688
 Instance Only Database: false
                    CDB: false
               PDB Name: 
    PDB Admin User Name: 
           SEHA Enabled: false
                  Class: Oltp
                  Shape: Odb1
                Storage: Acfs
          DB Redundancy: 
           CharacterSet: AL32UTF8
  National CharacterSet: AL16UTF16
               Language: AMERICAN
              Territory: AMERICA
                Home ID: 0b07ae45-a517-448d-bce9-7a1b1e6919b6
        Console Enabled: false
            TDE Enabled: false
     Level 0 Backup Day: Sunday
     AutoBackup Enabled: true
                Created: August 27, 2020 10:41:18 AM CEST
         DB Domain Name: <DB_DOMAIN>
    Associated Networks: Public-network 

For everyone who attended my presentation about ODA automation already knew that and they are aware that there is already a REST API available on the ODA.

But as I mentioned in this presentation there is no documentation for this API and there is no support and no guarantee that the API is available in the next release. Also every API change had to be detected by the user of this interface.

With the release 19.8 we now have an official documented REST API and an official supported and documented JAVA SDK.

Because curl is probably not the way we do our automation, at least at my customers we use tools like ansible. Ansible is a great tool for automation and provides us a lot of modules for doing different infrastructure tasks. Until this ODA release we were able to use the SHELL for automation of taks.

- name: "GET all Databases"
  shell: /opt/oracle/dcs/bin/odacli list-databases -j
    register: AVAILABLE_DATABASES

Now we are able to use the uri ansible module for managing the ODA. In the following examples I use the ansible uri module. From my perspective it is easier to read than an CURL command. If you struggle with creating curl commands reach out to me.

  - name: Get all Databases
    uri:
      url: https://{{ ODA_FQDN }}:7070/databases
      user: oda-admin
      password: "{{ PASSWORD }}"
      method: GET
      force_basic_auth: yes
      status_code: 200
      body_format: json
      validate_certs: no
    register: DB_INFOS

In this task we use the uri module with a few required parameters:

One important parameter is the url parameter. The Port for the REST API is 7070 and we connect to the databases endpoint.

The description of all endpoints can be found here (link to doc).

Further we use the method GET to gather information and we have to define the user and the password of an ODA admin account.

Important is to define the body_format to json. With this parameter ansible knows that the response will be in json format and already converts everything to this.

Endpoints

Of course we are able to do some more things than get a list of configured databases. Currently there are not that many endpoints available but I’m pretty sure they will arrive with further ODA releases and the current available endpoints provide us most of the daily tasks like creating, deleting and modifying databases.

The current possibilities of the REST API:

  • createDatabase
  • createDatabaseHome
  • deleteDatabase
  • deleteDatabaseByName
  • deleteDbHome
  • getDatabase
  • getDatabaseHome
  • getDatabaseHomeByName
  • getDbShapes
  • getJobDetail
  • getJobsList
  • getPendingJobsList
  • listDatabaseHomes
  • listDatabases
  • modifyDatabase

Create a database

To create a database we can use the same endpoint as for getting all databases (/databases). But instead of sending a GET- we are sending a POST-request with the required parameters in the body part of the request.

- name: Create Database
  uri:
    url: https://{{ ODA_FQDN }}:7070/databases
    user: oda-admin
    password: "{{ PASSWORD }}"
    method: POST
    force_basic_auth: yes
    validate_certs: no
    body_format: json
    body: 
      dbName: "{{ DB_NAME }}"
      databaseUniqueName: "{{ databaseUniqueName }}"
      adminPassword: "{{ PASSWORD }}"
      isCdb: "{{ isCdb }}"
      enableTDE: "{{ enableTDE }}"
      dbType: "SI"
      dbTargetNodeNumber: "0"
      dbClass: "OLTP"
      dbShape: "{{ dbShape }}"
      dbStorage: "{{ dbSt10,28orage }}"
      dbConsoleEnable: "{{ dbConsoleEnable }}"
      dbCharacterSet:
        characterSet: "{{ characterSet }}"
        nlsCharacterset: "{{ nlsCharacterset }}"
        dbTerritory: "{{ dbTerritory }}"
        dbLanguage: "{{ dbLanguage }}"
    headers:
      opc-idempotency-token: "DBCREATION{{ ansible_date_time.iso8601_basic }}"
  register: DB_CREATION_OUT

If we define the body part accordingly ansible creates a json formated document with the required subelements.

One important element is the opc-idempotency-token this can be anything and defines a unique token for this task. This token assures that the call is – as the name of the header elements suggests – idempotent. If reissue the same database creation task with the same token the ODA will not create a new job. In this example I used a timestamp ({{ ansible_date_time.iso8601_basic }}) in addition to a descriptive text (DBCREATION).

The response of the REST request will be saved in the variable DB_CREATION_OUT.

{
        "access_control_allow_origin": "*",
        "cache_control": "private, no-store",
        "changed": false,
        "connection": "close",
        "content_length": "233",
        "content_security_policy": "frame-ancestors 'none'",
        "content_type": "application/json",
        "cookies": {},
        "cookies_string": "",
        "date": "Thu, 27 Aug 2020 07:48:35 GMT",
        "elapsed": 4,
        "failed": false,
        "jobid": "ab94f304-e3d5-4133-a471-a6c3129913f1",
        "json": {
            "createTimestamp": 1598514515203,
            "description": "Database service creation with db name: SDK",
            "jobId": "ab94f304-e3d5-4133-a471-a6c3129913f1",
            "message": null,
            "reports": [],
            "resourceList": [],
            "status": "Created",
            "updatedTime": 1598514515205
        },
        "msg": "OK (233 bytes)",
        "redirected": false,
        "status": 200,
        "url": "https://<ODA_FQDN>:7070/databases",
        "vary": "Origin",
        "x_content_type_options": "nosniff",
        "x_frame_options": "DENY",
        "x_xss_protection": "1; mode=block"
    }

This variable can now be used to observe the database creation with a task similar to the following one, we need the jobId parameter for calling the jobs endpoint.

  - name: Check for finish
    uri:
      url: https://{{ ODA_FQDN }}:7070/jobs/{{ DB_CREATION_OUT.json.jobId }}
      user: oda-admin
      password: "{{ PASSWORD }}"
      method: GET
      force_basic_auth: yes
      validate_certs: no
      body_format: json
    register: JOB_STATUS
    until: JOB_STATUS.json.status == "Success" or JOB_STATUS.json.status == "Failure"
    failed_when: JOB_STATUS.json.status == "Failure"
    retries: 120
    delay: 10

Output of the status info

{
        "access_control_allow_origin": "*",
        "cache_control": "private, no-store",
        "changed": false,
        "connection": "close",
        "content_length": "1043",
        "content_security_policy": "frame-ancestors 'none'",
        "content_type": "application/json",
        "cookies": {},
        "cookies_string": "",
        "date": "Thu, 27 Aug 2020 08:41:32 GMT",
        "elapsed": 0,
        "failed": false,
        "json": {
            "createTimestamp": 1598517678561,
            "description": "Database service creation with db name: SDK",
            "jobId": "c6b92722-a2c6-4507-9b18-d92e82c7cb42",
            "message": null,
            "reports": [
                {
                    "endTime": 1598517682854,
                    "jobId": "c6b92722-a2c6-4507-9b18-d92e82c7cb42",
                    "parentTaskId": "TaskSequential_1973",
                    "reportLevel": "Info",
                    "startTime": 1598517682842,
                    "status": "Success",
                    "tags": [],
                    "taskDescription": null,
                    "taskId": "TaskSequential_1982",
                    "taskName": "Setting up ssh equivalance",
                    "taskResult": "",
                    "updatedTime": 1598517682854
                },
                {
                    "endTime": 1598517682942,
                    "jobId": "c6b92722-a2c6-4507-9b18-d92e82c7cb42",
                    "parentTaskId": "TaskSequential_1973",
                    "reportLevel": "Info",
                    "startTime": 1598517682942,
                    "status": "Running",
                    "tags": [],
                    "taskDescription": null,
                    "taskId": "TaskSequential_1990",
                    "taskName": "Creating volume datSDK",
                    "taskResult": "",
                    "updatedTime": 1598517682942
                }
            ],
            "resourceList": [
                {
                    "jobId": "c6b92722-a2c6-4507-9b18-d92e82c7cb42",
                    "resourceId": "a216352c-f717-4fae-8bb5-7a44009c28fb",
                    "resourceNewType": "Storage",
                    "resourceType": null,
                    "updatedTime": null
                }
            ],
            "status": "Running",
            "updatedTime": 1598517682199
        },
        "msg": "OK (1043 bytes)",
        "redirected": false,
        "status": 200,
        "url": "https://<ODA_FQDN>:7070/jobs/c6b92722-a2c6-4507-9b18-d92e82c7cb42",
        "vary": "Origin, Accept-Encoding",
        "x_content_type_options": "nosniff",
        "x_frame_options": "DENY",
        "x_xss_protection": "1; mode=block"
    }

This task gets every 10 seconds for 120 iterations the status of the create database job. The job is failed as soon as the status change to “Failure” if the status changes to “Success” the database creation was successful.

The output contains for every sub step an own element with the corresponding taskName and the individual taskResult. In difference to the most other responses the url element contains the link to its own. For other types of request the url contains either the url for the database list or some other parent object.

Deleting a database

Probably creating a database is not the only thing you want to do. In many cases you want to delete a database. This can be done with the REST API too. In case you delete a database you need the “DELETE” method. For deleting a database we do have to different endpoints.

  • deleteDatabase
  • deleteDatabaseByName

For the first one we need to define the database-id (ODA internal ID) in the body of the rest call. In the second one we provide the name of the database in the url.

- name: Delete Databases
  uri:
    url: https://{{ ODA_FQDN }}:7070/databases/{{ db_id[0] }}
    user: oda-admin
    password: "{{ PASSWORD }}"
    body_format: json
    method: DELETE
    force_basic_auth: yes
    validate_certs: no
    headers:
      opc-idempotency-token: "DBDELETION{{ ansible_date_time.iso8601_basic }}"
  register: DELETE_JOB

This DELETE request provides the following output.

{
        "access_control_allow_origin": "*",
        "cache_control": "private, no-store",
        "changed": false,
        "connection": "close",
        "content_security_policy": "frame-ancestors 'none'",
        "cookies": {},
        "cookies_string": "",
        "date": "Fri, 28 Aug 2020 18:32:53 GMT",
        "elapsed": 4,
        "failed": false,
        "jobid": "b8810959-b8f5-4f76-93ce-8b59ebddc941",
        "msg": "OK (unknown bytes)",
        "redirected": false,
        "status": 200,
        "url": "https://<ODA_FQDN>:7070/databases/53e13942-07db-4268-b64c-05dc9d6007b8",
        "vary": "Origin",
        "x_content_type_options": "nosniff",
        "x_frame_options": "DENY",
        "x_xss_protection": "1; mode=block"
    }

As mentioned in the output for the job status this output contains also an url element but this element contains the link to the database details endpoint.

To check the process of deletion you could create a task as follows:

- name: Check DB Deletion
  uri:
    url: https://{{ ODA_FQDN }}:7070/jobs/{{ DELETE_JOB.jobid }}
    user: oda-admin
    password: "{{ PASSWORD }}"
    body_format: json
    method: GET
    force_basic_auth: yes
    validate_certs: no
  delegate_to: localhost
  register: DELETE_LOOP
  until: DELETE_LOOP.json.status == "Success" or DELETE_LOOP.json.status == "failed"
  retries: 120
  delay: 10

With the following OUTPUT:

{
        "access_control_allow_origin": "*",
        "cache_control": "private, no-store",
        "changed": false,
        "connection": "close",
        "content_length": "1117",
        "content_security_policy": "frame-ancestors 'none'",
        "content_type": "application/json",
        "cookies": {},
        "cookies_string": "",
        "date": "Fri, 28 Aug 2020 19:16:14 GMT",
        "elapsed": 0,
        "failed": false,
        "json": {
            "createTimestamp": 1598642169468,
            "description": "Database service deletion with db name: SDK with id : d999c952-1044-4168-bc6d-67ff7fe01e2e",
            "jobId": "216d7845-d077-444b-8970-627f9cdc435b",
            "message": null,
            "reports": [
                {
                    "endTime": 1598642173565,
                    "jobId": "216d7845-d077-444b-8970-627f9cdc435b",
                    "parentTaskId": "TaskSequential_7085",
                    "reportLevel": "Info",
                    "startTime": 1598642173563,
                    "status": "Success",
                    "tags": [],
                    "taskDescription": null,
                    "taskId": "TaskZJsonRpcExt_7087",
                    "taskName": "Validate db d999c952-1044-4168-bc6d-67ff7fe01e2e for deletion",
                    "taskResult": "",
                    "updatedTime": 1598642173565
                },
                {
                    "endTime": 1598642173608,
                    "jobId": "216d7845-d077-444b-8970-627f9cdc435b",
                    "parentTaskId": "TaskSequential_7085",
                    "reportLevel": "Info",
                    "startTime": 1598642173606,
                    "status": "Running",
                    "tags": [],
                    "taskDescription": null,
                    "taskId": "TaskZJsonRpcExt_7098",
                    "taskName": "Database Deletion",
                    "taskResult": "",
                    "updatedTime": 1598642173608
                }
            ],
            "resourceList": [
                {
                    "jobId": "216d7845-d077-444b-8970-627f9cdc435b",
                    "resourceId": "d999c952-1044-4168-bc6d-67ff7fe01e2e",
                    "resourceNewType": "Db",
                    "resourceType": null,
                    "updatedTime": null
                }
            ],
            "status": "Running",
            "updatedTime": 1598642173542
        },
        "msg": "OK (1117 bytes)",
        "redirected": false,
        "status": 200,
        "url": "https://<ODA_FQDN>:7070/jobs/216d7845-d077-444b-8970-627f9cdc435b",
        "vary": "Origin, Accept-Encoding",
        "x_content_type_options": "nosniff",
        "x_frame_options": "DENY",
        "x_xss_protection": "1; mode=block"
    }

This output is very similar to the output of the database creation job status output. It also contains a sub element for every deletion task with the corresponding status.

One important thing to know is that there is a difference in the response you get from the API compared to the createDatabase endpoint.

  • jobId (Create Database)
  • jobid (Delete Database)

In the response of the createDatabase endpoint you have the parameter jobId with an I in upper case and in the deleteDatabase endpoint response you have the parameter jobid with a lower case I.

Conclusion

The REST API for the Oracle Database Appliance is a long awaited tool that makes it much easier for us to automate various tasks on an ODA. We now have a very easy way to integrate the database tasks – which before could have been automated with odacli – into deployment procedures and deployment processes.

The main advantage from my point of view is that we do have a standardized, officially maintained and supported API for working with an ODA.

If we combine the creation of a database with the ORDS REST API we are able to automate even more. We are then able to create pluggable databases and so on over a standardized API.

Therefore my wish list for the API is currently long (please let me know if you have also some wishes):

  • Different API Users with different privileges
  • Authentication with other methods than BASIC
  • Create PDBs with odacli or include ORDS into DB creation
  • Add the possibility for post script execution (Backup or Enterprise Manager configuration as examples).
  • Extension of the API to all odacli commands

I’m currently in a project in which I’m able to use most of these features and will be able to share some further experiences. Currently I’m really happy to have what is already here even if the possibilities are still limited but it already eases my life.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.