(For more resources related to this topic, see here.)
We have seen how the provisioning capabilities of the Oracle Enterprise Manager’s Database Lifecycle Management (DBLM) Pack enable you to deploy fully patched Oracle Database homes and databases, as replicas of the gold copy in the Software Library of Enterprise Manager. However, nothing placed in production should be treated as static. Software changes in development cycles, enhancements take place, or security/functional issues are found. For almost anything in the IT world, new patches are bound to be released. These will also need to be applied to production, testing, reporting, staging, and development environments in the data center on an ongoing basis.
For the database side of things, Oracle releases quarterly a combination of security fixes known as the Critical Patch Update (CPU). Other patches are bundled together and released every quarter in the form of a Patch Set Update (PSU), and this also includes the CPU for that quarter.
Oracle strongly recommends applying either the PSU or the CPU every calendar quarter. If you prefer to apply the CPU, continue doing so. If you wish to move to the PSU, you can do so, but in that case continue only with the PSU.
The quarterly patching requirement, as a direct recommendation from Oracle, is followed by many companies that prefer to have their databases secured with the latest security fixes. This underscores the importance of patching.
However, if there are hundreds of development, testing, staging, and production databases in the data center to be patched, the situation quickly turns into a major manual exercise every three months. DBAs and their managers start planning for the patch exercise in advance, and a lot of resources are allocated to make it happen—with the administrators working on each database serially, at times overnight and at times over the weekend.
There are a number of steps involved in patching each database, such as locating the appropriate patch in My Oracle Support (MOS), downloading the patch, transferring it to each of the target servers, upgrading the OPATCH facility in each Oracle home, shutting down the databases and listeners running from that home, applying the patch, starting each of the databases in restricted mode, applying any supplied SQL scripts, restarting the databases in normal mode, and checking the patch inventory.
These steps have to be manually repeated on every database home on every server, and on every database in that home. Dull repetition of these steps in patching the hundreds of servers in a data center is a very monotonous task, and it can lead to an increase in human errors.
To avoid these issues inherent in manual patching, some companies decide not to apply the quarterly patches on their databases. They wait for a year, or a couple of years before they consider patching, and some even prefer to apply year-old patches instead of the latest patches. This is counter-productive and leads to their databases being insecure and vulnerable to attacks, since the latest recommended CPUs from Oracle have not been applied.
What then is the solution, to convince these companies to apply patches regularly? If the patching process can be mostly automated (but still under the control of the DBAs), it would reduce the quarterly patching effort to a great extent. Companies would then have the confidence that their existing team of DBAs would be able to manage the patching of hundreds of databases in a controlled and automated manner, keeping human error to a minimum.
The Database Lifecycle Management Pack of Enterprise Manager Cloud Control 12c is able to achieve this by using its Patch Automation capability. We will now look into Patch Automation and the close integration of Enterprise Manager with My Oracle Support.
By navigating to Enterprise | Summary, a Patch Recommendations section will be visible in the lower left-hand corner, as shown in the following screenshot:
The graph displays either the Classification output of the recommended patches, or the Target Type output. Currently for this system, more than five security patches are recommended as can be seen in this graph. This recommendation has been derived via a connection to My Oracle Support (the OMS can be connected either directly to the Internet, or by using a proxy server). Target configuration information is collected by the Enterprise Manager Agent and is stored in the Configuration Management Database (CMDB) within the repository. This configuration information is collated regularly by the Enterprise Manager’s Harvester process and pushed to My Oracle Support.
Thus, configuration information about your targets is known to My Oracle Support, and it is able to recommend appropriate patches as and when they are released. However, the recommended patch engine also runs within Enterprise Manager 12c at your site, working off the configuration data in the CMDB in Enterprise Manager, so recommendations can in fact be achieved without the configuration having been uploaded on MOS by the Harvester (this upload is more useful now for other purposes, such as attaching configuration details during SR creation).
It is also possible to get metadata about the latest available patches from My Oracle Support in offline mode, but more manual steps are involved in this case, so Internet connectivity is recommended to get the full benefits of Enterprise Manager’s integration with My Oracle Support.
To view the details about the patches, click on the All Recommendations link or on the graph itself. This connects to My Oracle Support (you may be asked to log in to your company-specific MOS account) and brings up the list of the patches in the Patch Recommendations section.
The database (and other types of) targets managed by the Enterprise Manager system are displayed on the screen, along with the recommended CPU (or other) patches. We select the CPU July patch for our saiprod database. This displays the details about the patch in the section in the lower part of the screen.
We can see the list Bugs Resolved by This Patch, the Last Updated date and Size of the patch and also Read Me—which has important information about the patch.
The number of total downloads for this patch is visible, as is the Community Discussion on this patch in the Oracle forums. You can add your own comment for this patch, if required, by selecting Reply to the Discussion.
Thus, at a glance, you can find out how popular the patch is (number of downloads) and any experience of other Oracle DBAs regarding this patch—whether positive or negative.
You can view the information about the patch by clicking on the Full Screen button. You can download the patch either to the Software Library in Enterprise Manager or to your desktop. Finally, you can directly add this patch to a new or existing patch plan, which we will do next.
Go to Add to Plan | Add to New, and enter Plan Name as Sainath_patchplan. Then click on Create Plan. If you would like to add multiple patches to the plan, select both the patches first and then add to the plan. (You can also add patches later to the plan).
After the plan is created, click on View Plan. This brings up the following screen:
A patch plan is nothing but a collection of patches that can be applied as a group to one or more targets. On the Create Plan page that appears, there are five steps that can be seen in the left-hand pane. By default, the second step appears first. In this step, you can see all the patches that have been added to the plan.
It is possible to include more patches by clicking on the Add Patch… button. Besides the ability to manually add a patch to this list, the analysis process may also result in additional patches being added to the plan.
If you click on the first step, Plan Information, you can put in a description for this plan. You can also change the plan permissions, either Full or View, for various Enterprise Manager roles. Note that the Full permission allows the role to validate the plan, however, the View permission does not allow validation.
Move to step 3, Deployment Options. The following screen appears.
A new mechanism for patching has been provided in the Enterprise Manager Cloud Control 12c version, known as out-of-place patching. This is now the recommended method and creates a new Oracle home which is then patched while the previous home is still operational. All this is done using an out of the box deployment procedure in Enterprise Manager.
Using this mechanism means that the only downtime will take place when the databases from the previous home are switched to run from the new home. If there is any issue with the database patch, you can switch back to the previous unpatched home since it is still available. So, patch rollback is a lot faster.
Also, if there are multiple databases running in the previous home, you can decide which ones to switch to the new patched home. This is obviously an advantage, otherwise you would be forced to simultaneously patch all the databases in a home. A disadvantage of this method would be the space requirements for a duplicate home. Also, if proper housekeeping is not carried out later on, it can lead to a proliferation of Oracle homes on a server where patches are being applied regularly using this mechanism.
This kind of selective patching and minimal downtime is not possible if you use the previously available method of in-place patching, which uses a separate deployment procedure to shut down all databases running from an Oracle home before applying the patches on the same home. The databases can only be restarted normally after the patching process is over, and this obviously takes more downtime and affects all databases in a home.
Depending on the method you choose, the appropriate deployment procedure will be automatically selected and used.
We will now use the out-of-place method in this patch plan. On the Step 3: Deployment Options page, make sure the Out of Place (Recommended) option is selected. Then click on Create New Location.
Type in the name and location of the new Oracle home, and click on the Validate button. This checks the Oracle home path on the Target server. After this is done, click on the Create button.
The deployment options of the patch plan are successfully updated, and the new home appears on the Step 3 page.
Click on the Credentials tab. Here you need to select or enter the normal and privileged credentials for the Oracle home.
Click on the Next button. This moves us to step 4, the Validation step.
Click on the Analyze button. A job to perform prepatching analysis is started in the background. This will compare the installed software and patches on the targets with the new patches you have selected in your plan, and attempt to validate them. This validation may take a few minutes to complete, since it also checks the Oracle home for readiness, computes the space requirements for the home, and conducts other checks such as cluster node connectivity (if you are patching a RAC database).
If you drill down to the analysis job itself by clicking on Show Detailed Progress here, you can see that it does a number of checks to validate if the targets are supported for patching, verifies the normal and super user credentials of the Oracle home, verifies the target tools, commands, and permissions, upgrades OPATCH to the latest version, stages the selected patches to Oracle homes, and then runs the prerequisite checks including those for cloning an Oracle home. If the prerequisite checks succeed, the analysis job skips the remaining steps and stops at this point with a successful status. The patch is seen as Ready for Deployment.
If there are any issues, they will show up at this point. For example, if there is a conflict with any of the patches, a replacement patch or a merge patch may be suggested. If there is no replacement or merge patch and you want to request such a patch, it will allow you to make the request directly from the screen.
If you are applying a PSU and the CPU for that same release is already applied to the Oracle home, for example, July 2011 CPU, then because the PSU is a superset of the CPU, the MOS analysis will stop and mention that the existing patch fixes the issues. Such a message can be seen in the Informational Messages section of the Validation page.
In our case, the patch is Ready for Deployment. At this point, you can move directly to step 5, Review & Deploy, by clicking on it in the left-hand side pane.
On the Review & Deploy page, the patch plan is described in detail along with Impacted Targets. Along with the database that is in the patch plan, a new impacted target has been found by the analysis process and added to the list of impacted targets. This is the listener that is running from the home that is to be cloned and patched.
The patches that are to be applied are also listed on this review page, in our case the CPUJUL2011 patch is shown with the status Conflict Free.
The deployment procedure that will be used is Clone and Patch Oracle Database, since out-of-place patching is being used, and all instances and listeners running in the previous Oracle home are being switched to the new home.
Click on the Prepare button. The status on the screen changes to Preparation in Progress. A job for preparation of the out-of-place patching starts, including cloning of the original Oracle home and applying the patches to the cloned home. No downtime is required while this job is running; it can happen in the background.
This preparation phase is like a pre-deploy and is only possible in the case of out-of-place patching, whereas in the case of in-place patching, there is no Prepare button and you deploy straightaway.
Clicking on Show Detailed Progress here opens a new window showing the job details.
When the preparation job has successfully completed (after about two hours in our virtual machine), we can see that it performs the cloning of the Oracle home, applies the patches on the new home, validates the patches, runs the post patch scripts, and then skips all the remaining steps. It also collects target properties for the Oracle home in order to refresh the configurations in Enterprise Manager.
The Review & Deploy page now shows Preparation Successful!. The plan is now ready to be deployed.
Click on the Deploy button. The status on the screen changes to Deployment in Progress. A job for deployment of the out-of-place patching starts.
At this time, downtime will be required since the database instances using the previous Oracle home will be shut down and switched across.
The deploy job successfully completes (after about 21 minutes in our virtual machine); we can see that it works iteratively over the list of hosts and Oracle homes in the patch plan. It starts a blackout for the database instances in the Oracle home (so that no alerts are raised), stops the instances, migrates them to the cloned Oracle home, starts them in upgrade mode, applies SQL scripts to patch the instance, applies post-SQL scripts, and then restarts the database in normal mode.
The deploy job applies other SQL scripts and recompiles invalid objects (except in the case of patch sets). It then migrates the listener from the previous Oracle home using the Network Configuration Assistant (NetCA), updates the Target properties, stops the blackout, and detaches the previous Oracle home. Finally, the configuration information of the cloned Oracle home is refreshed.
The Review & Deploy page of the patch plan now shows the status of Deployment Successful!, as can be seen in the following screenshot:
On the Deployment Successful page, it is possible to click on Save as Template at the bottom of the screen in order to save a patch plan as a plan template. The patch plan should be successfully analyzed and deployable, or successfully deployed, before it can be saved as a template.
The plan template, when thus created, will not have any targets included, and such a template can then be used to apply the successful patch plan to multiple other targets. Inside the plan template, the Create Plan button is used to create a new plan based on this template, and this can be done repeatedly for multiple targets.
Go to Enterprise | Provisioning and Patching | Patches & Updates; this screen displays a list of all the patch plans and plan templates that have been created. The successfully deployed Sainath_patchplan and the new patch plan template also shows up here.
To see a list of the saved patches in the Software Library, go to Enterprise | Provisioning and Patching | Saved Patches. This brings up the following screen:
This page also allows you to manually upload patches to the Software Library. This scenario is mostly used when there is no connection to the Internet (either direct or via a proxy server) from the Enterprise Manager OMS servers, and consequently you need to download the patches manually.
For more details on setting up the offline mode and downloading the patch recommendations and latest patch information in the form of XML files from My Oracle Support, please refer to Oracle Enterprise Manager Lifecycle Management Administrator’s Guide 12c Release 2 (126.96.36.199) at the following URL:
The new version of Enterprise Manager Cloud Control 12c supplies out of the box administrator roles specifically for patching. These roles are EM_PATCH_ ADMINISTRATOR, EM_PATCH_DESIGNER, and EM_PATCH_OPERATOR. You need to grant these roles to appropriate administrators.
Move to Setup | Security | Roles. On this page, search for the roles specifically meant for patching. The three roles appear as follows:
The EM_PATCH_ADMINISTRATOR role can create, edit, deploy, or delete any patch plan and can also grant privileges to other administrators after creating them. This role has full privileges on any patch plan or patch template in the Enterprise Manager system and maintains the patching infrastructure.
The EM_PATCH_DESIGNER role normally identifies patches to be used in the patching cycle across development, testing, and production. This role would be the one of the senior DBA in real life. The patch designer creates patch plans and plan templates, and grants privileges for these plan templates to the EM_PATCH_ OPERATOR role.
As an example, the patch designer will select a set of recommended and other manually selected patches for an Oracle 11g database and create a patch plan. This role will then test the patching process in a development environment, and save the successfully analyzed or deployed patch plan as a plan template. The patch designer will then publish the Oracle 11g database patching plan template to the patch operator—probably the junior DBA or application DBA in real life.
Next, the patch operator creates new patch plans using the template (but cannot create a template), and adds a different list of targets, such as other Oracle 11g databases in the test, staging, or production environment. This role then schedules the deployment of the patches to all these environments—using the same template again and again.
Enterprise Manager Cloud Control 12 c allows automation of the tedious patching procedure used in many organizations today, to patch their Oracle databases and servers. This is achieved via the Database Lifecycle Management Pack, which is one of the main licensable packs of Enterprise Manager.
Sophisticated Deployment Procedures are provided out of the box to fulfill many different types of patching tasks, and this helps you to achieve mass patching of multiple targets with multiple patches in a fully automated manner, thus making tremendous savings in administrative time and effort. Some companies have estimated savings of up to 98 percent in patching tasks in their data centers. Different types of patches can be applied in this manner, including CPUs, PSUs, Patch sets and other one-off patches. Different versions of databases are supported, such as 9i, 10 g and 11 g. For the first time, the upgrade of single-instance databases is also possible via Enterprise Manager Cloud Control 12c.
There is full integration of the patching capabilities of Enterprise Manager with My Oracle Support (MOS). The support site retains the configuration of all the components managed by Enterprise Manager inside the company. Since the current version and patch information of the components is known, My Oracle Support is able to provide appropriate patch recommendations for many targets, including the latest security fixes. This ensures that the company is up to date with regards to security protection.
A full division of roles is available, such as Patch Administrator, Designer, and Operator. It is possible to take the My Oracle Support recommendations, select patches for targets, put them into a patch plan, deploy the patch plan and then create a plan template from it. The template can then be published to any operator who can then create their own patch plans for other targets. In this way patching can be tested, verified, and then pushed to production.
In all, Enterprise Manager Cloud Control 12 c offers valuable automation methods for Mass Patching, allowing Administrators to ensure that their systems have the latest security patches, and enabling them to control the application of patches on development, test, and production servers from the centralized location of the Software Library.
Resources for Article :
- Author Podcast – Bob Griesemer on Oracle Warehouse Builder 11g [Article]
- Managing Oracle Business Intelligence [Article]
- Author Podcast – Ronald Rood discusses the birth of Oracle Scheduler [Article]