We are going to take a side trip today. I was at Collaborate 2016 and one of the questions that came up was how do you provision 40 database instances for a lab. I really did not want to sit and click through 40 screens and log into 40 accounts so I decided to do a little research. It turns out that there is a relatively robust REST api that allows you to create, read, update, and delete database instances. The DBaaS Rest Api Documentation is a good place to start to figure out how this works.
To list instances that are running in the database service use the following command, note that “c url” should be shortened to remove the space. Dang blogging software! Note to make things easier and to allow us to script creation we define three variables on the command line. I did most of this testing on a Mac so it should translate to Linux and Cygwin. The three variables that we need to create are
- ODOMAIN – instance domain that we are using in the Oracle cloud
- OUID – username that we log in as
- OPASS – password for this instance domain/username
export ODOMAIN=mydomain export OUID=cloud.admin export OPASS=mypassword c url -i -X GET -u $OUID:$OPASS -H "X-ID-TENANT-NAME: $ODOMAIN" -H "Content-Type:application/json" https://dbaas.oraclecloud.com/jaas/db/api/v1.1/instances/$ODOMAIN
What should return is
HTTP/1.1 200 OK
Date: Sun, 10 Apr 2016 18:42:42 GMT
Server: Oracle-Application-Server-11g
Content-Length: 1023
X-ORACLE-DMS-ECID: 005C2NB3ot26uHFpR05Eid0005mk0001dW
X-ORACLE-DMS-ECID: 005C2NB3ot26uHFpR05Eid0005mk0001dW
X-Frame-Options: DENY
X-Frame-Options: DENY
Vary: Accept-Encoding,User-Agent
Content-Language: en
Content-Type: application/json
{"uri":"https:\/\/dbaas.oraclecloud.com:443\/paas\/service\/dbcs\/api\/v1.1\/instances\/metcsgse00027","service_type":"dbaas","implementation_version":"1.0","services":[{"service_name":"test-hp","version":"12.1.0.2","status":"Running","description":"Example service instance","identity_domain":"metcsgse00027","creation_time":"Sun Apr 10 18:5:26 UTC 2016","last_modified_time":"Sun Apr 10 18:5:26 UTC 2016","created_by":"cloud.admin","sm_plugin_version":"16.2.1.1","service_uri":"https:\/\/dbaas.oraclecloud.com:443\/paas\/service\/dbcs\/api\/v1.1\/instances\/metcsgse00027\/test-hp"},{"service_name":"db12c-hp","version":"12.1.0.2","status":"Running","description":"Example service instance","identity_domain":"metcsgse00027","creation_time":"Sun Apr 10 18:1:21 UTC 2016","last_modified_time":"Sun Apr 10 18:1:21 UTC 2016","created_by":"cloud.admin","sm_plugin_version":"16.2.1.1","service_uri":"https:\/\/dbaas.oraclecloud.com:443\/paas\/service\/dbcs\/api\/v1.1\/instances\/metcsgse00027\/db12c-hp"}],"subscriptions":[]}
If you get back anything other than a 200 it means that you have the identity domain, username, or password incorrect. Note that we get back a json structure that contains two database instances that were previously created, test-hp and db12c-hp. Both are up and running. Both are 12.1.0.2 instances. We don’t know much more than these but can dive a little deeper by requesting more information by included the service name as part of the request. A screen shot of the deeper detail is shown below.
A list of the most common commands are shown in the screen shot below
The key options to remember are:
- list: -X GET
- stop: -X POST –data ‘{ “lifecycleState” : “Stop” }’
- restart: -X POST –data ‘{ “lifecycleState” : “Restart” }’
- delete: -X DELETE **need to add the instance name at the end, for example db12c-hp in request above
- create: -X POST –data @createDB.json
In the create option we include a json file that defines everything for the database instance.
{
"serviceName": "test-hp",
"version": "12.1.0.2",
"level": "PAAS",
"edition": "EE_HP",
"subscriptionType": "HOURLY",
"description": "Example service instance",
"shape": "oc3",
"vmPublicKeyText": "ssh-rsa AAAAB3NzaC1yc2EAAAABJQAAAQEAnrfxP1Tn50Rvuy3zgsdZ3ghooCclOiEoAyIl81Da0gzd9ozVgFn5uuSM77AhCPaoDUnWTnMS2vQ4JRDIdW52DckayHfo4q5Z4N9dhyf9n66xWZM6qyqlzRKMLB0oYaF7MQQ6QaGB89055q23Vp+Pk5Eo+XPUxnfDR6frOYZYnpONyZ5+Qv6pmYKyxAyH+eObZkxFMAVx67VSPzStimNjnjiLrWxluh4g3XiZ1KEhmTQEFaLKlH2qdxKaSmhVg7EA88n9tQDWDwonw49VXUn/TaDgVBG7vsWzGWkRkyEN57AhUhRazs0tEPuGI2jXY3V8Q00w3wW38S/dgDcPFdQF0Q== rsa-key-20160107",
"parameters": [
{
"type": "db",
"usableStorage": "20",
"adminPassword": "Test123_",
"sid": "ORCL",
"pdb": "PDB1",
"failoverDatabase": "no",
"backupDestination": "none"
}
]
}
The vmPublicKeyText is our id_rsa.pub file that we use to connect to the service. I did not include a backup space but could have. I did not in this example because we have to embed a password in this space and I did not want to show a service with username and password.
Overall, I prefer scripting everything and running this from a command line. Call me old school but sitting for hours and clicking through screens when I can script it and get a notification when it is done appeals to me.