Exadata Cloud at Customer offers a very convenient method to manage your Oracle Database Exadata Cloud Services : REST APIs \o/
I am currently working on a PL/SQL package to interact with Exadata instances, from a central administrative database. Let’s see, step by step, what are the prerequisite to achieve this goal. (Note : I am currently learning PL/SQL, and I use Trivadis PL/SQL Cop, especially the very useful plugin for SQL Developer.)
Package specification
All the administrative tasks are executed from a central PDB in version 18.5 named CADMIN (service CADMIN_SVC) with a schema named CMANAGER. This schema contains a package EXACC_CDB_LIFECYCLE :
create or replace PACKAGE EXACC_CDB_LIFECYCLE AS
----------------------------------------------------
-- Constants
----------------------------------------------------
co_wallet_location CONSTANT VARCHAR2(4000 CHAR) := '/path/to/the/wallet';
----------------------------------------------------
-- Subtypes
----------------------------------------------------
SUBTYPE url_type IS varchar2(2000 CHAR);
SUBTYPE method_type IS varchar2(7 CHAR);
SUBTYPE json_result_type IS varchar2(4000 CHAR);
----------------------------------------------------
-- Procedures and functions
----------------------------------------------------
FUNCTION get_request_json_response (in_url IN url_type, in_method IN method_type, in_idcs IN VARCHAR2, in_username IN VARCHAR2, in_password IN VARCHAR2) return json_result_type;
FUNCTION cdb_get_latest_available_patch (in_restserver IN VARCHAR2, in_idcs IN VARCHAR2, in_username IN VARCHAR2, in_password IN VARCHAR2, in_service_name IN VARCHAR2) return VARCHAR2;
FUNCTION cdb_launch_patch_precheck (in_restserver IN VARCHAR2, in_idcs IN VARCHAR2, in_username IN VARCHAR2, in_password IN VARCHAR2, in_service_name IN VARCHAR2, in_patch_id IN VARCHAR2) return VARCHAR2;
END EXACC_CDB_LIFECYCLE;
In this example, I use a constant co_wallet_location which contains the path to the wallet of my central administrative database.
Get JSON response of any request
First, let’s deal with get_request_json_response function to return the JSON response of a specific URL. Its parameters are :
- an URL, consisting of your REST server + an endpoint path (here is how to find your REST server)
- an HTTP method
- an Oracle Identity Cloud Service tenant name (it should look like
idcs-*, and here is how to find it) - a username
- a password
FUNCTION get_request_json_response (
in_url IN url_type,
in_method IN method_type,
in_idcs IN VARCHAR2,
in_username IN VARCHAR2,
in_password IN VARCHAR2
) return json_result_type
IS
l_request sys.utl_http.req;
l_response sys.utl_http.resp;
l_result json_result_type;
BEGIN
sys.utl_http.set_wallet('file:'||co_wallet_location);
l_request := sys.utl_http.begin_request(in_url, in_method);
sys.utl_http.set_authentication(l_request, in_username, in_password);
sys.utl_http.set_header (l_request, 'x-id-tenant-name', in_idcs);
l_response := sys.utl_http.get_response(l_request);
sys.utl_http.read_text(l_response,l_result);
sys.utl_http.end_request(l_request);
return l_result;
END get_request_json_response;
A simple example to test this function is to get the list of all available patches for an Exadata Cloud at Customer database deployment named DBSERVICE01. Take a look at this document to get the description of this REST API and form the appropriate URL.
SQL> connect CMANAGER/awesome_password@CADMIN_SVC
SQL> select EXACC_CDB_LIFECYCLE.get_request_json_response(
'https://my-rest-server/paas/api/v1.1/instancemgmt/idcs-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/services/dbaas/instances/DBSERVICE01/patches/checks',
'GET',
'idcs-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
'some_username',
'some_cool_password'
) from dual ;
select EXACC_CDB_LIFECYCLE.get_request_json_response
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1148
ORA-06512: at "CMANAGER.EXACC_CDB_LIFECYCLE", line 15
Grant privilege to connect to a host with ACL
Right, I first have to grant the http privilege for host my-rest-server to CMANAGER with a privileged user :
SQL> connect SYSTEM/super_password@CADMIN_SVC
SQL> begin
dbms_network_acl_admin.append_host_ace (
host => 'my-rest-server',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'CMANAGER',
principal_type => xs_acl.ptype_db));
end;
/
PL/SQL procedure successfully completed.
Let’s retry :
SQL> connect CMANAGER/awesome_password@CADMIN_SVC
SQL> select EXACC_CDB_LIFECYCLE.get_request_json_response(
'https://my-rest-server/paas/api/v1.1/instancemgmt/idcs-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/services/dbaas/instances/DBSERVICE01/patches/checks',
'GET',
'idcs-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
'some_username',
'some_cool_password'
) from dual ;
select EXACC_CDB_LIFECYCLE.get_request_json_response
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-28845: No certificate
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1148
ORA-06512: at "CMANAGER.EXACC_CDB_LIFECYCLE", line 15
Provide a valid SSL certificate
As described in the documentation, it is necessary to provide a valid SSL certificate. This is why a wallet is needed. I get the certificate by following “section I” of My Oracle Support Doc 1998596.1 for example. Once I have it locally, I add it to the wallet with orapki :
orapki wallet add -wallet /path/to/the/wallet -trusted_cert -cert /path/to/the/certificate.crt
And check if it has been added correctly :
orapki wallet display -wallet /path/to/the/wallet
Let’s retry once again :
SQL> connect CMANAGER/awesome_password@CADMIN_SVC
SQL> select EXACC_CDB_LIFECYCLE.get_request_json_response(
'https://my-rest-server/paas/api/v1.1/instancemgmt/idcs-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/services/dbaas/instances/DBSERVICE01/patches/checks',
'GET',
'idcs-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
'some_username',
'some_cool_password'
) from dual ;
[{"id":12,"jobId":"161783","patchId":"28714316","startDate":"2019-04-25T14:22:16.103+0000",
"endDate":"2019-04-25T14:33:57.206+0000","performedBy":"some_username","inProgress":false,"
status":"PASSED_PRECHECK","href":"https://my-rest-server/paas/api/v1.1/instancemgmt/idcs-xx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/services/dbaas/instances/DBSERVICE01/patches/checks/job/1617
83","preCheckMessages":[],"statusMessage":"Service passed all the pre-checks"},{"id":4,"job
Id":"160511","patchId":"28828733","startDate":"2019-03-29T15:26:05.599+0000","endDate":"201
9-03-29T15:37:47.798+0000","performedBy":"some_username","inProgress":false,"status":"PASSE
D_PRECHECK","href":"https://my-rest-server/paas/api/v1.1/instancemgmt/idcs-xxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxx/services/dbaas/instances/DBSERVICE01/patches/checks/job/160511","preCheckM
essages":[],"statusMessage":"Service passed all the pre-checks"}]
Now it works 🙂 But the output is not really pretty, here is a more human-readable formatting :
[
{
"id":12,
"jobId":"161783",
"patchId":"28714316",
"startDate":"2019-04-25T14:22:16.103+0000",
"endDate":"2019-04-25T14:33:57.206+0000",
"performedBy":"some_username",
"inProgress":false,
"status":"PASSED_PRECHECK",
"href":"https://my-rest-server/paas/api/v1.1/instancemgmt/idcs-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/services/dbaas/instances/DBSERVICE01/patches/checks/job/161783",
"preCheckMessages":[
],
"statusMessage":"Service passed all the pre-checks"
},
{
"id":4,
"jobId":"160511",
"patchId":"28828733",
"startDate":"2019-03-29T15:26:05.599+0000",
"endDate":"2019-03-29T15:37:47.798+0000",
"performedBy":"some_username",
"inProgress":false,
"status":"PASSED_PRECHECK",
"href":"https://my-rest-server/paas/api/v1.1/instancemgmt/idcs-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/services/dbaas/instances/DBSERVICE01/patches/checks/job/160511",
"preCheckMessages":[
],
"statusMessage":"Service passed all the pre-checks"
}
]
This JSON response shows there are 2 available patches : 28714316 and 28828733.
Get the latest available patch for a database deployment
Now that I can retrieve the JSON response of any REST API, let’s create a function to make use of this result. I can get, for example, the latest available patch for a database deployment.
FUNCTION cdb_get_latest_available_patch (
in_restserver IN VARCHAR2,
in_idcs IN VARCHAR2,
in_username IN VARCHAR2,
in_password IN VARCHAR2,
in_service_name IN VARCHAR2
) return VARCHAR2
IS
l_url url_type := in_restserver||'/paas/api/v1.1/instancemgmt/'||in_idcs||'/services/dbaas/instances/'||in_service_name||'/patches/available';
l_method method_type := 'GET';
l_result json_result_type;
l_max_patchId varchar2(4000 CHAR);
BEGIN
-- get all the patches available
l_result := get_request_json_response(l_url,l_method,in_idcs,in_username,in_password);
-- get the max patchId
SELECT max(r_patchId) into l_max_patchId
FROM JSON_TABLE(
l_result,
'$[*]' COLUMNS (r_patchId VARCHAR2(50 CHAR) PATH patchId)
);
return l_max_patchId;
END cdb_get_latest_available_patch;
The parameters of this function are :
- a REST server (here is how to find yours)
- an Oracle Identity Cloud Service tenant name (it should look like
idcs-*, and here is how to find it) - a username
- a password
- a service name, which is an Exadata Cloud at Customer database deployment
First, get_request_json_response is called. But the core section of this function is :
SELECT max(r_patchId) into l_max_patchId
FROM JSON_TABLE(
l_result,
'$[*]' COLUMNS (r_patchId VARCHAR2(50 CHAR) PATH patchId)
);
Thanks to function JSON_TABLE, it is very easy to get the max patchId, which I assume should be the latest 🙂
SQL> connect CMANAGER/awesome_password@CADMIN_SVC
SQL> select EXACC_CDB_LIFECYCLE.cdb_get_latest_available_patch (
'https://my-rest-server',
'idcs-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
'some_username',
'some_cool_password',
'DBSERVICE01')
from dual ;
28828733
And now what ?
Launch precheck of latest available patch
In the same way, once I get the latest available patch number, I create a function to actually run the patch precheck against a database deployment :
FUNCTION cdb_launch_patch_precheck (
in_restserver IN VARCHAR2,
in_idcs IN VARCHAR2,
in_username IN VARCHAR2,
in_password IN VARCHAR2,
in_service_name IN VARCHAR2,
in_patch_id IN VARCHAR2
) return VARCHAR2
IS
l_url url_type := in_restserver||'/paas/api/v1.1/instancemgmt/'||in_idcs||'/services/dbaas/instances/'||in_service_name||'/patches/checks/'||in_patch_id;
l_method method_type := 'PUT';
l_result json_result_type;
l_jobId varchar2(4000 CHAR);
BEGIN
l_result := get_request_json_response(l_url,l_method,in_idcs,in_username,in_password);
SELECT r_jobId into l_jobId
FROM JSON_TABLE(
l_result,
'$.details[*]' COLUMNS (r_jobId VARCHAR2(50 CHAR) PATH jobId)
);
return l_jobId;
END cdb_launch_patch_precheck;
The parameter of function cdb_launch_patch_precheck are exactly the same as cdb_get_latest_available_patch + the patchId of course. The core section of this function is :
SELECT r_jobId into l_jobId
FROM JSON_TABLE(
l_result,
'$.details[*]' COLUMNS (r_jobId VARCHAR2(50 CHAR) PATH jobId)
);
Again, thanks to function JSON_TABLE, this function returns the corresponding jobID if a precheck job has been launched successfully. And if the precheck job has not been launched successfully or a precheck job is already running, it returns NULL.
There are loads of possibility thanks to the 30+ REST APIs already available. I am especially interested in the automation of the following lifecycle :
- Create a CDB with a named PDB
- Perform a patching precheck of the latest available patch
- If OK, apply this patch
- Check the status
- Eventually delete this CDB when needed
I’ll write other blog posts on this topic !
Floo Bar 
Reblogged this on Goryszewskig's Blog.
LikeLike
Pingback: Exadata Cloud at Customer : Automate CDB lifecycle with REST APIs in PL/SQL – Part 2 – Floo Bar