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