Database Security and Patches - Part 2 - Page 3
November 22, 2005
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:
If necessary, you can back out a patch by using the catbkout.sql script. However, this only applies to base releases of 184.108.40.206 or higher; so once applied to your 220.127.116.11 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 18.104.22.168.0 and the query against DBA_REGISTRY show 22.214.171.124.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 126.96.36.199.0 Oracle9i Packages and Types VALID 188.8.131.52.0 Oracle Workspace Manager VALID 184.108.40.206.0 JServer JAVA Virtual Machine VALID 220.127.116.11.0 Oracle XDK for Java VALID 18.104.22.168.0 Oracle9i Java Packages VALID 22.214.171.124.0 Oracle interMedia VALID 126.96.36.199.0 Spatial VALID 188.8.131.52.0 Oracle Text VALID 184.108.40.206.0 Oracle XML Database VALID 220.127.116.11.0 Oracle Ultra Search VALID 18.104.22.168.0 Oracle Data Mining VALID 22.214.171.124.0 OLAP Analytic Workspace UPGRADED 126.96.36.199.0 Oracle OLAP API UPGRADED 188.8.131.52.0 OLAP Catalog VALID 184.108.40.206.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:
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:
This demonstration of upgrading a 220.127.116.11 installation to 18.104.22.168 emphasizes several points and facts:
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?