Database Upgrade and
Migration
Best Practices
Thomas Goop
Senior Consultant, Tradeware AG
Rodrigo Jorge
Senior Principal Product Manager, Oracle
Mike Dietrich
Senior Director Product Management, Oracle
Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Mike Dietrich
Senior Director Product Management
Database Upgrade, Migration and Patching
MikeDietrich
@MikeDietrichDE
[Link]
2 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Rodrigo Jorge
Senior Principal Product Manager
Cloud Migration and Patching
RodrigoArauJorge
@RodrigoJorgeDBA
[Link]
3 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Click icon to add picture
Recorded Web
Seminars
[Link]
4 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Database Upgrade and Migration Best Practices
Upgrade Migration Customer Case
5 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Lifetime Support Policy
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
MARKET
Oracle 11.2 WAIVED EXTENDED EXTENDED DRIVEN
MARKET
Oracle 12.1 EXTENDED DRIVEN
LIMITE
Oracle [Link] D
Oracle 18
([Link])
Oracle 19 EXTENDED
([Link])
Oracle 21
Premier Support Waived Extended Support Paid Extended Support Market Driven Support Limited Error Correction
7
Copyright © 2022, Oracle and/or its affiliates
Release Types
LONG TERM INNOVATION
SUPPORT 2 years of Premier Support
5+ years of Premier Support No Extended Support
followed by
3+ years of Extended Support
Innovation
Long Term Support Extended
Innovation
Long Term Support Extended
Move production databases from
one
Long Term Support release to the
next
10 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Next Long Term Support release
Oracle Database
23c
Upgrade possible only from:
• Oracle Database 19c
• Oracle Database 21c
11 Copyright © 2022, Oracle and/or its affiliates
Do you want to
upgrade?
Oracle Database
[Link]
Oracle Database
[Link]
Oracle Database
[Link]
Oracle Database 18c
12 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Oracle Database
[Link]
Oracle Database
Oracle Database
[Link] Oracle Database 19c
Oracle Database 23c
[Link]
Oracle Database 18c
13 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Everybody must upgrade to Oracle
Database 19c, with or without
Multitenant
14
Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
3
user-created
pluggable databases
are always included - even in SE2
since Oracle Database 19c
15
Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Protection
alter system set MAX_PDBS=3
scope=both;
3 User-Created Pluggable Databases
CON_ID CON_NAME OPEN MODE RESTRICTED
------- ------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB3 READ WRITE NO
SQL> create pluggable database PDB4 ...
ORA-65010: maximum number of pluggable databases created
Prepare your upgrades and migrations
1 2 3
Install Oracle Home Download and deploy the Collect performance
including RU and MRP most recent AutoUpgrade information from current
source and test
MOS Note: 2118136.2 MOS Note: 2485457.1 thoroughly
MOS Note: 555.1
18 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Before upgrading, apply the most recent RU
• Always use the Patch Download Assistant note MOS Note: 2118136.2
Always add the most recent MRP on top
• Always check Recommended Patches note MOS Note:555.1
• Monthly Recommended Patch (MRP) bundles will be available from November 2022
for Oracle 19c
Release Update Contents
Bugs fixed
1200 1140
971
1000
874
800 721
603
573
600
502 474
472 398
400 334
294
228
200
0
19.3.0 19.4.0 19.5.0 19.6.0 19.7.0 19.8.0 19.9.0 19.10.0 19.11.0 19.12.0 19.13.0 19.14.0 19.15.0 19.16.0
Database 19 Release Updates and Revisions Bugs Fixed Lists (Doc ID 2523220.1)
If you don't apply one of the most
recent Release Updates, you will
miss thousands of fixes
22 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
AutoUpgrade automates your
migration
to Multitenant completely
Including Transparent Data Encryption
with AutoUpgrade's new keystore functionality
23 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Always use the latest
version of
AutoUpgrade
Download it from
MOS Note 2485457.1
24 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Backwards Compatible
$ java -jar [Link] -version
[Link] 22.4.220712
[Link] 2022/07/12 [Link] -0400
[Link] 161fde38
build.hash_date 2022/07/12 [Link] -0400
build.supported_target_versions 12.2,18,19,21
[Link] production
Checklist
Essential things to do
before you upgrade or
migrate your databases
26 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Execute database health checks at
least two weeks before an upgrade or
migration
27 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Database Health Check
Health check script
• Download from MOS Note: 136697.1
• In Multitenant, it must be run in each PDB separately
ORAchk Upgrade Readiness
Assessment
• Part of Autonomous Health Framework (AHF)
• Download from MOS Note: 1457357.1
• Upgrade Readiness Check – MOS Note: 2550798.1
28 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Collect at least one full month of
performance data before any
upgrade or migration
29 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Performance Information
AWR – Automatic Workload
Repository
Change the retention to a minimum of 40 days
exec
dbms_workload_repository.modify_snapshot_sett
ings(retention=>57600, interval=>30);
Collect SQL statements and
plans
Use AWR as main source
Capture from Cursor Cache for OLTP
Collect statements, plans and stats in SQL
Tuning Sets
30 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Refresh dictionary statistics,
especially on SYS and SYSTEM before
upgrade or migration
31 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Gather dictionary and fixed objects stats
Without stats refresh With stats refresh
• Initial upgrade phase: 25 min • Initial upgrade phase: 13 min
12 minutes saving in upgrade time
32 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Remove outdated parameters, especially
underscores and events, and set
recommendations
33 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Questionable Parameters
deferred_segment_creation=FALSE
memory_target and
memory_max_target
Avoid whenever possible
recyclebin=OFF
34 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Essential Underscore Parameters
_cursor_obsolete_threshold=1024
_sql_plan_directive_mgmt_control
=0
_exclude_seed_cdb_view=FALSE
36 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Try to avoid optimizer_features_enable
38 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Do not change COMPATIBLE – if possible
39 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Comparison
Fully independent from each other
COMPATIBLE OPTIMIZER_FEATURES_ENABLE
• Enables features • Just reverts to the parameters used in a
• Changes on-disk structures previous release
• Avoid using it if possible
• This is not a Swiss Army knife!
• You will turn off a lot of great features
40 Copyright © 2022, Oracle and/or its affiliates
Enable disabled optimizer fixes with
DBMS_OPTIM_BUNDLE when you
upgrade and migrate
42 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Disabled Optimizer Fixes
Image
Enable with:
SQL> set serverout on
SQL> execute dbms_optim_bundle.getBugsforBundle; execute
dbms_optim_bundle.enable_optim_fix
[Link].220719DBRU: es('ON','BOTH','YES')
Bug: 28044739, fix_controls: 28044739
Bug: 30771009, fix_controls: 30771009
Bug: 33636280, fix_controls: 33636280
• Consider careful testing
Bug: 33089096, fix_controls: 31545400
• SQL Performance Analyzer
Bug: 30618406, fix_controls: 30618406
Bug: 32614157, fix_controls: 32614157 • Adds lengthy _fix_control to
Bug: 33329027, fix_controls: 33329027
Bug: 33311488, fix_controls: 33311488 SPFILE
Bug: 32396085, fix_controls: 32396085
Bug: 32122197, fix_controls: 29972495 • Granular on/off possible
Bug: 32363981, fix_controls: 32363981
43 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Transporting statistics via a
staging table is often faster than
regathering statistics
44 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Virtual Classroom Seminars
Episode 3: Episode 9:
Performance Stability – Tips and Performance Testing in the Cloud
Tricks
[Link]
45 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Use SQL Performance Analyzer
to detect failing plans before
going live.
46 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Use SQL Plan Management and
SQL Tuning Advisor to fix
misbehaving SQL before going live.
47 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Detect and fix misbehaving SQL
1 Capture plans from AWR and Cursor
Cache
2 Simulate with SQL Performance
Analyzer
3 Fix SQL using binds with SQL Plan
Management
• Use captured plan and accept it with DBMS_SPM or EM
CC
4 Fix SQL using literals with SQL Profiles
• Accept SQL profiles with FORCE_MATCH=>TRUE
48 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Never ever upgrade or migrate
without evaluating and testing your
rollback and fallback options
successfully
49 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Virtual Classroom Seminars
Episode 10: More than 30 hours of pure
Secure your job - Fallback is your technical content are available on
insurance demand – 24x7
[Link]
50 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Oracle Cloud World 2022 – Upgrade and Patching
Labs
Hitchhiker's Guide to Database Upgrades to 19c
1 Bring your device – upgrade to 19c and ensure performance stability
Tuesday, October 18 3:00 p.m.
Hitchhiker's Guide to Database Upgrades to 19c
2 Bring your device – upgrade to 19c and ensure performance stability
Wednesday, October 19 9:00 a.m.
No Slides Zone – Database Patching Insights
3 Discuss all topics around database patching, issues, improvements,
secrets and much much more – no device needed, just you and your
questions
Thursday, October 20 9:00 a.m.
Copyright © 2022, Oracle and/or its affiliates
Database Upgrade and Migration Best Practices
Upgrade Migration Customer Case
52 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
t ab ase
Da
area
Visit our
DEMO BOOTH
DB-18
Oracle CloudWorld Hub
Tuesday 13-19
Wednesday 08-18
Thursday 08-14
53 Copyright © 2022, Oracle and/or its affiliates
Upgrade to Oracle Database 19c
LIT4101 Tuesday 16:00 Ascend Lounge, CloudWorld
Hub, The Venetian
Hitchhiker's Guide for Upgrading to Oracle Database 19c
HOL3999 Tuesday 16:00 Bellini 2002, The
Venetian, LevelWednesday
HOL3999 2 09:00 Titian 2201A, The Venetian,
Level 2
From SR to Patch - Insider Insights into the Oracle Database
Development
LRN1506 Process 15:45
Wednesday Murano 3202, The
Venetian, Level 3
No Slide Zone - Database Patching Insights
HOL4093 Thursday 09:00 Bellini 2001A , The
Venetian, LevelThursday
HOL4093 2 11:00 Bellini 2001A , The
Venetian, Level 2
Oracle Data Pump Deep Dive with Development
LRN3501 Thursday 11:30 Murano 3202, The
Venetian, Level 3
AutoUpgrade 2.0: Internals and New Features
LRN3500 Thursday 13:15 Murano 3202, The
Venetian, Level 3
Cloud Premigration Advisor Tool - Your Cloud Premigration
Advisor
LIT4104 Thursday 13:40 Ascend Lounge, CloudWorld
Hub, The Venetian
54 Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
Thank you
Thomas Goop
Senior Consultant, Tradeware AG
Rodrigo Jorge
Senior Principal Product Manager, Oracle
Mike Dietrich
Senior Director Product Management, Oracle
Oracle CloudWorld Copyright © 2022, Oracle and/or its affiliates
