Exadata Cloud at Customer : Automate CDB lifecycle with REST APIs in PL/SQL – Part 1

2 Replies
(Photo by Michael Fertig, on Unsplash)

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 : 

  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 :

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 :

  1. Create a CDB with a named PDB
  2. Perform a patching precheck of the latest available patch
  3. If OK, apply this patch
  4. Check the status
  5. Eventually delete this CDB when needed

I’ll write other blog posts on this topic !

2 thoughts on “Exadata Cloud at Customer : Automate CDB lifecycle with REST APIs in PL/SQL – Part 1”

  2. Pingback: Exadata Cloud at Customer : Automate CDB lifecycle with REST APIs in PL/SQL – Part 2 – Floo Bar

Leave a comment