Oracle RDBMS Patching

1
Oracle RDBMS Patching

• Brian HitchcockOCP 8, 8i, 9i DBA
• Sun Microsystems
• brian.hitchcock_at_sun.com
• brhora_at_aol.com

NoCOUG
Brian Hitchcock May 6, 2004
Page 1
2
Why Patch the RDBMS?

• To upgrade
• For example 8.1.7.0 to 8.1.7.4
• One-off patch
• Fix a specific bug
• Security patches
• Fix specific security issues for specific
  products
• This is the focus here
• But notice that I end up patching to 8.1.7.4 as
  well

3
Patching In General

• Is becoming a bigger issue
• More patches more often
• More patches for more products
• Think this is bad?
• Oracle apps patching makes this look easy
• Apps 11i patching is more complex
• Many more modules, interactions

4
Patching In General

• And, more fun
• No way to back out of a patch
• In general
• Specific patches may say you can deinstall
• But what if that patch required 8.1.7.4?
• Once applied, only one way to go back
• Full restore of ORACLE_HOME from backup
• No way to tell what patch level a database is at
• Other than version such as 8.1.7.4
• You must manually keep track of patches applied

5
Patching In General

• How often do you patch?
• Every time a new security patch is available?
• Quarterly?
• Security risk until latest patch(es) applied?
• Testing for each patch?
• For bug fix patch, testing is clear
• For other types of patches
• None?
• Complete?
• In between?

6
Patch Testing Details

• What is your policy?
• Apply all needed patches, test?
• Apply one patch and test?
• If testing shows problems, what to do?
• Need to test
• Your app software
• Vendor app software
• OS issues
• Security, chroot, other software components

7
How Do You Know?

• What patch(es) do you need to apply?
• Security alerts from Oracle
• Must review each one manually
• Metalink
• Your environment has hit a specific bug
• Need specific functionality
• Feature isnt available until 9.2.0.4

8
How Do You Know?

• For security patches
• Oracle sends out security alerts
• Each alert applies to specific products
• Your site doesnt need all of them
• No source for a single list of which patches you
  need
• I like to file a TAR to confirm the patches I
  need
• Some patches require other patches
• Fun, fun, fun!

9
Example, for 8.1.7.0

• Get current with all security alerts
• Political
• Nothing was done for a long time
• A manager read about a recent oracle alert
• Suddenly we have to apply lots of patches

10
Why Discuss 8.1.7.0?

• 8.1.7.0 is not cool!
• Cool DBAs only talk about 10g!
• But real world has 8.1.7.X databases
• The older a db version becomes the more patches
  you will need to stay current
• Same issues are happening for 9i
• Will happen for 10g
• Process is the same, starting version doesnt
  matter

11
Finding Security Alerts

• Metalink
• FAQ for security alerts
• Doc id 237007.1
• Item I, generic questions
• Number 10, what security patches do I need for my
  database?
• Points to number 13, security patch matrix
• 8.1.7.4 doesnt need patches below 48
• 9.2.0.4 doesnt need patches below 59
• When I did this I needed 48, 49, 50, 51, 54
• Security alert 62 hadnt been issued at that
  time
• Today I would need 62 as well

12
Finding Security Alerts

• FAQ for security alerts (contd)
• Item II, list of security alerts and notes
• Lists security alerts 18 through 66
• Review each security alert for patch
• Security alert 66 is most recent as of today
• Check Metalink frequently
• 237007.1 changed may 07, 2004 while I was
  creating the previous slide
• Note that more products means more patches
• Database plus app server etc.

13
Security Alerts

• Listing of security alerts from doc id 237007.1

II. List of Security Alerts and Notes (since Nov
2001) II.1. Security Alerts Doc 265308.1
Security Alert 66 Vulnerabilities in Oracle
Application Server Web Cache Doc 258997.1
Security Alert 65 Security Vulnerability in
Oracle9i Application and Database Servers Doc
263508.1 Security Alert 64 Buffer Overflow in
Oracle9i Database Server Doc 263509.1 Security
Alert 63 Security Vulnerabilities in Oracle9i
Lite Doc 258996.1 Security Alert 62 SSL Update
for CERT CA-2003-26 and older SSL issues Doc
253982.1 Security Alert 61 SQL Injection
Vulnerability in Oracle9i Application Server Doc
252706.1 Security Alert 60 Unauthorized Access
to Restricted Content in Oracle Files Doc
251910.1 Security Alert 59 Buffer Overflow in
Oracle Binaries Doc 246202.1 Security Alert 58
Buffer Overflow in the XML Database of Oracle9i
Database Server Doc 244523.1 Security Alert 57
Buffer Overflows in EXTPROC of Oracle Database
Server Doc 244335.1 Security Alert 56 Buffer
Overflow Vulnerability in Oracle E-Business
Suite Doc 244294.1 Security Alert 55
Unauthorized Disclosure of Information in Oracle
E-Business Suite Doc 237172.1 Security Alert 54
Buffer Overflow in Oracle Net Services for Oracle
Database Server Doc 235262.1 Security Alert 53
Report Review Agent (RRA/FNDFS) Vulnerability in
Oracle E-Business Suite Doc 229288.1 Security
Alert 52 Two Vulnerabilities in Oracle9i
Application Server Doc 229287.1 Security Alert
51 Buffer Overflow in the Oracle Executable of
Oracle Database Server Doc 229286.1 Security
Alert 50 Buffer Overflow in Oracle Database
14
Security Alerts
Doc 229285.1 Security Alert 49 Buffer Overflow
in Oracle Database Doc 229284.1 Security Alert
48 Buffer Overflow in Oracle Database Doc
224215.1 Security Alert 47 Vulnerabilities in
Oracle 9i Application Server Doc 216775.1
Security Alert 46 Buffer Overflow in iSQLPlus
(Oracle9i Database Server) Doc 214356.1 Security
Alert 45 Security Release of Apache 1.3.27 Doc
213415.1 Security Alert 44 Unauthorized Access
Vulnerability in the Oracle E-Business Doc
213413.1 Security Alert 43 Oracle9i Application
Server - Web Cache Administration Tool Crash on
Malformed Request Doc 213411.1 Security Alert
42 Security Vulnerability in Oracle Net Doc
207272.1 Security Alert 41 Oracle9i Application
Server Oracle Java Server Page Demos
Vulnerability Doc 207269.1 Security Alert 40
Oracle Net Listener Vulnerabilities Doc 207271.1
Security Alert 39 Oracle9i Application Server -
Web Cache Administrator Password Not
Encrypted Doc 207268.1 Security Alert 38
Security vulnerability in Oracle Net Doc 206034.1
Security Alert 37 OpenSSL Security
Vulnerability Doc 200873.1 Security Alert 36
Security Vulnerability in Apache HTTP Server of
Oracle9iAS Doc 198531.1 Security Alert 35
Buffer Overflow Vulnerability in Oracle9iAS
Reports Doc 198544.1 Security Alert 34 Security
Vulnerability in Oracle Net (Oracle9i Database
Server) Doc 185074.1 Security Alert 33 User
Privileges Vulnerability in Oracle9i Database
Server Doc 185073.1 Security Alert 32
Unauthorized Access Vulnerability in the Oracle
E-Business Suite Doc 182244.1 Security Alert 31
Oracle Configurator Security Issue Potential
Cross-site Scripting Attacks Doc 183556.1
Security Alert 30 SNMP Vulnerability in Oracle
Enterprise Manager, Master_Peer Agent Doc
175429.1 Security Alert 29 ALERT Oracle PL/SQL
extproc in Oracle 9i, Oracle 8i and Oracle8
Database
15
Security Alerts
Doc 175428.1 Security Alert 28 Vulnerabilities
in Oracle mod_plsql and JSP in Oracle 9iAS
V1.0.2.x Doc 169628.1 Security Alert 27
Vulnerabilities in Oracle 9i Application Server
Web Cache Doc 168862.1 Security Alert 26
Potential DoS Vulnerability in Oracle9i
Application Server Doc 168863.1 Security Alert
25 Vulnerabilities in MODPLSQL No Doc
Security Alert 24 Skipped Multiple Doc
(Security Alert 23 is split into 3 documents on
MetaLink) Doc 167001.1 Security Alert 23 Oracle
Home Environment Variable Buffer Overflow Doc
167004.1 Security Alert 23 CHOWN Path
Environment Variable Vulnerability Doc 167007.1
Security Alert 23 Oracle Home Environment
Variable Validation Vulnerability Doc 166869.1
Security Alert 22 Security Implications of the
Oracle9iAS v.1.0.2.2 Default SOAP
Configuration Doc 163726.1 Security Alert 21
Oracle Label Security Mandatory Security
Patch Doc 163727.1 Security Alert 20 Oracle
File Overwrite Security Vulnerability Doc
163728.1 Security Alert 19 Oracle Trace
Collection Security Vulnerability Doc 163729.1
Security Alert 18 Oracle9iAS Web Cache Overflow
Vulnerability
16
Patches Needed

