Oracle Edition-Based Redefinition (EBR) Lab

What is EBR?

Edition-Based Redefinition (EBR) is an Oracle Database feature that allows multiple versions of application code to exist simultaneously.

This enables:

Online upgrades
Near zero downtime deployments
Rolling application changes
Safe PL/SQL migrations

Very common in:

Oracle Fusion ERP
Oracle E-Business Suite Online Patching
SaaS applications

Core Concept

Same Data
Different Code
No Downtime

Tables are shared.
PL/SQL logic is versioned.

Architecture Overview

                 +----------------+
                 |   APP USERS    |
                 +--------+-------+
                          |
                    CURRENT EDITION
                          |
         -----------------------------------
         |                                 |
+--------v--------+             +----------v--------+
| EDITION V1      |             | EDITION V2        |
| pkg_orders old  |             | pkg_orders new    |
| view old        |             | view new          |
+-----------------+             +-------------------+

Editionable Objects

Supported

PACKAGE
PACKAGE BODY
PROCEDURE
FUNCTION
VIEW
SYNONYM
TRIGGER
TYPE

Not Editionable

TABLE
SEQUENCE

Because of this Oracle created:

Editioning Views
Crossedition Triggers

Requirements

Oracle Database 19c or newer
DBA privileges

Recommended:
- Dedicated PDB

Step 0 - Validate Database Environment

Before creating the EBR user, validate the database version, current container, database properties, and important parameters.

Validate Database Version

SELECT banner_full
FROM v$version;

Validate Current Container

SHOW CON_NAME;

SELECT
    con_id,
    name,
    open_mode,
    restricted
FROM v$pdbs
ORDER BY con_id;

Validate Database Properties

SELECT *
FROM database_properties
ORDER BY property_name;

Filtered Database Properties

SELECT
    property_name,
    property_value
FROM database_properties
WHERE property_name LIKE '%EDITION%'
   OR property_name LIKE '%DEFAULT%'
   OR property_name LIKE '%PDB%'
ORDER BY property_name;

Validate Important Parameters

SELECT
    name,
    value,
    isdefault,
    isses_modifiable,
    issys_modifiable
FROM v$parameter
WHERE name IN (
    'compatible',
    'enable_ddl_logging',
    'plsql_code_type',
    'plsql_optimize_level'
)
ORDER BY name;

Validate Current Edition

SELECT SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME') AS current_edition
FROM dual;

Expected:

ORA$BASE

Validate Existing Editions

SELECT
    edition_name,
    parent_edition_name,
    usable
FROM dba_editions
ORDER BY edition_name;

Step 1 - Create EBR User

CREATE USER ebr IDENTIFIED BY ebr;

GRANT CONNECT, RESOURCE TO ebr;

GRANT CREATE SESSION TO ebr;
GRANT CREATE TABLE TO ebr;
GRANT CREATE VIEW TO ebr;
GRANT CREATE PROCEDURE TO ebr;
GRANT CREATE SEQUENCE TO ebr;
GRANT CREATE SYNONYM TO ebr;

GRANT CREATE ANY EDITION TO ebr;
GRANT DROP ANY EDITION TO ebr;

ALTER USER ebr ENABLE EDITIONS;

Verify User

SELECT
    username,
    editions_enabled,
    account_status,
    default_tablespace,
    temporary_tablespace
FROM dba_users
WHERE username='EBR';

Expected:

EDITIONS_ENABLED = Y

Step 2 - Connect as EBR

CONN ebr/ebr

Verify Current Schema and Edition

SELECT
    SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AS current_schema,
    SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME') AS current_edition
FROM dual;

Step 3 - Create Base Table

CREATE TABLE customers (
    customer_id NUMBER,
    name        VARCHAR2(100)
);

Insert Data

INSERT INTO customers VALUES (1,'JOHN');

COMMIT;

Validate Table Data

SELECT *
FROM customers;

Validate Object Status

SELECT
    object_name,
    object_type,
    status,
    edition_name
FROM user_objects_ae
WHERE object_name = 'CUSTOMERS';

Step 4 - Create Initial Package

Package Specification

CREATE OR REPLACE PACKAGE pkg_customers AS

    PROCEDURE show_customer(p_id NUMBER);

END;
/

Package Body

CREATE OR REPLACE PACKAGE BODY pkg_customers AS

PROCEDURE show_customer(p_id NUMBER) IS

    v_name VARCHAR2(100);

BEGIN

    SELECT name
    INTO v_name
    FROM customers
    WHERE customer_id = p_id;

    DBMS_OUTPUT.PUT_LINE('V1 CUSTOMER='||v_name);

