Introduction:
Prior to 11g release 2, it was not possible to upgrade database objects while these were being actively accessed by the applications. But Oracle have introduced a concept of EBR (Edition-Based Redefinition) from 11gR2 which make it possible to perform.
In this blog, let’s see what editions are, editionable objects are and how EBR works in Oracle databases.
Before knowing about editionable objects, we should know what is an Edition ?
An Edition is a private environment where redefining of database objects took place.
Following objects are editionable:
-
- Synonym
- View
- Function
- Procedure
- Package (specification and body)
- Type (specification and body)
- Library
- Trigger
Remember, Tables / Indexes / Mviews / Public synonyms are Non-Editionable because changes to table structures are not editionable directly and if table data is duplicated in a new edition that can cause issues. To overcome it, cross-edition triggers and editioning views can be used when code release changes require to change table data. This makes sense, as it will not be practical to duplicate huge data every time, we upgrade the application.
An Editionable objects are the objects on which changes can be made in the privacy of a new and unexposed edition. For example, a change on View which is editionable object is invisible to the parent object.
How EBR works in Oracle database:
Every database must have at least one edition. Therefore, when you create a new database, an edition called ORA$BASE is created by default. A database supports several editions organized as a hierarchy with one root edition (by default ORA$BASE) and a parent-child relationship between every other edition.
In the above example,
A session can be connected to RL#1 or RL#2 edition or default edition and perform any changes on editionable objects within it.
If the editionable objects are dropped from RL#1 edition, then still those can be visible from RL#2 in that release as a copy inherited from parent but no changes are effected from RL#1. Same applies to RL#2 vice versa.
In the ora$base edition, editionable objects are accessible as a default when no other editions exist in the database.
Enable Edition :
The schema which requires to perform EBR should be enabled with editions type.
Connect to database and run as sysdba,
ALTER USER user_name ENABLE EDITIONS;
Note:
From 12.1 we can define specific objects as editionable or non-editionable and in 11.2 all the objects of editionable types are editionable.
The editionable objects under a scheme can be viewed using the below query.
select object_type,object_name,edition_name
from dba_objects_ae
where owner = ‘&schema’
order by nvl2(edition_name,2,1),object_type,object_name,last_ddl_time;
The editioning view name will include a suffix using the hashtag symbol. Oracle typically recommends keeping no more than 25 editions before enacting a manual clean up cycle.
The following diagram will demonstrate the concept of an editioned view on a table in Oracle E-Business Suite 12.2.
The editioned view will only expose the columns and data of the table that are allowed for the current edition. The table data itself will be maintained using crossedition triggers.
Custom code changes will refer to the editioning view or preferably the synonym but never the table name directly.
In summary, the typical steps that you must carry out to redefine editioned objects are the following:
-
- Create edition and set it as session specific.
- Alter the editioned objects and ensure that all objects are valid.
- Do application sanity with the new edition.
- Switch to the new edition and make it as permanent.
Case study:
A use case of Zero Downtime Upgrade of Core Banking Systems with Edition-Based Redefinition is the best example in a real time scenario.
