Using Edition Based Redefinition for Rolling Back Stored Program Unit Changes

We had a few days of warm, sunny weather in Milton Keynes recently and this induced Deb and I to purchase a Garden Umberella to provide some shade.
After a lifetime of Great British Summers we should have known better. The sun hasn’t been seen since.
As for the umbrella ? Well that does still serve a purpose – it keeps the rain off.

Rather like an umbrella Oracle’s Edition Based Redefinition feature can be utilized for purposes other than those for which it was designed.
Introducted in Oracle Database 11gR2, Edition Based Redefinition (EBR to it’s friends) is a mechanism for facilitating zero-downtime releases of application code.
It achieves this by separating the deployment of code to the database and that code being made visible in the application.

To fully retro-fit EBR to an application, you would need to create special views – Editioning Views – for each application table and then ensure that any application code referenced those views and not the underlying tables.
Even if you do have a full automated test suite to perform your regression tests, this is likely to be a major undertaking.
The other aspect of EBR, one which is of interest here, is the way it allows you to have multiple versions of the same stored program unit in the database concurrently.

Generally speaking, as a database application matures, the changes made to it tend to be in the code rather more than in the table structure.
So, rather than diving feet-first into a full EBR deployment, what I’m going to look at here is how we could use EBR to:

  • decouple the deployment and release of stored program units
  • speed up the process of rolling back the release of multiple stored program unit changes
  • create a simple mechanism to roll back individual stored program unit changes

There’s a very good introductory article to EBR on OracleBase.
Whilst you’re here though, forget any Cross-Edition Trigger or Editioning View complexity and let’s dive into…

Fun and Games when releasing Stored Program Units

As I’ve observed before, deploying a new version of a PL/SQL program unit is destructive in nature. By default, the old version of the program is overwritten by the new version and is unrecoverable from within the database.
This can be problematic, especially on those occasions when you discover that your source control repository doesn’t contain what you thought it did.

Having the safety net of the old version stored in the database, ready to be restored should the need arise, is not something to be sniffed at.
Incidentally, Connor Macdonald has his own roll-you-own method for backing up PL/SQL source.

Before we get into how EBR can help with this, we need to do a bit of configuration…

Setting up Editions

From 11gR2 onward, any Oracle database will have at least one Edition…

When you connect to the database, you can specify an Edition to connect to. By default this is the current Database Edition.
To start with, when you’re connected, both the current edition and session edition will be ORA$BASE :

select sys_context('userenv', 'current_edition_name') as default_edition,
    sys_context('userenv', 'session_edition_name') as session_edition
from dual;

However, by default, it does not appear that any database objects are associated with the ORA$BASE edition.
Taking the HR schema as an example :

select object_name, object_type, edition_name
from dba_objects_ae
where owner = 'HR'
and object_type != 'INDEX'
order by 1,2
/

When we execute this query, we get :

That’s because, at this point, HR is blissfully unaware of any Editions. However, if we enable editions for this user…

alter user hr enable editions
/

…and re-execute the query, we can see that things have changed a bit…

The Editionable objects in the schema ( Procedures, Triggers and the View) are now associated with the ORA$BASE edition.

The scope of an Edition is the database ( or the PDB if you’re on 12c). To demonstrate this, let’s say we have another schema – called EDDY :

create user eddy identified by eddy
/

alter user eddy temporary tablespace temp
/

alter user eddy default tablespace users
/

alter user eddy quota unlimited on users
/

grant create session, create table, create procedure to eddy
/

alter user eddy enable editions
/

Eddy is a bit of an ‘ed-banger and the schema contains the following…

create table rock_classics
(
    artist varchar2(100),
    track_name varchar2(100)
)
/

create or replace package i_love_rock_n_roll as
	function eddy_the_ed return varchar2;
end i_love_rock_n_roll;
/

create or replace package body i_love_rock_n_roll as
    function eddy_the_ed return varchar2 is
    begin
        return 'Run to the Hills !';
    end eddy_the_ed;
end i_love_rock_n_roll;
/

At this stage, these objects have the Edition you would expect. This time, we can query the USER_ version of the OBJECT_AE view whilst connected as EDDY…

select object_name, object_type, edition_name
from user_objects_ae
order by 2,1
/

I want to make some changes to the code in the EDDY application. In order to preserve the “old” code, as well as making deployment a fair bit easier, I need a new edition…

Using a New Edition

First off, as a user with the CREATE EDITION privilege…

create edition deep_purple
/

We can see that the new Edition has been created with ORA$BASE as it’s parent…

At present ( i.e. 12cR2), an Edition can have only one child and a maximum of one parent. Every Edition other than ORA$BASE must have a parent.
Therefore, it’s probably helpful to think of Editions as release labels rather than branches.

At this point, whilst we now have two editions in the database, it’s only possible for EDDY to use one of them.
If EDDY attempts to switch to the new Edition…

alter session set edition = deep_purple
/

…we get…

In order for EDDY to be able to use the new Edition, we need to grant it…

grant use on edition deep_purple to eddy
/

Now Eddy can see the new edition as well as the existing one :

alter session set edition = deep_purple
/
select property_value as default_edition,
    sys_context('userenv', 'session_edition_name') as session_edition
from database_properties
where property_name = 'DEFAULT_EDITION'
/

Now we have access to the new Edition, we’re going to make some changes to the application code.
First of all, we want to add a function to the package :

create or replace package i_love_rock_n_roll as
	function eddy_the_ed return varchar2;
    function motor_ed return varchar2;
end i_love_rock_n_roll;
/

create or replace package body i_love_rock_n_roll as
    function eddy_the_ed return varchar2 is
    begin
        return 'Run to the Hills !';
    end eddy_the_ed;
    function motor_ed return varchar2
    is
    begin
        return 'Sunrise, wrong side of another day';
    end motor_ed;
end i_love_rock_n_roll;
/

We’re also going to create a new standalone function :

create or replace function for_those_about_to_rock
	return varchar2 as
begin
	return 'We salute you !';
end for_those_about_to_rock;
/

Looking at how these changes have affected the Editions with which these objects are associated with is revealing :

select object_name, object_type, edition_name
from user_objects_ae
order by 1,2
/

OBJECT_NAME              OBJECT_TYPE   EDITION_NAME
-----------              -----------   ------------
FOR_THOSE_ABOUT_TO_ROCK  FUNCTION      DEEP_PURPLE
I_LOVE_ROCK_N_ROLL       PACKAGE       ORA$BASE
I_LOVE_ROCK_N_ROLL       PACKAGE       DEEP_PURPLE
I_LOVE_ROCK_N_ROLL       PACKAGE BODY  ORA$BASE
I_LOVE_ROCK_N_ROLL       PACKAGE BODY  DEEP_PURPLE
ROCK_CLASSICS            TABLE

The new function, for_those_about_to_rock, is assigned to the current session edition as we would expect. However, it appears that the i_love_rock_n_roll package is now assigned to both versions.
That’s not right, surely ?

Let’s do a quick check…

select i_love_rock_n_roll.motor_ed
from dual
/

MOTOR_ED
--------
Sunrise, wrong side of another day

So, we can see the new package function.
However, if we now switch to the other Edition…

alter session set edition = ORA$BASE
/

Session altered.

…and try to invoke the standalone function we just created…

select i_love_rock_n_roll.motor_ed
from dual
/

Error starting at line : 1 in command -
select i_love_rock_n_roll.motor_ed
from dual

Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00904: "I_LOVE_ROCK_N_ROLL"."MOTOR_ED": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:
*Action:

However, we can still see the original package…

select i_love_rock_n_roll.eddy_the_ed
from dual
/

EDDY_THE_ED
-----------
Run to the Hills !

Where it gets really interesting – for our current purposes at least, is that we can see the source code for both versions of the package in the USER_SOURCE_AE view.
For the original Package Header :

select text
from user_source_ae
where type = 'PACKAGE'
and name = 'I_LOVE_ROCK_N_ROLL'
and edition_name = 'ORA$BASE'
order by line
/

…we get …

TEXT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
package i_love_rock_n_roll as
	function eddy_the_ed return varchar2;
end i_love_rock_n_roll;

…but we can also get the new version from the same view…

select text
from user_source_ae
where type = 'PACKAGE'
and name = 'I_LOVE_ROCK_N_ROLL'
and edition_name = 'DEEP_PURPLE'
order by line
/

…returns…

TEXT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
package i_love_rock_n_roll as
	function eddy_the_ed return varchar2;
    function motor_ed return varchar2;
end i_love_rock_n_roll;

One other point to note is that you can grant privileges on an object that only exists in your “new” edition…

SQL> grant execute on for_those_about_to_rock to hr;

Grant succeeded.

…but when connected as that user, the object will not be visible…

select eddy.for_those_about_to_rock from dual;

Error starting at line : 1 in command -
select eddy.for_those_about_to_rock from dual
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00904: "EDDY"."FOR_THOSE_ABOUT_TO_ROCK": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:
*Action:

…nor will the grantee be able to see the Edition if they do not otherwise have privileges to do so…

alter session set edition = deep_purple;

Error starting at line : 1 in command -
alter session set edition = deep_purple
Error report -
ORA-38802: edition does not exist
38802. 00000 -  "edition does not exist"
*Cause:    This error occurred because a reference was made to an edition that
           does not exist or that the current user does not have privileges on.
*Action:   Check the edition name and verify that the current user has
           appropriate privileges on the edition.

Releasing code using Editions

As we can see, Editions allow us to separate the deployment of code from the actual release.
We’ve already deployed our application changes but they are only visible to eddy at the moment.
NOTE – as I said at the start, we’re only using EBR for releasing stored program units. If we had any table DDL then we’d need to deal with that separately from EBR in these particular circumstances.

Anyhow, once we’re sure that all is well, we just need to “release” the code from the DEEP_PURPLE edition as follows :

alter database default edition = deep_purple
/

Now when we connect as hr…

select sys_context('userenv', 'session_edition_name')
from dual
/

SYS_CONTEXT('USERENV','SESSION_EDITION_NAME')
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
DEEP_PURPLE

…and the new function is now accessible…

select eddy.for_those_about_to_rock
from dual
/

FOR_THOSE_ABOUT_TO_ROCK
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
We salute you !                                                                                                                                                        

Note that, whilst the Editionable objects in the HR schema itself have not directly inherited the new Edition…

select object_name, object_type, edition_name
from user_objects_ae
where object_type in ('PROCEDURE', 'TRIGGER', 'VIEW')
/

OBJECT_NAME               OBJECT_TYPE         EDITION_NAME
------------------------- ------------------- ------------------------------
UPDATE_JOB_HISTORY        TRIGGER             ORA$BASE
ADD_JOB_HISTORY           PROCEDURE           ORA$BASE
SECURE_EMPLOYEES          TRIGGER             ORA$BASE
SECURE_DML                PROCEDURE           ORA$BASE
EMP_DETAILS_VIEW          VIEW                ORA$BASE                      

…they are still usable now that we’ve migrated to the DEEP_PURPLE edition…

select first_name, last_name
from emp_details_view
where department_id = 60
/

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Alexander            Hunold
Bruce                Ernst
David                Austin
Valli                Pataballa
Diana                Lorentz                  

Rolling back the entire release

If we need to rollback all of the code changes we’ve made EBR makes this process very simple.

Remember, the objects owned by EDDY in the DEEP_PURPLE Edition are :

select object_name, object_type
from dba_objects
where owner = 'EDDY'
order by 2,1
/

OBJECT_NAME              OBJECT_TYPE
-----------              -----------
FOR_THOSE_ABOUT_TO_ROCK  FUNCTION
I_LOVE_ROCK_N_ROLL       PACKAGE
I_LOVE_ROCK_N_ROLL       PACKAGE BODY
ROCK_CLASSICS            TABLE         

…and the package members are…

Now, to rollback all of the application changes associated with the DEEP_PURPLE Edition, we simply need to run…

alter database default edition = ora$base
/

We can see that this has had the desired effect

select object_name, object_type
from dba_objects
where owner = 'EDDY'
order by 2,1
/

OBJECT_NAME         OBJECT_TYPE
-----------         -----------
I_LOVE_ROCK_N_ROLL  PACKAGE
I_LOVE_ROCK_N_ROLL  PACKAGE BODY
ROCK_CLASSICS       TABLE

The function has disappeared, along with the additional package member…

Well, that’s nice and easy, but how could we use EBR to rollback a single change rather than the entire release ?

