Riding on two horses – ODA Database Patching
Recently I have been working with ODA 220.127.116.11.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 18.104.22.168.
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.
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 22.214.171.124. 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.
- 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.
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
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. 🙂