Database Security and Oracle Patches - Part 4

Friday Dec 23rd 2005 by Steve Callan
Share:

In part 3 of this series, a patch for an Oracle 10.2.0.1 database was obtained from MetaLink and cached in a cache repository. In Part 4, the Oracle's OPatch utility will be used to actually apply the cached patch.

At the end of Part 3, a patch for an Oracle 10.2.0.1 database was obtained from MetaLink and cached in a cache repository. During the installation, the patch was not applied (although it could have been at that time). In this part of the series, Oracle's OPatch utility will be used to actually apply the cached patch.

What is OPatch?

Detailed information about OPatch is available in Chapter 8 (Oracle Software Patching Using OPatch) of Oracle® Universal Installer and OPatch User's Guide
10g Release 2 (10.2) for Windows and UNIX
. From the documentation:

OPatch is an Oracle supplied utility to assist you with the process of applying interim patches to Oracle's software. OPatch is a Java-based utility which requires the Oracle Universal Installer to be installed. It is platform independent and runs on all supported operating systems. OPatch supports the following:

  • Applying an interim patch.
  • Rolling back the application of an interim patch.
  • Detecting conflict when applying an interim patch after previous interim patches have been applied. It also suggests the best options to resolve a conflict.
  • Reporting on installed products and interim patch.

Prior to release 10.2 (OPatch for 10.2 is only compatible with 10.2 and nothing earlier), OPatch was available from MetaLink as a patch in and of itself (p2617419_10102_GENERIC.zip for release 10.1.0.2). With 10.2, OPatch is installed as part of the RDBMS software.

A question frequently seen on Oracle-related Web sites is "how do I find out what version or patch am I using?" One of OPatch's features - the last bulleted item in the list above – is its ability to report on installed products.

Putting OPatch to work

Although "opatch" is an Oracle executable, it is not located in the bin directory. Under the OPatch directory in ORACLE_HOME, there exists a Perl (for UNIX) and a batch (for Windows) opatch file. Running "opatch" by itself produces the following output. The log file location was edited where "<>" appears (and yes, there really are two periods after "All rights reserved," but that is from Oracle, not me).

C:\>%ORACLE_HOME%\Opatch\opatch
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 : C:<$OH>\cfgtoollogs\opatch\opatch-<date_info>.log

 Usage: opatch [ -help ] [ -r[eport] ] [ command ]
            command := apply
                       lsinventory
                       query
                       rollback
                       version
 <global_arguments> := -help       Displays the help message for the command.
                       -report     Print the actions without executing (deprecated).
 example:
   'opatch -help'
   'opatch apply -help'
   'opatch lsinventory -help'
   'opatch rollback -help'

OPatch succeeded.
C:\>
Output from using "opatch" by itself
Adding the lsinventory option to opatch produces output like so:

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 : C:<$OH>\cfgtoollogs\opatch\opatch-<date>.log
Lsinventory Output file location : 
  C:\oracle\product\10.2.0\db_1\cfgtoollogs\opatch\lsinv\lsinventory-2005_Dec
-----------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 10g                                                  10.2.0.1.0
There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.
-----------------------------------------------------------------------
OPatch succeeded.
Output from using "opatch" by itself

Adding the lsinventory option to opatch produces output like so:

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 : C:<$OH>\cfgtoollogs\opatch\opatch-<date>.log
Lsinventory Output file location : 
  C:\oracle\product\10.2.0\db_1\cfgtoollogs\opatch\lsinv\lsinventory-2005_Dec
-----------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 10g                                                  10.2.0.1.0
There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.
-----------------------------------------------------------------------
OPatch succeeded.
Opatch "lsinventory" report

Going one step further, adding the report parameter (opatch lsinventory –detail) produces an extensive report of installed components. Instead of trying to read everything on the screen, review the log file because it contains what is shown on your monitor (but in a slightly different format). A partial log file is shown below.

SEVERE:OPatch invoked as follows: 'lsinventory -detail '
INFO:Starting LsInventorySession at Sat Dec 17 21:15:32 MST 2005
INFO:Lsinventory Output file location : $OH\cfgtoollogs\opatch\lsinv\lsinventory-<date>.txt 
INFO:------------------------------------------------------------------
INFO:Installed Top-level Products (1): 
INFO:Oracle Database 10g                             10.2.0.1.0
INFO:There are 1 products installed in this Oracle Home.
INFO:
INFO:Installed Products (114): 
INFO:Agent Required Support Files                    10.2.0.1.0
INFO:Assistant Common Files                          10.2.0.1.0
INFO:Bali Share                                      1.1.18.0.0
INFO:Buildtools Common Files                         10.2.0.1.0
INFO:Character Set Migration Utility                 10.2.0.1.0
INFO:Database Configuration and Upgrade Assistants   10.2.0.1.0
INFO:Database SQL Scripts                            10.2.0.1.0
INFO:Database Workspace Manager                      10.2.0.1.0
INFO:DBJAVA Required Support Files                   10.2.0.1.0
INFO:Enterprise Edition Options                      10.2.0.1.0
Detailed reporting from lsinventory

However, a better-looking output file is contained under the lsinv directory (the "INFO:" parts of each line are not displayed). As you can see, a very simple command answers the commonly seen "how do I see what my patch information is" question.

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]
Opatch apply's options

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"


File system structure of staged patches

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.
Extract of the output using the "–report" option

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.
Extract of the output from applying the patch

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.
Updated "lsinventory" output after applying the patch

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
Extract from the log file after applying the patch

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)>]
Extract from the log file made by using the "–help" option

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 catcpu from the $OH\bundle\patch1 directory

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
Extract from the opatch_history.txt file

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved