![Page 1: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/1.jpg)
![Page 2: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/2.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Edition-based redefinition:the key to online application upgrade
Bryn LlewellynDistinguished Product ManagerDatabase DivisionOracle HQOakTable Membertwitter: @BrynLite
Summer 2016
![Page 3: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/3.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Safe Harbor Statement
The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.
![Page 4: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/4.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Online Application Upgrade– the final piece of the HA jigsaw puzzle
High Availability
SurviveHardware Failure
Planned Software Changes
Change Infrastructure:Operating System, Oracle Database
Change objects’physical properties
Change application’sdatabase objects
![Page 5: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/5.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Online Application Upgrade– the final piece of the HA jigsaw puzzle
High Availability
SurviveHardware Failure
Planned Software Changes
Change Infrastructure:Operating System, Oracle Database
Change objects’ meaning: patching and upgrading
Change objects’physical properties
Change application’sdatabase objects
![Page 6: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/6.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Agenda
Scope of this presentation
The challenge – and the statement of the solution
Description of case study
Explanation of the edition, the editioning view, and the crossedition trigger
Readying an application for EBR
Implementation of case study as an EBR exercise
EBR task flow
Conclusion / Q&A
1
2
3
4
5
6
7
8
![Page 7: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/7.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Scope of this presentation
• This presentation focuses on capabilities, new in Oracle Database 11.2, and enhanced in 12.1, that support online upgrade of the database tier of an application
• The online upgrade of other tiers of the application will need their own specific solutions. These are only sketched in this presentation
• The take-away from this presentation is that Oracle Database offers both an isolation mechanism to allow pre- and post-upgrade application backends to co-exist in the same database, and a way for client code to choose the particular isolated environment that it wants
![Page 8: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/8.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Agenda
Scope of this presentation
The challenge – and the statement of the solution
Description of case study
Explanation of the edition, the editioning view, and the crossedition trigger
Readying an application for EBR
Implementation of case study as an EBR exercise
EBR task flow
Conclusion / Q&A
1
2
3
4
5
6
7
8
![Page 9: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/9.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Online Application Upgrade
• Supporting online application upgrade means maintaining uninterrupted availability of the application
• But end-user sessions can last tens of minutes or longer
– Users of the old app don’t want to abandon an ongoing session
– Users wanting to start a session must use the new app, but cannot wait until no-one is using the old app
• This implies that it must be possible to use the pre-upgrade application and the post-upgrade application at the same time – a.k.a. hot rollover
![Page 10: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/10.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
The challenge
• The installation of the upgrade into the production database must not perturb live users of the pre-upgrade application
–Many objects must be changed in concert. The changes must be made in privacy
• Transactions done by the users of the pre-upgrade application must by reflected in the post-upgrade application
• For hot rollover, we also need the reverse of this:
– Transactions done by the users of the post-upgrade application must by reflected in the pre-upgrade application
![Page 11: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/11.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
The solution: edition-based redefinition
• EBR brings these key features: the edition, the editioning view,and the crossedition trigger
– Code changes are installed in the privacy of a new edition
– Data changes are made safely by writing only to new columnsor new tables not seen by the old edition
– An editioning view exposes a different projection of a table into each editionto allow each to see just its own columns
– A crossedition trigger propagates data changes made by the old edition intothe new edition’s columns, or (in hot- rollover) vice-versa
![Page 12: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/12.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Hot rollover across the stack
Edition V1
App V1
Traffic Director
![Page 13: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/13.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Hot rollover across the stack
Edition V1
App V1
Traffic Director
![Page 14: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/14.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Hot rollover across the stack
Edition V1
App V1
Traffic Director
![Page 15: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/15.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Hot rollover across the stack
Edition V1
App V1
Traffic Director
![Page 16: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/16.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Hot rollover across the stack
Edition V1
App V1
Traffic Director
![Page 17: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/17.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Hot rollover across the stack
Edition V1
App V1
Edition V2
Traffic Director
![Page 18: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/18.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Hot rollover across the stack
Edition V1
App V1 App V2
Edition V2
Traffic Director
![Page 19: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/19.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Hot rollover across the stack
Edition V1
App V1 App V2
Edition V2
Traffic Director
![Page 20: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/20.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Hot rollover across the stack
Edition V1
App V1 App V2
Edition V2
Traffic Director
![Page 21: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/21.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Hot rollover across the stack
Edition V1
App V1 App V2
Edition V2
Traffic Director
![Page 22: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/22.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Hot rollover across the stack
Edition V1
App V1 App V2
Edition V2
Traffic Director
![Page 23: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/23.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Hot rollover across the stack
Edition V1
App V1 App V2
Edition V2
Traffic Director
![Page 24: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/24.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Hot rollover across the stack
Edition V1
App V1 App V2
Edition V2
Traffic Director
![Page 25: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/25.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Hot rollover across the stack
Edition V1
App V1 App V2
Edition V2
Traffic Director
![Page 26: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/26.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Hot rollover across the stack
Edition V1
App V1 App V2
Edition V2
Traffic Director
![Page 27: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/27.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Hot rollover across the stack
Edition V1
App V1 App V2
Edition V2
Traffic Director
![Page 28: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/28.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Hot rollover across the stack
Edition V1
App V2
Edition V2
Traffic Director
![Page 29: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/29.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Hot rollover across the stack
App V2
Edition V2
Traffic Director
![Page 30: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/30.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Agenda
Scope of this presentation
The challenge – and the statement of the solution
Description of case study
Explanation of the edition, the editioning view, and the crossedition trigger
Readying an application for EBR
Implementation of case study as an EBR exercise
EBR task flow
Conclusion / Q&A
1
2
3
4
5
6
7
8
![Page 31: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/31.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Description of case study
![Page 32: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/32.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Case study
• The HR sample schema, that ships with Oracle Database, represents phone numbers in a single column:
– Diana Lorentz 590.423.5567
– John Russell 011.44.1344.429268
• Users now need to ring phone numbers from any country in the world
• So we want a uniform representation with two columns:– Country Code
– Number Within Country
![Page 33: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/33.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Case study
![Page 34: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/34.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Agenda
Scope of this presentation
The challenge – and the statement of the solution
Description of case study
Explanation of the edition, the editioning view, and the crossedition trigger
Readying an application for EBR
Implementation of case study as an EBR exercise
EBR task flow
Conclusion / Q&A
1
2
3
4
5
6
7
8
![Page 35: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/35.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
The edition – setting the stage
• Scenario – for now think only about synonyms, views, and PL/SQL units
– The application has 1,000 mutually dependent code objects
– In general, there’s more than one schema
– They refer to each other by name – in general, by schema- qualified name
– The upgrade needs to change 10 of these
![Page 36: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/36.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
The edition – setting the stage
1,000 v1 objects
900 unchanged v1 objects+
10 changed v2 objects
Pre-upgrade application backend
Post-upgrade application backend
![Page 37: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/37.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
The edition – setting the stage
• Of course, you can’t change the 10 objects in place because this would change the pre-upgrade app
• How can an old and a new occurrence of the “same” object co-exist?
• Before EBR, the only dimensions that determine which object you mean, when one object refers to another, are its name and its owner
• In short, the naming mechanisms, historically, were not rich enough to support online application upgrade
![Page 38: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/38.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
The edition extends the naming mechanism
• EBR introduces the new nonschema object type, edition – each edition can have its own private occurrence of “the same” object
• A database must have at least one edition
• You create a new edition as the child of an existing edition – and an edition can’t have more than one child
• A database session specifies which edition to use
(of course, the database has a default edition)
![Page 39: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/39.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
The edition extends the naming mechanism
• Through 11.1, an object is identified by its name and its owner
• From 11.2, an editioned object is identified by its name, its owner,and the edition where it was created
• However, when you identify it you can mention only its name and owner. This reference is interpreted in the context of a current edition
– in SQL execution
– in the text of a stored object
![Page 40: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/40.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
The semantic model for “create edition”
• When you create a new edition, every editioned objectin the parent edition is copied into the new edition
![Page 41: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/41.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
The mental model of the implementation
Object_1
Object_2
Object_3
Object_4
Pre-upgrade edition
![Page 42: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/42.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
The mental model of the implementation
Pre-upgrade edition Post-upgrade edition
Object_1
Object_2
Object_3
Object_4
Object_1
Object_2
Object_3
Object_4
is child of
(inherited)
(inherited)
(inherited)
(inherited)
![Page 43: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/43.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
The mental model of the implementation
Pre-upgrade edition Post-upgrade edition
Object_1
Object_2
Object_3
Object_4
Object_1*
Object_2*
Object_3
Object_4
is child of
(actual)
(actual)
(inherited)
(inherited)
![Page 44: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/44.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
The mental model of the implementation
Pre-upgrade edition Post-upgrade edition
Object_1
Object_2
Object_3
Object_4
Object_1*
Object_2*
Object_3
Object_4
is child of
Pre-upgrade edition
(dropped)
(actual)
(inherited)
(inherited)
![Page 45: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/45.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Editions
• If your upgrade needs only to change synonyms, views, or PL/SQL units, you now have all the tools you need
• Simply run the scripts that you, anyway, have written using a new edition while the application stays online
• Then change the default edition and let new session start in the new edition
• No “package state discarded” errors ever again!
![Page 46: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/46.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Editionable and noneditionable object types
• Not all object types are editionable
– Synonyms, views, and PL/SQL units of all kinds (including, therefore, triggersand libraries), and are editionable
–Objects of all other object types – for example tables – are noneditionable
• You version the structure of a table manually– Instead of changing a column, you add a replacement column
– Then you rely on the fact that a view is editionable
![Page 47: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/47.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Editioning views
• An editioning view may only project and rename columns
• You can’t have more than one editioning view for a particular table in a particular edition
• The EV must be owned by the table’s owner
• Application code should refer only to the logical world
• You can create table-style triggers (before or after statement or each row) on an editioning view using the “logical” column names
• A SQL optimizer hint can request an index on the physical table by specifying the “logical” column names
• Like all views, an editioning view can be read-only
![Page 48: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/48.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Editioning views – performance
• Any SQL statement that refers to one, or several, EVs will get the same execution plan as the statement you’d get if you replaced each of those references, by hand, with a reference to the table that the EV covers
• So using an EV in front of every table brings no performance consequences
• Tests have proved this
![Page 49: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/49.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Editioned and noneditioned objects – slight return
• An object whose type is noneditionable is never editioned
• An object whose type is editionable is editioned only when you request it for that object (requires that the owner is editions-enabled)
– Starting with Version 12.1, you control the editioned stateat the granularity of the single name
• Theorem (the NE-on-E prohibition):a noneditioned object cannot ordinarily depend on an editioned object
– For example, a table cannot depend on an editioned UDT
– If you want to use a type as the datatype for a column,that UDT must not be editioned
![Page 50: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/50.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Materialized views and indexes on virtual columns
• Starting with Version 12.1, objects of these kinds have metadatathat is explicitly set by the create and alter statements
– the evaluation edition explicitly specifies the name of the edition in whichthe resolution of editioned names will be done• within the closure of the object’s static dependency parents (at compile time)
• and for those objects that are identified dynamically during SQL execution (at run time)
– The usable edition range explicitly specifies the set of adjacent editionswithin which the optimizer will consider the object, for query re-write,when computing the execution plan
![Page 51: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/51.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Public synonyms
• A public synonym is just a synonym that happens to be owned bythe Oracle-maintained user called “public”
• In 11.2, “public” cannot be editions enabled, so a public synonym that tried to denote an editioned object would violate the NE-on-E prohibition
• Starting with Version 12.1, “public” is editions enabled,but all existing public synonyms are noneditioned at the per-object level
• You can now make your own public synonyms editionedat the per-object level
![Page 52: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/52.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Tables with UDT columns
• An ordinary (as opposed to virtual) column cannot specifythe evaluation edition or the usable edition range metadata
• Therefore, a UDT that defines the datatype for a table columnmust remain noneditioned
• In an EBR exercise, if the aim is to redefine the UDT,then the “classic” replacement column paradigm is used
![Page 53: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/53.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
The crossedition trigger
• Of course, DML does not stop during online application upgrade
• If the upgrade needs to change the structure that stores transactional data – like the orders customers make using an online shopping site – thenthe installation of values into the replacement columns must keep pace with these changes
• Triggers have the ideal properties to do this safely
• Each trigger must fire appropriately to propagate changes madeto pre-upgrade columns into the post- upgrade columns – and vice versa
![Page 54: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/54.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
The crossedition trigger
• Crossedition triggers directly access the table
• They have special firing rules
• You create crossedition triggers in the Post_Upgrade edition
– The paradigm is: don’t do any DDLs in the Pre_Upgrade edition
• The firing rules rules assume that
– Pre-upgrade columns are changed – by ordinary application code – only by sessions using the Pre_Upgrade edition
– Post-upgrade columns are changed only by sessions using the Post_Upgrade edition
![Page 55: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/55.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
The crossedition trigger
• A forward crossedition trigger is fired by application DML issued by sessions using the Pre_Upgrade edition
• A reverse crossedition trigger is fired by application DML issued bysessions using the Post_Upgrade edition
![Page 56: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/56.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Why such a long name?
• DDL stands for data definition language
• “create or replace” and “alter” re-define an existing object
– these bare commands are in-place redefinition
• Online table redefinition (there’s that word again) creates a secret copy, keeps it in step, and then does the twizzle. Similar for online index rebuild
– this is copy-based redefinition
• Edition-based redefinition lets you redefine many objects in concert
![Page 57: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/57.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Agenda
Scope of this presentation
The challenge – and the statement of the solution
Description of case study
Explanation of the edition, the editioning view, and the crossedition trigger
Readying an application for EBR
Implementation of case study as an EBR exercise
EBR task flow
Conclusion / Q&A
1
2
3
4
5
6
7
8
![Page 58: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/58.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Readying an application for EBR
– your very last old-fashioned offline upgrade tobuy your entry ticket to the wonderful worldof edition-based redefinition
![Page 59: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/59.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
The design before EBR adoption
• Application code accesses tables directly, in the ordinary way
![Page 60: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/60.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Readying the application for editions
• Put an editioning view in front of every table
– The EV and the table it covers can’t have the same name
– Rename each table to an obscure but related name (e.g. append an underscore,or lowercase the name)
– Create an editioning view for each table that has the same namethat the table originally had
• NOTE:
– If a schema has an object, whose type is noneditionable, that depends on an object whose type is editionable, then the adoption plan must accommodate this(using new-in-12.1 functionality) by controlling the editioned state of objectswhose type is editionable, at the granularity of the individual object
– Else, the editioned state can be conveniently set for the whole schema
![Page 61: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/61.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Readying the application for editions
• Revoke privileges from the tables and grant them to the editioning views
• Move VPD policies to the editioning views
• “Move” triggers to the editioning views
– Just drop the trigger and re-run the original (or mechanically edited)create trigger statement to recreate it on the editioning view
• Of course
– All indexes on the original Employees table remain valid but User_Ind_Columnsnow shows the new values for Table_Name and Column_Name
– All constraints (foreign key and so on) on the original Employees remain in forcefor Employees_
• NOTE: this readying work must be done by the developersof the application that adopts EBR
![Page 62: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/62.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Agenda
Scope of this presentation
The challenge – and the statement of the solution
Description of case study
Explanation of the edition, the editioning view, and the crossedition trigger
Readying an application for EBR
Implementation of case study as an EBR exercise
EBR task flow
Conclusion / Q&A
1
2
3
4
5
6
7
8
![Page 63: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/63.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Case study
![Page 64: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/64.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
PK Phone ...
Employees_
Pre-upgrade
Employees Maintain_Emps
![Page 65: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/65.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
PK Phone ...
Employees_
Employees Maintain_Emps
Employees Maintain_Emps
Pre-upgrade
Post-upgrade
![Page 66: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/66.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Employees Maintain_Emps
Employees Maintain_Emps
PK Phone #Cntry...
Employees_
Pre-upgrade
Post-upgrade
![Page 67: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/67.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Employees Maintain_Emps
PK Phone #Cntry...
Employees_
Employees Maintain_Emps
Pre-upgrade
Post-upgrade
![Page 68: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/68.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Employees Maintain_Emps
PK Phone #Cntry...
Employees_
Employees Maintain_Emps
Fwd_Xed
Pre-upgrade
Post-upgrade
![Page 69: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/69.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Employees Maintain_Emps
PK Phone #Cntry...
Employees_
Employees Maintain_Emps
Pre-upgrade
Post-upgrade
Fwd_Xed
![Page 70: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/70.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Employees Maintain_Emps
PK Phone #Cntry...
Employees_
Employees Maintain_Emps
Fwd_XedRvrs_Xed
Pre-upgrade
Post-upgrade
![Page 71: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/71.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Employees Maintain_Emps
PK Phone #Cntry...
Employees_
Employees Maintain_Emps
Fwd_XedRvrs_Xed
Pre-upgrade
Post-upgrade
![Page 72: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/72.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Employees Maintain_Emps
PK Phone #Cntry...
Employees_
Employees Maintain_Emps
Pre-upgrade
Post-upgrade
![Page 73: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/73.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
PK Phone #Cntry...
Employees_
Employees Maintain_Emps
Pre-upgrade
Post-upgrade
![Page 74: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/74.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Retiring the former Run Edition in Oracle Database 12.2
• Sneak preview…
![Page 75: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/75.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Rolling backa failed EBR exercise
![Page 76: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/76.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Employees Maintain_Emps
PK Phone #Cntry...
Employees_
Employees Maintain_Emps
Fwd_XedRvrs_Xed
Pre-upgrade
Post-upgrade
![Page 77: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/77.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Employees Maintain_Emps
PK Phone #Cntry...
Employees_
Employees Maintain_Emps
Fwd_XedRvrs_Xed
Pre-upgrade
Post-upgrade
![Page 78: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/78.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Employees Maintain_Emps
PK Phone #Cntry...
Employees_
Pre-upgrade
![Page 79: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/79.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Agenda
Scope of this presentation
The challenge – and the statement of the solution
Description of case study
Explanation of the edition, the editioning view, and the crossedition trigger
Readying an application for EBR
Implementation of case study as an EBR exercise
EBR task flow
Conclusion / Q&A
1
2
3
4
5
6
7
8
![Page 80: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/80.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Pre-EBR app in use
![Page 81: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/81.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Pre-EBR app in use
EBR-ready Pre_Upgrade app in use
offline:ready the app for EBR
![Page 82: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/82.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
EBR-ready Pre_Upgrade app in use
Pre_Upgrade app still in use,Post_Upgrade app ready for touch test
create the new editiondo the DDLs
transform the data
![Page 83: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/83.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Pre_Upgrade app still in use,Post_Upgrade app ready for touch test Do the touch test
![Page 84: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/84.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
EBR-ready Pre_Upgrade app in use
Pre_Upgrade app still in use,Post_Upgrade app ready for touch test
Rollback
![Page 85: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/85.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
EBR-ready Pre_Upgrade app in use
Pre_Upgrade app still in use,Post_Upgrade app ready for touch test
create the new editiondo the DDLs
transform the data
![Page 86: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/86.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Pre_Upgrade app still in use,Post_Upgrade app ready for touch test Do the touch test
![Page 87: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/87.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Pre_Upgrade app still in use,Post_Upgrade app ready for touch test
Hot rollover period:Pre_Upgrade app and
Post_Upgrade app in concurrent use
Open up forPost_Upgrade DMLs
![Page 88: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/88.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Hot rollover period:Pre_Upgrade app and
Post_Upgrade app in concurrent use
Post_Upgrade app remains in use,Pre_Upgrade app is retired,“ground state” is regained
Retire thePre_Upgrade app
![Page 89: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/89.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Post_Upgrade app remains in use,Pre_Upgrade app is retired,“ground state” is regained
![Page 90: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/90.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Agenda
Scope of this presentation
The challenge – and the statement of the solution
Description of case study
Explanation of the edition, the editioning view, and the crossedition trigger
Readying an application for EBR
Implementation of case study as an EBR exercise
EBR task flow
Conclusion / Q&A
1
2
3
4
5
6
7
8
![Page 91: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/91.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Edition-based redefinition
• EBR brings the edition, the editioning view, and the crossedition trigger
– Code changes are installed in the privacy of a new edition
– Data changes are made safely by writing only to new columnsor new tables not seen by the old edition
– An editioning view exposes a different projection of a table into each editionto allow each to see just its own columns
– A crossedition trigger propagates data changes made by the old edition intothe new edition’s columns, or (in hot- rollover) vice-versa
![Page 92: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/92.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Evolutionary capability improvements
• Some table DDLs that used to fail if another session had outstanding DML now always succeed
• Others, that cannot succeed while there’s outstanding DML, are now governed by a timeout parameter
• Online index creation and rebuild now never cause other sessions to wait
• The dependency model is now fine-grained: e.g. adding a new column to a table, or a new subprogram to a package spec, no longer invalidates the dependants
![Page 93: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/93.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
E-Business Suite Release 12.2
• The GA of E-Business Suite 12.2 was announced in October 2013
• From now on, all patches are done as EBR exercises
• Critical business operations will not be interrupted
• Revenue generating activities will stay on line
• The short downtime required by patching will be predictable
• The units change:
downtime now measured not in days,not in hours,but in minutes!
![Page 94: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/94.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Nota bene
• Online application upgrade is a high availability subgoal
• Traditionally, HA goals are met by features that the administrator can choose to use at the site of the deployed application
– independently of the design of the application
–without the knowledge of the application “vendor”
• The features for online application upgrade are used by the application “vendor”–when preparing the application for EBR
–when implementing an EBR exercise
• Site administrators, of course, will need to understand the features
![Page 95: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/95.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Next steps
• Read the edition-based redefinition chapter inthe Oracle Database Development Guide (12.1)
• Read my whitepaper, published on the OTN page for EBR
oracle.com/ebr
and look at the other collateral listed there
• Internet search for edition-based redefinition
![Page 96: Ebr the key_to_online_application_upgrade at amis25](https://reader031.vdocuments.us/reader031/viewer/2022021918/58a82f331a28abbe408b5faf/html5/thumbnails/96.jpg)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.