3 Things To Consider When Patching Your Database

In a perfect world, DBAs would patch their databases regularly, and what I mean by that is every quarter, every time the quarterly patches come out.

Since we live in a perfectly imperfect world, most often, that is not the case.

You might be patching your database once a year, twice a year, once every two years, or not at all.

It all depends on the circumstances you are in. Often, the decision to patch doesn’t belong to the DBA. And if it is not your decision to take, then no mater how much you want to patch, you won’t be able to.

If you get the chance to patch, what process do you follow? How would you proceed?
I’ve got a few requests from DBAs just like you, who are reading my posts, to write about patching practices and processes, about what steps you would take to patch.

So here it is for all the DBAs that want and can patch their database, a short guide on the things to consider!

1. How To Sell Your Idea To The Business: “We Need To Patch”
2. Things To Consider Before Patching
3. Six Steps To Patch Your Database

1. How To Sell Your Idea To The Business: “We Need To Patch”

If you want the support of the business, you need to have a good sales pitch. You need to be able to sell the idea of patching.
It is not enough to state: “we need to patch because that is what other DBAs do”, or “because databases should be patched”.
The first question that you’ll get is “ok, but WHY?”.

You need to convince the business to spend the money and the effort (which comes down to money) on patching activities.
Think about what benefits will the patching provide, and start from there.

Some of the benefits of regularly patching are:
– being on supported version
– having security vulnerabilities fixed
– having bug fixes in place
– being SOX or CSOX compliant, or any other compliance requirement fulfilled.

Once you get the support from the business, you can start planning your patching activities.

2. Things To Consider Before Patching

When you plan your patching, you need to think about a timeline and a process. There are many questions to ask and find answers to!

How much time do I have to patch all the databases, all the environments?
What environment am I going to start with? Never ever start with the production environment!!!
What resources do I have available?
What are the steps I need to perform before, during and after patching?
What testing needs to be completed?
Who is going to test?

You should patch at least one environment prior to patching production.

The sandbox environment, or the test environment, or however you call the environment before production, is your learning environment.
This environment gives you the opportunity to practice the patch deployment! This environment gives you the chance to make mistakes!
This environment gives you the chance to create your deployment plan for production, and fine tune it, until your plan works seamlesly!!!

Remember, A Good DBA Always Has A Deployment Plan!!!

3. Six Steps To Patch Your Database

A) What Patch Are You Going To Deploy?

The answer seems obvious, but bare with me for a second.
I find it challenging at times to navigate Oracle Support site to find the patch that I need.

Now, answer this honestly, did it ever happened to you that you downloaded the wrong patch and tried to deploy it?
It sure did to me! Somehow I downloaded a database patch for a Solaris operating system, and I needed Linux instead. Good thing OPatch caught the problem and returned an error. It can happen to the best of us!

When I say what patches are you going to deploy, I mean to identify and download the patches that are applicable for your database version, for your specific platform.

B) Prepare The Environment For Patching

How you prepare the environment, is different for every database and every DBA.
Know that most patches require an outage, however the magnitude of the outage will be different, depending on what you are running (RAC, Dataguard, standalone database)

For simplicity, let’s assume the database is taking an outage for patching. In this case, before you start, you need to confirm there are no users connected to the database. This includes Oracle agent connections, or connections from Enterprise Manager. Nobody should be connected beside yourself!

C) Compile invalid objects

I always get a screenshot of the invalid objects before I start patching. This way you know if an object got invalidated by the patch, or if it was invalid to start with.

D) Review and run all the steps outlined in the ReadMe file of the patch you are going to deploy. I am not going into details with each step as these steps could be different for each patch.

One of the required steps is to stop all the processes that are running out of the Oracle Home. Depending on the setup you have you will need to stop processes such as the listener, the database, cluster services, tfa and so one, anything that is running out of the homes you are going to patch.

Steps prior to patch deployment would include verifying the Oracle Inventory is valid, verifying there are no conflicts between patches, verifying the latest version of OPatch,and so on.

Steps to deploy the actual patch are outlined in the Read Me of the patch.

Once patching is complete, you will need to startup the database, and run the post patch scripts provided by Oracle.

In the end startup all the processes you stopped before, the listener, cluster services, tfa and so on.

E) Once your patching is complete, take another screenshot of the invalid objects and compare with the original list you got in step C.
Get your confirmation that the patch was successful, by querying sys.registry$history.

F) Collect all the evidence you need, such as patch logs, opatch outputs and so on. You might need the evidence to prove that you patched.

If you patched your test database first, then now is the time to update the deployment plan you created, and add any steps you missed, or any workarounds you had to come up with.

A good idea is to create a template for applying patches, that you can reuse again and again, with small modifications!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana