Database Patching Tips #JoelKallmanDay
Bonus Step - What a Wonderful Patch (Patching Tips)
I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com
Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there.
Failure to patch your database software may lead to losses of data that can cost money and reputation. That is the point of security. Also there are many other benefits, including adding features, and fixing bugs that make your applications run slow or not work as intended.
Every database administrator should have a patching strategy that suits up with his/her organization. I will share my tips that can also be useful for others and may not be suitable for some. Because every organization has their own constraints and unique environment.
Generally Applicable
* First know your database environment and identify all the features you are using actively. Know your connected clients/applications capabilities and how they make use of databases.
* Define a regularly scheduled routine to patch your databases. Start patching with your dataguard environments, test the new version with developers and inspect alert.logs and crs daemons trace files. Compare your before and after log files. You should know your usual warning/info messages and should catch new ones with no delay after patching.
* It is advised to apply patches and critical security fixes immediately(monthly released), but that is not applicable in all organizations, some organizations have exhausting change management procedures. In COBIT 2019 Framework, Technology Adoption Strategy is defined in three categories.( First mover, Follower and Slow Adopter) I would rather to go with a Follower Strategy and choose to follow Release Updates with one version behind from latest one, and apply latest MRP (Monthly Release Update) on top. Your patching strategy should be in alignment with the direction set by your organization governance.
* Inform your clients/end users, superiors and application developers for your planned time even if it requires no downtime as it may end up with unexpected results and may require a rollback. For that reason, have a good rollback plan. A rollback plan allows you to quickly reverse the undesirable effects of patching and go back to the pre-patched version.
* Do one change at a time. For example, If you are planning to reset some hidden parameters set in your database, do not apply this change just after applying patches. Wait some time, monitor your environment and make sure that everything is working smoothly and then plan new change.
* Sometimes certain servers or apps cannot be upgraded or patched to maintain compatibility with a critical application that is in use. In these situations, you should have an alternative strategy for securing that systems from the vulnerability left exposed by the inability to patch it.
What to Read Before Patching
* ALWAYS READMEs. Everytime before you start patching, you should read readmes. With every version, something changes. For example One version OJVM requires STARTUP UPGRADE, one version does not. One one-off patch requires you to change some files permissions, one does not. Every time When I apply patches to Enterprise Manager Cloud Control product, i got surprised with the different patching commands. (some requires opatch apply, some requires opatch napply, some requires bitonly, some is through java, some through OMSPatcher and much more..) You should read not only Release Update readmes, but also all one-offs and bundle patch readmes. You should know as much as possible about patching in advance, but you should start patching as you know nothing about patching prior. As Oracle guru Jonathan Lewis says - Everything Changes Constantly - a wonderful blog post about how things change.
* Oracle Database Release Notes (For version 19 click). You can find Open Bugs Affecting Your OS, Known Issues and Bugs for your OS, Issues Affecting All Platforms and much more.
* Oracle Database Upgrade Guide (For version 19 click). Chapter 10 Oracle Database Changes, Desupports, and Deprecations is a must-read. You will find Behavior Changes, Desupported Features, Desupported Parameters, Deprecated Features and Deprecated Initialization Parameters.
* Oracle Database New Features Guide (For version 19 click) This one describes the features that are new in Oracle Database 19c Release Updates (RUs). You update to the latest version, what you can benefit is listed in here.
* Blogs about patching. Whom to follow? There are lots of awesome bloggers about Oracle products. When it comes to patching, First and Foremost I would advise Mike Dietrich and Daniel Overby Hansen as they are both from Product Management of Database Upgrade, Migration and Patching.
Which Patches to Apply
It DEPENDS and it is up to you. You can patch proactively or reactively. You can take action before hitting the bug or you can apply patch when you face a bug. That decision is too much subjective. You may apply every patch you see in Oracle Database 19c Important Recommended One-off Patches (Doc ID 555.1), and you may not.
My motto is "Less is More". That is subjective again. What describes less? My database team are extremely familiar with our databases characteristics and performance and follow alert.logs daily. Our environment serves in an air-gapped network. These points are also effective in decision taking. I decide as follows:
* For 19c version ,
* I read and examine thoroughly all the documents above. I eliminate one-offs as much as possible. The ones related with features we are not using effectively and symptoms not matching with our environments are good candidates to eliminate. Merge Patches with the most amount of "Bugs Resolved by this Patch" are good candidates to consider whether it worths applying, mostly I choose not to apply.
* What I apply directly with no hesitation.
* Latest MRP
* OJVM
* DataPump Bundle Patch. I use Data Pump Recommended Proactive Patches For 19.10 and Above (Doc ID 2819284.1) document to detect the latest one.
* Spatial Bundle Patch. I use What Is The Latest Spatial Patch Bundle for 12c and Higher Databases?(Doc ID 2514624.1) document to detect the latest one.
* Why should apply OJVM with no hesitation?
* Spatial Data Option(SDO), Oracle XDK(XDK) and Oracle Multimedia(ORDIM) components are dependent on OJVM. If you are using one of them, then you can't remove OJVM. If you can't remove OJVM, then you should upgrade it. Because Critical Patch Updates Affected Product lists usually involve Java VM. Vulnerability tools report lots of vulnerabilities related to it. You can have more information about it in "Is Oracle Java Virtual Machine (OJVM) Used In My Database?" blog by Daniel Overby Hansen.
* Do you need to apply OJVM patches to Grid Infrastructure?
Answer is NO. More information in "Do you need to apply OJVM patches to Grid Infrastructure?
* Why should apply DataPump Bundle Patch with no hesitation?
* Here is what Mike Dietrich says about it in "Apply the Data Pump Bundle Patch – non-rolling but online?"
You may ask yourself “Oh, another Oracle patch. Why should I apply this one now?“. I can see the question coming asking if this is really necessary for an occasional Data Pump user. The clear answer is: Yes, it is. It does not only fix a lot of issues, not only for obvious Data Pump but also for DBMS_METADATA and Transportable Tablespaces. But it also speeds up patching whenever dpload.sql is involved dramatically. We saw patching times of over 1 hour when datapatch gets executed just with 4 PDBs – and it went down to 4 minutes with the Bundle Patch in place. There are a ton of optimizations you’d only recognize if we’d go through all the patches included.
But overall, if you don’t use Data Pump or any of the above-mentioned functions, if you don’t care about patching duration, you may not need this one. It is optional, and not a must. But it is recommended to apply it for most customers.
* Why should apply Spatial Bundle Patch with no hesitation?
* We are using it effectively. We don't want to miss a thing about it.
In Place or Out-of-Place?
Tim Hall did a survey about it.
When patching your production Oracle GI/DB installations, Which method do you use?
In-Place = Current ORACLE_HOME
Out-Of-Place = New ORACLE_HOME
By a small margin, In-Place Patching is preferred more. (%55,4 vs %44,6)
* Also I have been using In-Place patching much more, but recently switched to Out-Of-Place method.
* Out-of-place upgrades reduce downtime because you can complete the initial installation and configuration of the upgrade before you take down the existing installation. Out-of-place patch upgrades are easier, safer, and provide faster fallback if needed: If you encounter issues with the new installation, then simply point clients back to the original Oracle Database installation Oracle home, and restart the database.
* Also when you are using In-Place patching, your .patch_storage file under ORACLE_HOME is storing more and more information and every time when you are patching this home, prerequisite checks are taking much more time. Here is a blog post(Cleaning up older patch artifacts – improving opatch performance) about it by Mike Dietrich.
* Out-of-place is the Oracle recommended patching method. It is up to you. But If you are using Fleet Patching, then out-of-place is the only way to go. Make sure that If you have any script referencing the ORACLE_HOME, they will need to be updated. Hopefully, you’ve centralized this into a single environment setup script. Choose a naming standard, stick to it and with every release update just replace the version numbers.
* In my opinion, Use In-Place Patching for one-offs that should be fixed immediately, Use Out-of-Place Patching for regularly scheduled Updates.
* Scalable Methods for Patching
* If you have lots of database servers to patch, Fleet Patching and Provisioning is the way to go. No human errors, minimal downtime, managing centrally, saving your time and much more are possible with it. As Product Manager Daniel says, It will make your life so much easier. More information is available on blog post.
Hope it helps.
Comments
Post a Comment