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 9.2.0.4 or higher; so once applied to your 9.2.0.1 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 9.2.0.7.0 and the query
against DBA_REGISTRY show 9.2.0.1.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
NAME VALUE
------------------ --------
java_pool_size 33554432
shared_pool_size 50331648
SQL> show parameter pfile
NAME VALUE
------- ---------------------------------------------
spfile %ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA
With an SPFILE, the two pool
sizes are altered with:
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='150M' SCOPE=spfile;
System altered.
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile;
System altered.
Shutdown, startup migrate,
and run catpatch.sql:
SQL> conn / as sysdba
Connected.
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 9.2.0.7.0
Oracle9i Packages and Types VALID 9.2.0.7.0
Oracle Workspace Manager VALID 9.2.0.1.0
JServer JAVA Virtual Machine VALID 9.2.0.7.0
Oracle XDK for Java VALID 9.2.0.9.0
Oracle9i Java Packages VALID 9.2.0.7.0
Oracle interMedia VALID 9.2.0.7.0
Spatial VALID 9.2.0.7.0
Oracle Text VALID 9.2.0.7.0
Oracle XML Database VALID 9.2.0.7.0
Oracle Ultra Search VALID 9.2.0.7.0
Oracle Data Mining VALID 9.2.0.7.0
OLAP Analytic Workspace UPGRADED 9.2.0.7.0
Oracle OLAP API UPGRADED 9.2.0.7.0
OLAP Catalog VALID 9.2.0.7.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:
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 9.2.0.1 installation to 9.2.0.7 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 9.2.0.4 and later, you can recover back to your starting version, but the
same is not true between 9.2.0.7 and 9.2.0.1.
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
» 
Comment and Contribute
