12c: Zero App downtime using EBR

EBR, Edition-based redefinition allows multiple versions of PL/SQL objects, views and synonyms in a single schema, which makes it possible to upgrade the database component of an application while it is in use. An edition (nonschema object) is effectively a version label that can be assigned to all editionable objects in a schema. In Oracle 11gR2, if editioning was enabled for a user and you created a new object that was an editionable object in that user's schema, the object you created was automatically editionable. It was not possible to create non-editionable objects of one of these types in an edition enabled schema.

Oracle 12c allows you to create non-editionable objects of these editionable types in an edition enabled schema. Please remember EBR may require considerable design investment to work well. EBS Suite 12.2 used EBR to drastically reduce planned outages.

How it works:

To upgrade an application while it is in use, you copy the database objects that comprise the application and redefine the copied objects in isolation. Your changes do not affect users of the application, they continue to run the unchanged application. When you are sure that your changes are correct, you make the upgraded application available to all users.

Editionable and Noneditionable Object Types

Before a schema object type can be editionable in a schema, it must be editionable in the database. The schema object types that are editionable in the database are determined by the value of the COMPATIBLE initialization parameter and are shown by the dynamic performance view V$EDITIONABLE_TYPES

C:\Users\ibukhary>sqlplus sys@pdb2 as sysdba

select * from V$EDITIONABLE_TYPES

EDITIONABLE_TYPE	TYPE#	CON_ID
VIEW	4	0
SYNONYM	5	0
PROCEDURE	7	0
FUNCTION	8	0
PACKAGE	9	0
PACKAGE BODY	11	0
TRIGGER	12	0
TYPE	13	0
TYPE BODY	14	0
LIBRARY	22	0
SQL TRANSLATION PROFILE	114	0




You cannot enable editions for Oracle-supplied users, Common users in a CDB.

Example:

1- Create the edition as the child of an existing edition (ora$base bydefault).An edition can have at most one child.
SQL>  CREATE EDITION ED1;


Edition created.

When you create an edition, all editioned objects in its parent edition are copied to it. Changes to an editioned object in one edition do not affect copies of that editioned object in other editions.

2- Enable editions for a user

SQL> alter user rd enable editions;


User altered.

3- Grant the USE privilege on the edition to other user. Each database session uses exactly one edition at a time. The edition that a database session is using at any one time is called its current edition.
SQL> GRANT USE ON EDITION ED1 to RD;


Grant succeeded.

You could make the edition the database default edition
ALTER DATABASE DEFAULT EDITION = ED1

4- Connect with your test user ie;RD in my case, When you connect to the database, you can specify your initial session edition. Your initial session edition can be the database default edition or any edition on which you have the USE privilege.

SQL> SELECT EDITION_NAME FROM ALL_EDITIONS;

EDITION_NAME

--------------------------------------------

ORA$BASE

ED1

Create Test procedure, observe the properties editionable and noneditionable. This procedure is an editioned object in ora$base (default edition)

c:\app\ibukhary\product\11.2.0\dbhome_1\BIN>sqlplus rd/rd@pdb2

SQL> ALTER SESSION SET EDITION = ora$base;


Session altered.

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE EDITIONABLE PROCEDURE proc1 AS
  2  BEGIN
  3    DBMS_OUTPUT.PUT_LINE('Proc1 Edition-1');
  4* END;
  5  /


Procedure created.

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE NONEDITIONABLE PROCEDURE proc2 AS
  2  BEGIN
  3    DBMS_OUTPUT.PUT_LINE('Proc2 Non-Edition');
  4* END;
SQL> /


Procedure created.

-- Check the status of objects
SQL> COLUMN object_name FORMAT A20
SQL> COLUMN editionable FORMAT A15
SQL> SELECT object_name, editionable FROM   user_objects
  2  ORDER BY object_name;

OBJECT_NAME          EDITIONABLE
--------------------          ---------------
BINVAL_TEST
PROC1                      Y
PROC2                      N
TESTREDAC
TEST_REDAC
TSDP_TEST


6 rows selected.

