Patch a Thousand Databases, Using Oracle Enterprise Manager Grid Control

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.

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

Consider an Oracle Enterprise Manager 10g Grid Control installation that has been patched to Release 5 (10.2.0.5.0). The home page of the console appears as in Figure 1:

Figure 1

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

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

Clicking the job link 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. 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. The job status shows that the job is running. It 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.

If the Oracle Enterprise Manager Grid Control site does not have an internet connection to My Oracle Support (many companies restrict internet access), offline updates can be performed. This is explained in FAQ #8, “How can I patch if my OMS [Oracle Management Service] is Offline or Disconnected from Internet?” in the “ Achieving Grid Automation with Deployment Procedures” document, available on OTN.

To understand how to do this, look at the patching setup by clicking Setup at the top corner of the screen and then selecting Patching Setup. You’ll see three tabs:

Figure 2

On the first tab, My Oracle Support and Proxy Connection (see Figure 2), you enter your login to My Oracle Support. The patch search URL is set to the Oracle site: http://updates.oracle.com. Click the Test button to verify that your login was successful.

You can select either a direct connection to the internet or, if your company allows connection to the internet via a proxy server, Manual Proxy Configuration, which will let you use a proxy server host for both http and https. Supply a username and password for each server. Finally, you can test the reachability of the agent via the displayed URL.

Figure 3

On the second tab, Online and Offline Settings (see Figure 3), you select either the online or offline mode for patch advisories. 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 for such cases.

You must first 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

You can now manually upload the XML files to the Oracle Management Service metadata cache, using the Online and Offline Settings tab. The URLs shown above are also displayed on this tab for your reference. Click the Upload button.

Subsequently the XML files will be in the Metadata Dump directory, which also appears on this tab. If the RefreshFromMetalink job is now run, it will use these XML files and perform the same actions as it would have if it were connected to the internet.

Time-Related Aspects of Offline Patching

Certain companies do not apply the latest CPUs. Their policy may be to apply a CPU that Oracle published earlier, say six months ago. This was an important requirement of the POC that was conducted at the financial institution that is our example in this article.

The reasoning was that the earlier CPU would be safer, because it was older and more established —institutions such as these prefer not to apply any new software, including patches, given their conservative security policy.

In the scenario described in the previous section, the RefreshFromMetalink job uses the information in the downloaded XML files, which is the latest information and therefore pertains to the latest CPUs. The patch advisory will therefore advise patching to the latest CPU, which is not what the company may want.

The solution suggested in this case was to download the XML files at regular intervals and place them in manually created directories on the DBA workstation or a common DBA network directory. They can then be uploaded to the Oracle Management Service as required, thereby controlling which CPU is actually considered by the patch advisory. Make sure to change the settings to Offline in Online and Offline Settings under Setup -> Patching Setup. Uploading an older set of XML files will force the advisory to consider the corresponding older CPU to satisfy this requirement of the POC.

OS-Level RPM Patching

The third tab, Linux Patching Setup (see Figure 4), is where you can perform OS-level RPM patching for your Linux servers, using the Oracle Unbreakable Network. (This article doesn’t discuss OS-level patching.)

Figure 4

Results of the RefreshFromMetalink Job

Examine the successful run of the RefreshFromMetalink job. On the Oracle Enterprise Manager Grid Control console’s home page, click Patch Advisories, which takes you to the computational findings of the RefreshFromMetalink job, as you can see in Figure 5.

Figure 5

On the Patch Advisories tab, the latest findings are the Critical Patch Update 2009, which affects four distinct homes. Clicking the Critical Patch Update link takes you to the published patch document in My Oracle Support (after you supply your login details).

The actual patch numbers are listed in the Interim Patches to Apply section. In this section, note that the patch to be used for Oracle Database 11.1.0.6 is 8333655 via the “Critical Patch Update April 2009” advisory. Verify this in the My Oracle Support document—this patch number is the correct number for the Windows platform.

Figure 6

The other tabs, Affected Homes (see Figure 6) and Remedies (see Figure 7), 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 home (see Figure 7).

