Oracle RDBMS Patching Brian Hitchcock OCP 8 8
Oracle RDBMS Patching Brian Hitchcock OCP 8, 8 i, 9 i DBA Sun Microsystems brian. hitchcock@sun. com brhora@aol. com No. COUG Brian Hitchcock May 6, 2004 Page 1
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… No. COUG Brian Hitchcock May 6, 2004 Page 2
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 11 i patching is more complex Ÿ Many more modules, interactions No. COUG Brian Hitchcock May 6, 2004 Page 3
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 No. COUG Brian Hitchcock May 6, 2004 Page 4
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? No. COUG Brian Hitchcock May 6, 2004 Page 5
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 No. COUG Brian Hitchcock May 6, 2004 Page 6
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 isn’t available until 9. 2. 0. 4 No. COUG Brian Hitchcock May 6, 2004 Page 7
How Do You Know…? Ÿ For security patches – Oracle sends out security alerts Ÿ Each alert applies to specific products Ÿ Your site doesn’t 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! No. COUG Brian Hitchcock May 6, 2004 Page 8
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 No. COUG Brian Hitchcock May 6, 2004 Page 9
Why Discuss 8. 1. 7. 0? Ÿ 8. 1. 7. 0 is not cool! Ÿ Cool DBAs only talk about 10 g! Ÿ 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 9 i – Will happen for 10 g Ÿ Process is the same, starting version doesn’t matter No. COUG Brian Hitchcock May 6, 2004 Page 10
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 doesn’t need patches below #48 9. 2. 0. 4 doesn’t need patches below #59 – When I did this I needed 48, 49, 50, 51, 54 Ÿ Security alert #62 hadn’t been issued at that time – Today I would need #62 as well… No. COUG Brian Hitchcock May 6, 2004 Page 11
Finding Security Alerts Ÿ FAQ for security alerts (cont’d) – 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. No. COUG Brian Hitchcock May 6, 2004 Page 12
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 Oracle 9 i Application and Database Servers Doc 263508. 1 Security Alert #64: Buffer Overflow in Oracle 9 i Database Server Doc 263509. 1 Security Alert #63: Security Vulnerabilities in Oracle 9 i 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 Oracle 9 i 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 Oracle 9 i 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 Oracle 9 i 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 No. COUG Brian Hitchcock May 6, 2004 Page 13
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 9 i Application Server Doc 216775. 1 Security Alert #46: Buffer Overflow in i. SQL*Plus (Oracle 9 i 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: Oracle 9 i 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: Oracle 9 i Application Server Oracle Java Server Page Demos Vulnerability Doc 207269. 1 Security Alert #40: Oracle Net Listener Vulnerabilities Doc 207271. 1 Security Alert #39: Oracle 9 i 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: Open. SSL Security Vulnerability Doc 200873. 1 Security Alert #36: Security Vulnerability in Apache HTTP Server of Oracle 9 i. AS Doc 198531. 1 Security Alert #35: Buffer Overflow Vulnerability in Oracle 9 i. AS Reports Doc 198544. 1 Security Alert #34: Security Vulnerability in Oracle Net (Oracle 9 i Database Server) Doc 185074. 1 Security Alert #33: User Privileges Vulnerability in Oracle 9 i 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 9 i, Oracle 8 i and Oracle 8 Database No. COUG Brian Hitchcock May 6, 2004 Page 14
Security Alerts Doc 175428. 1 Security Alert #28: Vulnerabilities in Oracle mod_plsql and JSP in Oracle 9 i. AS V 1. 0. 2. x Doc 169628. 1 Security Alert #27: Vulnerabilities in Oracle 9 i Application Server Web Cache Doc 168862. 1 Security Alert #26: Potential Do. S Vulnerability in Oracle 9 i 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 Meta. Link) 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 Oracle 9 i. AS 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: Oracle 9 i. AS Web Cache Overflow Vulnerability No. COUG Brian Hitchcock May 6, 2004 Page 15
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 No. COUG Brian Hitchcock May 6, 2004 Page 16
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… No. COUG Brian Hitchcock May 6, 2004 Page 17
Getting Patches Ÿ Metalink – – Patches Simple Search Ÿ Enter specific patch number Ÿ Specify platform – Download Ÿ Patch zip file Ÿ Readme file No. COUG Brian Hitchcock May 6, 2004 Page 18
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 No. COUG Brian Hitchcock May 6, 2004 Page 19
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 No. COUG Brian Hitchcock May 6, 2004 Page 20
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 No. COUG website – If patch fails Ÿ Restore ORACLE_HOME from backup No. COUG Brian Hitchcock May 6, 2004 Page 21
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 No. COUG Brian Hitchcock May 6, 2004 Page 22
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 No. COUG Brian Hitchcock May 6, 2004 Page 23
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 No. COUG Brian Hitchcock May 6, 2004 Page 24
Cases Ÿ 1) Ora. Inventory not in place Ÿ 2) Installer not in place Ÿ 3) 64 bit oracle Ÿ 4) chroot Ÿ 5) not following instructions No. COUG Brian Hitchcock May 6, 2004 Page 25
Case 1 -- Ora. Inventory Ÿ 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) No. COUG Brian Hitchcock May 6, 2004 Page 26
Case 1 -- Ora. Inventory Ÿ Review 8. 1. 7. 4 readme – – – Existing database Many post patch tasks Before applying 8. 1. 7. 4 Ÿ Backup db Ÿ Shutdown listener No. COUG Brian Hitchcock May 6, 2004 Page 27
Case 1 -- Ora. Inventory – 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 No. COUG Brian Hitchcock May 6, 2004 Page 28
Case 1 -- Ora. Inventory Ÿ 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 No. COUG Brian Hitchcock May 6, 2004 Page 29
Case 1 -- Ora. Inventory Ÿ And now? – – Dependencies There are no patches that need to be applied from the patch set Oracle 8 i 8. 1. 7. 4. 0 Ÿ Huh? Ÿ Off to Metalink – – Doc ID 115236. 1 Ora. Inventory is missing No. COUG Brian Hitchcock May 6, 2004 Page 30
Case 1 -- Ora. Inventory Ÿ What is Ora. Inventory? – – – 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 Ora. Inventory Verifies that patch should be applied Ÿ Example, 8. 1. 7. 4 patch on 8. 1. 7. 0 Oracle_home No. COUG Brian Hitchcock May 6, 2004 Page 31
Case 1 -- Ora. Inventory Ÿ Where does it live? – Installer creates in Oracle_base Ÿ (my experience) Ÿ What happened here? – – – ora. Inventory didn’t exist Installer couldn’t tell what had been installed Installer decided it couldn’t install anything Ÿ No inventory, can’t apply any patches No. COUG Brian Hitchcock May 6, 2004 Page 32
Case 1 -- Ora. Inventory Ÿ 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 “Tar&Toss” Ÿ my manager came up with that – – This isn’t supported by Oracle This saves time initially Ÿ Wastes time later No. COUG Brian Hitchcock May 6, 2004 Page 33
Case 1 -- Ora. Inventory Ÿ OK, that’s 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 can’t lose? Ÿ Tnsnames. ora, password file – – Don’t place anything of your own in oracle_home Document your database before patching No. COUG Brian Hitchcock May 6, 2004 Page 34
Case 1 -- Ora. Inventory Ÿ How to be sure – – – Nothing unique in oracle_home? Can’t 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? No. COUG Brian Hitchcock May 6, 2004 Page 35
Case 1 -- Ora. Inventory Ÿ 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 No. COUG Brian Hitchcock May 6, 2004 Page 36
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 No. COUG Brian Hitchcock May 6, 2004 Page 37
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 v$version shows Ÿ Oracle 8 i … 64 bit Production No. COUG Brian Hitchcock May 6, 2004 Page 38
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… No. COUG Brian Hitchcock May 6, 2004 Page 39
Case 4 -- chroot Ÿ Yet another environment – – – All set to apply patches Shutdown database, listener Start installer Ÿ Can’t display OUI GUI back to my workstation Ÿ Chroot – – – Removes many OS libraries Have to manually identify which are needed Copy from another system No. COUG Brian Hitchcock May 6, 2004 Page 40
Case 5 – Complete the Patch Ÿ User calls – – Dev db doesn’t work Error is ‘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!” Didn’t hear from the user again No. COUG Brian Hitchcock May 6, 2004 Page 41
Lessons Learned Ÿ Verify – Ora. Inventory 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 No. COUG Brian Hitchcock May 6, 2004 Page 42
Lessons Learned Ÿ For a new install – – – Oracle_home not a top level directory Oracle_base /u 01/app/oracle Oracle_home $ORACLE_BASE/product/<version> Oracle_home /u 01/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 No. COUG Brian Hitchcock May 6, 2004 Page 43
- Slides: 43