Following the first part of this blog serie, here are some new useful functions to automate CDB lifecycle on Exadata Cloud at Customer. Please make sure to read the previous post first, as it contains all the rudiments to build the PL/SQL package.
Check status of a precheck job
Now that I know how to launch a precheck against the latest available patch, I want to have a function to check the corresponding job status. Since the function cdb_launch_patch_precheck returns the jobID of the launched precheck job, I can reuse this value to check the status of an ongoing precheck job :
FUNCTION cdb_get_precheck_status (in_restserver IN VARCHAR2, in_idcs IN VARCHAR2, in_username IN VARCHAR2, in_password IN VARCHAR2, in_service_name IN VARCHAR2, in_job_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/job/'||in_job_id;
l_method method_type := 'GET';
l_result json_result_type;
l_status varchar2(4000 CHAR);
BEGIN
l_result := get_request_json_response(l_url,l_method,in_idcs,in_username,in_password);
SELECT r_status into l_status
FROM JSON_TABLE(
l_result,
'$' COLUMNS (r_status VARCHAR2(50 CHAR) PATH status)
);
return l_status;
END cdb_get_precheck_status;
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
- a jobId
The core section of this function is :
SELECT r_status into l_status
FROM JSON_TABLE(
l_result,
'$' COLUMNS (r_status VARCHAR2(50 CHAR) PATH status)
);
Thanks to function JSON_TABLE, this function returns the corresponding status of the precheck job. If the precheck job does not exist, it returns Can Not Complete. The valid values as specified in the documentation are : PASSED_PRECHECK, DIDNT_PASS_PRECHECK, and DIDNT_FINISHED_PRECHECK.
Launch latest available patch
Now that I have a valid precheck job, in other words, the previous function returns PASSED_PRECHECK, I can launch the application of the latest available patch with the following function :
FUNCTION cdb_apply_patch (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/'||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_apply_patch;
The parameters of function cdb_apply_patch are almost the same as cdb_get_precheck_status except that the jobId is replaced by the patchId. 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 patch job has been launched successfully. And if the patch job has not been launched successfully or a patch job is already running, it returns NULL.
Check status of a patch job
And finally, I can check the status of the ongoing patch job with the following function :
FUNCTION cdb_get_apply_status (in_restserver IN VARCHAR2, in_idcs IN VARCHAR2, in_username IN VARCHAR2, in_password IN VARCHAR2, in_service_name IN VARCHAR2, in_job_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/applied/job/'||in_job_id;
l_method method_type := 'GET';
l_result json_result_type;
l_status varchar2(4000 CHAR);
BEGIN
l_result := get_request_json_response(l_url,l_method,in_idcs,in_username,in_password);
SELECT r_status into l_status
FROM JSON_TABLE(
l_result,
'$.patchingResult[*]' COLUMNS (r_status VARCHAR2(50 CHAR) PATH patchingStatus)
);
return l_status;
END cdb_get_apply_status;
The parameter of function cdb_get_apply_status are exactly the same as cdb_get_precheck_status. The core section of this function is :
SELECT r_status into l_status
FROM JSON_TABLE(
l_result,
'$.patchingResult[*]' COLUMNS (r_status VARCHAR2(50 CHAR) PATH patchingStatus)
);
Again, thanks to function JSON_TABLE, this function returns the corresponding status of the patch job. If the patch job does not exist, it returns NULL. The valid values are : IN_PROGRESS, COMPLETED, and COMPLETED_ROLLBACK. Maybe there are more but only COMPLETED and COMPLETED_ROLLBACK are specified in the documentation.
With all the functions described in this post and in the first part of this blog serie, I now have everything I need to :
- Get the latest available patch for a database deployment
- Launch precheck of latest available patch
- Check status of a precheck job
- Launch latest available patch
- Check status of a patch job
Floo Bar 