Figure 7

As you can see, patch 8333655 is the remedy for the 11.1.0 home. Clicking Show Remedy Details on that tab takes you to the Remedy Details screen (Figure 8).

Figure 8

On this screen, clicking Apply Selected Patch starts the patch wizard (Figure 9), which takes you through the steps of patching this home.

Figure 9

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 screen (see Figure 10).

Figure 10

During this process, Oracle requests your e-mail address, so it can inform you of any security issues in your configuration. The job is then scheduled. At the appropriate time or if the job is set to run immediately, the job starts its run (see Figure 11).

Figure 11

The job can also be found running on the Jobs tab of Oracle Enterprise Manager Grid Control. It goes through a series of steps, such as downloading and caching the patch file, and then stages it to the database, finds the OPatch utility to apply the patch to the database, shuts down the database, applies the patch, and starts up the database. In the end, the job completes successfully and the database is patched.

However, it is not a good idea to install the patch by using this particular method—the flow from the CPU Advisory to the Patch wizard is not the recommended flow. The Patch Wizard way of patching databases is useful only for applying single patches on single databases and is not very flexible or customizable, and will ultimately be superceded in the future.

To use advanced features such as multiple patch application, patch flow customization, sudo, and pluggable authentication modules (PAM) support, you must use the new and extremely powerful deployment procedures functionality, which is discussed in the coming section.

Patching Through Deployment Procedures

Using the deployment procedures functionality is highly recommended, because it enables patching of multiple homes on different targets with multiple patches and also lets you customize the steps involved in the deployment procedures.

Figure 12

To use deployment procedures, you can select the Patch number from the CPU Advisory, click the Patch button and then pick Deployment Procedures. Or, you can select the Deployments tab from the Oracle Enterprise Manager Grid Control console. Under Patching, click Patching through Deployment Procedures (see Figure 12). A list of deployment procedures supplied by Oracle appears (see Figure 13), along with any customized procedures you may have created by simply copying one of the procedures and modifying the component steps.

Figure 13

In this first scenario, select the supplied Patch Oracle Database procedure. Click the View button to see the steps in the deployment procedure (see Figure 14). This is the procedure for patching standalone Oracle Database installations with Critical Patch Updates, interim patches, and patch sets. However, major upgrades, such as from 11.1 to 11.2, are not supported with this procedure.

Figure 14

As you can see in Figure 14, the deployment procedure steps involve various activities such as optionally upgrading the OPatch utility; staging the patch; starting an Oracle Enterprise Manager blackout (so that no alerts are raised for this planned downtime); stopping the database; applying the patch; running a root script, if applicable; starting the database in the upgrade or migrate mode; applying a SQL script, if applicable (if it is a patch set or a CPU); applying a post-SQL script; stopping the database; restarting it; applying more SQL scripts; stopping the blackout; and finally refreshing the host configuration collection—note this last step particularly.

Click Schedule Deployment, which takes you to a series of wizard pages (see Figure 15), where you need to specify the software updates (single or multiple) to be applied, a list of targets (single or multiple) for the deployment, the credentials, the schedule, and other details.

Figure 15

On the first page, specify the staging location, which is set by default to %emd_root%/EMStage. In the case of this Oracle Enterprise Manager Grid Control installation on Windows, it is the C:\OracleHomes\agent10g\EMStage directory.

The Standalone Database Updates section is where you actually select the patches to apply. Click Add, which brings up a screen (see Figure 16) where you can select either Search My Oracle Support or Search Software Library—the latter being the list of patches that have already been downloaded either directly from My Oracle Support or manually. (The manual download of patches is described later in this article.)

Figure 16

Select the release and platform and click the Go button, and a list of applicable patches is displayed. On this list, you can quickly locate the 8333655 patch—the ORACLE 11G 11.1.0.6.0 PATCH 16 BUG FOR WINDOWS 32 B—the CPU patch we noticed in the patch advisory. Select this patch.

