I have been using EBR in a real production system for more than 4 years now.
EBR – an acronym for Edition-Based Redefinition – is a powerful and unique feature (or, more precisely, a set of features) that enables patching and upgrading live Oracle-based applications in an online fashion, with zero downtime.
As an Oracle Developer and DBA I find EBR one of the most important tools in my toolkit, and I take advantage of every opportunity to let other Oracle developers and DBAs know about it.
I love EBR because:
- It enables us to do any change to active production systems – from a small bug fix to a major version upgrade – in an online fashion, using hot rollover: we expose a new version (a new edition) while users are still using the old version, and different users may use two (or even more) versions at the same time.
- We perform the upgrade in the privacy of a new unexposed edition, which is a huge benefit, in my opinion, because it eliminates the regular pressure of completing the upgrade as soon as possible to reduce downtime, and the stress of not making any harm by mistake to a live system.
- We can do the upgrade at any time, not necessarily at off-peak hours.
- And EBR is supported in all the editions (since Oracle 11.2), including standard edition, and requires no special license.
I’m starting today a series of posts about EBR. I’ll explain its capabilities, how to use it and in what situations, and I’ll share insights and tips from my personal experience.
In each one of the first posts we’ll see one development use case which requires some change (or changes) in our data model and/or our PL/SQL code. We’ll see what are the challenges in performing these changes using an online upgrade, and how EBR helps overcoming these challenges.
In subsequent posts (after this first batch of posts), I’ll cover some advanced topics and challenging issues (well, life is not always a bed of roses…).
Before we start talking about EBR itself, let’s create our demo environment, which I’ll use in all the next posts of this series.
We’ll start with a single table in our schema – the PEOPLE table:
create table people ( id number(9) not null constraint people_pk primary key, first_name varchar2(15) not null, last_name varchar2(20) not null, phone_number varchar2(20) );
We have a package – PEOPLE_DL, with two procedures – add and remove, for inserting a new record into PEOPLE and deleting a record from PEOPLE, respectively:
create or replace package people_dl as procedure add ( i_id in people.id%type, i_first_name in people.first_name%type, i_last_name in people.last_name%type, i_phone_number in people.phone_number%type ); procedure remove ( i_id in people.id%type ); end people_dl; /
create or replace package body people_dl as procedure add ( i_id in people.id%type, i_first_name in people.first_name%type, i_last_name in people.last_name%type, i_phone_number in people.phone_number%type ) is begin insert into people (id,first_name,last_name,phone_number) values (i_id,i_first_name,i_last_name,i_phone_number); -- dbms_output.new_line; dbms_output.put_line('person was added using the BASE version'); -- end add; procedure remove(i_id in people.id%type) is begin delete people where id = i_id; end remove; end people_dl; /
Note that the current implementation of the people_dl.add procedure prints the message “person was added using the BASE version” (in addition to inserting a record to the PEOPLE table). We’ll change this message every time we change the implementation, so it will help us observing which implementation is being used.
We also have another package – app_mgr, with a single procedure – do_something. Its implementation is quite silly – it adds a person and immediately removes it, using the procedures from the people_dl package. The only thing that really matters here is that the app_mgr‘s body uses the people_dl package (and therefore depends on it).
create or replace package app_mgr as procedure do_something; end app_mgr; /
create or replace package body app_mgr as procedure do_something is begin people_dl.add(123, 'John', 'Doe', null); people_dl.remove(123); commit; end do_something; end app_mgr; /
As we’ll see, the dependencies between objects are a key player in the EBR game. It will be helpful to visualize the major dependencies between the objects in our schema (“S” and “B” represent a package spec and a package body, respectively):
For other parts of the EBR series, please visit the index page