Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Thursday, June 04, 2015

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. 

2 comments:

Vertie Martin said...

I like the helpful info you provide in your articles. I'll bookmark your weblog and check again here frequently. I'm write my essay reviews quite sure I'll learn lots of new stuff right here! Best of luck for the next!

Prwatech said...

Very excellent post!!! Thank you so much for your great content. Keep posting.....

Hadoop Training in bangalore
Hadoop Training Institute In Bangalore