Back on the first page, specify that the OPatch utility should also be upgraded. You can also specify whether the default SQL script of the patch or any specific SQL scripts should be applied. On the next page, select the database targets to be patched. This is where you can select multiple database targets residing on multiple servers on a corporate site.

The next page then requests the e-mail address to which to send security updates and information on security issues and asks for the My Oracle Support password of the account.

After this you need to specify the credentials for all the homes and hosts and then schedule the deployment procedure to take place either immediately or at any future time. This enables you to run the patching of several databases overnight (once the patching has been properly tested on a development or test database).

When the summary page is accepted, the deployment procedure is immediately scheduled. Click it to show the status of its running steps (Figure 17).

Figure 17

You can view the job anytime by going to the Deployments tab, selecting Patching through Deployment Procedures, and then selecting Procedure Completion Status. You will see the deployment procedure that is running.(see Figure 18).

Figure 18

Note that when the deployment procedure is running, certain job tasks such as Upgrade OPatch are also visible on the Jobs tab of Oracle Enterprise Manager Grid Control, running as separate jobs and completing. However, the main deployment procedure job is seen as a whole only on the Procedure Completion status screen.

Result of Initial Deployment Procedure Run

Deployment Procedure Runs on Unix platforms are usually successful. However, in this initial run on the Windows platform, certain steps of the deployment procedure run show that they have failed. Figure 19, for instance, indicates that the Iterates over a list of Hosts step has failed.

Figure 19

If you drill down, you will be able to see where the failure has occurred. In this case, the failure is at the Apply Patches step, and the output log (which is displayed when you have drilled down all the way) shows the following:

Output Log 
Step is being run by operating system user : 'sisystemsporushh' 
 
Run privilege of the step is : Normal  

This is Provisioning Executor Script(Windows)
…
Directive Type is SUB_Perl
…
The output of the directive is:
…
Tue Jun 2 00:37:40 2009 - Found the metadata files; '8333655' is an Interim patch
…
Tue Jun 2 00:37:40 2009 - OPatch from 'C:/app/porushh/product/11.1.0/db_1/OPatch/opatch.pl' 
  will be used to apply the Interim Patch.
…
Tue Jun 2 00:37:57 2009 - Invoking OPatch 11.1.0.6.6
…
Following patches will be rolled back from Oracle Home on application of the patches in the given list :
   7210195
…
Do you want to proceed? [y|n]
Y (auto-answered by -silent)
User Responded with: Y
OPatch continues with these patches:   8333655  

Do you want to proceed? [y|n]
Y (auto-answered by -silent)
User Responded with: Y

Running prerequisite checks...
                                Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:

                                Following files are active :
                                C:\app\porushh\product\11.1.0\db_1\bin\oci.dll
UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.
…
OPatch failed with error code = 73
Apply of Interim Patch(es) failed
Tue Jun 2 00:37:57 2009 - Patching failed.

This indicates that OPatch has failed because the oci.dll file in the 11g db home was locked. This is a common known issue for patching in Windows—the OS frequently keeps DLL files in memory for unspecified times, for Windows performance reasons, even after the programs that were using them have been terminated. (The DLL file is Microsoft's implementation of the shared library concept.)

Our financial institution used a third-party utility, ProcessExplorer, which found that the file had been locked by the Windows process svchost (Service Host). Multiple svchost processes were running. Seeing which copy of svchost was running which service involved using tasklist /svc in a command window.

The solution that ultimately worked was to use another third-party utility, Unlocker, to unlock oci.dll just after the databases and listeners were stopped by the deployment procedure. Make sure you exit the process explorer before doing the unlocking, because the process explorer itself locks other DLLs. Here are the steps the financial institution used:

• Start the patching process.

• In Windows Explorer, go to the 11g Oracle Home/bin and find oci.dll.

• Wait until the Oracle Enterprise Manager Grid Control patching reaches the step of stopping databases.

• Right-click on oci.dll and unlock the lock of scvhost on this file, using the third-party Unlocker.

• Patching then continues successfully, because the file is not locked anymore.

The very purpose of the deployment procedures is automation, so manual tinkering of this nature can and should be avoided. These issues are documented as known, and the flexibility of deployment procedures makes it possible to handle them. Oracle enables us to customize the deployment procedures and modify or add more steps.

The flexibility of deployment procedures is useful in special cases, including inserting customer steps such as sending e-mail messages, adding a backup before a patch application, forking a cron, or handling known issues via a workaround. These are not typical requirements, though.

In the second part of this article, the deployment procedure will be customized to work around the issue of Windows locking the DLL file.

Customization of Deployment Procedures

Oracle Enterprise Manager Grid Control offers deployment procedures that are highly flexible and are used to patch Oracle databases with multiple patches and customize the patch flow. As discussed earlier in this article, the Patch Oracle Database deployment procedure in Oracle Enterprise Manager Grid Control had failed at the Apply Patches step, because the OS (Windows) had locked the oci.dll file in the Oracle home’s bin subdirectory.

The oci.dll file needs to be unlocked before the Apply Patches step is executed. The additional command for using the third-party Unlocker utility on the command line to unlock oci.dll is

cmd -c  "c:; "\Program Files\Unlocker"\Unlocker C:\app\porushh\product\11.1.0\db_1\bin\oci.dll  -S"

To add this step to the actual patching flow, you can do the following:

Select Deployments -> Patching through Deployment Procedures. In the list of procedures under Deployment Procedure Manager, select the presupplied Patch Oracle Database procedure and then click Create Like, which enables you to create a customized deployment procedure.

In the customized procedure, you can change various things such as the status of the procedure that will trigger notifications and the error handling mode of each step of the procedure—whether to stop on error or continue on error. Also, you can easily delete steps from or insert them into your customized procedure.

Select the Apply Patches step, and click the Insert button. You can now add the Unlock oci.dll in Windows step to execute the host command (see Figure 20). Then click the Save button to save the entire new customized deployment procedure.

Figure 20

The customized deployment procedure can now be scheduled as before. It runs successfully and applies the patch (see Figure 21) without interference from Windows locking. Both the Unlock oci.dll in Windows and the Apply Patches steps are seen to have succeeded.

Figure 21

Power of Deployment Procedures

The foregoing has amply demonstrated the power of deployment procedures in Oracle Enterprise Manager Grid Control, and through customization, numerous issues such as Windows locking can be resolved and Oracle Enterprise Manager Grid Control becomes a really powerful tool for automation of database patches in a large corporate setting.

Executing OPatch on the command line in the Oracle home now shows that the newly applied patch has been registered in the Oracle inventory:

 
C:\>cd \
C:\>set ORACLE_HOME=C:\app\porushh\product\11.1.0\db_1
C:\>set ORACLE_SID=FINPRD1
C:\>cd C:\app\porushh\product\11.1.0\db_1\BIN
C:\app\porushh\product\11.1.0\db_1\BIN>opatch lsinventory
Invoking OPatch 11.1.0.6.0

Oracle Interim Patch Installer version 11.1.0.6.0
Copyright (c) 2007, Oracle Corporation. All rights reserved.
….
Installed Top-level Products (1):
Oracle Database 11g                                                  11.1.0.6.0
There are 1 products installed in this Oracle Home.
Interim patches (1) :
                                Patch 8333655      : applied on Tue Jun 02 16:21:48 SGT 2009
   Created on 20 Mar 2009, 12:29:01 hrs US/Pacific
   Bugs fixed:
…..
OPatch succeeded.

Manual Download of Actual Patches

If the corporate policy prevents the Oracle Enterprise Manager Grid Control installation from connecting directly to My Oracle Support and downloading actual patches, there is an option for you to manually download the required patch to your workstation and then upload it to the Oracle Enterprise Manager Grid Control software library and patch cache.

This procedure is documented in FAQ #8, “How can I patch if my OMS is Offline or Disconnected from Internet?” in the “ Achieving Grid Automation with Deployment Procedures” document on OTN.

You do this via the Deployments tab on the Oracle Enterprise Manager Grid Control console. In the Patching section, click View/Upload Patch. On the Patch Cache screen, which appears, you see a list of patches that have been uploaded manually or downloaded automatically from My Oracle Support.

To manually upload a patch you do not see, click Upload Patch. On the screen that appears (see Figure 22), you must fill in all the details of the patch manually.

Figure 22

If you are uploading an OPatch patch, select Oracle System Management Products for the Product Family field and Universal Installer for the Product field. For database patches such as this CPU, select Oracle Database for both Product Family and Product. You can see this procedure documented in FAQ #6, “What does Upgrade OPatch mean?” in the “ Achieving Grid Automation with Deployment Procedures” document.

After filling in the details, click the Upload button. The patch is uploaded and now appears in the software library and patch cache list (see Figure 23).

Figure 23

From now on, when you schedule the deployment procedure and want to select the patch to apply, the patch you have uploaded will appear when you search the software library. In this way, you have achieved the same effect as with a direct connection to My Oracle Support, with the cost of a few extra manual steps but with the satisfaction that you have complied with the rules of Corporate Security (keeping them happy).

Deleting a Patch

To delete an existing patch, you must remove it from the patch cache list, shown in Figure 23, as well as from the software library. To access the software library, go to Deployments and click the Provisioning subtab on the blue bar at the top of the screen. Expand Oracle Software Updates to see the patch component. Select it, and click Delete (see Figure 24).

Figure 24

You must then also purge the component from the repository, so that the binary files don’t continue to occupy space on the file system. The Purge facility is on the Administration tab on the same screen.

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.

From the Oracle Enterprise Manager Grid Control console home page, click Patch Advisories under Critical Patch Advisories for Oracle Homes. Select Affected homes, the Oracle9i home, and then Show Remedies.

On this screen, under Remedies for Selected Advisories, you see two patches, 9.2.0.8 (4547809) and 8300340. Clicking the first patch shows that it is the 9.2.0.8 patch set created on August 21, 2006. Clicking the second patch shows that it was created on April 8, 2009, and is part of the April CPU. This signifies that the patch advisory is recommending an upgrade to 9.2.0.8 before the April CPU can be applied, thus satisfying the requirement of this POC test.

Customizing Patch Reports

A final requirement of the POC was a report on the applied interim patches on each database on any host in the corporation. Because the reports in the Oracle Home Patch Advisories section display the patches applied on all the Oracle homes on a host, the POC requirement could be met only via the customization of reports, which is easily achievable in Oracle Enterprise Manager Grid Control.

The supplied reports show the patch, bugs fixed, installation time, host, home directory, and platform but do not show the databases, because the patch is actually at the Oracle home level and not at the database level. If you want to add in the database name, you can modify the report by creating a copy and then modifying the SQL statement. For example, the “Applied Interim Patches” report uses the following modifiable SQL statement:

SELECT distinct
         patch as PATCH,
                 bugs as BUGS,
                 installation_time as TIMESTAMP,
                 host as HOST,
                 home_location as HOME_DIRECTORY,
                 platform as PLATFORM
from mgmt$applied_patches patch,
     mgmt$em_homes_platform  home,
     mgmt$target tgt
where home.HOME_ID = patch.CONTAINER_GUID
and patch.target_guid = tgt.target_guid
and patch.installation_time>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,
  ??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)