Rolling back a single change

To demonstrate this, we need to set the current Edition back to DEEP_PURPLE…

alter database default edition = deep_purple
/

Remember that, where relevant, EBR ensures that a copy of an object’s source code for previous Editions is kept in the Data Dictionary.
We can use this stored code to restore these versions to the current Edition.
NOTE – the owner of this next procedure will need the ALTER ANY PROCEDURE privilege :

create or replace procedure restore_version
(
    i_owner dba_source_ae.owner%type,
    i_name dba_source_ae.name%type,
    i_type dba_source_ae.name%type,
    i_source_edition dba_source_ae.edition_name%type,
    i_target_edition dba_source_ae.edition_name%type
)
is
--
-- Simple procedure to demonstrate restoring a given Edition's version
-- of a stored program unit.
--
    -- The DDL we execute will complete the current transaction so...
    pragma autonomous_transaction;

    rb_source clob;
begin
    if i_owner is null or i_name is null or i_type is null
        or i_source_edition is null or i_target_edition is null
    then
        raise_application_error(-20000, 'Values for all parameters must be supplied');
    end if;

    -- Make sure our session is in the target edition. If not then error.
    if upper(i_target_edition) != upper(sys_context('userenv', 'session_edition_name')) then
        raise_application_error(-20001, 'Session must be in the target edition');
    end if;

    for r_code in
    (
        select line,text
        from dba_source_ae
        where owner = upper(i_owner)
        and name = upper(i_name)
        and type = upper(i_type)
        and edition_name = upper(i_source_edition)
        order by line
    )
    loop
        if r_code.line = 1 then
            rb_source := 'create or replace '
                ||replace(lower(r_code.text), lower(i_type)||' ', i_type||' '||i_owner||'.');
        else
            rb_source := rb_source||r_code.text;
        end if;
    end loop;

    if nvl(length(rb_source),0) = 0 then
        raise_application_error(-20002, 'Object source not found');
    end if;    

    -- execute the ddl to restore the object
    execute immediate rb_source;

end restore_version;
/

In the current example we have the Package header and Package body of EDDY.I_LOVE_ROCK_N_ROLL in both the ORA$BASE and DEEP_PURPLE Editions.
If we want to reverse these changes but leave the rest of the release unaffected, we can simply invoke this procedure…

begin
    restore_version('EDDY', 'I_LOVE_ROCK_N_ROLL', 'PACKAGE', 'ORA$BASE', 'DEEP_PURPLE');
    restore_version('EDDY', 'I_LOVE_ROCK_N_ROLL', 'PACKAGE BODY', 'ORA$BASE', 'DEEP_PURPLE');
end;
/

We can now see that the original package has been restored and is available in the DEEP_PURPLE Edition, along with the other code from the release. However, the package function we’ve removed isn’t :

As it stands, this would is a one-time operation on as we’re effectively restoring the old version by creating it in the new Edition. At that point the stored program units are identical in both Editions.
The obvious solution would be to change the session edition programatically in the procedure. Unfortunately, attempts to do so run into :

ORA-38815: ALTER SESSION SET EDITION must be a top-level SQL statement

Of course, you could issue the ALTER SESSION commands in the script you’re using to call the procedure. However, you would then also need to make a copy of the current Edition code before restoring the old version and it all gets fairly involved.

Conclusion

Whilst all of this isn’t quite using EBR for it’s intended purpose, it does offer a couple of advantages over the more traditional method of releasing stored program unit changes.
First of all, you can separate the deployment of code into your production environment and making it visible to users.
Secondly, releasing the code becomes a single ALTER DATABASE statement, as does rolling back those changes.
Finally, it is possible to quickly revert individual stored program units should the need become evident once the release has been completed.
All of this functionality becomes available without you having to write much code.
The downside is that a reversion of an individual program unit is a one-time operation unless you write some custom code around this, which is what we were trying to get away from to start with.
Additionally, without implementing any Editioning Views, you will still have to manage structural changes to tables in the same way as before.

The weather forecast is sunny for this coming weekend. Unfortunately, that means I’ll have to mow the lawn rather than sit under the umbrella. Honestly, I’m sure astro-turf can’t be that expensive…

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s