upgrade to oracle database 12.2 real world …...- patch oracle 12.1 restart database home to at...

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

Upgrade to Oracle Database 12.2Real World Customer Cases

Upgrade to Oracle 12.2 - Real World Customer Cases

Presented with

Mike DietrichMaster Product ManagerDatabase Upgrades & MigrationsOracle Corporation

Alain FuhrerOracle Database AdministratorSwiss Mobiliar Insurance


• preupgrade.jar$ java -jar preupgrade.jar TEXT TERMINAL

• Checks source environment

• Detailed recommendations

• Fixup scripts

• Rerunable

• Always download from:MOS Note: 884522.1

Upgrade to Oracle 12.2 - Real World Customer Cases

New Preupgrade Tool

2

https://support.oracle.com/epmos/faces/DocumentDisplay?id=884522.1

~40%faster upgrades than to Oracle 11g

Upgrade to Oracle 12.2 - Real World Customer Cases 3


• catctl.pl$ perl catctl.pl -l /logs catupgrd.sql

• Non-CDBs and CDB$ROOT:

– 4 (default) - 8 workers

• PDBs:– Limited only by computing power

• Resumable: -R option$ perl catctl.pl -R -l /logs catupgrd.sql


catctl.pl - Parallel Upgrade

Upgrade & Migrate to Oracle Database 12.2 - Live and Uncensored 4

Parallel Phase #:19 [UPGR] Files:33 Time: 72s

Restart Phase #:20 [UPGR] Files:1 Time: 0s

Serial Phase #:21 [UPGR] Files:3 Time: 19s




[..]


*************** Catproc CDB Views **************

[..]

***************** Catproc PLBs *****************

[..]

*************** Catproc DataPump ***************

[..]

****************** Catproc SQL *****************

[..]

************* Final Catproc scripts ************



************** Final RDBMS scripts *************


************ Upgrade Component Start ***********



**************** Upgrading Java ****************



***************** Upgrading XDK ****************



********* Upgrading APS,OLS,DV,CONTEXT *********


***************** Upgrading XDB ****************







[..]



********* Upgrading CATJAVA,OWM,MGW,RAC ********


**************** Upgrading ORDIM ***************

[..]

***************** Upgrading SDO ****************


Overview and Timeline

• Upgrade 337 databases

– Oracle Database 12.1.0.2 to Oracle Database 12.2.0.1

– 82 production databases

– 18 container databases

• 350 PDB’s

• Max of 50 PDB’s in one CDB in dev

• Move from schema-based consolidation to PDBs

• Timeline

– End of 2019


Customer

Project

Constraints

Preparation

Upgrade

Success?

Remarks


Pros and Cons

• Additionally 550 – 600 PDBs

• Pure CDB architecture with Oracle 12.2 – Except third party apps limitations


Customer

Project

Constraints

Preparation

Upgrade

Success?

Remarks


Constraints

• Small DBA Team

–Only 4.8 FTE + 1 newbie

• Freeze periods for the Mobiliar applications

• Developers want Oracle 12.2 features NOW!

• Highly complex applications

–Queries with more than 1000 bind variables and 250 outer joins


Customer

Project

Constraints

Preparation

Upgrade

Success?

Remarks


Performance Tracking

• Mobiliar’s own AWR Warehouse

– Compare performance before/after

– 3 TB of historical performance data

– Covers entire database lifetime


Customer

Project

Constraints

Preparation

Upgrade

Success?

Remarks

PerformanceWarehouse


Upgrade Testing and Releases

• Regression tests

– During the testing phase of the Mobiliar Software Release

– DATABASE RELEASE UPDATE (RU): 12.2.0.1.170718

– Two Oracle Homes on the same server

– Upgrade to Oracle Database 12.2

• With catctl.pl embedded into home-built shell script

– Performance tests performed by application owner


Customer

Project

Constraints

Preparation

Upgrade

Success?

Remarks


4 Upgrade Checkpoints for CDBs

1) Check PDB default service name

2) Check length of PDB name

3) Check patch level of GI source home

4) Choose suitable parallel upgrade degree on your HW


Customer

Project

Constraints

Preparation

Upgrade

Success?

Remarks


Behavior Change: PDB default service

• Oracle recommendation: Create your own PDB service

• Oracle 12.1.0.2: You can disable the default service

• Oracle 12.2.0.1: Disabling the default service causes issues

• https://mobiliardbblog.wordpress.com/2017/08/14/oracle-12-2-multitenant-never-stop-the-default-pdb-service

• Solution:

- Don’t disable the default service

- Code fix in the next Oracle release


Customer

Project

Constraints

Preparation

Upgrade

Success?

Remarks

IssuesSQL> alter session set container=PDB1;

ERROR:

ORA-44787: Service cannot be switched into.

https://mobiliardbblog.wordpress.com/2017/08/14/oracle-12-2-multitenant-never-stop-the-default-pdb-service


PDB Naming

• Maximum PDB name length: 30 byte

• Upgrade to Oracle 12.2.0.1 failed for PDB names >25 bytes

• Solution:

- Apply new prvtcr.plb package (bug 26024601)


Customer

Project

Constraints

Preparation

Upgrade

Success?

Remarks

Issues

Serial Phase #:109 [DOVVNZ_VVN_VERKENTSCHAEDIG] Files:1 wait_for_completion:

unexpected error in next_proc()

catconExec: unexpected error in wait_for_completions

Unexpected error encountered in catctlMain; Error Stack Below; exiting

Died at catctl.pl line 7822.

main::catctlDie("\x{a}Unexpected error encountered in catconExec; exiting\x{a} 2")

called at catctl.pl line 4556


Patch Level of source GI

• Grid Infrastructure 12.2.0.1 did not allow to add any 12.1 databases when a 12.2 database was registered first

- https://mobiliardbblog.wordpress.com/2017/08/18/be-careful-that-the-patch-level-of-your-oracle_home-is-up-to-date-before-upgrading-grid-infrastructure-to-release-12-2/

• Solution:

- Patch Oracle 12.1 Restart database home to at least January 2017GI PSU/BP (bug 20007009)


Customer

Project

Constraints

Preparation

Upgrade

Success?

Remarks

Issues

srvctl add database -db my12db1_01 -oraclehome /u01/app/oracle/product/12102_1 \

-domain oracle.com -dbname MY12DB1 -instance my12db1 \

–spfile /u01/app/oracle/product/12102_1/dbs/spfilemy12db1.ora

PRCG-1036 : Internal error.

PRCD-1095 : Failed to retrieve databases for version 11.1 and earlier

PRKC-1137 : Unable to find Version object with string value 12.2.0.1.0

https://mobiliardbblog.wordpress.com/2017/08/18/be-careful-that-the-patch-level-of-your-oracle_home-is-up-to-date-before-upgrading-grid-infrastructure-to-release-12-2/


Examine the optimal parallel degree

• catctl.pl allows to upgrade many PDBs at the same time:$ORACLE_HOME/perl/bin/perl catctl.pl -n xx -l $upgrade_base/upgrade catupgrd.sql

• CDB upgrade paralleldegree balancing:

• Result with32 CPU cores:


Customer

Project

Constraints

Preparation

Upgrade

Success?

RemarksParallel Degree: -n Total Upgrade Time

8 5 h

32 [Default] 2.6 h

64 3.5 h


Upgrade Timing for 50 PDBs


Customer

Project

Constraints

Preparation

Upgrade

Success?

Remarks

Upgrade Steps (parallel degree: -n 32) Duration

Execution of preupgrade.jar

2h 46 min

Execution of pre upgrade fixup scripts

All in one Upgrade of CDB$ROOT and all PDB’s

Recompile of CDB$ROOT and all PDB’s after upgrade to 12.2

Enable local undo mode for container database

Immediate create level 0 backup of container database

Create guarantee restore point and change oracle home to 12.2

Drop guarantee restore point after successful upgrade

PRE TASKS (online)

Upgrade TASKS (offline)

POST TASKS (online)

5 min

32 min

6 min

8 min

13 min

5 min

3h 29 min

10 min

1 min

11 min

Total Upgrade Time 3h 53 min


Upgrade Conclusion

• Parallel upgrade catctl.pl unfolds its full power when upgrading many PDBs at the same time

– 50 PDBs upgraded in less than 4 hours

• Still some issues/bugs to check and fix before upgrade

• We feel very well prepared for the new annual Oracle releases

• If you plan to or use already Oracle Multitenant, upgrade to Oracle Database 12.2

- The new features are really good


Customer

Project

Constraints

Preparation

Upgrade

Success?

Remarks


Upgrade Reference Program Benefits

• Great help and add a lot of value to our project’s progress

• When we encounter issues, we fix them before going live

• Oracle monitored our SR’s and pushed things forward

• In summary we can say, that we are very happy to be part of the Upgrade Reference Program for the second time and hope we can also benefit from it in the future


Customer

Project

Constraints

Preparation

Upgrade

Success?

Remarks

mobiliar.ch/db-blog

upgrade to oracle database 12.2 real world …...- patch oracle 12.1 restart database home to at...

Documents