Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 22, 2005

Database Security and Patches - Part 2 - Page 3

By Steve Callan

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 or higher; so once applied to your 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 and the query against DBA_REGISTRY show

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
------- ---------------------------------------------

With an SPFILE, the two pool sizes are altered with:

System altered.
System altered.

Shutdown, startup migrate, and run catpatch.sql:

SQL> conn / as sysdba
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
Oracle9i Packages and Types         VALID
Oracle Workspace Manager            VALID
JServer JAVA Virtual Machine        VALID
Oracle XDK for Java                 VALID
Oracle9i Java Packages              VALID
Oracle interMedia                   VALID
Spatial                             VALID
Oracle Text                         VALID
Oracle XML Database                 VALID
Oracle Ultra Search                 VALID
Oracle Data Mining                  VALID
OLAP Analytic Workspace             UPGRADED
Oracle OLAP API                     UPGRADED
OLAP Catalog                        VALID

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 installation to 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 and later, you can recover back to your starting version, but the same is not true between and

  • 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

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM