Database Security and Patches - Part 2

Tuesday Nov 22nd 2005 by Steve Callan

Part 2 of this series covers the mechanics of installing a patch.

Part 2 of this series covers the mechanics of installing a patch. Overall, installing a patch is quite easy, but depending upon the platform and version, the number of steps may be different. The base version used as the example is Oracle (for Windows NT/2000/XP) – exactly what you get on the download from Oracle Technology Network. The patch process uses Patchset 4163445 ( PATCHSET FOR ORACLE DATABASE SERVER).

Overview of the patch process

There are four general phases involved in the patch process:

  • Get the patchset (MetaLink access)
  • Upgrade the Oracle Universal Installer (not always required)
  • Install the patch using OUI
  • Running maintenance, clean up, or one-off scripts (not always required)

Upgrading the OUI can be viewed as a bootstrap process (bootstrap in the same sense as used in computer science related to starting up an operating system or developing a new language). What typically takes place is a requirement to start the upgrade by using your currently installed version of OUI. The end result is a newer version of OUI on your computer. The idea is that it takes OUI to install OUI.

Once the new version of OUI is installed, OUI is launched again as its new invocation and the patch itself is installed. The windows are very similar to what you see when installing the RDBMS software in the first place. Lastly, it may be necessary to run one or more additional scripts. For example, applying to an HP Itanium running Red Hat Linux includes running a CPU-related fix-it script in addition to a catpatch.sql script.

One other patch-related program is something known as OPatch. Documentation about OPatch is rolled into Oracle® Universal Installer and OPatch User's Guide, 10g Release 2 (10.2) for Windows and UNIX. From Chapter 8 of the guide:

OPatch is an Oracle supplied utility to assist you with the process of applying interim patches to Oracle's software. OPatch is a Java-based utility which requires the Oracle Universal Installer to be installed. It is platform independent and runs on all supported operating systems.

OPatch supports the following:

  • Applying an interim patch.
  • Rolling back the application of an interim patch.
  • Detecting conflict when applying an interim patch after previous interim patches have been applied. It also suggests the best options to resolve a conflict.
  • Reporting on installed products and interim patch.

OPatch and using Enterprise Manager to manage patches will be covered in a subsequent article. For now, our emphasis is on Oracle9i and the "old fashioned" way of installing a patch.

Sorting out patches and patchsets

Unlike the base version of each Oracle product (available at Oracle Technology Network), virtually all patches are obtainable only via MetaLink. Access to MetaLink requires a support contract, so if you are on your own (i.e., student, learning Oracle on your own, etc.), your access to patches for a product are quite limited. HTML DB is an example of where Oracle has released a patch outside of MetaLink, but with respect to the RDBMS product, you can pretty much count on the requirement to have access to MetaLink.

Once you are in MetaLink, what do you look for? It helps to know the patch number ahead of time because you can search by number instead of by product. One semantics-related concept that helps in your search is understanding Oracle's terminology. As an analogy (in most cases), when someone refers to a database, there is not a distinction between database and instance, and as we all know, sometimes the distinction is important. With respect to a patch, more often than not we really mean patchset. If we want to turn a software installation into a version, we have to apply a patchset.

Additionally, since the base releases tend to start off with a one, the "7" in "" means patchset 6. And to be precise, not every patchset upgrades the version release. Patchsets, by definition, are cumulative fixes of other problems. As a general rule of thumb, you can almost always safely or routinely apply a "family" patchset, but other patchsets (and patches) should be applied only when certain conditions are met in your particular environment. Of course, it does not help that Oracle refers to patches and patchsets by patch number (see the left column in the pictures below).

How does Oracle view a patchset?

Patch sets are a mechanism for delivering fully tested and integrated product fixes. Patch sets provide bug fixes only; they do not include new functionality and they do not require certification on the target system.

Patch sets include all of the libraries that have been rebuilt to implement the bug fixes in the set. All of the fixes in the patch set have been tested and are certified to work with each other. Because patch sets include only low impact fixes, you are not required to certify applications or tools against the server unless directed to by the operating system vendor.

Patch sets are cumulative. Patch set release includes all fixes in patch sets and earlier as well as new fixes for patch set This means that unless the patch set documentation indicates otherwise, you can apply this patch set to any earlier release 9.2 installation. You do not have to install intermediate patch sets.

Patch sets contain generic fixes that apply to all platforms. Patch sets may also include additional platform-specific patches.

Oracle's definition of a patch set

Click for larger image

A sample listing of patchsets for Oracle9i.

Click for larger image

A sample listing of patches for Oracle9i.

The table below summarizes how Oracle software is released.




Base release








Patch 4159795

Installing patchset

Selecting the link for 4163445 shows the following:

Patchset 4163445 Information Page

Start of the README for 4163445

