Category: EBR

Pre-19c Crossedition Triggers

As shown in the previous post, finding all the crossedition triggers in the database is easy using DBA_TRIGGERS_AE, but this can be done only as of Oracle 19c – the release in which this data dictionary view was added.

Here are two options that can be used before 19c.

A Proper Way

We can use DBA_OBJECTS_AE to find all the triggers across all editions, and then for each one to “jump” to its edition using dbms_sql and check whether it is a crosseedition trigger or not using DBA_TRIGGERS. Continue reading “Pre-19c Crossedition Triggers”

USER_TRIGGERS_AE

One of the fundamental building blocks of EBR (Edition Based Redefinition) is Crossedition Triggers.

This special type of triggers is used when we change the structure of tables in an online application upgrade, and the data of these tables may be changed while we do the upgrade and afterwards, until no session uses the pre-upgrade edition. These triggers keep the pre-upgrade representation of the data and post-upgrade representation of the data in sync.

Crossedition triggers are temporary in nature – they should be dropped when all the clients use the edition in which the triggers were created (or one of its descendent editions).

In theory, crossedition triggers live for a very short period.

In real life, when we have multiple active editions and different types of clients that may keep using older editions for a while before upgrading to the latest edition, it may take days and even weeks before we can drop the crossedition triggers. In my opinion, this is perfectly fine, and actually one of the benefits that I see in EBR.

Another significant difference between crossedition triggers and regular triggers is that crossedition triggers are not inherited by descendent editions. However, they may be triggered by DML operations that are executed in a descendent edition.

This means that we need a way to check which crossedition triggers exist in our schema/database.
Continue reading “USER_TRIGGERS_AE”

EBR – Part 12: Editions and Services

This is part 12 of a post series about Oracle Edition-Based Redefinition.

Visit the index page for all the parts of the series

Introduction

In the previous part of this series I wrote about using the Database Default Edition as a way for exposing new editions when using EBR for online application upgrades. As I wrote there, this is a simple method, but in my opinion it is also an impractical method in most cases. From my experience, the best way to expose new editions is by using services. This gives us high levels of both flexibility and control.

When we create or modify an Oracle service, we can specify the session edition for subsequent database connections using this service.
If our new edition includes changes that require code changes in the client-side (for example, when we make API changes), then we’ll expose the new edition by using a new service.
If the new edition includes only changes that are transparent to the client-side (for example, changes in package bodies only), then we don’t have to create a new service. Instead, we can modify the latest service to use the new edition from now on.

Examples

Let’s see some examples. Continue reading “EBR – Part 12: Editions and Services”

EBR – Part 11: Database-Level Default Edition

This is part 11 of a post series about Oracle Edition-Based Redefinition.

Visit the index page for all the parts of the series

Introduction

As we’ve seen in the previous posts, the process of an online application upgrade, in high level, includes the following steps:

  • Creating a new edition
  • Applying all the necessary changes in the privacy of the new, unexposed, edition
  • Making sure that all the objects in the new edition are valid, and that all the objects that should be actual in the new edition are indeed actual
  • Exposing the new edition to the clients

How do we expose the new edition?

In the previous examples I did it by explicitly changing the client’s session edition, from the client session itself. But that was just for demo purposes, and it is certainly not the recommended way.
The best way to expose new editions, in my opinion, is by using services. This gives us high levels of both flexibility and control.
I’ll dedicate the next post for exposing new editions via services.
In this post I’ll show another option – Database Default Edition. This method is very simple, but also very limiting, and therefore it’s suitable only for very specific cases.

Database Default Edition

There is always one database-level default edition. We can see it using this query: Continue reading “EBR – Part 11: Database-Level Default Edition”

Dropping Virtual Columns Causes Unnecessary Invalidation

When you drop a column from a table (or set a column unused), any view that references this column becomes invalid.
As of Oracle 11gR1, where Fine Grained Dependency Tracking was introduced, views that reference other columns of the table, but do not reference the dropped column, should not become invalid. And this is usually true, but this week I discovered a case where it’s not.

When dropping a virtual column, even views that do not reference the dropped column become invalid.

Let’s see an example (tested in 11g, 12c and 18c):

We create a table with 3 columns, one of them is virtual

ORA$BASE> create table t (
  2    column_in_view             number,
  3    regular_column_not_in_view number,
  4    virtual_column_not_in_view number as (42) virtual
  5  );

Table created.

And we create a view that projects only one of the table columns

ORA$BASE> create or replace view v as
  2    select column_in_view from t;

View created.

The view is valid at this point

ORA$BASE> select status from user_objects where object_name = 'V';

STATUS
-------
VALID

Dropping the non-referenced non-virtual column does not invalidate the view (which is a good thing, of course)

