Get a tour of the automated patching capabilities now available to Oracle DBAs.
Patching has always been one of the many joys (or dreads) of Oracle DBAs. Ever since the initial days of Oracle, patches have been published by Oracle Support, downloaded by DBAs (very early on, via posted floppy disks or CDs), and then applied to the target database—whether to fix minor database bugs; apply security fixes; or upgrade the database from one patch version to the other, such as from 7.3.2 to 7.3.4, 8.1.5 to 8.1.6, or 9.2.0.4 to 9.2.0.6.
With Critical Patch Updates (CPUs) now published quarterly by Oracle and with several companies seeking to make application of CPUs mandatory for all their databases as part of their corporate security policy, the onus is on DBAs to make sure the myriad databases in their domain are patched in a controlled and timely manner.
In the case of Oracle Applications, patching takes on a whole new meaning: Oracle E-Business Suite patch sets are a superset of database patch sets; frequently take many hours to apply; and, in many cases, need restarting if their worker processes fail. These worker processes perform parallel patch-related updates to the E-Business PL/SQL code, and were one of the first innovative uses of multitasking for applying large patches. But this still translates into late nights for many DBAs who apply the patches manually and are obliged to keep checking for errors, in case they have to restart the patch.
Oracle Real Application Clusters (Oracle RAC) came along in Oracle9i Database. Oracle RAC is Oracle’s active-active clustered database, so patch application takes on a new dimension—the patches have to be applied to each node at the Oracle Clusterware level and then at the Oracle Database level, adding to the complexity of the manual steps. Oracle Clusterware is available in Oracle RAC 9i on the Linux platform and in Oracle RAC 10g on all platforms, so Oracle Clusterware is now also included in the patching effort.
Combine all of this with the ever-increasing number of databases in a DBA’s domain—in many midsize and large corporations, there are hundreds and even thousands of development, test, and production databases—and you have the recipe for grinding, manual, mind-numbing steps repeated in an extremely monotonous fashion, leaving the doors open for eventual human error. The free-thinking human mind has never liked machinelike monotony, and sooner or later, subconsciously or not, it is bound to commit an error in any repetitive manual process. The fact is that DBAs are not machines.
All in all, DBAs did have a dreary future filled with continually increasing patching drudgery until Oracle Enterprise Manager came along. With a brand-new interface and architecture in Oracle Enterprise Manager 10g Grid Control Release 5, the prime aim was to ease the management load for DBAs worldwide.
Oracle Enterprise Manager Grid Control achieved this by streamlining and automating many day-to-day DBA tasks—performance management (diagnosis and tuning); creation and execution of scheduled database backups; execution of scheduled scripts at the OS and database levels; creation and use of Oracle Data Guard standbys; and controlled configuration management of the server, the OS, and the database—and also by tackling head-on the necessary bugbear of database patching, the subject of this article.
To give you the best results for these varied tasks, your Oracle Enterprise Manager Grid Control site must be properly architected. For an overview, see "Grid Control Architecture for Very Large Sites," which explains how a large central Oracle Enterprise Manager Grid Control site was able to manage and monitor 600 to 700 targets, including databases, servers, and listeners.
The database patching facility, first introduced in Oracle Enterprise Manager Grid Control Release 2 (10.2.0.2), is a feature of Oracle Enterprise Manager Provisioning Pack, which is licensed separately. This article is based on the real-life experience of a major financial institution in Southeast Asia and explains how a proof of concept (POC) was conducted to convince the management and DBAs of the benefits of database patch automation with Oracle Enterprise Manager Grid Control for thousands of databases.
This is one of the strongest advantages of Oracle Enterprise Manager Grid Control over non-Oracle database management tools—the third-party tools don’t even come near Oracle Enterprise Manager Grid Control in this area. And besides, why would you use anything other than an Oracle product to manage Oracle Database instances ?
First Look at Database Patching with Oracle Enterprise Manager Grid Control
Applied Interim Patches Report Based on Applied Patches View
Oracle Enterprise Manager Grid Control offers an exhaustive set of reports pertaining to database patching. On the Reports tab, look at the Oracle Home Patch Advisories section. The “Applied Interim Patches” report shows the interim patches applied on Oracle homes across all the host targets and includes the CPU patch that was recently applied in the POC. This report is based on the Oracle Enterprise Manager Grid Control MGMT$APPLIED_PATCHES repository view, in the Oracle Enterprise Manager Grid Control repository database.
However, in the iterative tests of the POC, it was found that when the deployment procedure executes, if the Apply Patches step—which patches the database—is completed, the Oracle Enterprise Manager Grid Control MGMT$APPLIED_PATCHES repository view is not updated with the applied patch until after the entire deployment procedure is completed.
So if a later step in the deployment procedure fails, the entire job will be marked as failed and the Oracle Enterprise Manager Grid Control repository Applied Patches view will not be updated, even though the database itself has already been patched (as you can verify by running opatch lsinventory at the operating system prompt ).
The POC team debated whether the view should be updated as soon as the patching step completes, rather than not until the last step. This question was finally resolved when the deployment procedure and the way it interacts with the view was understood.
The MGMT$APPLIED_PATCHES view gathers information from the following four tables in the repository: mgmt_inv_container con, mgmt_ecm_snapshot snap, mgmt_inv_patch patch, and mgmt_targets tgt. The definition of the view is as follows:
CREATE OR REPLACE FORCE VIEW
"SYSMAN"."MGMT$APPLIED_PATCHES"
("PATCH", "BUGS", "INSTALLATION_TIME", "HOST", "HOME_LOCATION",
"HOME_NAME", "CONTAINER_GUID", "TARGET_GUID")
AS
SELECT
to_char(patch.id) as patch, ecm_util.concat_col('distinct BUG_NUMBER',
'mgmt_inv_patch_fixed_bug', 'PATCH_GUID = ''' || patch.patch_guid || '''',',') as bugs,
patch.timestamp as installation_time, tgt.target_name as host,
con.container_location as home_location, con.container_name as home_name,
con.container_guid, tgt.target_guid
FROM
mgmt_inv_container con,
mgmt_ecm_snapshot snap,
mgmt_inv_patch patch,
mgmt_targets tgt
WHERE
con.snapshot_guid = snap.snapshot_guid AND
snap.is_current = 'Y' AND
snap.snapshot_type = 'host_configuration' AND
con.container_guid = patch.container_guid AND
tgt.target_name = snap.target_name
WITH READ ONLY;
The view has a WHERE clause that says, “snap.is_current = 'Y' AND snap.snapshot_type = 'host_configuration'.” This indicates why the patch information was not updated: the last step in the deployment procedure is Host configuration collection. This final step is responsible for refreshing the host configuration. If one of the earlier steps fails, the deployment procedure will stop and never come to the last step unless you customize the later steps of the procedure to “continue on error.” The default is “stop on error.”
The host configuration can also be manually refreshed (Deployments -> Refresh Host Configuration). Otherwise, the refresh takes place automatically every 24 hours. If a patch is applied with OPatch—that is, outside Oracle Enterprise Manager Grid Control at the UNIX or DOS prompt—you must run the Refresh Host Configuration job manually if you want the view updated immediately, or else the information will be available in the view only after 24 hours.
Suggestion of Required Patch Set for Interim Patches by Patch Advisory
One of the POC tests at the financial institution was specified as follows: “Check if Oracle Enterprise Manager Grid Control can advise that a 9.2.0.x database needs to be upgraded to at least 9.2.0.8 before a CPU can be applied.” This means that if a recommended CPU (interim patch) is to be installed, the test needed to find out if Oracle Enterprise Manager Grid Control will also advise that the 9.2.0.8 patch set is required for the CPU.
The POC team installed 9.2.0.1, created a database, and added the database as a target in Oracle Enterprise Manager Grid Control. The RefreshFromMetalink job was executed to make sure that the critical patch advisory was up-to-date.
Full article...
