Applying patches with OEM

Patching has always been one of the many joys (or dreads) of Oracle DBAs.

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 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.

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.

Here is the OEM patching screen:

You’ll immediately notice the informational message on the OEM console:

“The Patch Advisory information may be stale. My Oracle Support refresh job has not run successfully in 72 hours.”

Clicking the button RefreshFromMyOracleSupport. takes you to the job activity screen, where you see that the job is scheduled to run once a day. The Oracle Enterprise Manager Grid Control site was down for a few days, because it is a POC site. Since the site was down, the job had not run for 72 hours.

The job downloads the metadata of the latest patch advisories, products, and product versions that are available from My Oracle Support Community (MOSC).

It also computes the targets for the latest Critical Patch Updates. You must be sure to run this job before any patching is attempted with Oracle Enterprise Manager Grid Control.

To run the job immediately, create a copy. Select Refresh From My Oracle Support from the Job Activity page under Jobs, and click Create Like. Name this new job ONE-TIME REFRESH FROM MY ORACLE SUPPORT, and submit it.

OEM connects to My Oracle Support over the internet; downloads several XML files into a directory, C:\MetalinkMetadataDump (user-settable); and succeeds in a few minutes, after which the message “The Patch Advisory Information may be stale” is no longer visible on the Oracle Enterprise Manager Grid Control console’s home page—this information has been updated by the job.

Now we click the "patching setup" button on OEM:

OEM Patching setup screen

The Oracle Enterprise Manager Grid Control servers may not have access to the internet, either directly or via a proxy. The offline mode is available if you download the XML files manually from the internet onto a workstation:
        http://updates.oracle.com/ARULink/XMLAPI/download_seed_data?table=aru_products
http://updates.oracle.com/ARULink/XMLAPI/download_seed_data?table=aru_platforms
http://updates.oracle.com/ARULink/XMLAPI/download_seed_data?table=aru_languages
http://updates.oracle.com/ARULink/XMLAPI/download_seed_data?table=aru_product_groups
http://updates.oracle.com/ARULink/XMLAPI/download_seed_data?table=aru_product_releases
http://updates.oracle.com/ARULink/XMLAPI/download_seed_data?table=aru_component_releases
http://updates.oracle.com/ARULink/XMLAPI/query_advisories
http://updates.oracle.com/ARULink/XMLAPI/download_seed_data?table=aru_releases

OS-Level RPM Patching in OEM

The third tab, Linux Patching Setup, is where you can perform OS-level RPM patching for your Linux servers:

After the refreshfrommetalink button we see this:

The Affected Homes and Remedies tabs, are related and enable you to specify any home in your Oracle Enterprise Manager Grid Control targets; select it; and click Show Remedies, which takes you from the Affected Homes tab to the Remedies tab and lists the patches applicable for that Oracle home.

The affected platforms OEM page tab:

Patches by Oracle Homes:

In the patch wizard, in the carefully controlled steps that are presented to you, you must select the patch first, select the destination, set the credentials, then either ask to stage the patch (where it is downloaded to a subdirectory of the Oracle home) or stage and apply it at the same time, schedule this entire job, and finally confirm the details on the summary OEM screen.