ORA$BASE> alter table t drop column regular_column_not_in_view;

Table altered.

ORA$BASE> select status from user_objects where object_name = 'V';

STATUS
-------
VALID

However, dropping the non-referenced virtual column does invalidate the view

ORA$BASE> alter table t drop column virtual_column_not_in_view;

Table altered.

ORA$BASE> select status from user_objects where object_name = 'V';

STATUS
-------
INVALID

And we can see the same behavior when setting the columns unused instead of dropping them:

ORA$BASE> drop table t;

Table dropped.

ORA$BASE> create table t (
  2    column_in_view             number,
  3    regular_column_not_in_view number,
  4    virtual_column_not_in_view number as (42) virtual
  5  );

Table created.

ORA$BASE> create or replace view v as
  2    select column_in_view from t;

View created.

ORA$BASE> select status from user_objects where object_name = 'V';

STATUS
-------
VALID

ORA$BASE> alter table t set unused column regular_column_not_in_view;

Table altered.

ORA$BASE> select status from user_objects where object_name = 'V';

STATUS
-------
VALID

ORA$BASE> alter table t set unused column virtual_column_not_in_view;

Table altered.

ORA$BASE> select status from user_objects where object_name = 'V';

STATUS
-------
INVALID

ORA$BASE>

When using EBR (Edition-Based Redefinition) for online application upgrades, Fine Grained Dependency Tracking is very important. The common practice for dropping a column with no downtime is as follows:

  • In a new edition: take the column out of the editioning view that covers the table (and remove any references to it from the code)
  • Expose the new edition
  • As soon as all the sessions use the new edition: drop the column from the table (or set it unused)

This practice fails when the dropped column is a virtual one, due to the behavior we’ve seen above (which, in my opinion, is a bug). Until this bug is solved, I see two workarounds (which I don’t really like):

  • Just leave the virtual column there, unexposed to the editioning view
  • Create a new table (without that column) to replace the existing one (with the necessary migration work, including cross-edition triggers)

I’ll write more about this, and about other invalidation cases, in my EBR series.

EBR – Part 10: Data Dictionary Views for Editioning Views

This is part 10 of a post series about Oracle Edition-Based Redefinition.

Visit the index page for all the parts of the series

Introduction

In a previous post I wrote about Editioning Views and their significant role in online application upgrades using EBR.
In this post we’ll see how editioning views are represented in the data dictionary views.

I’ll discuss only the USER_* views, but everything is true for the corresponding ALL_*, DBA_* and CDB_* views as well

Setup

At this point, after completing three upgrades already (in part 3, part 5 and part 9), our latest edition is V3, and we have the PEOPLE$0 table and two actual instances of the PEOPLE editioning view (one in V2 and one in V3).

Let’s create a new edition – V4:

-- connected as system
ORA$BASE> create edition v4;

Edition created.

ORA$BASE> grant use on edition v4 to demo_ebr;

Grant succeeded.

In the new edition, let’s change the PEOPLE editioning view and give different aliases to some of the columns:

ORA$BASE> conn demo_ebr
Enter password:
Connected.
ORA$BASE> @set_edition v4

Session altered.

V4> create or replace editioning view people as
  2  select id,
  3         first_name   as given_name,
  4         last_name    as family_name,
  5         phone_number,
  6         eye_color
  7  from   people$0;

View created.

And let’s create also a regular view on top of PEOPLE:

V4> create view brown_eyed_people as
  2  select *
  3  from   people
  4  where  eye_color = 'Brown';

View created.

Note that the BROWN_EYED_PEOPLE regular view references the PEOPLE editioning view. As described and emphasized, only editioning views reference tables; all the other editioned objects (including regular views) should reference editioning views (or other regular views) only.

USER_OBJECTS

In USER_OBJECTS and in USER_OBJECTS_AE both regular views and editioning views appear simply as views (the OBJECT_TYPE column contains the value “VIEW”). Editioning views have no distinctive indication here.

V4>  select object_type,
  2         object_name,
  3         edition_name
  4  from   user_objects
  5  where  object_type in ('TABLE', 'VIEW')
  6  order by 1,2;

OBJECT_TYPE  OBJECT_NAME                    EDITION_NAME
------------ ------------------------------ ------------
TABLE        PEOPLE$0
VIEW         BROWN_EYED_PEOPLE              V4
VIEW         PEOPLE                         V4

V4>  select object_type,
  2         object_name,
  3         edition_name
  4  from   user_objects_ae
  5  where  object_type in ('TABLE', 'VIEW')
  6  order by 1,2,3;

OBJECT_TYPE  OBJECT_NAME                    EDITION_NAME
------------ ------------------------------ ------------
TABLE        PEOPLE$0
VIEW         BROWN_EYED_PEOPLE              V4
VIEW         PEOPLE                         V2
VIEW         PEOPLE                         V3
VIEW         PEOPLE                         V4