and patch.installation_time<= MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,
  ??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)

You can join this to mgmt$target_components, which would have the same home as the mgmt$applied_patches home, and then select the targets that are databases in this home.

With this strategy, an Oracle Enterprise Manager Grid Control customized report was created as “Applied Interim Patches (Customized - Database Level).” Here is the SQL used in this report:

 
 SELECT distinct
         patch as "Applied Interim Patch",
                 installation_time as "Time Applied",
                 tgtcomp.target_name as "Database Target Name",
                 patch.home_location as "Oracle Home Directory",
                 patch.home_name as "Oracle Home Name",
                 host as Host
from mgmt$applied_patches patch,
     mgmt$em_homes_platform  home,
     mgmt$target tgt,
     mgmt$target_components tgtcomp
where home.HOME_ID = patch.CONTAINER_GUID
and patch.target_guid = tgt.target_guid
and tgtcomp.home_name = patch.home_name
and tgtcomp.target_type = 'oracle_database'
and patch.installation_time>MGMT_VIEW_UTIL.ADJUST_TZ
  (??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)
and patch.installation_time<= MGMT_VIEW_UTIL.ADJUST_TZ
  (??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)
order by installation_time desc

The headings of the columns in the report have been changed, and the styled text in the Report definition was also changed from INSTR_APPLIED_PATCHES_ALL_HOSTS to this customized line: “The report shows the interim patches applied on Oracle Databases across all the hosts in the last 31 days. Use the time period selector to view the interim patches applied within a time-period. Thanks.” The interim patches applied to each database are now displayed, thus satisfying the POC requirement.

