Daniel Overby HansenSenior Principal Product ManagerCopyright 2020, Oracle and/or its affiliates, Confidential: Internal/Restricted/Highly RestrictedUpdated: Nov 11, 2020Photo by Karsten Würth on Unsplash1Upgrade Your Databases to 19c
Daniel Overby HansenSenior Principal Product ManagerCloud abase
Database UpgradePhoto by Kai Pilger on UnsplashWhat you shouldn't do
Don't do this Outdated VersionsMonday, January 14, 2019 7:08 AMI’m working into a deal in LAD Customer, migrating about aprox 70 db oracle database version 7 (supportingOLD application developed on Oracle Forms) distributed around all country, my customer is want tomodernized their application to 3 layer architecture (using an J2EE app) and move all data to an oracle DBcentral repository.We are intend to recommend best architecture and sizing but we do not know Oracle 7 database.
Don't do this Outdated VersionsGA: 29-NOV-2006
Database UpgradePhoto by SpaceX on UnsplashWhy you "want" to upgrade
Lifetime Support Policy20272026202520242023Oracle 12.12022EXTENDED20212020Waived 09Oracle 11.2MARKETDRIVENEXTENDEDOracle 12.2.0.1LIMITEDOracle 18(12.2.0.2)Oracle 19EXTENDEDMarket Driven SupportLimited Error Correction(12.2.0.3)Premier SupportWaived Extended SupportPaid Extended Support
Release Types Long Term Support vs Innovation ReleasesLong Term Support Release 5 years of Premier Support followed by 3 years of Extended SupportInnovation Release 2 years of Premier Support, but there is no Extended SupportInnovationInnovationLong Term SupportPremier SupportEXTENDEDPaid Extended Support MOS Note: 742060.1 - Release Schedule of Current Database Releases
Oracle 12.2 Release FamilyIncludes: Oracle 12.2.0.1, Oracle 18c (12.2.0.2), Oracle 19c (12.2.0.3)LIMITEDOracle 18(12.2.0.2)Oracle 19(12.2.0.3) MOS Note:742060.1 - The Single Source of Truth MOS Note:161818.1 - Releases Support Status 9201820172016Oracle 12.2.0.1
Photo by Pablo Heimplatz on UnsplashCheckBefore Upgrade
Your Key to Successful Database Upgrades123Download and installOracle 19cDownload and installnewest RUDownload and usenewest AutoUpgradeeDeliveryMOS: 2521164.1MOS: 2485457.14
Get started Quick Start GuideSimple overviewRead it, try itDownload from oracle.com
Database Upgrade Supported Releases
Platform Certification Linux x86-64Database 11.2.0.4Database 12.1.0.2Database 19cOracle Linux 4CertifiedOracle Linux 5CertifiedCertifiedOracle Linux 6CertifiedCertifiedOracle Linux le Linux 8Red Hat 4CertifiedRed Hat 5CertifiedCertifiedRed Hat 6CertifiedCertifiedRed Hat 7CertifiedCertifiedCertifiedCertifiedCertifiedRed Hat 814Copyright 2020, Oracle and/or its affiliates
Platform Certification Windows ServerDatabase 11.2.0.4Database 12.1.0.2Microsoft Windows x64 2003CertifiedMicrosoft Windows x64 2003 R2CertifiedMicrosoft Windows x64 2008CertifiedCertifiedMicrosoft Windows x64 2008 R2CertifiedCertifiedMicrosoft Windows x64 2012CertifiedCertifiedMicrosoft Windows x64 2012 R2CertifiedCertifiedDatabase 19cCertifiedMicrosoft Windows x64 2016CertifiedMicrosoft Windows x64 2019Certified15Copyright 2020, Oracle and/or its affiliates
More Links on the Upgrade BlogOracle Clusterware Certification on 8-as-well/Oracle Database 19c Certification on cle-database-19c-is-certified-on-ol8-and-rhel8/
Oracle 19c InstallationGold Image1. Create ORACLE HOME directory2. Download image3. Unpack into ORACLE HOME4. ./runInstaller5. root.sh
APEX Upgrade In AdvanceAPEX upgrade Not part of the database upgrade MOS Note: 1088970.1 - Master Note APEX UpgradesAPEX certification Minimum APEX Version for Oracle 19c: APEX 18.2 MOS Note: 1344948.1 - APEX Database and Web Server CertificationDownload the newest APEX
Upgrade 19c Speed it upCheck when dictionary stats have been gathered the last timeSELEECTto char(max(end time),'dd-mon-yy hh24:mi') latest, operationFROMdba optstat operationsWHEREoperation in ('gather dictionary stats', 'gather fixed objects stats')GROUP BYoperation;Refresh stats a day before the upgrade
Upgrade 19c Make it run faster Stats not gathered on several clustered indexes (bug 25286819)execexecexecexecexecexecexecexecdbms stats.gather schema stats('SYS');dbms stats.gather index stats('SYS','I OBJ#');dbms stats.gather index stats('SYS','I FILE# BLOCK#');dbms stats.gather index stats('SYS','I TS#');dbms stats.gather index stats('SYS','I USER#');dbms stats.gather index stats('SYS','I TOID VERSION#');dbms stats.gather index stats('SYS','I MLOG#');dbms stats.gather index stats('SYS','I RG#'); autoupgrade.jar does this for you automatically!
Upgrade 19c Effect of having good statsThe larger the dictionary, the bigger the effectDurationReductionNo dictionary and fixed objects stats15 min 55 secGathered dictionary and fixed objects stats14 min 10 sec11 %Gathered schema and cluster index stats13 min 41 sec3.4 % to previousTotal downtime saved2 min 14 sec14 % overall This example has been done with one of the tiny Hands-On Lab databases
Operating System Recommendations MOS Note: 1587357.1Oracle Database on Unix AIX, HP-UX, Linux, Solaris and MS Windows –Installation and Configuration Requirements Quick Reference
Photo by Joshua Sortino on UnsplashAutoUpgradeThe ONLY recommended way toupgrade databases
AutoUpgrade Requirements1.Java 8 or newer 2.Default in your ORACLE HOME since 12.1.0.2Check: ORACLE HOME/jdk/bin/java -versionThe AutoUpgrade package A jar file of 2 MB in size
AutoUpgrade Availability and SupportAll architectures (CDB and non-CDB)All supported operating systemsAll editions (SE2, EE)All types (single instance and RAC)Upgrades from all supported versions: 11.2.0.4 and newerUpgrades to version: 12.2.0.1 with Jan 2019 Update or newer 18.5.0 (includes Jan 2019 Update) or newer 19.3.0 or newer
AutoUpgrade What you don't need AgentsEM Cloud ControlDBUAExtra license
Getting Started
AutoUpgrade Get ight 2020, Oracle and/or its affiliatesAlways download latest version from MOS
AutoUpgrade Get StartedDownloadConfigureAnalyzeCheckUpgradeCheck your version java -jar autoupgrade.jar –versionbuild.hash 04dd9f2build.version 19.7.5build.date 2020/02/11 15:28:49build.max target version 19build.type productionCompare to latest version on MOS29Copyright 2020, Oracle and/or its affiliates
AutoUpgrade Get t versionupg1.source home /u01/app/oracle/product/12.2.0.1upg1.target home /u01/app/oracle/product/19upg1.sid CDB1Or, generate a sample config file java -jar autoupgrade.jar -create sample file configCreated sample configuration file /home/oracle/sample config.cfgPro tip: upg1 is a prefix that you decide.Use it to define multiple databases30Copyright 2020, Oracle and/or its affiliates
AutoUpgrade Get StartedDownloadConfigureAnalyzeCheckUpgradeCheck your database java -jar autoupgrade.jar -config CDB1.cfg -mode analyze.------------------- Final Summary -------------------Number of databases[ 1 ]Jobs finished successfully[1]Jobs failed[0]Jobs pending[0]------------- JOBS FINISHED SUCCESSFULLY ------------Job 101 for CDB1Pro tip: Analyze is similar torunning preupgrade.jar31Copyright 2020, Oracle and/or its affiliates
AutoUpgrade Get ight 2020, Oracle and/or its affiliatesPreupgrade report is saved here:/home/oracle/autoupg default/CDB1/CDB1/100/prechecks
AutoUpgrade Get ight 2020, Oracle and/or its affiliatesPreupgrade report is saved here:/home/oracle/autoupg default/CDB1/CDB1/100/prechecks This is your logging base directory. Can be changed in the config file
AutoUpgrade Get ight 2020, Oracle and/or its affiliatesPreupgrade report is saved here:/home/oracle/autoupg default/CDB1/CDB1/100/prechecks Database SID
AutoUpgrade Get ight 2020, Oracle and/or its affiliatesPreupgrade report is saved here:/home/oracle/autoupg default/CDB1/CDB1/100/prechecks Job number Most likely 100 Increments everytime you start AutoUpgrade
AutoUpgrade Get ight 2020, Oracle and/or its affiliatesEvaluate the preupgrade report
AutoUpgrade Get ight 2020, Oracle and/or its affiliatesPreupgrade report comes in: HTML Text JSON XML
AutoUpgrade Get StartedDownloadConfigureAnalyzeCheckUpgradeUpgrade java -jar autoupgrade.jar -config CDB1.cfg -mode deployHave a cup of coffee and wait, or .38Copyright 2020, Oracle and/or its affiliates
AutoUpgrade Get ight 2020, Oracle and/or its affiliatesMonitorupg lsj ---- ------- --------- --------- ------- -------------- -------- --------------- Job# DB NAME STAGE OPERATION STATUS START TIME UPDATED MESSAGE ---- ------- --------- --------- ------- -------------- -------- --------------- 101 CDB1 PREFIXUPS EXECUTING RUNNING 20/11/24 13:38 13:39:26 Remaining 12/13 ---- ------- --------- --------- ------- -------------- -------- ---------------
AutoUpgrade Get StartedDownloadConfigureAnalyzeCheckUpgradeAll the detailsupg status -job 101Progress----------------------------------Start time:20/11/24 13:38Elapsed (min):13Last tion:EXECUTINGStatus:RUNNINGStage summary:SETUP 1 minGRP 1 minPREUPGRADE 1 minPRECHECKS 1 minPREFIXUPS8 minDRAIN 1 minDBUPGRADE3 min (IN PROGRESS)Job Logs Locations----------------------------------Logs Base:/home/oracle/autoupg default/CDB1/CDB1Job logs:/home/oracle/autoupg default/CDB1/CDB1/101Stage logs:/home/oracle/autoupg le/autoupg default/CDB1/CDB1/temp40Copyright 2020, Oracle and/or its affiliates
AutoUpgrade Get StartedDownloadConfigureAnalyzeCheckUpgradeAll the details - continued.Additional ls:[Upgrading] is [0%] completed for [cdb1-cdb root] --------- --------------- CONTAINER PERCENTAGE --------- --------------- CDB ROOT UPGRADE[12%] PDB SEED UPGRADE PENDING PDB3 UPGRADE PENDING --------- --------------- Error Details:None41Copyright 2020, Oracle and/or its affiliates
AutoUpgrade Get upg Job 101 completed------------------- Final Summary -------------------Number of databases[ 1 ]Jobs finished successfully[1]Jobs failed[0]Jobs pending[0]------------- JOBS FINISHED SUCCESSFULLY ------------Job 101 for CDB1---- Drop GRP at your convenience once you consider it is no longer needed ---Drop GRP from CDB1: drop restore point AUTOUPGRADE 9212 CDB1122010And it includes: 42Copyright 2020, Oracle and/or its affiliatesRecompilation (utlrp)Timezone file upgradePostupgrade fixups. and so much more
Advanced Options
AutoUpgrade Upgrade One or Many Databasesupg1.source home /u01/app/oracle/product/12.2.0.1upg1.target home /u01/app/oracle/product/19upg1.sid CDB1upg1.source home /u01/app/oracle/product/12.2.0.1upg1.target home /u01/app/oracle/product/19upg1.sid CDB1upg2.source home /u01/app/oracle/product/12.2.0.1upg2.target home /u01/app/oracle/product/19upg2.sid CDB2upg3.source home /u01/app/oracle/product/11.2.0.4upg3.target home /u01/app/oracle/product/19upg3.sid DB11204upg4.source home /u01/app/oracle/product/11.2.0.4upg4.target home /u01/app/oracle/product/19upg4.sid SALESupg5.source home /u01/app/oracle/product/11.2.0.4upg5.target home /u01/app/oracle/product/19upg5.sid DEMOupgn.source home /u01/app/oracle/product/12.1.0.2upgn.target home /u01/app/oracle/product/19upgn.sid HR
AutoUpgrade Adjust init parameters##Example global pfile configuration entries#global.del during upgrade pfile /database/pfiles/global during delinit.oraglobal.add during upgrade pfile /database/pfiles/global during addinit.oraglobal.del after upgrade pfile /database/pfiles/global after delinit.oraglobal.add after upgrade pfile /database/pfiles/global after addinit.ora##Example local pfile configuration entries#upg2.del during upgrade pfile /database/pfiles/hr during delinit.oraupg2.add during upgrade pfile /database/pfiles/hr during addinit.oraupg2.del after upgrade pfile /database/pfiles/hr after delinit.oraupg2.add after upgrade pfile /database/pfiles/hr after addinit.ora
AutoUpgrade Execute external scripts#Pass return code#Example global script executionback to AutoUpgrade#global.before action /database/scripts/run this before.sh Yglobal.after action /database/scripts/run this after.sh##Example local script execution#upg2.before action /database/scripts/run this on UPG2 before.shupg2.after action /database/scripts/run this on UPG2 after.sh Permitted extension options: Unix shell (.sh) Microsoft Windows batch (.bat, .cmd) Microsoft Windows PowerShell (.ps1)
AutoUpgrade Protection - GRP options##By default a GRP will be created. Default for EE: Yes#upg2.restoration no##By default a GRP will be kept. This option overwrites the default.#upg2.drop grp after upgrade yes
AutoUpgrade Other options##Example global directive - Default: Underscores stay as before#global.remove underscore parameters yes##If unset, utlrp.sql won't be run - Default: YES#upgr2.run utlrp no##If unset, time zone upgrade won't be performed - Default: YES#As Timezone can't be downgraded, it may be useful to postpone and skip it#upgr2.timezone upg no
AutoUpgrade Start time VALID values Format: DD/MM/YYYY HH24:MI:SS NOW [this is the default]30/12/2018 15:30:0001/11/2019 01:30:152/5/2019 3:30:50
AutoUpgrade 4 Operation Modesautoupgrade.jar-analyze-fixups-deploy-upgrade
AutoUpgrade Best Practiceautoupgrade.jar-analyze-deploy java -jar autoupgrade.jar -config config.cfg -mode analyze java -jar autoupgrade.jar -config config.cfg -mode deploy
AutoUpgrade Move to new hardwareautoupgrade.jar-analyze-fixupsSource Systemautoupgrade.jar-upgradeTarget System java -jar autoupgrade.jar -config config.cfg -mode analyze java -jar autoupgrade.jar -config config.cfg -mode deploy java -jar autoupgrade.jar -config config.cfg -mode upgrade
AutoUpgrade Job Console Most important commandsupg lsjresumestatusrestoreabort-job-job-job-job number number number number //////////List jobsRestarts a jobShow job statusRestores database from GRPAborts the specified job
AutoUpgrade Log File Structure Logs written in TEXT and JSON format /cfgtoollogs ./upgrade/autoStatus Log /database 1 ./job number de./postupgrade./temp /database 2 HTML ReportUpgrade Logs
AutoUpgrade What if .1. ZIP the entire global log directory:java -jar autoupgrade.jar -config config.cfg -zip2. Add the alert.log3. Upload it to MOS
AutoUpgrade What if . you have RAC?Since AutoUpgrade 19.8 (2020/03/27) RAC database upgrades are supported Works best with SPFILE in ASM Oracle Grid Infrastructure upgrade must be done separately upfront
DEMO
AutoUpgrade Coming soon!Data Guard Disable Data Guard Broker and defer log transportUnplug/plug upgrade
AutoUpgrade Blog Post w-autoupgrade-utility-in-oracle-19c/
After UpgradePhoto by Ales Krivec on UnsplashBest Practices and things to take care on
Best Practice COMPATIBLE Parameter When should you change COMPATIBLE? 7-10 days after the upgrade Which value? 11.2.012.1.012.2.018.0.019.0.0 Change COMPATIBLE for RUs? NEVER! Flashback to GRP Downgrade
Things to do right after upgradeConfigure statistics history retention period Check space usage:SQL select space usage kbytes/1024mbfrom v sysaux occupants whereoccupant name 'SM/OPTSTAT'; Check retention Default: 31 daysAdjust setting Example: 10 daysSQL select dbms stats.get stats history retention from dual;SQL exec dbms stats.alter stats history retention(10);
Post Upgrade Unified Audit Trail Internal structure changed to improve query performance of UNIFIED AUDIT TRAIL view Old records must be converted to new format To convert:SQL EXEC DBMS AUDIT MGMT.TRANSFER UNIFIED AUDIT RECORDS; Only applicable if upgrading from Oracle Database 12.1
More Information Client ConnectivityMOS Note: 207303.1Client / Server Interoperability Support Matrix for Different Oracle Versions
Oracle 19c Multimedia Removal The Oracle Multimedia API gets removed during upgrade The Locator still exists and works ORDIM component remains VALID in DBA REGISTRY Recommendation Check, if you use Oracle Multimedia If not, you can remove it before upgrade More information media-will-be-removed-in-oracle-database-19c/ MOS Note: 2347372.1 – Oracle Multimedia Statement of Direction MOS Note: 2375644.1 – How To Migrate Data From Oracle Multimedia Data Types to BLOB columns
Oracle 19c Streams Removal The Oracle Streams API gets removed during upgrade See: AC6D-C9326798E82B
Oracle 19c DBMS JOBDBMS JOB package is still supported Grant the CREATE JOB privilege During and after upgrade, for each job in DBMS JOB a corresponding entry will be created withDBMS SCHEDULER The check in preupgrade.jar is only checking for inconsistencies or any issuesMOS Note: 2380601.1 - Database Preupgrade tool check list
Oracle 19c DBMS JOBhttps://mikedietrichde.com/2019/05/24/dbms job-behavior-change-in-oracle-19c-during-upgrade/
Health Check hcheck.sqlIf your database is highly important, do a health check hcheck.sql - Script to Check for Known Problems (MOS Note: 136697.1)
Photo by Adam Muise on UnsplashUpgrade with Data GuardCopyright 2020 Oracle and/or its affiliates.
Upgrade with Data Guard ConceptStop redo applyand shut downPrimary databaseOracle Home 11.2.0.4Redo ApplyOracle Home 19cRedo ApplyUpgrade databaseOracle Home 11.2.0.4StandbydatabaseUpgrade viaredo applyInstall new Oracle HomeRemember use latestRelease UpdateOracle Home 19cMount standbyin new Oracle Home
Upgrade with Data Guard Demo72Copyright 2020, Oracle and/or its affiliates
Upgrade with Data Guard Blog ade-with-data-guard/73Copyright 2020, Oracle and/or its affiliates
SummaryFurther Information
https://MikeDietrichDE.com75Copyright 2020, Oracle and/or its affiliateshttps://DOHdatabase.com
YouTube Oracle Database Upgrades and MigrationsYouTube Channel76Copyright 2020, Oracle and/or its affiliates
HOL https://MikeDietrichDE.com
Thank you!
Minimum APEX Version for Oracle 19c: APEX 18.2 MOS Note: 1344948.1 - APEX Database and Web Server Certification Download the newest APEX. Upgrade 19c Speed it up Check when dictionary stats have been gathered the last time Refresh stats a day before the upgrade SELEECT
Oracle Database 19c includes new features and enhancements, as well as a long -term support commitment, that make it an attractive upgrade target for existing Oracle databases. Moving to Oracle Database 19c may be part of an effort that includes moving to newly purchased server hardware, migrating to different storage architectures, such as Oracle
ORACLE DATABASE UPGRADE MOHAMMAD SHOAIB ANSARI Page 2 of 37 Oracle database upgrade steps can be summarised as follows 1. PRE-REQUISITES a. Check Database Upgrade Certification Matrix b. Check OS Certification Matrix c. Oracle 19c Binaries Installation for Upgrade d. Oracle 12c Database RMAN Backup e. Check For Invalid Objects f.
The Oracle Database 19c enhanced Easy Connect Plus syntax is a superset of Easy Connect that supports more connection options. The syntax is available in Oracle Database drivers (JDBC, ODP.Net, cx_Oracle, node-oracledb etc) that use Oracle Client 19c and connect to Oracle Database 11.2 or later. The Oracle Database 19c Easy Connect Plus syntax is:
Oracle Grid Infrastructure 19c installation files for Linux x86-64. This can be downloaded from Oracle site. Search the Internet for “oracle grid infrastructure 19c download”. At the time of this writing, its link is here. This tutorial was implemented using Oracle Grid Infrastructure 19c (version 19.3).
10 Enterprise Chat and Email Upgrade Guide To upgrade to ECE 12.6(1), you need to complete a number of tasks, which include potential infrastructural upgrades, and completing certain pre-upgrade, upgrade, and post-upgrade tasks. This chapter will help you to plan your upgrade. Planning the Upgrade Read this guide before upgrading to ECE 12.6(1).
A4: Detailed Analysis of Lighting & Appliance Upgrades 105 Introduction 105 Lighting upgrades 105 Heating upgrade 110 Cooling upgrade 115 Shower rose upgrade 118 Water heating upgrade - high efficiency gas 122 Water heating upgrade - gas boosted solar 127 Refrigerator upgrade 131 Clothes washer upgrade 136 Dishwasher upgrade 139
2. Click on the FREE UPGRADE button. 3. Click the UPGRADE button. It is recommended that you should also read What’s New . Be sure to download the UPGRADE – not the 21 Day Demo. Step #1 -- DOWNLOAD THE UPGRADE PROCESS VERY IMPORTANT ---- THE UPGRADE IS HUNG UP If during the upgrade process,
awakening – relaxed, reflective, taking its time – which soon turns to a gently restless frustration and impatience as Arianna waits for Theseus to return. The following aria, whilst sensuous, continues to convey this sense of growing restlessness, with suggestions of the princess's twists into instability reflected in the music. In the .