Oracle Database Patching DOAG Regionaltreffen Berlin, 08.03.2017

Dr. Norbert Leiendecker

Principal Sales Consultant Public Sector Architects for Cloud & IT-Technologies

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

Oracle Patcharten – Welcher ist der richtige?

CPU

SPU

PSU

BP

DBBP

QFSDP

PSR

OJVM PSU

„Würdest du mir bitte sagen, wie ich von hier aus weitergehen soll?“ „Das hängt zum großen Teil davon ab, wohin du möchtest“, sagte die Katze Lewis Carroll, „Alice im Wunderland“

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

3

Guter Startpunkt zum entlanghangeln... • MOS-Note 1962125.1
 Overview of Database Patch Delivery Methods

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

4

Begriffserläuterungen – Testarten von Patches • Funktionale Tests – Test eines speziellen Bereichs (Feature oder Teilbereich eines Features) – Regression Test => Ergebnis/Verhalten einer Operation entspricht den Erwartungen?

• Stress Tests – Lasttests bis an die Grenzen der Ressourcen

• Performance Tests – Verschiedene Workloads und Features – Performancemessung spezieller Operationen („Atomics“ Tests) – Gesamtperformance spezieller Workloads/Features

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

5

Begriffserläuterungen - Versions Nummern Term

Version Number

Example

Major Release

First 2 digits of the version

11.2

Base Release

First 4 digits of a Major Release

11.2.0.1

Patch Set Release (PSR)

4th digit of the version

11.2.0.4

Patch Set Update (PSU)

5th digit of a version

11.2.0.4.160419

5th digit of a version + text to indicate which series of bundle

Exadata Database Bundle Patch 11.2.0.4.160419

Designated by a month/year

11.2.0.4 Jan 2015 SPU

Bundle Patch (BP) Security Patch Update (SPU)

yymmdd

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

6

Grundlegende Patch-Arten Reaktiv Proaktiv • Interim Patches (historisch “One-Off”)

• beheben Bugs von gravierender Auswirkung

– Spez. Form “Diagnostic Patch”

• Für Kombination aus “Defekt + Version + Plattform”

• kontrolliertes Risiko durch intensive QS

• durchlaufen einfache QS-Tests

• Verfügbar auf support.oracle.com àTab. "Patches & Updates"

• werden in nächstes relevantes Patch-SetRelease integriert – Wenn Deadline für Patch-Set schon erreicht, wird Interims-Patch in übernächstes Patch-Set-Release integriert

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

7

Reaktive Patches • Sonderform ist Online- bzw. Hot-Patching fähig – Enthält nur eine shared Library

• Kann gegen laufende RDBMS Instanz installiert werden • opatch apply online –connectString ::

• Patch Online-fähig? – README – cd ; opatch query –all online => „Patch is an online patch: true“

• MOS-Note 761111.1 – „RDBMS Online Patching Aka Hot Patching“

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

8

Proaktive Patches • Security Patch Update (SPU) • Patch Set Update (PSU) • Bundle Patch (BP) • Database Proactive Bundle Patch (DBBP) • Combo Patch • Quarterly Full Stack Download Patch (QFSDP) • Andere proaktive Patches

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

9

Proaktive Patches Standard Konfigurationen

Engineered Systems

Proactive Bundle Patch (DBBP) Bundle Patch (BP) Patch Set Update (PSU) Security Patch Update (SPU)

Quarterly Full Stack Download Patch (QFSDP)

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

10

Sonderfall „Proaktive Patches“ – PSR bzw. Patchset • Patch Set Release - PSR

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

11

Sonderfall „Proaktive Patches“ - PSR • Patch Set Release – PSR • „Patch Set“ irreführend, da Full Release! • Über otn.oracle.com oder edelivery.oracle.com normalerweise nur Download des Basis-Releases – Z.B. 11.2.0.1 via OTN, 11.2.0.4 nur über MOS

• Out-of-Place => Installation in neues Oracle Home • In-place supported, aber nicht empfohlen • GI Upgrades immer Out-of-Place

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

12

Security Patch Update - SPU • Sammlung von Security-Fixes (ehemals Critical Patch Updates=CPUs) • Rhythmus: vierteljährlich • Stets RAC Rolling und „Standby First“ installierbar Database SPUs laufen mit 12c aus => Ab 12.1.0.1 Security-Fixes in Bundle Patch oder PSU integriert

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

13

... Und doch wieder CPU??

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

14

„RAC Rolling“- und „Standby First“-fähige Patches • Was bedeutet „RAC Rolling“? – Ein Knoten nach dem anderen wird gepatcht – Mindestens 1 Knoten immer aktiv – Kein kompletter Cluster-Shutdown – Ggf. dennoch Downtime durch auszuführende Skripte in der/den Datenbanken

