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.