USER_VIEWS

In USER_VIEWS and in USER_VIEWS_AE there is a column – EDITIONING_VIEW – that tells whether a view is an editioning one or not: 

V4>  select view_name,
  2         editioning_view
  3  from   user_views
  4  order by 1;

VIEW_NAME            EDITIONING_VIEW
-------------------- -----------------
BROWN_EYED_PEOPLE    N
PEOPLE               Y

V4>  select view_name,
  2         editioning_view,
  3         edition_name
  4  from   user_views_ae
  5  order by 1,3;

VIEW_NAME            EDITIONING_VIEW   EDITION_NAME
-------------------- ----------------- ------------
BROWN_EYED_PEOPLE    N                 V4
PEOPLE               Y                 V2
PEOPLE               Y                 V3
PEOPLE               Y                 V4

USER_EDITIONING_VIEWS

There is a dedicated data dictionary view – USER_EDITIONING_VIEWS – that contains only the editioning views.
For each editioning view we can see the view name and the name of the table covered by this view. Recall that (in a specific edition) an editioning view covers one and only one table, and a table may be covered by one editioning view at the most.

V4> select * from user_editioning_views;

VIEW_NAME       TABLE_NAME
--------------- ---------------
PEOPLE          PEOPLE$0

And there is a corresponding USER_EDITIONING_VIEWS_AE view:

V4> select * from user_editioning_views_ae;

VIEW_NAME       TABLE_NAME      EDITION_NAME
--------------- --------------- ------------
PEOPLE          PEOPLE$0        V2
PEOPLE          PEOPLE$0        V3
PEOPLE          PEOPLE$0        V4

USER_EDITIONING_VIEW_COLS

There is a dedicated data dictionary view – USER_EDITIONING_VIEW_COLS – that contains for every editioning view all its columns and the corresponding projected columns from the covered table.

V4> select *
  2  from user_editioning_view_cols
  3  order by 1,2;

VIEW_NAME       VIEW_COLUMN_ID VIEW_COLUMN_NAME     TABLE_COLUMN_ID TABLE_COLUMN_NAME
--------------- -------------- -------------------- --------------- --------------------
PEOPLE                       1 ID                                 1 ID
PEOPLE                       2 GIVEN_NAME                         2 FIRST_NAME
PEOPLE                       3 FAMILY_NAME                        3 LAST_NAME
PEOPLE                       4 PHONE_NUMBER                       4 PHONE_NUMBER
PEOPLE                       5 EYE_COLOR                          5 EYE_COLOR

There is also a corresponding USER_EDITIONING_VIEW_COLS_AE view. Let’s use it to see how the PEOPLE editioning view has evolved in the last 3 editions:

V4> select v2,v3,v4
  2  from   (select *
  3          from   user_editioning_view_cols_ae
  4          where  view_name = 'PEOPLE')
  5  pivot(
  6    max(view_column_name || nullif(' [' || nullif(table_column_name, view_column_name) || ']', ' []'))
  7    for edition_name in ('V2' as v2, 'V3' as v3, 'V4' as v4)
  8  )
  9  order  by view_column_id;

V2             V3             V4
-------------- -------------- -------------------------
ID             ID             ID
FIRST_NAME     FIRST_NAME     GIVEN_NAME [FIRST_NAME]
LAST_NAME      LAST_NAME      FAMILY_NAME [LAST_NAME]
PHONE_NUMBER   PHONE_NUMBER   PHONE_NUMBER
               EYE_COLOR      EYE_COLOR

For other parts of the EBR series, please visit the index page.

EBR – Part 9: Adding a New Column

This is part 9 of a post series about Oracle Edition-Based Redefinition.

Visit the index page for all the parts of the series

Introduction

In part 7 (“Editioning Views”) I introduced our third development use case – adding a new column that represents a new business logic.

I emphasize the fact it’s a new business logic, because sometimes we add new columns that come instead of existing columns, for replacing an existing business logic. As we’ll see in future posts, such cases require some additional handling in online upgrades.

Now, after part 8 (“The Last Planned Downtime”), in which we converted our schema to use editioning views and made it fully ready for online upgrades, we can implement the requirement in an online way.

The Task

We want to add a new attribute – Eye Color – to the Person entity.
For that we need to add a new column – EYE_COLOR – to the PEOPLE table, add make the corresponding changes in the PEOPLE_DL and APP_MGR packages (adding an input parameter – i_eye_color – to the people_dl.add procedure and changing the implementation of people_dl.add and app_mgr.do_something accordingly).

The Current State