Downloading the "patch" file requires no explanation, and uncompression or extraction of the file can generally be done anywhere except under ORACLE_HOME. For this patchset, the instructions direct you to use a newer version of Oracle Universal Installer (version, to be precise). The newer version of OUI comes bundled with the patchset, so phase one (use OUI to upgrade OUI) applies here. Other considerations include upgrading standby databases, clearing statistics out of the SYS schema (shouldn't be there anyway, right?), and checking for post-release updates.

Before actually starting OUI, some preliminary steps include:

  • Download and uncompress p4163445_9207_WINNT.zip
  • Shutdown all database instances
  • Stop all processes
  • Take a backup
  • Decide to install the patchset interactively or non-interactively

An interactive session is nothing more than responding to OUI. A noninteractive session uses a response file. Since this example uses the interactive mode, we have to start OUI using setup.exe in the folder where the ZIP file was extracted. The target or source file, products.xml, is in the stage folder.

Run setup.exe and update the path and name of your Oracle 9.2 installation.

The installation process is straightforward, and the installation successful message at the end is a good sign.

Post-installation tasks

Assuming your database is run of the mill DBCA-created, and you are not using optional accessories or tools, the post-installation tasks (which apply to databases now since the RDBMS software update is finished) require altering some parameters and running a script. The complete list of steps is spelled out in the README file, and there can be quite a few additional steps involved if your database uses advanced features. For this example, the post-installation steps include:

  • Check for 10MB of free space in the SYSTEM tablespace (if using JServer)
  • Set SHARED_POOL_SIZE and JAVA_POOL_SIZE to at least 150MB each. If you installed/created a seed database, this applies to your database(s).
  • Start the instance, use STARTUP MIGRATE, and run the catpatch.sql script. Located in rdbms\admin, the script may have to be run more than once.
  • Run utlrp.sql to fix invalid PL/SQL packages (suggested to be done now instead of at runtime).
  • Restart the database
  • Update the RMAN catalog using UPDATE CATALOG (obviously, only required if you're using RMAN)

If necessary, you can back out a patch by using the catbkout.sql script. However, this only applies to base releases of or higher; so once applied to your database, you are committed. The patch process via OUI can be repeated without harm, and a repeat requires using the interactive mode.

How to find which patchset your database is using

This question appears with some regularity on various Oracle-related Web sites. A simple query of DBA_REGISTRY provides this information, and it is also shown on the output when SQL*Plus is started. Why does the SQL*Plus startup output show and the query against DBA_REGISTRY show

Click for larger image

Click for larger image

The difference between these two outputs should drive home the point that patching applies not only to the RDBMS software, but also to all databases using that particular installation.

Checking for type of parameter file and pool sizes (output below was edited):

SQL> show parameter pool
NAME               VALUE
------------------ --------
java_pool_size     33554432
shared_pool_size   50331648
SQL> show parameter pfile
------- ---------------------------------------------

With an SPFILE, the two pool sizes are altered with:

System altered.
System altered.

Shutdown, startup migrate, and run catpatch.sql:

SQL> conn / as sysdba
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup migrate
ORACLE instance started.
Total System Global Area  370221140 bytes
Fixed Size                   454740 bytes
Variable Size             343932928 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
> spool patch.log (optional, don't forget to turn off)
SQL> @?\rdbms\admin\catpatch

At the end of the script, output similar to the query previously run against DBA_REGISTRY is shown. Processing time is around 30 minutes, and the log file size is just over 460KB file with 60,000+ lines.

COMP_NAME                           STATUS      VERSION
----------------------------------- ----------- ----------
Oracle9i Catalog Views              VALID
Oracle9i Packages and Types         VALID
Oracle Workspace Manager            VALID
JServer JAVA Virtual Machine        VALID
Oracle XDK for Java                 VALID
Oracle9i Java Packages              VALID
Oracle interMedia                   VALID
Spatial                             VALID
Oracle Text                         VALID
Oracle XML Database                 VALID
Oracle Ultra Search                 VALID
Oracle Data Mining                  VALID
OLAP Analytic Workspace             UPGRADED
Oracle OLAP API                     UPGRADED
OLAP Catalog                        VALID

Restart the database, run the utlrp.sql script, and then if applicable, upgrade the RMAN recovery catalog. This completes the patchset installation process.

Something curious in Oracle's documentation

All of the script command examples shown in the README file are of the form:

ORACLE_BASE\ORACLE_HOME\<rdbms, Apache, etc. path>\<script_name>.sql

The leading part of the path is prefaced with ORACLE_BASE. Isn't ORACLE_HOME under ORACLE_BASE in the first place? The examples should simply show:

ORACLE_HOME\<rdbms, Apache, etc. path>\<script_name>.sql

In Closing

This demonstration of upgrading a installation to emphasizes several points and facts:

  • Upgrading is not difficult and is nothing more than following a series of steps laid out in the README documentation.

  • Upgrading can be time consuming (time being relative), so plan ahead for downtime and upgrades across your environment, particularly if you use RAC and standby databases.

  • Always, always, take a backup before starting. Do not put yourself or your database into an unrecoverable situation. Although patchsets are cumulative in application, they are not necessarily comprehensive in terms of putting things back the way they were. From and later, you can recover back to your starting version, but the same is not true between and

  • Differentiate between patch and patchset when appropriate, just like when it is necessary to do the same between database and instance.

For DBAs working in a supported environment, patching may seem to be fairly routine. Even though it seems routine, I have seen instances of patches being applied while Oracle was running. What are the consequences? On UNIX, the SQL*Plus executable gets broken; oracle, listener.log and other files suffer permissions/access problems; and Apache (if the HTTP Server was running) outputs core-like dump files to the point where its containing partition becomes full (i.e., no more disk space on the partition); and you are left with orphaned processes. How hard would it have been to follow the instructions in the README file?

» See All Articles by Columnist Steve Callan

Mobile Site | Full Site