Database Security and Patches - Part 2 - Page 3

November 22, 2005

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