• For security alerts
• 48, 49, 50, 51, 54
• Review each alert to find needed patch info
• Need patches
• 2376472 (8.1.7.4)
• 2642117 (alert 48) 8.1.7.4 required
• 2642267 (alert 49) 8.1.7.0 required
• 2642439 (alert 50) 8.1.7.0 required
• 2620726 (alert 51) 8.1.7.4 required
• 2784635 (alert 54) 8.1.7.4 required

17
Patches Needed

• Create stage directory for each patch
• Ftp from oracle
• Patches require patches
• To apply some of these security patches
• You must be at 8.1.7.4
• Patch to 8.1.7.4 before applying these patches
• Note that I had no plan to patch to 8.1.7.4
• One patch leads to other patches

18
Getting Patches

• Metalink
• Patches
• Simple Search
• Enter specific patch number
• Specify platform
• Download
• Patch zip file
• Readme file

19
Getting Patches

• What is patch number for 8.1.7.4 patch?
• Should be simple to find
• Metalink
• Patches
• Simple search
• Product Oracle Database Family
• Release 8.1.7
• Patch type Patchset/Minipack
• Platform Solaris Sparc 32-bit
• 24 results
• Correct patch?
• 2376472 8.1.7.4 Patch set for oracle data server

20
Patching Process

• What does it take to apply a patch?
• Dot release
• 8.1.7.4
• Oracle installer (OUI)
• One-off, security patches
• README shows steps to install patch
• Example, security patch
• Shutdown database, listener
• Execute patch.sh supplied as part of patch

21
Patching Process

• Production
• Must backup ORACLE_HOME
• Full backup of database
• Document the db
• This will come up later
• I use dbdoc script, see Managing Multiple
  Databases on NoCOUG website
• If patch fails
• Restore ORACLE_HOME from backup

22
Patching Process

• Development
• Full export
• Document the db
• If patch fails
• Reinstall Oracle software
• Import export
• However,
• If practicing prod patching on dev db
• Should practice the prod db process

23
Fresh Install?

• Before creating any databases
• Install Oracle software
• Apply all needed patches
• Much quicker
• Many post patch steps only apply if database
  already exists

24
Patch Install Steps

• Can be simple
• Can be complex
• Example, 8.1.7.4 patch
• May require use of Oracle Installer
• May require use of OUI that is part of the patch
• Patch may require certain patch level
• Example, patch can only be applied to 8.1.7.4
• You must review the README file for each patch
• Script the steps for each patch

25
Cases

• 1) OraInventory not in place
• 2) Installer not in place
• 3) 64-bit oracle
• 4) chroot
• 5) not following instructions

26
Case1 -- OraInventory

• Existing 8.1.7.0 database
• Patch to latest security alert
• At the time, this was security alert 54
• Downloaded all needed patches
• 8.1.7.4
• 2642117 (alert 48)
• 2642267 (alert 49)
• 2642439 (alert 50)
• 2620726 (alert 51)
• 2784635 (alert 54)

27
Case 1 -- OraInventory

• Review 8.1.7.4 readme
• Existing database
• Many post patch tasks
• Before applying 8.1.7.4
• Backup db
• Shutdown db
• Shutdown listener

28
Case 1 -- OraInventory

• Script the steps
• Patch readme file README_8174.html
• How to install this patch set
• Steps 6 through 18
• Oracle Label Security
• Disabling system triggers
• Check JIS
• Catalog.sql, catproc.sql
• Set 10520 trace
• Java objects
• Enable system triggers
• Recompile invalid objects

29
Case 1 -- OraInventory

• Start installer
• Installer not installed
• Find original cpio files from 8.1.7.0 install
• Run installer (OUI) from there
• Script inputs for installer
• File locations
• Source
• Destination
• UNIX group name

30
Case 1 -- OraInventory