At this point, V2 is our latest edition and it is exposed to the production clients. When connected to V2, we see the following objects: Continue reading “EBR – Part 9: Adding a New Column”

EBR – Part 8: The Last Planned Downtime

This is part 8 of a post series about Oracle Edition-Based Redefinition.

Visit the index page for all the parts of the series

Introduction

I concluded the previous post by stating that the application code should never reference tables directly; instead, every table should be covered by an editioning view and the application code should reference the views.

Starting from Scratch?

If you start developing a new system from scratch, I strongly recommend to follow this practice, even if initially there is no requirement for online upgrades. That way, you will be ready for online upgrades from day one, so should such a requirement arise at a later stage you will not need to change anything – neither your data structures nor your code. The only overhead is maintaining an editioning view for every table, and I’m sure you’ll realize very quickly that this overhead is negligible. As long as downtime during your upgrades is acceptable, you only need to enable your schema owner for editions (a one-time operation, as shown in part 3), and (by default) you will simply use a single edition – the default one.

Existing Systems

If you have an existing system, and you want to enjoy the benefits of online upgrades and zero downtime, you need to do a one-time conversion of your schema – to cover all the tables by editioning views. Actually, even if you have an existing system and you do not want to enjoy these benefits, I would still recommend getting ready for online upgrades now. The rationale is the same as in the previous section (“Starting from Scratch?”).

Converting the Demo Schema

Let’s handle first the demo schema I’ve been using in this post series. We currently have the following objects: Continue reading “EBR – Part 8: The Last Planned Downtime”

EBR – Part 7: Editioning Views

This is part 7 of a post series about EBR.

Visit the index page for all the parts of the series

Introduction

Our next use case is adding a column that represents a new logic to the PEOPLE table, and making the corresponding changes in the PEOPLE_DL and APP_MGR packages. Of course, as we speak about EBR, the upgrade from the previous version to the new one should be online.
An online upgrade means that the application users should be able to continue working uninterruptedly. The code objects that they use should remain valid and available at any time.
In addition to the challenges raised from the first and second use cases, the current use case introduces another challenge. And unlike the previous challenges, this one cannot be overcome just by using a new edition.

The Current State

At this point, after completing two upgrades already – in part 3 and part 5, we have three editions, with the following actual objects:

V2> select object_name,object_type,status, edition_name
  2  from   user_objects
  3  order  by object_name,object_type;

OBJECT_NAME  OBJECT_TYPE  STATUS  EDITION_NAME
------------ ------------ ------- ------------
APP_MGR      PACKAGE      VALID   ORA$BASE
APP_MGR      PACKAGE BODY VALID   V2
PEOPLE       TABLE        VALID
PEOPLE_DL    PACKAGE      VALID   V2
PEOPLE_DL    PACKAGE BODY VALID   V2
PEOPLE_PK    INDEX        VALID

6 rows selected.

As discussed in part 6, a table is a non-editioned object, and therefore we have a single instance of the PEOPLE table – referenced by all the instances of the PEOPLE_DL package body in all the editions.

The Task

We want to add a new attribute – Eye Color – to the Person entity.
For that we need to add a new column – EYE_COLOR – to the PEOPLE table, add make the corresponding changes in the PEOPLE_DL and APP_MGR packages (adding an input parameter – i_eye_color – to the people_dl.add procedure and changing the implementation of people_dl.add and app_mgr.do_something accordingly).

Our Problem

If we try to add the new column to the table, then Continue reading “EBR – Part 7: Editioning Views”

EBR – Part 6: Editionable and Non-Editionable, Editioned and Non-Editioned

This is part 6 of a post series about EBR.
In part 1 we created the baseline model and code – a table (PEOPLE) and two packages (PEOPLE_DL and APP_MGR).
In part 2 we saw that even a simple change – a package body compilation – can be dangerous in a busy system.
In part 3 we learned about editions and how they can be used for solving the problems described in part 2, so changes can be applied in both online and safe way. I discussed enabling, creating and using editions; session edition; and editioned objects.
In part 4 we discussed another challenge of online upgrades – invalidation of dependent objects.
In part 5 we saw the importance of explicit actualization of dependent objects.
In this part we’ll dive into the differences between editioned and non-editioned objects, editionable and non-editionable object types, and the relationships between them.

Visit the index page for all the parts of the series

In a previous post, we enabled the user DEMO_EBR for editions. As a result, some of the existing objects of DEMO_EBR (the package specs and package bodies) have become editioned, and the others (a table and an index) have not. Why is that?
Before I answer this question, let’s see what an editioned object is exactly, and what a non-editioned object is.

Editioned and Non-Editioned Objects

An editioned object is Continue reading “EBR – Part 6: Editionable and Non-Editionable, Editioned and Non-Editioned”