In the earlier releases, it was not possible to upgrade database objects while these were being actively accessed by applications. With the Edition Based Redefinition (EBR) feature introduced in 11g Release 2, this became possible without any downtime or, downtime is reduced to just application services bounce.
In this article, we will discuss some basic concepts of EBR and steps to implement it to support an Applications upgrade. Once an application supports EBR, this feature then can be used to reduce application downtime.
In the earlier releases, it was not possible to upgrade database objects while these were being actively accessed by applications. With the Edition Based Redefinition (EBR) feature introduced in 11g Release 2, this became possible without any downtime or, downtime is reduced to just application services bounce. Let us take a look at some of the important features of EBR.
What is Edition?
An edition is a kind of a private environment in which we create the new versions of database objects (or redefine the objects) without actually changing the current definition of objects being used by application users. Application users can continue to use unchanged/old version of objects while these are being changed. When change is completed to a new/child edition, then we can make this new edition as default so that all users can use it now. Starting 11gR2, every database has a default edition named ora$base and multiple editions can exist in a database simultaneously.
Editionable and Non-Editionable Objects:
The object types which can be versioned/redefined using this feature are called the Editionable objects. Below are the lists of object types which can be editioned. All other object types are non-editionable.
Editionable Objects include SYNONYM, VIEW, FUNCTION, LIBRARY, PROCEDURE, PACKAGE and PACKAGE BODY, TRIGGER, TYPE and TYPE BODY
Non-editionable objects include TABLES, MVIEWS, PUBLIC SYNONYMS etc. So based on these types, there are some limitations for EBR object types such as:
- non edition object cannot depend on an editioned object.
- public synonym cannot refer to an editioned object.
- materialized view cannot depend on an editioned view.
- function-based index cannot depend on an editioned function.
The TABLE is a kind of non-editionable data-type. However, in a real upgrade scenario, we may need to modify the table structure. This is where the editioning views comes into the picture. Editioning views allows us to treat the base table as if it is editioned, which means, we can create an editioned view (similar to a base table) with modified definition. However, we can't add indexes and constraints to an editioned view. Indexes and constraints can be added to a base table only. Editioning views can be read-only or read-write.
As the name suggests, these kind of triggers come into the picture when we need to maintain data-consistency between two different editions. For example, if users are using an application object in a current/online edition and in the case that the object is being modified/redefined by patching in another child edition, then this type of trigger i.e. cross editioned triggers are used. These triggers contain the code to maintain data consistency between two editions.
Forward crossedition triggers move data from columns used by the old edition to columns used by the new edition; reverse crossedition triggers do the reverse. Crossedition triggers are temporary i.e. these should be dropped after the restructured tables are available to all users.
How to use EBR to upgrade an application:
An application should meet some minimum requirements before it can use the EBR feature. These are:
- User/Schema should be editions enabled
- Prepare application to use editioning views.
A schema/database user should be edition enabled before it can use EBR. This can be done at user creation time or by using Alter User later. The EDITIONS_ENABLED column of the static data dictionary view DBA_USERS or USER_USERS shows which users have editions enabled.
ALTER USER user_name ENABLE EDITIONS;
Prepare application to use editioning views - An application that uses one or more tables must cover each table with an editioning view. An editioning view covers a table so that it points to the base table. Below are steps that can be performed to prepare an application for EBR. Please note that these are first time tasks (and may take longer the first time so that application is ready for EBR), but later on (from next time) will minimize downtime once application is ready for EBR:
- Rename the table a new name (so that we can assign its current name to its editioning view). We can optionally rename the columns also.
- Create the editioning view with the same name of original table.
- If triggers were there on original tables then drop these and recreate these on editioning view.
- Revoke the privileges from all users on the tables and grant the same privilege on the editioning view.
Once application is ready to support EBR, we can use below steps to perform an application upgrade using EBR:
- Create a new/child edition.
- Make the new edition as session default edition.
- Make the necessary changes to the objects of the application.
- Ensure that all objects are valid and changes working as expected.
- Make the upgraded application available to all users (i.e. make the new edition as default edition for the database).
- Retire/Drop the old edition.
DBA Tasks for Edition-Based Redefinition:
Although, it is mainly Application developers who are in essence the primary owners for upgrading an application using EBR, they may ask for help to perform edition-related tasks that require DBA privileges. Some of these are, but not limited to:
- Grant or revoke privileges to create, alter, and drop editions (for example, CREATE EDITION and DROP EDITION may be used)
- Enable editions for a schema/user (for example, ALTER USER user_name ENABLE EDITIONS;)
- Set the database default edition (for example, ALTER DATABASE DEFAULT EDITION = edition_name;)
- Set the edition attribute of a database service (can be done using dbms_service package or by using srvctl in case of RAC)
Viewing Information about Editions from dictionary tables:
DBA_EDITIONS, DBA_EDITION_COMMENTS, DBA_EDITIONING_VIEWS, DBA_EDITIONING_VIEWS_AE are some of the views which shows information about the editions directly whereas views such as DBA_OBJECTS, DBA_OBJECTS_AE, DBA_ERRORS, DBA_ERRORS_AE, DBA_USERS, DBA_SERVICES etc. have column in them which shows information related to edition for these views.
EBR is a good feature which can reduce applications downtime to a great extent if used and implemented properly. Oracle E-Business Suite 12.2 is a good example of applications which use this feature to reduce patching and upgrade downtimes. However, its use is not limited to Oracle Standard products and can be implemented on custom/legacy applications with proper planning. For any questions on the topic click below. You can also leave a comment in the field below.