• Was bedeutet „Standby First“ ?

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

15

Data Guard Standby-First Patch Apply ①

②

③

Binary Installation auf der Standby-Seite



①

Shutdown der Standby-Instanz

②

Binary Installation

③

Restart Standby

④

Restart Media Recovery





Testen der Standby

Unterschiedliche Database Homes maximal 31 Tage!



①

Konvertieren zu Snapshot Standby

②

Ggf. für das Patchen notwendige SQLs ausführen

③

Testen

④

Konvertieren zurück zur Physical Standby







Patching abschließen (Primary-Seite)



①

Shutdown der Primary

②

Binary Installation

③

Startup Primary

④

Ggf. für das Patchen notwendige SQLs ausführen





Oracle Patch Assurance - Data Guard Standby-First Patch Apply (Doc ID 1265700.1) Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

16

Patch Set Update – PSU











1/2

• Sammlung von Fixes für kritische Bugs • Inklusive Security-Fixes • Enthält keine Änderungen des Optimizers • Enthält keine Änderungen, die Applikationen beeinflussen • Kann mehrere Komponenten des Stacks beinhalten (z.B. GI PSU = GI + DB) • Rhythmus: vierteljährlich (Dienstag, der 17. Jan/Apr/Jul/Okt am nächsten liegt) – http://www.oracle.com/technetwork/topics/security/alerts-086861.html - CriticalPatchUpdates

• kumulativ • DB PSU und GI PSU stets RAC Rolling und „Standby First“ installierbar • OJVM PSU weder RAC Rolling noch „Standby First“ installierbar

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

17

Patch Set Update – PSU











2/2

• Inklusive Security-Fixes – „ Oracle will issue Security Alerts for vulnerability fixes deemed too critical to wait for distribution in the next Critical Patch Update.“
 – Automatische Benachrichtigung über Security Alerts kann abonniert werden – Anleitung: http://www.oracle.com/technetwork/topics/security/securityemail-090378.html

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

18

Bundle Patch - BP • Sammlung von Fixes für Bugs eines Features, Produkts oder Konfiguration – z.B. Windows Database Bundle Patch, Database Patch for Exadata,
 Database Proactive Bundle Patch (DBBP)

• Obermenge von PSU • Kann mehrere Komponenten des Stacks beinhalten – z.B. Database Patch for Exadata => Fixes für Datenbank und Grid Infrastruktur

• Rhythmus: vierteljährlich • Stets RAC Rolling und „Standby First“ installierbar • Seit April 2016: „Database Patch for Engineered Systems“ und „Database In-Memory“ umbenannt in „Database Proactive Bundle Patch“

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

19

Andere Proactive Patches • Für sehr spezielle Einsatzzwecke • Außerhalb des normalen SPU/PSU/BP Zyklus • Z.B. Time-Zone Patches alle 6 Monate • Typischerweise als „Interim Patch“

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

20

Note 1454618.1


Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

21

Note 756671.1


Oracle Recommended Patches – Oracle Database

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

22

Note 1389167.1 – Get Proactive with Oracle Database

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

23

Beispiele

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

24

DBBP „opatch lspatches“ zeigt nicht diese Nummer, sondern diese

goofy*emrep-/u01/app/oracle/product/12102/dbhome_1/OPatch >./opatch lspatches 24846605;OCW Interim patch for 24846605 24340679;DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679) 21555660;Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

25

PSU

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

26

PSU

ol6rac1*+ASM1-/home/oracle >opatch lspatches 23727148; 24007012;ACFS Patch Set Update : 12.1.0.2.161018 (24007012) 24006101;Database Patch Set Update : 12.1.0.2.161018 (24006101) 23854735;OCW Patch Set Update : 12.1.0.2.161018 (23854735) 21436941;WLM Patch Set Update: 12.1.0.2.5 (21436941) OPatch succeeded. Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

27

Quarterly Full Stack Download Patch – QFSDP • Sammlung von verschiedenen, konfliktfreien Patches in „einem Patch“ • Z.B. „QFSDP for SuperCluster“ enthält: – Quarterly Database Patch for Exadata – OJVM PSU – Weitere SuperCluster System Patches (SCMU, Firmware, Treiber usw.)

• Wird auch von Platinum Services genutzt

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

28

Quarterly Full Stack Download Patch - QFSDP • Platinum Certification in Exachk – MOS-Note 1968952.1 – System „Platinum Ready“ Check

• Certified Platinum Configs ! – http://www.oracle.com/us/support/library/certified-platinum-configs-1652888.pdf

• Nicht mehr als zwei QFSDPs auslassen! • Kein proaktiver Hinweis durch Platinum-Support!

