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 18.104.22.168 (for Windows NT/2000/XP)
exactly what you get on the download from Oracle Technology Network. The
patch process uses Patchset 4163445 (22.214.171.124 PATCHSET FOR ORACLE DATABASE
Overview of the patch process
There are four general
phases involved in the patch process:
Get the patchset (MetaLink
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 126.96.36.199 to an HP Itanium running
Red Hat Linux includes running a CPU-related fix-it script in addition to a
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
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 188.8.131.52 software installation into a 184.108.40.206
version, we have to apply a patchset.
Additionally, since the base
releases tend to start off with a one, the "7" in "220.127.116.11"
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
Oracle's definition of a patch set
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
Patch sets are cumulative. Patch set release 18.104.22.168 includes all fixes
in patch sets 22.214.171.124 and earlier as well as new fixes for patch set 126.96.36.199.
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.
The table below summarizes
how Oracle software is released.
Installing patchset 188.8.131.52
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 10.1.0.4, 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
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
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 184.108.40.206 or higher; so once applied to your 220.127.116.11 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 18.104.22.168.0 and the query
against DBA_REGISTRY show 22.214.171.124.0?
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
SQL> show parameter pfile
With an SPFILE, the two pool
sizes are altered with:
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='150M' SCOPE=spfile;
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile;
Shutdown, startup migrate,
and run catpatch.sql:
SQL> conn / as sysdba
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
> spool patch.log (optional, don't forget to turn off)
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 126.96.36.199.0
Oracle9i Packages and Types VALID 188.8.131.52.0
Oracle Workspace Manager VALID 184.108.40.206.0
JServer JAVA Virtual Machine VALID 220.127.116.11.0
Oracle XDK for Java VALID 18.104.22.168.0
Oracle9i Java Packages VALID 22.214.171.124.0
Oracle interMedia VALID 126.96.36.199.0
Spatial VALID 188.8.131.52.0
Oracle Text VALID 184.108.40.206.0
Oracle XML Database VALID 220.127.116.11.0
Oracle Ultra Search VALID 18.104.22.168.0
Oracle Data Mining VALID 22.214.171.124.0
OLAP Analytic Workspace UPGRADED 126.96.36.199.0
Oracle OLAP API UPGRADED 188.8.131.52.0
OLAP Catalog VALID 184.108.40.206.0
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:
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:
Apache, etc. path>\<script_name>.sql
This demonstration of
upgrading a 220.127.116.11 installation to 18.104.22.168 emphasizes several points and facts:
Upgrading is not difficult and is
nothing more than following a series of steps laid out in the README
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 22.214.171.124 and later, you can recover back to your starting version, but the
same is not true between 126.96.36.199 and 188.8.131.52.
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