Patching has always been one of the many joys (or dreads) of Oracle DBAs. ">The effort compounds as the Database sprawl grows and the architecture complexity increases. Additionally, there is an increasing pressure to patch the systems with minimal or zero downtime.
EM12c's Database patch automation functionality provides an end-to-end management starting with proactive patch recommendations (like quarterly PSUs and CPUs), complete pre-flight checker to ensure predictable patching during maintenance window and automation for patching the comprehensive list of products under the Oracle Database family. (
EM12c Patch Automation overview)
With the introduction of "Out of Place" patching in EM12c, the automation feature got a complete overhaul. Over the past 3 years I have seen customers moving to this mode to achieve their automation goals. (What is Out of Place?)
Patching Data guard environments (read as Primary and its corresponding Standby Databases) has always been a challenging task for the DBAs. In addition to handling the differences in steps needed for the configuration, the very nature of its distributed environment and incorporating any additional custom process tasks makes it more demanding. Till now in EM we only supported automation in disparate steps and in “In Place” mode.
Starting 12.1.0.4 (EM12c R4), the support is enhanced to be more tighter, can be done in 'Out of Place' mode, and has new supplemental features to manage the process along with its additional tasks.
In this post we will take a closer look of the data guard stand by patching feature and then dive into a use case, you could try out in your environment.
Oracle Data Guard
Patching using EM12c
Quick Overview:
Patch databases in Data Guard setup
Standalone (P)– Standalone (S)*
RAC (P) – RAC (S)
RAC (P)– Standalone (S)
DB-A(P),DB-B (S) – DB-B(P) , DB-A(S) (common in dev. environments)
Multiple Standby environments
Supports both In-Place and Out of Place methods, for RAC patch in rolling or
parallel modes.
Built in best practices – patch Standby first.
Supports switch over and non switch
over based flows too.
Intelligent to identify patch levels
across primary and standby- run compensatory actions like run SQL on primary when patching
standby’s with switchover.
Advance the
automation with Change Activity Planner integration to handle rollout process
across mass scale.
*(P) - Represents the Primary Database and (S) represents its Standby .
What's Hot?
# 1 Complete End to End Patching:
The patch automation is end to end beginning with pro-active recommendations for the Primary and Standby DBs based on Oracle's quarterly recommended patches like Patch Set Updates (PSU), Grid Infrastructure (GI) PSU and Critical Patch Updates (CPU). As seen in the summary above, the patch automation covers a wide range of Data Guard deployments.
It ranges from simple Primary site with Standalone or RAC DB with Standalone Standby DB to more commonly seen mixed configuration in the development environments like the Oracle Home in a host is shared by a Primary and Standby of the DBs with its corresponding other DB in another host sharing an Oracle Home, and all the way to complex deployments where mission critical DBs with multiple standbys spread across sites.
The automation is configuration aware and executes the right steps needed to patch the Standby and the Primary. For example, it patches standby ~ brings it back in the proper state (like mount and read only) and handles application of the SQL to the Primary. Additionally, as a part of the new enhancements the user is guided to the right way of patching the system to begin with the standby site but allows flexibility to handle cases where the patching starts with the Primary site.
Note the current model is process based, where in patching of Primary and Standby are done in silo's at different time windows giving the administrator opportunity to do switch over and testing as needed. In the future, we will also introduce an option to make this procedural where in complete system can be patched in a single go without any pause. Said that, the process can be well managed using the Change Activity Planner as explained in the later portion of this post.
#2 Built-in best practices
The solution has quite a few gems built-in, let's look at few important ones.
Screenshot of a Patch Plan created for patching the Standby RAC Cluster
a. Standby 1st Patching:
The patch automation follows Oracle's recommendations of patching the Standby first. The solution is intelligent to understand the topology of the databases picked, and its patch levels to guide the user with the right inputs. The examples below show cases it.
a. User starting off with the Standby Database receives thumbs up.
b. If a user starts off with a Primary, it detects if the corresponding Standby Database is patched or not and raises flag.
c. Also in situations where the user picks the new standby after the switch over of Primary after patching the standby, the automation auto pulls the Primary configuration into the patch plan to complete the SQL application.
b. "Out of Place" Patching:
'Out of Place' is a very precious gem in the EM12c's patch automation feature. The feature is extended to patching of the Data Guard databases with Release 4.
Its very important and popular mode of patching for 3 primary reasons:
(i) Reduced or Zero Downtime: Since you are patching the in-active Oracle Home (OH), it directly saves time otherwise spent in applying patching to the existing OH. It gets even better in Clusters where both GI and RAC OH's can be patched in 'Out of Place' with Zero downtime.
(ii) Flexibility in scheduling maintenance: 'Out of Place' patching comes handy when you have a consolidated environment where multiple Databases share a single OH. Most of the times, it's hard for the DBAs to get a common maintenance window from their application colleagues. When you do patching in this mode, the databases with approval for maintenance can be selectively switched to the new OH, leaving the rest of the DBs running in the existing OH without any impact.
(iii) Risk Free: In situation where you find issues post patching, since the patching process is 'out-of-place' the old OH is available to handle these situations. Using the patch automation's 'Switch Back' feature the user can switch the DB back to its old OH restoring normalcy quickly and can introspect the failure without any impact to business.
To Switch or Not to Switch
Typically, after patching the Standby Database it is switched with the Primary, tests are run, after successful tests the new standby is patched and finally the databases are switched back bringing the system back to its original configuration state. Different enterprises opt to switch or not switch during their patching practices based upon various parameters such as the usage, location etc.. You can still automate the patching with or without switching. In some cases like RAC Clusters in primary or both in primary and standby one could choose to patch without switch over, as patching happens in rolling fashion without affecting the data transport between the sites. (The example in the end of the post covers this scenario).
If you opt to do switch over, EM 12c Rel 4 supports automated switch over either from its UI under the Database's menu option :
or via the new emcli option:
Advance Automation - Additional features for Enterprises:
The common question I been asked from the customers is "How do I adapt this automation into my enterprise?". Enterprises normally have large number of environments, typically have few DBAs dedicated to complete the patching effort sometimes they are spread out across the globe, and have custom practices with additional steps involved in the patching practice. Other than the standard practice of moving to command line interface (EMCLI) to ease the automation as you mature, you can leverage couple features available in the automation suite to fit the automation within your enterprise.
Use Patch Templates:
Just as the literal meaning of 'Template', Patch Template's stores the patches and the deployment options used in the testing cycles. User can create a patch template from a successfully deployed patch plan. Patch templates do not store targets.
The lead DBA creates the patch templates after the testing and shares it with the other DBAs. Then the DBA creates a Patch Plan from the templates and adds the target to be patched during their maintenance cylce.
It simplifies the DBA communication by replacing the list of patches and options to choose from the documentation with just the name of the Patch Template to use for the rollout. This also removes human error of missing out the patches, which can happen while copying patches from a document.
Change Activity Planner:
Change Activity Planner (CAP) helps in planning and handling long running process across large scale like patch rollouts. I covered its introduction in the EM12c R3 with this blog post.
We have done quite a few enhancements to Change Activity Planner feature in Release 4. Let's take a look at the top three features that could be leveraged for handling patching of Standby Databases in scale.
As a lead DBA or Engineering:
Use the new graphical interface to weave the tasks involved in patching standby's as followed in your enterprise
CAP now supports Jobs and Deployment Procedures as tasks in addition to Patch Plan and Manual tasks. Add additional tasks like backup jobs from your EM Library or call into Switch backs using jobs or manual steps.
As a DBA or Operations DBA:
• Review
the tasks assigned to you
• Directly Create the Patch Plan from the task , track the progress from the task without having to land in Patching feature.
Example Scenario
Let's take a quick look at an example of how you could leverage EM12c R4 in patching the Clusters in Standby with the quarterly recommended GI PSU (applicable to both GI and RAC layers)
Since cluster environment's both Primary and Standby DBs can be patched in rolling fashion in their silo's without affecting the data transport, you can skip the switch over process.
Hope this was good comprehensive introduction to the new enhanced patch automation support for Data Guard Databases. I encourage you to explore the functionality and provide us with feedback.
References:
Lifecycle Management Guide in OTN Documentation for step by step patching tutorial for different configuration.