The POC team also created an “Applied Interim Patches (Customized - Database Level and Select which Database)” report, which also allows the selection of individual databases.

Here is the SQL statement used for this report:

 
         SELECT distinct
         patch as "Applied Interim Patch",
                 installation_time as "Time Applied",
                 tgtcomp.target_name as "Database Target Name",
                 patch.home_location as "Oracle Home Directory",
                 patch.home_name as "Oracle Home Name",
                 host as Host
from mgmt$applied_patches patch,
     mgmt$em_homes_platform  home,
     mgmt$target tgt,
     mgmt$target_components tgtcomp
where home.HOME_ID = patch.CONTAINER_GUID
and patch.target_guid = tgt.target_guid
and tgtcomp.home_name = patch.home_name
and tgtcomp.target_type = 'oracle_database'
and tgtcomp.target_guid = ??EMIP_BIND_TARGET_GUID??
and patch.installation_time>MGMT_VIEW_UTIL.ADJUST_TZ
  (??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)
and patch.installation_time<= MGMT_VIEW_UTIL.ADJUST_TZ
  (??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)
order by installation_time desc

The line that selects the database target is

and tgtcomp.target_guid = ??EMIP_BIND_TARGET_GUID??

When this report is executed, it enables the DBA to select the database from a list of supplied database targets. The report will now display the interim patches applied only to the selected database in the specified time period.

This is just an example of the customization that is possible in Oracle Enterprise Manager Grid Control Reports—not just in the sphere of database patching but in all other types of reports as well.

Oracle Enterprise Manager Grid Control Repository

How do you know what information is in the Oracle Enterprise Manager Grid Control repository? The MGMT$ views are documented in the “ Enterprise Manager Extensibility Guide for 10g Release 5 ” document, available on OTN.

You can also use the new Oracle SQL Developer Data Modeler. It is downloadable from OTN, currently installs as a standalone utility, and will most likely be incorporated into a new release of Oracle SQL Developer in the not-too-distant future. (Oracle SQL Developer keeps getting better and better, just like Oracle Enterprise Manager Grid Control!)

Oracle SQL Developer Data Modeler allows the reverse engineering of an existing Oracle Database instance, and you can access it by selecting File -> Import -> Data Dictionary. In this way, you can import the data dictionary from any Oracle9i Database, Oracle Database 10g or Oracle Database 11 g, or even from Microsoft SQL Server 2000 and 2005 databases, from DB2/390 7 and 8 databases, or DB2/UDB 7 and 8 databases.

Use Oracle SQL Developer Data Modeler to reverse-engineer the SYSMAN schema in the Oracle Enterprise Manager Grid Control repository, especially the MGMT$ views. Then you can understand what information is available, in the form of a data model of the repository.

The New "Analyze" Mode from Patch 8653501

In July 2009, Oracle released a new one-off patch on top of Oracle Enterprise Manager 10.2.0.5. Patch Number 8653501 introduces fixes mainly for the Patch Automation feature. This patch can be downloaded from My Oracle Support and applied on the Grid Control OMS server(s). The additional steps in the Readme should also be completed.

Patch 8653501 introduces the following Patch Automation enhancements:

