Patching & Upgrade

Automate Patching and Upgrade your Cloud Databases using OCI CLI

Introduction

Patching and Upgrade your Oracle Databases in the Oracle Cloud is as easy as pushing the button in the web-based Cloud Console. However, if you want to schedule the task, and especially if you are planning to patch and upgrade tens or maybe hundreds of databases, then the OCI CLI is the tool of choice.

Do NOT confuse the OCI CLI with the dbcli or dbaascli tools, which are available locally on the database compute machines and can only manage the local databases on that specific system. Have a look at this blog post for an overview about the different CLIs in the Oracle Cloud.

Patching and upgrade via OCI CLI is available for Virtual Machine, Bare Metal Machine, and Exadata DB Systems. In this blog, for the sake of simplicity, we’ll use VM DB Systems. The process for Exadata is slightly different, as it uses out of place patching by moving the database to another database home. But it is still very easy. I’ll cover that in an extra blog.

VM DB Systems allow creating only one database home with one database that is the same version as the database home.

The Environment

I provisioned a VM DB System with version 18.11, so I can patch it first to 18.12, and then upgrade to 19c. A direct upgarde from 18.11 to 19c is possible. I’ll patch it to 18.12 first only for the demo purpose of this blog.

Each cloud resource has an Oracle Cloud Identifier (OCID), that is displayed on the resources’ details page. But as we want to do everything programmatically using the CLI, this is how we get the OCIDs for all our databases. Fist, list all compartment OCIDs:

oci iam compartment list --all --compartment-id-in-subtree true | jq '.data[]."id"'

Then, for each compartment, list all database OCIDs:

oci db database list --compartment-id $COMPARTMENT_OCID | jq '.data[]."id"'

Using FOR loops in shell to iterate through all compartments and databases:

ALL_COMPARTMENT_OCIDS=$(oci iam compartment list --all --compartment-id-in-subtree true | jq '.data[]."id"')
for COMPARTMENT_OCID in $ALL_COMPARTMENT_OCIDS
do
	ALL_DATABASE_OCIDS=$(oci db database list --compartment-id $COMPARTMENT_OCID | jq '.data[]."id"')
	for DATABASE_OCID in $ALL_DATABASE_OCIDS
	do
		echo $DATABASE_OCID
		#patch/upgrade precheck
		#patch/upgrade apply
	done
done

Patching

List the available patches for the database:

oci db patch list by-database --database-id $DATABASE_OCID --all

Using OCI CLI, run a precheck:

PATCH_OCID=ocid1.dbpatch.oc1.eu-frankfurt-1..........ar6q
oci db database patch --database-id $DATABASE_OCID --patch-id $PATCH_OCID --patch-action PRECHECK

Check the status:

oci db patch-history list by-database --database-id $DATABASE_OCID | jq '.data[0]."lifecycle-state"'

First, you’ll get “IN_PROGRESS”. Wait until you get the status of “SUCCEEDED”. Hopefully.

The status in the console also gets updated immediately representing the current status of “UPDATING”.

After the pre-check was successful, apply the patch by using “APPLY” instead of “PRECHECK”:

oci db database patch --database-id $DATABASE_OCID --patch-id $PATCH_OCID --patch-action APPLY

The APPLY also runs a precheck automatically beforehand. After the patch is successfully applied, you’ll get the status of “SUCCEEDED”:

oci db patch-history list by-database --database-id $DATABASE_OCID | jq '.data[0]."lifecycle-state"'

The web console now shows version 18.12

Upgrade

Only upgrades to 19.7, 19.8, and 19.9 are possible at the moment. Using 19.0.0.0 always upgrades your database to the latest 19c RU available.

Using OCI CLI, run precheck:

oci db database upgrade-with-db-version --database-id $DATABASE_OCID --db-version 19.0.0.0 --action PRECHECK

Check the status:

oci db database list-upgrade-history --database-id $DATABASE_OCID --sort-by TIMESTARTED --sort-order DESC | jq '.data[0]."lifecycle-state"'

After the precheck was successful, run the actual upgrade using “UPGRADE” instead of “PRECHECK”:

oci db database upgrade-with-db-version --database-id $DATABASE_OCID --db-version 19.0.0.0 --action UPGRADE

Again, check the status:

oci db database list-upgrade-history --database-id $DATABASE_OCID --sort-by TIMESTARTED --sort-order DESC | jq '.data[0]."lifecycle-state"'

The web console now shows version 19.9.

In case your upgrade failed, you can fallback using the following command:

oci db database upgrade-rollback --database-id $DATABASE_OCID

As Flashback Database technology is used, the rollback is only available for Enterprise Edition databases.

Details & Log Information

If you want to get more detailed information about the process, this is where the dbcli comes into play. On the local DB machine, as root, run:

dbcli list-jobs
dbcli describe-job -i <job-id> #very probably the last one in the list

For even more details, have a look at the DCS agent log file:

tail -f /opt/oracle/dcs/log/dcs-agent.log

Further Reading

Would you like to get notified when the next post is published?