Managing Patches
Database patches are cumulative for all previous Critical Patch Updates
– Database patches include non-security fixes
– Windows patches are really version upgrades
– Testing should be similar to a version upgrade (i.e., 9.2.0.7 to 9.2.0.8)
Database patches provide the greatest security benefit –Apply them ASAP
– Apply database patches now, other patches later
– Otherwise, enable Listener Invited Nodes feature
Common CPU Patching Mistakes
1. CPU Forgotten Steps
2. Database Upgrades
3. ORACLE_HOME vs. Database
4. ORACLE_HOME and New Database
#1 CPU Forgotten Steps
CPU is two parts:
1.OPatchto update files in the ORACLE_HOME
2.catcpu.sql to update database objects
2.catcpu.sql to update database objects
Some CPUs require additional manual steps, for example the January 2008 CPU requires all views to be recompiled due view/SQL complier bugs in July 2007 CPU
Remember to query SYS.REGISTRY$HISTORY to verify CPU row is present
#2 Database Upgrades
Scenario:
– Latest CPU patch is applied (January 2009)
– Upgrade database to new version or patchset (10.2.0.3 to 10.2.0.4)
- Do I have to reapply the latest CPU after the database upgrade?
Yes!!!, you must apply 10.2.0.4 January 2009 patch
Database Upgrades and CPU Patches
| Database Version Upgrade Patch | Latest CPU Patch Included In Upgrade Patch |
| 9.2.0.8 | July 2006 |
| 10.1.0.5 | October 2005 |
| 10.2.0.3 | October 2006 |
| 10.2.0.4 | April 2008 |
| 11.1.0.6 | October 2007 |
| 11.1.0.7 | January 2009 |
#3 ORACLE_HOME vs. Database
Scenario:
– Latest CPU patch is applied (January 2009) to ORACLE_HOME
– Install a new database from the patched ORACLE_HOME
- Do I have to run the catcpu.sqlfrom the January 2009 CPU?
Yes!!!, since some of the SQL statements in the catcpu.sql do not exist as files in the Oracle Home
Remembet that catcpu.sql does perform some drops and grants
#4 ORACLE_HOME and New Database
Scenario:
– Latest CPU patch is applied (January 2009) to ORACLE_HOME
– Install a new database from the patched ORACLE_HOME using DBCA and a seeded database
- Do I have to run the catcpu.sqlfrom the January 2009 CPU?
Yes !!!!, since the seeded database files are pre‐loaded with packages and none of the vulnerable packages would be updated without running catcpu.sql
What is OPatch?
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.
Important Note
If Opatch is installed, please remember to set your Oracle Home Directory:
C:\app\product\11.2.0\dbhome_1\OPatch> set ORACLE_HOME=C:\app\product\11.2.0\dbhome_1
And also to add the Opatch Directory to your path:
set PATH=C:\app\product\11.2.0\dbhome_1\OPatch;%PATH%
Then you can see what is installed with:
C:\app\product\11.2.0\dbhome_1\OPatch> cd OPatch
C:\app\product\11.2.0\dbhome_1\OPatch> opatch version
Invoking OPatch 11.1.0.6.6
OPatch Version: 11.1.0.6.6
OR
C:\app\product\11.2.0\dbhome_1\OPatch> opatch lsinventory
Invoking OPatch 11.1.0.6.6
Oracle Interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation. All rights reserved.
Oracle Home : C:\app\product\11.2.0\dbhome_1
Central Inventory : C:\Program Files\Oracle\Inventory
from : n/a
OPatch version : 11.1.0.6.6
OUI version : 11.2.0.1.0
OUI location : C:\app\product\11.2.0\dbhome_1\oui
Log file location : C:\app\product\11.2.0\dbhome_1\cfgtoollogs\opatch\opatch2012-08-27_14-21-49PM.log
Patch history file: C:\app\product\11.2.0\dbhome_1\cfgtoollogs\opatch\opatch_history.txt
Lsinventory Output file location : C:\app\product\11.2.0\dbhome_1\cfgtoollogs\opatch\lsinv\lsinventory2012-08-27_14-21-49PM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.1.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
Opatch has several options to execute it:
- "lsinventory" = Adding the lsinventory option to opatch produces a report saying the patches that were applied
- "-report" = Will report to screen and will NOT apply the real patch. Using "-report" is a good way of performing nondestructive testing of the patch installation process.
- "rollback"= Will undo the patch that was applied. Example: opatch rollback –id 4667809
Applying a patch is simple as:
- opatch lsinventory -> To see he list of patches already installed
- opatch apply
--> To REALLY aplply the patch
Steps to get Opatch (probably not needed, 1 time only)
From a command prompt:
ftp updates.oracle.com (metalink username and password)
ftp> bin
## For 9i R2(9.2.x) and 10GR1(10.1.x)
ftp> cd /2617419
ftp> get p2617419_10102_GENERIC.zip
ftp> quit
## For 10GR2(10.2.x) ftp> cd /4898608
ftp> cd 4898608
ftp> get p4898608_10202_GENERIC.zip
ftp> quit
Opatch Installation (probably not needed, 1 time only)
## For 9i R2(9.2.x) and 10GR1(10.1.x)
Then upload or move the p2617419_10102_GENERIC.zip to ORACLE_HOME and uncompress it. Example:
% mv p2617419_10102_GENERIC.zip $ORACLE_HOME
% cd $ORACLE_HOME
% unzip p2617419_10102_GENERIC.zip
.......
........
% cd OPatch
% opatch version
PERL5LIB=/oracle/V920/Apache/perl/lib/5.00503:./perl_modules; export PERL5LIB
/oracle/V920/Apache/perl/bin/perl ./opatch.pl version
./opatch.pl version: 1.0.0.0.49
## For 10GR2(10.2.x)
% mv p4898608_10202_GENERIC.zip $ORACLE_HOME
% cd $ORACLE_HOME
% unzip p4898608_10202_GENERIC.zip
.....
.....
% cd OPatch
% opatch version
Invoking OPatch 10.2.0.2.1
OPatch Version: 10.2.0.2.1
Finally, you can set the Opatch directory in your PATH variable so you can execute the opatchcommand from anywhere. Example:
For Korn / Bourne shell
% export PATH=$PATH:$ORACLE_HOME/Opatch
For C Shell
% setenv PATH $PATH:$ORACLE_HOME/Opatch
Opatch Post-installation (post-deinstallation) steps (probably not needed, 1 time only)
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.sqlscript 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).
Example Applying Patch 4751921
After the Patch is Installed:
1.Log in as sys as sysdba.
2. cd $ORACLE_HOME/rdbms/admin
3. spool catpatch_database_name
4. shutdown immediate
5. startup migrate
6. @catpatch.sql ( this takes at least 1 hour ). After catpatch completed,
7. select object_name,owner from dba_objects where ststus='INVALID';
( YOU WILL GET BETWEEN 230-3300 INVALID OBJECTS , DON'T PANIC )
8. @utlrp.sql
9.select object_name,owner from dba_objects where ststus='INVALID'; ( YOU WILL GET near 0 invalid objects )
10. shutdown immediate;
11. startup
Listing Patches
All patches that are installed with Oracle's OPatch Utility (Oracle's Interim Patch Installer) can be listed by invoking the opatch command with the lsinventory option. Here is an example:
$ cd $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..
...
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.
Another Method using SYS.REGISTRY$HISTORY Table
Since January 2006, contains 1 row for most recent CPU patch applied
Semi-reliable method for determining if CPU patch is applied
SELECT comments, action_time, id "PATCH_NUMBER", version
FROM sys.registry$history
WHERE action = 'CPU';
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-
So, as yet another way to answer the "what patches are installed" question, you can use the OPatch history file.
References
- Oracle Critical Patch Update January 2009 Advisory, 13 January 2009, http://www.oracle.com/technology/deploy/security/critical-patch-updates/cpujan2009.html
- Security Alerts and Critical Patch Updates -Frequently Asked Questions", 30 July 2007, Oracle Metalink Note ID 360470.1
- Oracle Database Server and Networking Patches for Microsoft Platforms, 23 April 2007, Oracle Metalink Note ID 161549.1
- How can I see if a Critical Patch Update is installed on the database, 26 March 2009, Oracle Metalink Note ID 352783.1
- Critical Patch Update January 2009 Availability Information for Oracle Database and Fusion Middleware Products, 13 January 2009, Oracle Metalink Note ID 753340.1
No comments:
Post a Comment