Database Security and Oracle Patches - Part 4 - Page 2

December 23, 2005

Using OPatch to apply an interim patch

The options used for the apply option are many.

The apply option applies an interim patch to a specified Oracle home. 
The ORACLE_HOME environment variable must be set to the Oracle home to 
be patched. The following syntax is used for this option:
<Path_to_OPatch</opatch apply [-delay (value)] [-force] \
[-invPtrLoc (path)] [-jdk (location)] [-jre (location)] [-local] \
[-minimize_downtime] [-no_bug_superset] [-no_inventory] \
[-oh (Oracle Home location)] \
[-post (options to be passed into post) [-opatch_post_end]]\
[-pre (options to be passed into pre) [-opatch_pre_end]] \
[-retry (value)] [-silent] [-verbose] [-no_relink] \ [-no_sysmod (patch
 location)][-remote_nodes (comma separated node names)][-local_node (node
_name)][patch_location]

With ORACLE_HOME set, the simplest form of the command syntax, using Patch 4667809 as our source, is "opatch apply patch_location," or

opatch apply EMSTagedPatches\4667809

You may have to add an extra patch name (EMSTagedPatches\4667809\4667809) if you unzipped the patch file and let the unzip process create a new folder with the same name. That is why the folder structure below contains two "4667809's"

I added the "-report" parameter in the example below because using this parameter "Prints the action to the screen without executing it." Using "-report" is a good way of performing nondestructive testing of the patch installation process. The output to the screen includes:

Skip patching component oracle.rdbms, 10.2.0.1.0 and its actions.
The actions are reported here, but are not performed.

The final lines of the screen output include an overall status of the patch process.

***********************************************************************
**                                                                   **
** ATTENTION                                                         **
**                                                                   **
** Please note that the Patch Installation (Patch Deinstallation) is **
** not complete until all the Post Installation (Post Deinstallation)**
** instructions noted in the Readme accompanying this patch have been** 
** successfully completed.                                           **
**                                                                   **
***********************************************************************
***********************************************************************
-----------------------------------------------------------------------

The local system has been patched and can be restarted.

The dry run seems to have worked well, so let's try the real thing.

***********************************************************************
**                                                                   **
** ATTENTION                                                         **
**                                                                   **
** Please note that the Patch Installation (Patch Deinstallation) is **
** not complete until all the Post Installation (Post Deinstallation)**
** instructions noted in the Readme accompanying this patch have been** 
** successfully completed.                                           **
**                                                                   **
***********************************************************************
***********************************************************************
-----------------------------------------------------------------------
Return code = 0
The local system has been patched and can be restarted.
OPatch succeeded.

Does the inventory output reflect any changes?

C:\>%ORACLE_HOME%\opatch\opatch lsinventory
Invoking OPatch 10.2.0.1.0
Oracle interim Patch Installer version 10.2.0.1.0
Copyright (c) 2005, Oracle Corporation.  All rights reserved..

Oracle Home       : C:\oracle\product\10.2.0\db_1
Central Inventory : n/a
   from           : C:\Program Files\Oracle\Inventory
OPatch version    : 10.2.0.1.0
OUI version       : 10.2.0.1.0
OUI location      : C:\oracle\product\10.2.0\db_1\oui
Log file location : $OH\cfgtoollogs\opatch\opatch-<date>.log
Lsinventory Output file location : $OH\cfgtoollogs\opatch\lsinv\lsinventory-<date>.txt
-----------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 10g                                                  10.2.0.1.0
There are 1 products installed in this Oracle Home.

Interim patches (1) :
Patch  4667809      : applied on Sat Dec 17 22:27:54 MST 2005
   Created on 24 Nov 2005, 07:28:10 hrs
   Bugs fixed:
     4629654, 4569842, 4383610, 4554682, 4562889, 4505011, 4577670, 
     4629844, 4529672, 4667809, 4439465, 4583539
-----------------------------------------------------------------------
OPatch succeeded.

The log files shows which files were touched (partial list shown below).

Files Touched:
     classes12.jar --> ORACLE_HOME\jdbc\lib\classes12.jar
     classes12.zip --> ORACLE_HOME\jdbc\lib\classes12.zip
     classes12dms.jar --> ORACLE_HOME\jdbc\lib\classes12dms.jar
     ojdbc14dms.jar --> ORACLE_HOME\jdbc\lib\ojdbc14dms.jar
     ojdbc14.jar --> ORACLE_HOME\jdbc\lib\ojdbc14.jar
     oracle.sym --> ORACLE_HOME\rdbms\admin\oracle.sym
     oracle.exe --> ORACLE_HOME\bin\oracle.exe
     catcpu.sql --> ORACLE_HOME\bundle\Patch1\catcpu.sql