• And now?
• Dependencies
• There are no patches that need to be applied from
  the patch set Oracle 8i 8.1.7.4.0
• Huh?
• Off to Metalink
• Doc ID 115236.1
• OraInventory is missing

31
Case 1 -- OraInventory

• What is OraInventory?
• Documents exactly what was installed
• Created as part of software installation
• Created by the installer
• What does it do?
• When installing a patch
• Installer checks OraInventory
• Verifies that patch should be applied
• Example, 8.1.7.4 patch on 8.1.7.0 Oracle_home

32
Case 1 -- OraInventory

• Where does it live?
• Installer creates in Oracle_base
• (my experience)
• What happened here?
• oraInventory didnt exist
• Installer couldnt tell what had been installed
• Installer decided it couldnt install anything
• No inventory, cant apply any patches

33
Case 1 -- OraInventory

• Ok, but what caused this?
• To save time, copy existing oracle installation
• Tar up oracle_home
• Move to new machine
• Untar
• Lovingly referred to as TarToss
• my manager came up with that
• This isnt supported by Oracle
• This saves time initially
• Wastes time later

34
Case 1 -- OraInventory

• OK, thats weird, but what now?
• How to re-create the inventory?
• There is only one way
• Reinstall the Oracle software
• In this case, a full reinstall of 8.1.7.0
• Reinstall will over-write oracle_home
• Anything you cant lose?
• Tnsnames.ora, password file
• Dont place anything of your own in oracle_home
• Document your database before patching

35
Case 1 -- OraInventory

• How to be sure
• Nothing unique in oracle_home?
• Cant be sure
• Make backup
• I had enough disk space
• Copy oracle_home to another filesystem
• Now need to reinstall 8.1.7.0
• Disk space to stage the software?

36
Case 1 -- OraInventory

• After software reinstalled
• Install 8.1.7.4 patch
• Works this time!
• Apply the 5 patches in order
• Startup the database
• Test application
• Everyone is happy!
• But this took much longer than we planned

37
Case 2 -- Installer Not In Place

• Applying same patches to another machine
• Installer not installed
• Base software (8.1.7.0) not on disk
• Not enough disk space for software CD image
• Have to free up disk space just to
• Copy the CD image to get the installer on disk
• Proceed with the patching process
• Saves disk space in the short term
• Wastes time later

38
Case 3 - 64-bit Oracle

• Different scenario
• No security patches
• Simple patch from 8.1.7.0 to 8.1.7.4
• No problem
• Stage the 8.1.7.4 patch to the db machine
• Downtime for patching is almost here
• Reviewing dbdoc output
• Select from vversion shows
• Oracle 8i - 64bit Production

39
Case 3 - 64-bit Oracle

• 64-bit Oracle?
• This is a development db
• Production is 32-bit
• I assumed dev would be 32-bit
• I staged the 32-bit 8.1.7.4 patch
• 20 minutes to
• Download 64-bit patch from Oracle web site
• Check README for 64-bit, same as 32-bit
• Calm down
• No one can explain why

40
Case 4 -- chroot

• Yet another environment
• All set to apply patches
• Shutdown database, listener
• Start installer
• Cant display OUI GUI back to my workstation
• Chroot
• Removes many OS libraries
• Have to manually identify which are needed
• Copy from another system

41
Case 5 Complete the Patch

• User calls
• Dev db doesnt work
• Error is blah blah blah
• Metalink
• Error seen when patch partially applied
• Call user
• Did you apply a patch?
• Yes
• Did you complete all the post patch steps?
• Oh, umh, ok, thanks!
• Didnt hear from the user again

42
Lessons Learned

• Verify
• OraInventory exists
• If not, enough disk space to backup oracle_home?
• Installer is installed
• If not, disk space for source CDs?
• Correct patch(es)
• 32-bit versus 64-bit
• Installer GUI can display to your workstation
• Finish all patch install steps
• Document this

43
Lessons Learned

• For a new install
• Oracle_home not a top level directory
• Oracle_base /u01/app/oracle
• Oracle_home ORACLE_BASE/product/ltversiongt
• Oracle_home /u01/app/oracle/product/8.1.7.0
• Install the installer
• A 10 minute patch can become a 5 hour mess
• Verify things before the scheduled patch time
• Document all the steps
• Takes time the first time
• Saves time on all the other servers
• Saves time when you have to redo things