Edition-Based Redefinition (EBR) for online application migration

Posted on , updated on by Yannick Jaquier
Oracle , ,

Table of contents

Preamble

Edition-Based Redefinition (EBR) has been introduced in Oracle database 11gr2. And the feature is free:

EBR is available for use in all editions of Oracle Database without the need to license it.

The purpose is to handle database application code change without impacting users than can continue to work transparently. Users can be migrated at different time or by bunch of application server for example.

For the noneditioned objects (like tables) you must use different strategies. To go further in my test exercise I have club EBR with a very old feature exposed by DBMS_REDEFINITION package. This feature that I have personally never used is there since Oracle database 9i (!!)

My test database will be a pluggable database inside a 21c (21.6) Enterprise Edition instance. My test server is running Redhat 7.9 (Maipo), has 12 cores and 64GB of memory.

Edition-Based Redefinition (EBR) and DBMS_REDEFINITION test case

One table with a primary key:

SQL> CREATE TABLE test01 (
  id INTEGER,
  first_name VARCHAR2(20) NOT NULL,
  last_name VARCHAR2(20) NOT NULL,
  CONSTRAINT test01_pk PRIMARY KEY(id)
);
 
TABLE created.

One trigger to format first name and last name:

SQL> CREATE OR REPLACE TRIGGER test01_first_last_name
before INSERT OR UPDATE ON test01
FOR each ROW
BEGIN
  :NEW.first_name:=INITCAP(:NEW.first_name);
  :NEW.last_name:=INITCAP(:NEW.last_name);
END;
/
 
TRIGGER created.

One procedure to display a welcome message with user id as parameter:

SQL> CREATE OR REPLACE PROCEDURE greet(pid NUMBER)
IS
  first_name VARCHAR2(20);
  last_name VARCHAR2(20);
 
BEGIN
  SELECT first_name, last_name INTO first_name, last_name FROM test01 WHERE id=pid;
  dbms_output.put_line('Hello ' || first_name || ', ' || last_name);
END;
/
PROCEDURE created.

I insert a new row and confirm everything is working fine:

SQL> INSERT INTO test01(id,first_name,last_name) VALUES(1,'yannick','JAQUIER');
 
1 ROW created.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> SELECT * FROM test01;
 
        ID FIRST_NAME           LAST_NAME
---------- -------------------- --------------------
         1 Yannick              Jaquier
 
SQL> SET serveroutput ON
SQL> EXEC greet(1);
Hello Yannick, Jaquier
 
PL/SQL PROCEDURE successfully completed.

Allow your test user to manipulate editions:

SQL> ALTER USER yjaquier enable editions;
 
USER altered.

Edition-Based Redefinition (EBR) testing

In my small test case the high level objective is to modify my base table (test01) to add a full_name column that will be the concatenation of first_name and last_name and use this full_name column is the new greet procedure. The test01_first_last_name must aso be modified to table into account this newly added column.

Application preparation for EBR

To list the objects that are editionable (as explained in official documentation table objects are not):

SQL> SET lines 200
SQL> col object_name FOR a30
SQL> col edition_name FOR a10
SQL> SELECT object_name, object_type, editionable, edition_name FROM user_objects;
 
OBJECT_NAME                    OBJECT_TYPE             E EDITION_NA
------------------------------ ----------------------- - ----------
TEST01                         TABLE
TEST01_PK                      INDEX
TEST01_FIRST_LAST_NAME         TRIGGER                 Y
GREET                          PROCEDURE               Y
 
6 ROWS selected.

The currently existing edition:

SQL> col parent_edition_name FOR a20
SQL> SELECT * FROM all_editions;
 
EDITION_NA PARENT_EDITION_NAME  USA
---------- -------------------- ---
ORA$BASE                        YES

At any time to know your currnt edition:

SQL> col default_edition FOR a10
SQL> col session_edition FOR a10
SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') AS default_edition,
     SYS_CONTEXT('userenv', 'session_edition_name') AS session_edition
     FROM dual;
 
DEFAULT_ED SESSION_ED
---------- ----------
ORA$BASE   ORA$BASE

To simulate structure change on a table between your different edition you have to implement editioning views. To populate figures onto those tables from different (linked) editions you have to implement what is called forward crossedition trigger (from pre to post) and reverse crossedition triggers (from post to pre).

To create the view that ideally have the same name as the table you need to rename your table and create a view based on it:

SQL> ALTER TABLE test01 RENAME TO test01_tab;
 
TABLE altered.
 
SQL> CREATE OR REPLACE editioning VIEW test01 AS
     SELECT id, first_name, last_name FROM test01_tab;
 
VIEW created.
 
SQL> DROP TRIGGER test01_first_last_name;
 
TRIGGER dropped.
 
SQL> CREATE OR REPLACE TRIGGER test01_first_last_name
     before INSERT OR UPDATE ON test01
     FOR each ROW
     BEGIN
       :NEW.first_name:=INITCAP(:NEW.first_name);
       :NEW.last_name:=INITCAP(:NEW.last_name);
     END;
 
TRIGGER created.
 
SQL> ALTER PROCEDURE greet compile;
 
PROCEDURE altered.

Then I add a new column to test01_tab table. This could be done is a fully online fashion using DBMS_REDEFINITION:

SQL> ALTER TABLE test01_tab ADD full_name VARCHAR2(40) NOT NULL;
ALTER TABLE test01_tab ADD full_name VARCHAR2(40) NOT NULL
            *
ERROR AT line 1:
ORA-01758: TABLE must be empty TO ADD mandatory (NOT NULL) COLUMN
 
SQL> ALTER TABLE test01_tab ADD full_name VARCHAR2(40);
 
TABLE altered.
 
SQL> UPDATE test01_tab SET full_name=first_name||' '||last_name WHERE full_name IS NULL;
 
1 ROW updated.
 
SQL> COMMIT;
 
COMMIT complete.

And all this just to be in a better initial shape. Notice the view that is editionable:

SQL> SELECT object_name, object_type, editionable, edition_name FROM user_objects;
 
OBJECT_NAME                    OBJECT_TYPE             E EDITION_NA
------------------------------ ----------------------- - ----------
TEST01_PK                      INDEX
TEST01_FIRST_LAST_NAME         TRIGGER                 Y ORA$BASE
GREET                          PROCEDURE               Y ORA$BASE
TEST01_TAB                     TABLE
TEST01                         VIEW                    Y ORA$BASE

Creating the new edition and new edition objects

If we acknowledge that our curernt release is v1 then let’s create a release v2 edition and switch to it:

SQL> CREATE edition releasev2 AS child OF ora$base;
 
Edition created.
 
SQL> ALTER SESSION SET edition=releasev2;
 
SESSION altered.

In this new edition I update all my applicative objects to match the new full_name column:

SQL> CREATE OR REPLACE editioning VIEW test01 AS
     SELECT id, first_name, last_name, full_name FROM test01_tab;
 
VIEW created.
 
SQL> CREATE OR REPLACE PROCEDURE greet(pid NUMBER)
     IS
       full_name VARCHAR2(40);
     BEGIN
       SELECT full_name INTO full_name FROM test01 WHERE id=pid;
       dbms_output.put_line('Hello ' || full_name);
     END;
     /
 
PROCEDURE created.
 
SQL> CREATE OR REPLACE TRIGGER test01_first_last_name
     before INSERT OR UPDATE ON test01
     FOR each ROW
     BEGIN
       :NEW.first_name:=INITCAP(:NEW.first_name);
       :NEW.last_name:=INITCAP(:NEW.last_name);
       :NEW.full_name:=:NEW.first_name||' '||:NEW.last_name;
     END;
     /
 
TRIGGER created.

Let’s create the corssedition triggers. In my case I only need a forward crossedtion trigger to update the full_name column in case something is inserted in release v1 of my application (ora$base edition). Those crossed edition triggers must be created in releasev2 edition. My reverse crossedition trigger is empty because I have no business logic to handle in my test case. I have simply added it for reference of the syntax:

SQL> CREATE OR REPLACE TRIGGER test01_forward
     before INSERT OR UPDATE ON test01_tab
     FOR each ROW
     forward crossedition
     BEGIN
       :NEW.full_name:=INITCAP(:NEW.first_name)||' '||INITCAP(:NEW.last_name);
     END;
     /
 
TRIGGER created.
 
SQL> CREATE OR REPLACE TRIGGER test01_reverse
     before INSERT OR UPDATE ON test01_tab
     FOR each ROW
     REVERSE crossedition
     BEGIN
       NULL;
     END;
     /
 
TRIGGER created.

Testing the new edition and crossedition triggers

So now I have:

SQL> SELECT object_name, object_type, editionable, edition_name FROM user_objects_ae ORDER BY object_name, edition_name;
 