END;

END;
/

Validate Package Objects

SELECT
    object_name,
    object_type,
    status,
    edition_name
FROM user_objects_ae
WHERE object_name = 'PKG_CUSTOMERS'
ORDER BY object_type;

Step 5 - Test Initial Version

SET SERVEROUTPUT ON

EXEC pkg_customers.show_customer(1);

Expected output:

V1 CUSTOMER=JOHN

Step 6 - View Existing Editions

SELECT *
FROM dba_editions;

Better Validation Query

SELECT
    edition_name,
    parent_edition_name,
    usable
FROM dba_editions
ORDER BY edition_name;

Step 7 - Create New Edition

CREATE EDITION version2 AS CHILD OF ora$base;

Validate Edition

SELECT
    edition_name,
    parent_edition_name,
    usable
FROM dba_editions
WHERE edition_name = 'VERSION2';

Step 8 - Switch Session to New Edition

ALTER SESSION SET EDITION = version2;

Verify Edition

SELECT SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
FROM dual;

Expected:

VERSION2

Step 9 - Modify Package ONLY in Version2

CREATE OR REPLACE PACKAGE BODY pkg_customers AS

PROCEDURE show_customer(p_id NUMBER) IS

    v_name VARCHAR2(100);

BEGIN

    SELECT name
    INTO v_name
    FROM customers
    WHERE customer_id = p_id;

    DBMS_OUTPUT.PUT_LINE('V2 CUSTOMER='||v_name);

END;

END;
/

Validate Package Status

SELECT
    object_name,
    object_type,
    status,
    edition_name
FROM user_objects_ae
WHERE object_name = 'PKG_CUSTOMERS'
ORDER BY object_type,
         edition_name;

Step 10 - Validate Isolation

Session 1

ALTER SESSION SET EDITION = ora$base;

EXEC pkg_customers.show_customer(1);

Expected:

V1 CUSTOMER=JOHN

Session 2

ALTER SESSION SET EDITION = version2;

EXEC pkg_customers.show_customer(1);

Expected:

V2 CUSTOMER=JOHN

Important Observation

Both editions use the same physical table:

customers

But execute different code versions.

Editioning Views

Why Editioning Views Exist

Tables are not editionable.

Oracle uses Editioning Views to abstract table structure changes.

Step 11 - Create New Table Version

CREATE TABLE customers2 (

    customer_id NUMBER,
    name        VARCHAR2(100),
    email       VARCHAR2(100)

);

Insert Sample Data

INSERT INTO customers2
VALUES (1,'JOHN','[email protected]');

COMMIT;

Validate Data

SELECT *
FROM customers2;

Step 12 - Create Editioning View

CREATE EDITIONING VIEW ev_customers AS

SELECT
    customer_id,
    name
FROM customers2;

Validate Editioning View

SELECT
    view_name,
    editioning_view
FROM user_views
WHERE view_name = 'EV_CUSTOMERS';

Crossedition Triggers

Purpose

Synchronize old column model
Synchronize new column model

Very common in:

Oracle Fusion
Oracle EBS Online Patching

Validate Crossedition Triggers

SELECT
    trigger_name,
    trigger_type,
    triggering_event,
    table_name,
    status,
    crossedition
FROM user_triggers
ORDER BY trigger_name;

Useful Queries

Current Edition

SELECT SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
FROM dual;

Editioned Objects

SELECT
    object_name,
    object_type,
    edition_name,
    status
FROM user_objects_ae
ORDER BY object_name,
         edition_name;

All Editions

SELECT
    edition_name,
    parent_edition_name,
    usable
FROM dba_editions
ORDER BY edition_name;

GoldenGate + EBR Considerations

GoldenGate replicates:

Physical tables

GoldenGate does NOT automatically understand:

Edition context
Editioning logic

Common Issues

DDL out of order
Wrong edition execution
Editioning view mismatches
Invalid synonyms
ORA-38802
ORA-38818
ORA-42314

Useful GoldenGate Queries

SELECT
    owner,
    object_name,
    object_type,
    edition_name,
    status
FROM dba_objects_ae
WHERE owner = 'EBR'
ORDER BY object_name,
         edition_name;

Mental Model

Tables   = Shared
Code     = Versioned
Views    = Compatibility Layer
Triggers = Migration Bridge

Oracle Documentation

https://docs.oracle.com/en/database/oracle/oracle-database/23/adfns/editions.html

https://docs.oracle.com/en/database/oracle/oracle-database/23/adfns/edition-based-redefinition.html

https://docs.oracle.com/cd/E26401_01/doc.122/e22954/T202991T531065.htm