... You will be responsible (i) for contacting Oracle to schedule the remote patch installation and (ii) for approving any patch to be installed. ... Quelle: http://www.oracle.com/us/support/library/platinum-services-policies-1652886.pdf Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

29

Quarterly Full Stack Download Patch - QFSDP

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

30

Sonderfall Oracle JavaVM Component Database PSU (OJVM) • Oracle Java Virtual Machine • Java Code direkt in der Datenbank ausführen • Multimedia, Spatial, OLAP, XDK, CDC,... erfordern OJVM – Defaultmäßig installiert, aber nicht zwingend – Check: SELECT version, status FROM dba_registry

WHERE comp_id=‘JAVAVM‘;

• Weder RAC Rolling noch „Standby First“ installierbar – „Mitigation Patch“ als Notpflaster => Patcht nur den Security-Anteil

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

31

Sonderfall Oracle JavaVM Component Database PSU (OJVM) • OJVM nur für DB/DB-Home relevant • JDBC auch für Client-Only, Instant Client und Grid Infrastructure Homes • Ausführliche MOS-Note zum OJVM PSU: 1929745.1
 • Hoffnungsschimmer am Horizont! – „STARTUP UPGRADE“ wegen Rollback des Vorgänger OJVM-PSUs – OJVM 12.1.0.2.170117 PSU à Rollback funktioniert ohne UPGRADE-Mode – April OJVM PSU sollte Klarheit bringen – Note 2217053.1 vom 27.2.2017(!)


„RAC Rolling Install Process for the Oracle JavaVM Component Database PSU (OJVM PSU) Patches“

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

32

Combo Patches • Kombination aus OJVM PSU und – DBBP – GI PSU – DB PSU

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

33

opatchauto und opatch • Opatch User‘s Guide in EM CC 12cR5-Doku – http://docs.oracle.com/cd/E24628_01/doc.121/e39376/toc.htm

• Master Note For Opatch (Doc ID 293369.1) • opatch: Java-basiertes Tool zum Patchen/Rollback • opatchauto: Patch Orchestration Tool; nutzt selbst wieder „opatch“ – Pre-patch checks – Patch apply – Start/Stop running servers – Post-patch checks – Rollback patches, falls Deinstallation erforderlich

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

34

opatchauto und opatch • opatchauto, falls GI+RAC gepatcht wird – Beispiel: DBBP oder GI PSU (enthält GI- und DB-Patches) – opatchauto apply –generatesteps => Was würde opatchauto alles ausführen?

• opatch, falls nur SI DB-Home gepatcht wird – Beispiel: DB PSU

• „Rolling“ Upgrade sowohl mit opatchauto, als auch mit opatch • Opatch-Version ≥ 12.2.0.1.5 bzw. ≥ 11.2.0.3.14 – Unabhängig von OCM => kein ocm-Response File mehr notwendig

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

35

Beispiel: „Rolling“ Upgrade DB PSU • 1. Möglichkeit mit GI PSU 12.1.0.2161018 (Patch 24412235): – opatchauto apply //24412235 –oh – Patcht einen Knoten nach dem anderen, beim letzten dann noch „datapatch“


• 2. Möglichkeit mit DB PSU 12.1.0.2.161018 (Patch 24006101): – cd //24006101; opatch apply – Patcht einen Knoten nach dem anderen, aber Shutdown/Startup DB-Instanz pro Knoten manuell und expliziter „datapatch“-Aufruf am Ende.

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

36

Beispiel GI+RAC: opatchauto apply Step 1

Step 4

Stop the Database

Patch GI Home Directory

Step 2

Patch RAC Home Directory

Step 3

Stop and Unlock the GI Stack

For each RAC Home:

Run:

Run prepatch.sh Patch the RAC Home Run postpatch.sh

rootcrs.pl -prepatch

Step 5

Change Owner/ Permissions back to Prepatch Status Run:

Step 6

Start the Database

rootadd_rdbms.sh

Lock and Start the GI Stack Run: rootcrs.pl - postpatch

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

37

[root@goofy ~]# opatchauto apply /stage/24968615 => Database Proactive Bundle Patch 12.1.0.2.170117

Beispiel DBBP Jan 2017 GI + SI-DB (Auszug) Verify

OPatchauto session is initiated at Tue Jan 24 10:29:58 2017 Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/12102/dbhome_1 Patch applicablity verified successfully on home /u01/app/oracle/product/12102/dbhome_1 Verifying patch inventory on home /u01/app/oracle/product/12102/dbhome_1 Patch inventory verified successfully on home /u01/app/oracle/product/12102/dbhome_1 Preparing to bring down database service on home /u01/app/oracle/product/12102/dbhome_1 Successfully prepared home /u01/app/oracle/product/12102/dbhome_1 to bring down database service