OBJECT_NAME                    OBJECT_TYPE             E EDITION_NA
------------------------------ ----------------------- - ----------
GREET                          PROCEDURE               Y ORA$BASE
GREET                          PROCEDURE               Y RELEASEV2
TEST01                         VIEW                    Y ORA$BASE
TEST01                         VIEW                    Y RELEASEV2
TEST01_FIRST_LAST_NAME         TRIGGER                 Y ORA$BASE
TEST01_FIRST_LAST_NAME         TRIGGER                 Y RELEASEV2
TEST01_FORWARD                 TRIGGER                 Y RELEASEV2
TEST01_PK                      INDEX
TEST01_REVERSE                 TRIGGER                 Y RELEASEV2
TEST01_TAB                     TABLE
 
10 ROWS selected.

If I add a row in ora$base release:

SQL> ALTER SESSION SET edition=ora$base;
 
SESSION altered.
 
SQL> SELECT * FROM test01 ORDER BY id;
 
        ID FIRST_NAME           LAST_NAME
---------- -------------------- --------------------
         1 Yannick              Jaquier
         2 Larry                Ellison
 
SQL> INSERT INTO test01(id,first_name,last_name) VALUES(3,'safra','katz');
 
1 ROW created.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> SELECT * FROM test01 ORDER BY id;
 
        ID FIRST_NAME           LAST_NAME
---------- -------------------- --------------------
         1 Yannick              Jaquier
         2 Larry                Ellison
         3 Safra                Katz
 
SQL> ALTER SESSION SET edition=releasev2;
 
SESSION altered.
 
SQL> SELECT * FROM test01 ORDER BY id;
 
        ID FIRST_NAME           LAST_NAME            FULL_NAME
---------- -------------------- -------------------- ----------------------------------------
         1 Yannick              Jaquier              Yannick Jaquier
         2 Larry                Ellison              Larry Ellison
         3 Safra                Katz                 Safra Katz

Using service to point to desired edition

The default database edition is ora$base. If I connect with my account:

SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM dual;
 
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------------------------------------------
ORA$BASE
 
SQL> DESC test01;
 Name                                      NULL?    TYPE
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 FIRST_NAME                                NOT NULL VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(20)

And change the default database edition:

SQL> ALTER DATABASE DEFAULT edition=releasev2;
 
DATABASE altered.
 
SQL> SELECT property_value FROM database_properties WHERE property_name = 'DEFAULT_EDITION';
 
PROPERTY_VALUE
--------------------------------------------------------------------------------
RELEASEV2

If I reconnect with my account I directly land in releasev2 edition:

SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM dual;
 
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------------------------------------------
RELEASEV2
 
SQL> DESC test01;
 Name                                      NULL?    TYPE
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 FIRST_NAME                                NOT NULL VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(20)
 FULL_NAME                                          VARCHAR2(40)
 
SQL> ALTER DATABASE DEFAULT edition=ora$base;
 
DATABASE altered.

Even if changing the database default edition is usable when everything is done in real life it is not that practical. You might want to go step by step and have a reduced bunch of users accessing the new version of your application while most part of your users is still using the previous release. Nowadays with containerized applications and the easiness to spin up a web server it would be really easy to have most of your user accessing the old legacy url, for example https://wwww.myapplication.com while some beta tester users would access something like https://next.myapplication.com. This can be achieved from Oracle database standpoint with the help of services where you can specify the target edition.

Your database will be split in different edition and will server different version of your application. In my example I have two but you are obviously not limited to two:

ebr01
ebr01

I create two services each of them targeting one of my two editions:

SQL> EXEC dbms_service.create_service(service_name => 'pdb1_v1', network_name => 'pdb1_v1', edition => 'ora$base');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC dbms_service.create_service(service_name => 'pdb1_v2', network_name => 'pdb1_v2', edition => 'releasev2');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC dbms_service.start_service(service_name => 'pdb1_v1');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC dbms_service.start_service(service_name => 'pdb1_v2');
 
PL/SQL PROCEDURE successfully completed.

Then when using them with EZConnect method to avoid creating entries in tnsnames.ora file I land directly in the desired edition (ora$base being the default edition of the database):

SQL> CONNECT yjaquier/secure_password@//server01.domain.com:1541/pdb1_v1
Connected.
SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM dual;
 
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------------------------------------------
ORA$BASE
 
SQL> CONNECT yjaquier/secure_password@//server01.domain.com:1541/pdb1_v2
Connected.
SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM dual;
 
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------------------------------------------
RELEASEV2

To complete the process you can make releasev2 the default database edition and drop ora$base edition with something like:

SQL> DROP edition ora$base CASCADE;
 
Edition dropped.

DBMS_REDEFINITION for online table redefinition

The small test case is to add a full_name column to my test01 test table. Is the operation possible with DBMS_REDEFINITION:

SQL> SET serveroutput ON SIZE 999999
SQL> EXEC dbms_redefinition.can_redef_table(uname=>'yjaquier', tname=>'test01');
 
PL/SQL PROCEDURE successfully completed.

I create the target table with obviously a different name:

SQL> CREATE TABLE test01_int (
  id INTEGER,
  first_name VARCHAR2(20) NOT NULL,
  last_name VARCHAR2(20) NOT NULL,
  full_name VARCHAR2(40) NOT NULL
);
 
TABLE created.

I start the redefinition, full_name will be a concatenation, with a space in the middle, of first_name and last_name:

SQL> EXEC dbms_redefinition.start_redef_table(uname=>'yjaquier', orig_table=>'test01', int_table=>'test01_int', col_mapping=>'id id,first_name first_name,last_name last_name,first_name||'' ''||last_name full_name', refresh_dep_mviews=>'Y', enable_rollback=>TRUE);
 
PL/SQL PROCEDURE successfully completed.

Let’s insert a row to test:

SQL> INSERT INTO test01 VALUES(2,'larry','ellison');
 
1 ROW created.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> SET lines 200
SQL> SELECT * FROM test01;
 
        ID FIRST_NAME           LAST_NAME
---------- -------------------- --------------------
         1 Yannick              Jaquier
         2 Larry                Ellison
 
SQL> SELECT * FROM test01_int;
 
        ID FIRST_NAME           LAST_NAME            FULL_NAME
---------- -------------------- -------------------- ----------------------------------------
         1 Yannick              Jaquier              Yannick Jaquier

Newly added row can be synchronize using:

SQL> EXEC dbms_redefinition.sync_interim_table(uname=>'yjaquier', orig_table=>'test01', int_table=>'test01_int');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT * FROM test01_int;
 
        ID FIRST_NAME           LAST_NAME            FULL_NAME
---------- -------------------- -------------------- ----------------------------------------
         1 Yannick              Jaquier              Yannick Jaquier
         2 Larry                Ellison              Larry Ellison

Complete the redefinition process with:

SQL>
DECLARE
  errors PLS_INTEGER;
BEGIN
  dbms_redefinition.copy_table_dependents(
    uname             => 'yjaquier',
    orig_table        => 'test01',
    int_table         => 'test01_int',
    copy_indexes      => dbms_redefinition.cons_orig_params, -- change from non-default to cpy indexes
    copy_constraints  => FALSE,
    num_errors        => errors); 
  dbms_output.put_line('Number of errors that occurred while cloning dependent objects: ' || errors);
END;
/
 
SQL> -- Sync last figures
SQL> EXEC dbms_redefinition.sync_interim_table(uname=>'yjaquier', orig_table=>'test01', int_table=>'test01_int');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> -- Complete redefinition
SQL> EXEC dbms_redefinition.finish_redef_table(uname=>'yjaquier', orig_table=>'test01', int_table=>'test01_int', disable_rollback=>TRUE);
 
PL/SQL PROCEDURE successfully completed.
 
SQL> DESC test01;
 Name                                                                                                              NULL?    TYPE
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ID                                                                                                                         NUMBER(38)
 FIRST_NAME                                                                                                        NOT NULL VARCHAR2(20)
 LAST_NAME                                                                                                         NOT NULL VARCHAR2(20)
 FULL_NAME                                                                                                         NOT NULL VARCHAR2(40)
 
SQL> SELECT * FROM test01;
 
        ID FIRST_NAME           LAST_NAME            FULL_NAME
---------- -------------------- -------------------- ----------------------------------------
         1 Yannick              Jaquier              Yannick Jaquier
         2 Larry                Ellison              Larry Ellison

Without copy_constraints => false I got:

ERROR AT line 1:
ORA-01442: COLUMN TO be modified TO NOT NULL IS already NOT NULL
ORA-06512: AT "SYS.DBMS_REDEFINITION", line 2615
ORA-06512: AT "SYS.DBMS_REDEFINITION", line 2615
ORA-06512: AT "SYS.DBMS_REDEFINITION", line 2053
ORA-06512: AT "SYS.DBMS_REDEFINITION", line 1914
ORA-06512: AT "SYS.DBMS_REDEFINITION", line 457
ORA-06512: AT "SYS.DBMS_REDEFINITION", line 1907
ORA-06512: AT "SYS.DBMS_REDEFINITION", line 2594
ORA-06512: AT "SYS.DBMS_REDEFINITION", line 3811
ORA-06512: AT "SYS.DBMS_REDEFINITION", line 6556
ORA-06512: AT line 4

References

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

Share the knowledge!

Related Posts:

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>