1. The Deployment Procedures can now be invoked in "Analyze" mode; this will verify the applicability of the patch on the database environment. In the Review page during the Deployment procedure execution, two new mode buttons— Analyze and Deploy—can now be seen. If the Deployment Procedure is submitted in Analyze Mode, this will perform a complete patchability check before the actual patching cycle. The results with recommendations can be seen under the Results tab.
2. Two new reports can be seen under the Reports tab:
3. a. EM Target Patchability Report, which describes comprehensively the automated patchability of the database environment as managed by Grid control.

   b. Patching Deployment Procedure Execution Summary Report, which gives a summary view of the deployment procedure's execution status, across any time period.

Lessons Learned

The large DBA team of our financial institution was used to doing all database administration tasks manually for many hundreds of databases. The team members were very impressed when the POC showed them how Oracle Enterprise Manager Grid Control could assist them in their continual patch deployment tasks.

The deployment procedures now supplied in Oracle Enterprise Manager Grid Control for database patching are very mature—they enable DBAs to search for patches for any version of any Oracle software from My Oracle Support; download the selected patch(es) to a staging area on the Oracle Management Service host; and then apply the patch(es) on any target database(s), using the supplied or customized deployment procedure.

The patch(es) can be scheduled to be applied at any time with the Oracle Enterprise Manager Grid Control scheduler. The deployment procedure automatically starts a blackout for the database (so no alerts are generated), shuts it down, applies the patch(es), and then starts the database again. The same is repeated for the other databases selected for patch application.

Furthermore, the deployment procedure can be customized for situations in which additional steps need to be added or if it is decided not to stop on errors, and so on.

The DBAs felt that the use of deployment procedures would save them a lot of manual steps in the case of patches that have to be applied to hundreds of databases. For starters, think how much time it would take to FTP/SCP a manually downloaded patch to hundreds of servers.

In the case of Oracle Enterprise Manager Grid Control, the patch or patches are downloaded once and stored on the central Oracle Management Service host in the software component library. The patches are then sent to each database target host automatically as part of the deployment procedure, where they are placed in the patch staging location, under Agent Home on the target host (%emd_root%/EMStage, where %emd_root% is the target Agent Home location). From this staging area, they are then applied to the appropriate Oracle homes on that target host. Thus, the deployment procedure has removed even the manual drudgery of the FTP/SCP of each patch as required to each server.

This has convinced the DBAs and management that Oracle has supplied a lot of capabilities in Oracle Enterprise Manager Grid Control’s Oracle Provisioning and Patch Automation Pack for the hitherto manual and tedious task of patching in the Oracle world. Quite simply, this is the next generation of patch management.

Conclusion and Additional Information

Besides the standalone Oracle Database instances discussed in this article, the deployment procedures cover application of patches to Oracle RAC in zero-downtime rolling mode and nonrolling mode, Oracle Clusterware, Oracle Automatic Storage Management, and Oracle Application Server—in addition to deployment procedures for patching Solaris, Windows, and Linux hosts. A good detailed reference on this topic is “ Enterprise Manager Administrator's Guide for Software and Server Provisioning and Patching“, available on OTN.

For setting up Oracle Recovery Manager (RMAN) backups for your corporate databases, you can also read another recent article of mine, “ Oracle RMAN Backups: Pushing the Easy Button,” which explains how a large corporation moved to setting up and scheduling all of its RMAN backups with Oracle Enterprise Manager Grid Control. The world of Oracle Enterprise Manager Grid Control is growing rapidly, and it is a great help to the modern DBA.

Porus Homi Havewala, an Oracle ACE director, is a principal consultant at S & I Systems, in Singapore. He has extensive experience in Oracle technology going back to 1994 and has served as a production DBA, senior consultant, e-business technical DBA and systems administrator, development DBA, and database designer/modeler (using Oracle Designer, of course). He was involved as the lead database architect and technical team leader for the first production Oracle Enterprise Manager Grid Control site in the world, Telstra in Australia. He is an Oracle Enterprise Manager Grid Control evangelist and has written several OTN articles on Oracle Enterprise Manager Grid Control.