Monday, May 31, 2010

Oracle Edition Based Redefinition the end of a BIG BANG for application upgrades

When upgrading an application or implementing improvements on stored PL/SQL procedures rule is compiling of those objects needed. During this phase, the end users can not using the application, they must wait until the whole change in the application implemented is finished or otherwise unexpected behavior may occur. In Oracle 11g Release 2 this problem is solved by usubg editions. With editions it is now possible to do live deployments of the new code to compile, whitehout users have to wait for implementing the changes. This is the end of a 'BIG BANG' at application upgrades. It is even possible to have multiple application versions operational.

In this blog i will descibe the mechanism of the Edition Based Redefinition.

The topics addressed are:

  • Existing already online Oracle database options
  • What are Editions
  • New Oracle objects : Edition View
  • New Oracle object : Forward cross edtion trigger

Edition-Based Redefinition is a mechanism to designed minimize downtime by database application upgrades. It is one of the top options in the new Oracle Database 11g Release 2. It is a next step in High Availability: a database application upgrade without downtime. It also allows you to have multiple aplication versions operational of the same application on the same data set ( run in parallel).

Over the years many solutions have become available for online changes to run on an Oracle database with minimal impact.

Options changes online:

  • Changing of most Oracle parameters (only 90 of the 350 are not online to change)
  • Reorganize objects
  • creating online new indexes
  • Application patches on Oracle database with Oracle Real Application Clusters
  • Upgrading Oracle database major release of new major release;

Almost any object can be changed at the database level while the database is up and running with a few notable exceptions such as the recreation of a stored procedure, change of a trigger, add and withdrawal of privileges and changing a view. In short, the objects that makes the application can not be changes online whithout downtime of the appliaction for the end users.

A new version of an application is deployed to the database, a number of steps are needed to implemented those changes. New objects are created and existing objects are modified. These changes often lead to invalidation of dependent objects and must be recompiled. From the very first change to an existing object to the recompilation of all invalid objects and the conversion of data, the application must be downtime. Depending on the size of the application and the nature of the changes the downtime takes a few minutes to several hours.

Edition-Based Redefinition is now an opportunity for running different versions of the application level inside the Oracle database. The new release of the application will be built alongside the existing release(s). The sessions continue at the current release. Only when the new release is fully installed, compiled and tested it can be activated. New database sessions continue from that point using the new release of the application and existing sessions remain active against the previous release.

To create a new version inside the database , you have to create an isolated environment to do this without touching the existing objects. Until now it was not possible in the Oracle database to create a new version of the application without overwriting existing objects.


Edition-Based Redefinition introduces editions. Editions are isolated environments, a kind of parallel worlds in which objects can be created and modified without impact on existing properties. A single schema can have two or more versions of stored PL / SQL objects, types and views.

In the past, a unique objects inside the database ware based on the following two components: the owner of the object and the object name itself. This ensures that no two stored procedures named "P" in a single scheme may exist.

Edition-Based Redefinition now adds a third dimension "edition" to uniquely identify an object: All objects are now identified by the edition of the session (EDITION), the owner of the object (OWNER) and the object name (OBJECT).

Edition Based Redefinition (Edition, Owner,Object)

Each database consists of at least one Edition , the default name ORA $ BASE.

A new edition is created with the command:


Each database is connected to an edition of the application and each edition is a successor of a previous edition. When creating a new edition Edition it inherits all the objects defined in its predecessor. An edition of the database can be changed with an alter database command:


All new sessions in an Oracle Database 11g Release 2 database using the default standard edition level ORA $ BASE. An edition can be changed at session level with an alter session command. This applies only to editions that you have been granted on.


This statement from the client application can be executed by using a logon trigger. The mechanism by using a logon trigger, the Edition-Based Redefinition is than transparent to the application that makes use of the database.

Making different versions of objects by using editions for the following objects are supported: views, packages, procedures, triggers, synonyms, and (user defined) types. The main component of the database (the data and tables) are NOT supported.

The tables are outside the methodology Edition-Based Redefinition. Now how to deal with new table structure as a result of an application change? The solution is simple, the tables disappears for the user by introducing a new type of view, the 'edition view " with the forward cross Edition trigger .

Edition View

Edition view is makes a shield around the existing table. All references to the table are replaced by references to the edition view. The constraints and auditing policies continue to refer to the table structure. The views are a very limited edition type of strong views. Editions views are only needed for data projection based on a table structure. The current rule for the edition views is that they only can be made on a table without options of column expressions. Renaming and / or deletion of columns in a view edtitioning is no problem.

Forward cross edtion trigger

Trigger of type forward cross trigger Edition are designed to synchronize data between the previous and the new edition of the table. The forward cross Edition trigger is activated when a statement is executed from an edition that precedes the edition in which it was create.

Edition Based Redefinition (Edition based before/after triggers)

Edition Based Redefinition is the mechanism that Oracle has introduced as a transition that takes place between different application releases with minimal downtime. The database can have a large number of parallel editions. It is possible to have multiple versions of applications, simultaneously active, by using different editions inside database.

Edition Based Redefinition is a complex step

To have a completely Edition Based Redefinition is a complex step. The complexity lies in the creation and design of the 'edition views "associated with" forward triggers cross Edition " on the existing data and table structure. This is also the most important consideration when introducing Edition Based Redefinition. The application of Edition Based Redefinition only focused on PL/SQL objects and views is easy to implement.


Edition Based Redefenition is a new mechanism for certain organizations with strict requirements regarding the availability of database and application. An interesting feature is that different user groups, different versions of the same application can be use which asked for similar data / tables of the database structure. Oracle has stated that the coming period extensions and refinements Edition Based Redefinition expected.


Before you can use the process Edition Based Redefenition to remove / add columns to table Editions views are needed. The views are the buffers between the application and the physical schema at the database level. The first launch of the Edition views involves a downtime of the application. This is a single failure of the application by introducing the Edition Based Redefinition and will never be needed again in the future.

It is wise to take into account Edition Based Redefenition by development of new applications. This strategy will allow you to upgrade the application online without downtime in the future.

For an existing application to convert to a full edition based application, the following general steps are needed:

  • Time wimdow to migrate to a complete edition based application
  • Rename the existing table.
  • Make editions views with the name of the original tables.
  • remove triggers to existing tables and move them to the views editions.
  • Re-create the triggers on the views editions
  • Objects privilege review, moving the table, and reissue the views editions.


No comments:

Post a Comment