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 220.127.116.11 or higher; so once applied to your 18.104.22.168 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 22.214.171.124.0 and the query
against DBA_REGISTRY show 126.96.36.199.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 188.8.131.52.0
Oracle9i Packages and Types VALID 184.108.40.206.0
Oracle Workspace Manager VALID 220.127.116.11.0
JServer JAVA Virtual Machine VALID 18.104.22.168.0
Oracle XDK for Java VALID 22.214.171.124.0
Oracle9i Java Packages VALID 126.96.36.199.0
Oracle interMedia VALID 188.8.131.52.0
Spatial VALID 184.108.40.206.0
Oracle Text VALID 220.127.116.11.0
Oracle XML Database VALID 18.104.22.168.0
Oracle Ultra Search VALID 22.214.171.124.0
Oracle Data Mining VALID 126.96.36.199.0
OLAP Analytic Workspace UPGRADED 188.8.131.52.0
Oracle OLAP API UPGRADED 184.108.40.206.0
OLAP Catalog VALID 220.127.116.11.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 18.104.22.168 installation to 22.214.171.124 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 126.96.36.199 and later, you can recover back to your starting version, but the
same is not true between 188.8.131.52 and 184.108.40.206.
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