OCI DBCS patching using Software Images via REST API
This time it will be about patching of DBCS in OCI. There are several ways how to patch a database in OCI but Software Images is something that grabs my attention. The reason is that I have been told it suppose to use “out of place” patching which means it should use another ORACLE_HOME for patching and once the ORACLE HOME is patched it will relocate the database to the patched ORACLE HOME so only datapatch remains for applying. This is actually the method which is recommended also by Oracle. Despite it should use “out of place” when you’ll check the process of patching via dbcli job you will find there “Database inplace image patching with dbhomeId” and indeed it is basically doing untar from the software image directly into your main DB Home (I was using only one home and no additional have been created during the patching process). Also when reviewing Oracle documentation there is written “You can use a database software image to update the database software of an existing virtual machine or bare metal database in Oracle Cloud Infrastructure. This is sometimes referred to as in-place patching” (https://docs.oracle.com/en-us/iaas/Content/Database/Concepts/databasesoftwareimage.htm) so it turned out that the information I got, was apparently NOT 100% correct or I misunderstood. Anyway, what I like the most is that it will reduce overall patching time from approximately one hour to half an hour per database system in my case (setup with 3PDBs) and this is more important for me than the way how it is achieved.
But first things first. Let’s get the current patch info from the database we are going to patch.
Once you know what patch version you have on your DB you can start with the API part. First of all, you need to fulfill requirements in terms of having a user who can do API calls in OCI. Then you need to create an HTTP header for API calls. I’m using cURL for API calls all of the steps are related to this but you can whatever tool you want.
Again I’m not going to describe steps that are already well documented.
For user creation in OCI follow this link https://docs.oracle.com/en-us/iaas/Content/Identity/Tasks/managingusers.htm#Managing_Users
For using REST API there is another article which will guide you through how to build an HTTP header for curl API requests https://www.ateam-oracle.com/oracle-cloud-infrastructure-oci-rest-call-walkthrough-with-curl
OK so what now?
When you have prepared all necessary things you just need to identify what are available patches, create the software image and then patch the DB. Just a note that I’m doing this article using the 19c Oracle database so you may need to adjust the version according to your setup.
Get list of available patches
Using the format from the link mentioned above (from Oracle A-team) you need to setup a bash script to perform HTTP GET request. In bold I have highlighted the most important parts for achieving the goal so as to get the list of patches. You need to use dbVersions namespace to get also minor database versions. Also, you need to make sure you are using the right endpoint that means make sure you are doing REST call to the region where your resource resides and you are using the correct endpoint in terms of resource you want to get the information for instance “database.eu-amsterdam-1.oraclecloud.com” is not the same like “iaas.eu-amsterdam-1.oraclecloud.com”. In some cases you will get the correct response also when doing a call over “wrong” host but don’t take it as it will always work.
#OCID of the tenancy calls are being made in to
tenancy_ocid="ocid1.tenancy.oc1..<unique_id>"
# OCID of the user making the rest call
user_ocid="ocid1.user.oc1..<unique_id>"
# path to the private PEM format key for this user
privateKeyPath="/home/sampleuser/.oci/oci_api_key.pem"
# fingerprint of the private key for this user
fingerprint="99:aa:....."
# The REST api you want to call, with any required paramters.
rest_api="/20160918/dbVersions/19.0.0.0/minorVersions?compartmentId=ocid1.compartment.oc1..<unique_id>&shapeFamily=VIRTUALMACHINE"
# The host you want to make the call against
host="database.eu-amsterdam-1.oraclecloud..."
####################################################################################
date=`date -u "+%a, %d %h %Y %H:%M:%S GMT"`
date_header="date: $date"
host_header="host: $host"
request_target="(request-target): get $rest_api"
# note the order of items. The order in the signing_string matches the order in the headers
signing_string="$request_target\n$date_header\n$host_header"
headers="(request-target) date host"
echo "====================================================================================================="
printf '%b' "signing string is $signing_string \n"
signature=`printf '%b' "$signing_string" | openssl dgst -sha256 -sign $privateKeyPath | openssl enc -e -base64 | tr -d '\n'`
printf '%b' "Signed Request is \n$signature\n"
echo "====================================================================================================="
set -x
curl -v -X GET -sS https://$host$rest_api -H "date: $date" -H "Authorization: Signature version=\"1\",keyId=\"$tenancy_ocid/$user_ocid/$fingerprint\",algorithm=\"rsa-sha256\",headers=\"$headers\",signature=\"$signature\"" | json_reformatWhen you have your get_patch_versions.sh script and will run it you will get a response with the list available of patch versions (I removed some HTTP related parts to make it shorter).
Create custom Software Image
Now when we know that a newer patch is available (in this case 19.11) we can proceed to create a Software Image. The relevant namespace is databaseSoftwareImages . This time it will be HTTP POST call and the script (let’s call it create_image.sh ) will look as follows.
#OCID of the tenancy calls are being made in to
tenancy_ocid="ocid1.tenancy.oc1..<unique_id>"
# OCID of the user making the rest call
user_ocid="ocid1.user.oc1..<unique_id>"
# path to the private PEM format key for this user
privateKeyPath="/home/sampleuser/.oci/oci_api_key.pem"
# fingerprint of the private key for this user
fingerprint="99:aa:....."# The REST api you want to call, with any required paramters.
rest_api="/20160918/databaseSoftwareImages"# The host you want to make the call against
host="database.eu-amsterdam-1.oraclecloud..."# the json file containing the data you want to POST to the rest endpoint
body="./create_image.json"
##################################################################################### extra headers required for a POST/PUT request
body_arg=(--data-binary @${body})
content_sha256="$(openssl dgst -binary -sha256 < $body | openssl enc -e -base64)";
content_sha256_header="x-content-sha256: $content_sha256"
content_length="$(wc -c < $body | xargs)";
content_length_header="content-length: $content_length"
headers="(request-target) date host"
# add on the extra fields required for a POST/PUT
headers=$headers" x-content-sha256 content-type content-length"
content_type_header="content-type: application/json";date=`date -u "+%a, %d %h %Y %H:%M:%S GMT"`
date_header="date: $date"
host_header="host: $host"
request_target="(request-target): post $rest_api"# note the order of items. The order in the signing_string matches the order in the headers, including the extra POST fields
signing_string="$request_target\n$date_header\n$host_header"
# add on the extra fields required for a POST/PUT
signing_string="$signing_string\n$content_sha256_header\n$content_type_header\n$content_length_header"echo "====================================================================================================="
printf '%b' "signing string is $signing_string \n"
signature=`printf '%b' "$signing_string" | openssl dgst -sha256 -sign $privateKeyPath | openssl enc -e -base64 | tr -d '\n'`
printf '%b' "Signed Request is \n$signature\n"echo "====================================================================================================="
set -x
curl -v -X POST --data-binary "@create_image.json" -sS https://$host$rest_api -H "date: $date" -H "x-content-sha256: $content_sha256" -H "content-type: application/json" -H "content-length: $content_length" -H "Authorization: Signature version=\"1\",keyId=\"$tenancy_ocid/$user_ocid/$fingerprint\",algorithm=\"rsa-sha256\",headers=\"$headers\",signature=\"$signature\""
You might notice that there is used also payload file create_image.json which holds data which tells the API what patchSet to use, displyName, databaseSoftwareImageOneOffPatches (if you want to install also some one-off or OJVM patch) and so on. The structure is as follows.
{
"displayName":"TestImage19_11",
"compartmentId":"ocid1.compartment.oc1..<unique_id>",
"databaseVersion":"19.0.0.0",
"patchSet":"19.11.0.0",
"databaseSoftwareImageOneOffPatches":["32399816"],
"imageShapeFamily":"VM_BM_SHAPE",
"definedTags":{},
"freeformTags":{}
}When all is set then just run your create_image.sh and you will get similar output like when doing at previous step with details about compartment, software image id and lifecycleState which will be “PROVISIONING”. You need to wait until it will turn to “AVAILABLE” (usually approx. 10minuts).
Get custom Software Images
To get information about your custom images we can create another script. It will be HTTP GET like in the first example. The namespace will be again databaseSoftwareImages but this time with the get operation. Let’s call the script get_custom_images.sh .
#OCID of the tenancy calls are being made in to
tenancy_ocid="ocid1.tenancy.oc1..<unique_id>"
# OCID of the user making the rest call
user_ocid="ocid1.user.oc1..<unique_id>"
# path to the private PEM format key for this user
privateKeyPath="/home/sampleuser/.oci/oci_api_key.pem"
# fingerprint of the private key for this user
fingerprint="99:aa:....."
# The REST api you want to call, with any required paramters.
rest_api="/20160918/databaseSoftwareImages?compartmentId=ocid1.compartment.oc1..<unique_id>"
# The host you want to make the call against
host="database.eu-amsterdam-1.oraclecloud..."
####################################################################################
date=`date -u "+%a, %d %h %Y %H:%M:%S GMT"`
date_header="date: $date"
host_header="host: $host"
request_target="(request-target): get $rest_api"
# note the order of items. The order in the signing_string matches the order in the headers
signing_string="$request_target\n$date_header\n$host_header"
headers="(request-target) date host"
echo "====================================================================================================="
printf '%b' "signing string is $signing_string \n"
signature=`printf '%b' "$signing_string" | openssl dgst -sha256 -sign $privateKeyPath | openssl enc -e -base64 | tr -d '\n'`
printf '%b' "Signed Request is \n$signature\n"
echo "====================================================================================================="
set -x
curl -v -X GET -sS https://$host$rest_api -H "date: $date" -H "Authorization: Signature version=\"1\",keyId=\"$tenancy_ocid/$user_ocid/$fingerprint\",algorithm=\"rsa-sha256\",headers=\"$headers\",signature=\"$signature\"" | json_reformat | grep -e "displayName" -e "lifecycleState" -e "timeCreated" -e "\"id\":"Get dbHome
One would expect that now is the time for applying the Software Image. But wait! You don’t have yet a dbHome ID. The namespace used will be dbHomes. So now let’s get the DbHome ID script named get_dbhome.sh .
#OCID of the tenancy calls are being made in to
tenancy_ocid="ocid1.tenancy.oc1..<unique_id>"
# OCID of the user making the rest call
user_ocid="ocid1.user.oc1..<unique_id>"
# path to the private PEM format key for this user
privateKeyPath="/home/sampleuser/.oci/oci_api_key.pem"
# fingerprint of the private key for this user
fingerprint="99:aa:....."
# The REST api you want to call, with any required paramters.
rest_api="/20160918/dbHomes?compartmentId=ocid1.compartment.oc1..<unique_id>"
# The host you want to make the call against
host="database.eu-amsterdam-1.oraclecloud..."
####################################################################################
date=`date -u "+%a, %d %h %Y %H:%M:%S GMT"`
date_header="date: $date"
host_header="host: $host"
request_target="(request-target): get $rest_api"
# note the order of items. The order in the signing_string matches the order in the headers
signing_string="$request_target\n$date_header\n$host_header"
headers="(request-target) date host"
echo "====================================================================================================="
printf '%b' "signing string is $signing_string \n"
signature=`printf '%b' "$signing_string" | openssl dgst -sha256 -sign $privateKeyPath | openssl enc -e -base64 | tr -d '\n'`
printf '%b' "Signed Request is \n$signature\n"
echo "====================================================================================================="
set -x
curl -v -X GET -sS https://$host$rest_api -H "date: $date" -H "Authorization: Signature version=\"1\",keyId=\"$tenancy_ocid/$user_ocid/$fingerprint\",algorithm=\"rsa-sha256\",headers=\"$headers\",signature=\"$signature\"" | json_reformat | grep -e "displayName" -e "\"id\":" -e "dbVersion" -e "dbSystemId" -e "lifecycleState"Apply custom Software Image (Finally!)
We have collected all information we need for the final step. Apply or upgrading dbHome is an HTTP PUT operation. Here it will be again dbHomes namespace but with operation update. It also requires some data to be delivered to the endpoint hence we need to create two scripts. For the HTTP call we create upgrade_dbhome.sh file. The file is parametrized which means it is expecting one parameter and that is the dbHome ID which we got in the previous step. We then call the script like this ./upgrade_dbhome.sh <dbHomeId>
#OCID of the tenancy calls are being made in to
tenancy_ocid="ocid1.tenancy.oc1..<unique_id>"
# OCID of the user making the rest call
user_ocid="ocid1.user.oc1..<unique_id>"
# path to the private PEM format key for this user
privateKeyPath="/home/sampleuser/.oci/oci_api_key.pem"
# fingerprint of the private key for this user
fingerprint="99:aa:....."# The REST api you want to call, with any required paramters.
rest_api="/20160918/dbHomes/$1"# The host you want to make the call against
host="database.eu-amsterdam-1.oraclecloud.com"# the json file containing the data you want to POST to the rest endpoint
body="./upgrade_dbhome.json"
##################################################################################### extra headers required for a POST/PUT request
body_arg=(--data-binary @${body})
content_sha256="$(openssl dgst -binary -sha256 < $body | openssl enc -e -base64)";
content_sha256_header="x-content-sha256: $content_sha256"
content_length="$(wc -c < $body | xargs)";
content_length_header="content-length: $content_length"
headers="(request-target) date host"
# add on the extra fields required for a POST/PUT
headers=$headers" x-content-sha256 content-type content-length"
content_type_header="content-type: application/json";date=`date -u "+%a, %d %h %Y %H:%M:%S GMT"`
date_header="date: $date"
host_header="host: $host"
request_target="(request-target): put $rest_api"# note the order of items. The order in the signing_string matches the order in the headers, including the extra POST fields
signing_string="$request_target\n$date_header\n$host_header"
# add on the extra fields required for a POST/PUT
signing_string="$signing_string\n$content_sha256_header\n$content_type_header\n$content_length_header"echo "====================================================================================================="
printf '%b' "signing string is $signing_string \n"
signature=`printf '%b' "$signing_string" | openssl dgst -sha256 -sign $privateKeyPath | openssl enc -e -base64 | tr -d '\n'`
printf '%b' "Signed Request is \n$signature\n"echo "====================================================================================================="
set -x
curl -v -X PUT --data-binary "@upgrade_dbhome.json" -sS https://$host$rest_api -H "date: $date" -H "x-content-sha256: $content_sha256" -H "content-type: application/json" -H "content-length: $content_length" -H "Authorization: Signature version=\"1\",keyId=\"$tenancy_ocid/$user_ocid/$fingerprint\",algorithm=\"rsa-sha256\",headers=\"$headers\",signature=\"$signature\""
But before we will run the script we need to prepare the payload. Let’s create file upgrade_dbhome.json with simple content holding custom Software Image ID and the action. Action can be either “PRECHECK” or “APPLY”.
{
"dbVersion":{
"databaseSoftwareImageId":"ocid1.databasesoftwareimage.oc1.eu-amsterdam-1.<unique_id>",
"action":"APPLY"
}
}The end
As a very last step you can check the that your dbHome is being updated using get_dbhome.sh script. During the upgrade process you can also use dbcli tool on the DB system being patched to get information about ongoing patching. When all succeeded don’t forget to apply also datapatch.