Invoke procedure, observe the output.
SQL> set serveroutput on
SQL> exec proc1
Proc1 Edition-1

PL/SQL procedure successfully completed.

5- Alter session to your child edition (eg; ED1)  and change the procedure

SQL> ALTER SESSION SET EDITION=ED1;

Session altered.

SQL> ed

Wrote file afiedt.buf

  1  CREATE OR REPLACE EDITIONABLE PROCEDURE proc1 AS

  2  BEGIN

  3    DBMS_OUTPUT.PUT_LINE('Proc1 Edition-2');

  4* END;

  5  /

Procedure created.

Oracle Database will actualize the procedure in the child edition and the change will affects only the actual object in the child edition, not the procedure in the parent edition.

Invoke procedure again; Observe the output

SQL> set serveroutput on

SQL> exec proc1

Proc1 Edition-2

PL/SQL procedure successfully completed.

Conceptually, the child edition invokes its own copy of the procedure (which is identical to the procedure in the parent edition,ora$base). However, the child edition actually invokes the procedure in the parent edition.

6- Return to the parent edition , invoke the procedure and observe the output.

SQL> ALTER SESSION SET EDITION = ora$base;

Session altered.

SQL>  set serveroutput on
SQL>  exec proc1
Proc1 Edition-1

PL/SQL procedure successfully completed.


SQL>


7- Make new edition available to all users (requires system privileges)
C:\Users\ibukhary>sqlplus sys@pdb2 as sysdba
SQL> ALTER DATABASE DEFAULT EDITION = ED1;


Database altered.

Please note that a service definition may override the database default edition.
BEGIN
   DBMS_SERVICE.modify_service(
   service_name => 'mysrv',
   edition => 'ED1',
   modify_edition => TRUE
);
END;


8- Retire old edition (requires system privileges), List grantees first

SELECT * FROM DBA_TAB_PRIVS

WHERE TABLE_NAME = UPPER('ora$base')

GRANTEE	OWNER	TABLE_NAME	GRANTOR	PRIVILEGE	GRANTABLE	HIERARCHY	COMMON	TYPE
SYSTEM	SYS	ORA$BASE	SYS	USE	YES	NO	NO	EDITION

Revoke use on old edition from all grantees
REVOKE USE ON EDITION ora$base FROM PUBLIC;

9- (Optional) Display information about editions

View Description
*_EDITIONS Describes every edition in the database.
*_EDITION_COMMENTS Shows the comments associated with every edition in the database.
*_EDITIONED_TYPES Lists the schema object types that are editioned by default in each
                                                schema.
*_OBJECTS Describes every object in the database that is visible in the current
                                                edition. For each object, this view shows whether it is editionable.
*_OBJECTS_AE Describes every object in the database, in every edition. For each
                                                object, this view shows whether it is editionable.
*_ERRORS Describes every error in the database in the current edition.
*_ERRORS_AE Describes every error in the database, in every edition.
*_USERS Describes every user in the database. Useful for showing which users
                                                have editions enabled.
*_SERVICES Describes every service in the database. The EDITIONS column shows                                                 the default initial current edition.
*_MVIEWS Describes every materialized view. If the materialized view refers to
                                                editioned objects, then this view shows the evaluation edition and the
                                                range of editions where the materialized view is eligible for query
                                                rewrite.
*_TAB_COLS Describes every column of every table, view, and cluster. For each
                                                virtual column, this view shows the evaluation edition and the usable
                                                range.

Notes:

• If only PL/SQL is changing from edition to edition, Editioning views are not required as in my example above. So implementing it is much easy and does not require much prior planning.
• If Tables might have columns definitions added, modified, dropped between edition then Editioning views are required to make each edition's users see only relevant data. EBR Editioning views (referenced by application rather than tables) represent data from an underlying table as unmodified and unfiltered and it can't use order by, group by, distinct, funtions, joins , operaters etc. 
• If Editioning views are created , Cross-Edition triggers may be needed to keep data synchronized. They are temporary and propagate changes between editions ie; changes in parent propagated to child called as Forward Cross-Edition and changes in child propagated to parent called as Reverse Cross-Edition.