EBR - Part 1: Overview and Setup

This is a link to an index page for all the parts of the series

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

EBR – Part 1: Overview and Setup

Leave a Reply Cancel reply