DB und CRS ê

Bringing down database service on home /u01/app/oracle/product/12102/dbhome_1 Following database has been stopped and will be restarted later during the session: dwh01 Database service successfully brought down on home /u01/app/oracle/product/12102/dbhome_1 Bringing down CRS service on home /u01/app/oracle/product/12102/grid Prepatch operation log file location: /u01/app/oracle/product/12102/grid/cfgtoollogs/crsconfig/hapatch_2017-01-24_10-31-42AM.log CRS service brought down successfully on home /u01/app/oracle/product/12102/grid

Apply DB und GI-Home

Start applying binary patch on home /u01/app/oracle/product/12102/dbhome_1 Binary patch applied successfully on home /u01/app/oracle/product/12102/dbhome_1 Start applying binary patch on home /u01/app/oracle/product/12102/grid Binary patch applied successfully on home /u01/app/oracle/product/12102/grid

DB und CRS é

Starting CRS service on home /u01/app/oracle/product/12102/grid Postpatch operation log file location: /u01/app/oracle/product/12102/grid/cfgtoollogs/crsconfig/hapatch_2017-01-24_10-37-00AM.log CRS service started successfully on home /u01/app/oracle/product/12102/grid Starting database service on home /u01/app/oracle/product/12102/dbhome_1 Database service successfully started on home /u01/app/oracle/product/12102/dbhome_1

DB datapatch

Trying to apply SQL patch on home /u01/app/oracle/product/12102/dbhome_1 [WARNING] The database instance 'emrep' from '/u01/app/oracle/product/12102/dbhome_1', in host'goofy' is not running. SQL changes, if any, will not be applied. To apply. the SQL changes, bring up the database instance and run the command manually from any one node (run as oracle). Refer to the readme to get the correct steps for applying the sql changes. Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

38

„oplan“-Utility


Beispiel: DBBP 12.1.0.2.170117
 
 


oplan generateApplySteps

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

39

Patch Download

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

40

Patch Download

wget.sh

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

41

Abfrage Patch-Status mit dbms_qopatch with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual) select x.* from a, xmltable('InventoryInstance/patches/*' passing a.patch_output columns patch_id number path 'patchID', patch_uid number path 'uniquePatchID', description varchar2(80) path 'patchDescription', applied_date varchar2(22) path 'appliedDate', sql_patch varchar2(8) path 'sqlPatch', rollbackable varchar2(8) path 'rollbackable' ) x; Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

42

Abfrage Patch-Status mit dbms_qopatch

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

43

Empfehlungen • Grundsätzlich: Entweder PSU oder DBBP. Kein „Mischen“ möglich. • Mindestens PSUs installieren – Nur minimales Testen erforderlich


• Ab 12.1.0.2: Database Proactive Bundle Patch (DBBP) – Umfassendere Anzahl von Fixes als PSUs – Gründlicheres Testen erforderlich


• OJVM PSU in beiden Fällen zusätzlich installieren – aber ggf. erst später wegen Downtime

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

44

Patchmethode ändern • Nach Upgrade auf neues Patch Set Release => 4. Stelle der Version • Beispiel: 12.1.0.1 mit DB PSU => Upgrade => 12.1.0.2 => DBBP oder • Fully Rollback/Deinstall DB PSU => Installieren aktuelles DBBP • Keine Mischung BP- und PSU-Methode für ein Oracle_Home! • Falls DBBP verwendet, kein weiteres Patching mit PSU möglich – Bei DBBP-Methode bleiben oder – DBBP komplett rückgängig machen und PSUs anwenden

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

45

Empfehlungen • Aktuelles „opatch“-Utility verwenden – Patch 6880880 (passendes Release und Plattform auswählen)

• README zum jeweiligen Patch lesen (auch „Known issues“) • opatch prereq CheckConflictAmongPatchesWithDetail –phBaseDir • opatch prereq CheckConflictAgainstOHWithDetail –phBaseDir • Möglichst aktuell bleiben • Patching ggf. vorab in cloned ORACLE_HOME testen – Ungepatchtes ORACLE_HOME als „Gold“-Image. – Duplizieren des „Gold“-Images (cpio, tar, ...) – Duplikat bekannt machen:
 $ perl $ORACLE_HOME/clone/bin/clone.pl ORACLE_BASE=... ORACLE_HOME=... ORACLE_HOME_NAME=...



– http://docs.oracle.com/database/122/LADBI/cloning-an-oracle-home.htm - LADBI-GUID-494E59C3-C381-4A35-8ABE-F6E5DBF29032

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

46

Oracle Patcharten – Welcher ist der richtige?

PSU

DBBP

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

47

[email protected] Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

48