Rolling back the patch

OPatch can also rollback patches, so let's see how easy this process is. Before rolling back the patch, I will add the "-help" option. OPatch produces a significant amount of help (plus the output is stored in a log file). The syntax list of options is fairly long, and the simplest version is just

opatch rollback –id <the patch ID number>

DESCRIPTION
    Rollback an existing one-off patch indicated by the reference-id.
SYNTAX
opatch rollback -id <ID> [-ph <Patch Location>]  [-delay <value>]
              [-invPtrLoc <Path to oraInst.loc> ] [-jdk <LOC>]
              [-jre <LOC> ] [-local]  [-oh <ORACLE_HOME>]
              [-retry <value>]  [-silent]  [-verbose]
              [-no_relink] [-pre <parameters for the pre
              script in escaped double quotes> [-opatch_pre_end] ]
              [-post <parameters for the post script in escaped
              double quotes>[ -opatch_post_end] ] [-no_sysmod]
              [-property_file <path to property file>]
              [-local_node <Local node name>]
              [-remote_nodes <List of remote nodes (node1,node2)>]

After executing opatch rollback –id 4667809, the software installation is back to 10.2.0.1 across the board.

Post-installation (post-deinstallation) steps

Don't forget to check the readme file for post-installation/deinstallation steps. In general, the post-installation steps require running of the catcpu.sql script, followed by using the utlprp.sql script used to recompile invalid objects. Is that SQL script named utlrp or utlprp? In this case, the README file says utlprp, whose function is essentially the same as that of utlrp. What is the difference between the scripts? As it turns out, utlrp simply calls utlprp. The deinstallation tasks are nearly identical, with the main difference being that you use the catcpu_rollback SQL script.

For Patch 4667809, the catcpu/catcpu_rollback scripts are related to applying and unapplying changes related to the OWA toolkit (OWA = Oracle Web Agent, which uses modplsql, which uses PL/SQL to generate dynamic Web pages, and that's why you see modplsql in the patch-related files folders).

The README file (text and HTML-based) for the patch shows catcpu being run from within its directory. If you try to run catcpu (I applied the patch again) from outside the bundle\patch1 directory, the script will fail because it looks for MODPLSQL in a relative as opposed to absolute path/directory location. The output from running the script shows 19 invalid objects ("to for" is from Oracle).

SQL> @catcpu
Session altered.
Session altered.
OWA_MESSAGE
-----------------------------------------------------------------------
Installed OWA version is: 10.1.2.0.0
You already have a newer version of the OWA packages
No install is required
No. of Invalid Objects is :19
Please refer to README.html to for instructions on validating these objects
Logfile for the current catcpu.sql session is : APPLY_ORCL_<date>.log
SQL>

Running utlprp.sql as is will cause a prompt for user input (which utlrp passed in for you). Enter a "1" and the script will proceed.

One other useful Opatch feature

Along with the log and inventory files, Opatch output includes a history file, which contains date and action performed information. The history file is named opatch_history.txt and is located in the $OH\cfgtools\opatch directory. As an example of its contents, the "rollback –help" action performed earlier was recorded as:

Date & Time : Sun Dec 18 12:00:50 MST 2005
Oracle Home : C:\oracle\product\10.2.0\db_1
OPatch Ver. : 10.2.0.1.0
Current Dir : C:\
Command     : rollback -help 
Log File    : $OH\cfgtoollogs\opatch\opatch-<date>.log

So, as yet another way to answer the "what patches are installed" question, you can use the OPatch history file.

In Closing

In the last four parts of this series, we have covered manual and not-so-manual methods of applying patches to Oracle's RDBMS software. Three significant changes – use of 10g's Database Control patch administration utility, the addition of a security site at OTN, and the improved distribution and documentation of OPatch – represent a huge improvement over what existed in the past. The next step taken by Oracle may incorporate something similar to what Microsoft, Sun, and other vendors do for their products: automatic updates and notifications of new updates. In the next and last part of this series, we will check to see if applying a patch (still) fixes a previous security flaw and go into some of the literature about hacking a database.

» See All Articles by Columnist Steve Callan