Riding on two horses – ODA Database Patching 

Little Background

Recently I have been working with ODA 12.1.2.3.0 with 2 nodes, but in our environment, the first node of ODA was used for production databases, whereas the second node was used for DEV databases. That means  if I reboot the ODA2, there will be  no impact on production and vice versa. There are three databases  running on each node with the version 12.1.0.2.

Situation

There were thousands of jobs (141439 ) running on DEV databases (ODA2 node).  One of the database consumed lot of resources. There was  no response from the database starting with ”AQ$_PLSQL_NTFN_%” via SYS user. This was officially identified as Bug 21665897 : FIX FOR BUG 14712567 CAUSES LARGE NUMBERS OF AQ$_PLSQL_NTFN_% TO BE SPAWNED. Hence, we had to apply the Patch 21665897 in order to fix them.

Patching

In this blog, I am not going show how to patch instead I will highlight some important points based on my experience.  As per thumb rule, if we apply patch on first node (GI or RDBMS home), then it is recommended to have same level of patch set or patches on other nodes in the cluster for consistency.

As mentioned above, in our setup both GI and RDBMS homes running on both nodes have the version of 12.1.0.2. The production databases run on ODA1 and the Development databases run on ODA2. We have to apply the patch on development node (ODA2) RDBMS to fix this listed bug.

Goal

  • No patches to be applied on GI Home either on ODA1 or ODA2
  • No Patches to be applied on RDBMS Home on ODA1 production databases
  • Patch to be applied only on RDBMS Home of ODA2.

Now the conflict is how the patching can impact the production database? if we keep different level of patches? However after reviewing readme and stuff and also received some great inputs from Mike Dietrich on this situation and his feedback and recently he wrote a blog post on the same and you can read here.

 Nassyam,

the GI version must be the same or higher than the resources it manages up to the 4th digit. The 5th digit can be higher for the database. I have actually several customers who are happy with lets say GI PSU Apr 2016 but use the most recent BPs for the database. That is allowed and does not cause any issues (or I haven’t seen any so far).

And you are right, as soon as you patch GI on the development node you should keep this in line with the production node as well. I think we say officially you can run for 24 hours or less in a mixed configuration but you should avoid this by all means.

Hope this helps – cheers
Mike

Well, that’s clear but we are not going to apply the patch on production database even within 24 hours, However the Oracle Homes or not either shared nor they are clustered databases, hence it is not going to conflict and that’s assumption and determination. 🙂

Few Important Points

I’ve considered as few of the important points in my view while patching.

  • Use opatch -local even though your database is single node.
  • Related to my Bug the AQ$_NTFN jobs will be started automatically with thousands of sessions during datapatch, hence changed the job_queue_processes to “0”.
  • Prior to datapatch if the database is with PDB, then the PDB’s should be in open & Upgrade mode.
  • Run the datapatch in all databases of OH.

Probably there are many more precautions and here i listed few of them what i’ve faced. Happy Reading. 🙂