Re: EBR

Hello Willy,

I think it is your second email on the same topic. I can try to answer.

I used to use EBR on 11.2.0.4 8 years ago. My experience is not fully relevant unless you use 11.2. For instance, we split our application schemas into two: one is for non-editionable objects (tables/materialized views), the other one is for editionable ones: your PL/SQL code. Then Oracle introduced editionable property at the object level in 12.1, so there is no need to do such a schema separation anymore.

By and large, it works fine. There were a few bugs for which we implemented workarounds, such as flashing the shared pool after switching the database to a new edition.
As you probably know, Oracle uses EBR itself in Oracle E-Business Suite. If they can do it with such a complex application as E-Business Suite is, we should be able to do it too with our own applications.

It took me some time to convince my development team that we should use EBR in that company as there was no real alternative. I think the main disadvantage of EBR is that your developers should learn about it and develop their applications accordingly. There is no free lunch after all.

When it comes to EBR implementation itself, my suggestion is not to invest heavily in Editioned Views in the beginning. We can already demonstrate the benefits of EBR by using it only for PL/SQL code: packages, procedures, functions, triggers.
This way you can deploy small PL/SQL fixes without any downtime. Of course, you need to think how you are going to switch your application sessions to the new edition. My approach was to utilize the connection test query in JDBC. I asked my developers to set it to a function that was checking the current database edition and compared it to the session edition. If they differ, the function called dbms_session.set_edition_deferred. This way my connection pools were steadily migrated to the new edition.

A few links that I would consider as an introduction for a person who is unfamiliar with EBR:
1) Bryn himself:
https://www.youtube.com/playlist?list=PL22OaGrT53hVXrAYvG2CYnZ0eKvDxIcg8 2) Oren Nakdimon's EBR series:
http://db-oriented.com/my-ebr-blog-post-series/ 3) since you have German domain name, you might know Sven Weller - he must be using EBR too:
https://svenweller.wordpress.com/2020/05/31/a-quirk-with-object-dependencies-under-ebr/

The alternative implementation which I was also using in one company: 1) two databases with a GoldenGate bi-directional replication between them; one of the databases is always a "primary", i.e. it is used by the application;
2) when there is a need to install a new application patch, then it is applied first to the standby database (the one which is not used by the application). Any patches should not break the replication, e.g. you cannot drop columns or change their data types - it will break the GoldenGate replication.
3) finally, once you applied your application patch, you can switch your application to the new patched database. The overall downtime while doing such a switch was about 10 seconds in my environment. 4) then you can safely apply the patch to the old primary/new standby database;

What is good about EBR is that it is free and is available in all editions. GoldenGate is not.

On Tue, 16 Mar 2021 at 05:46, Willy Klotz <willyk_at_kbi-gmbh.de> wrote:

> Hello Listeners,
>
>
>
> at one of my customers, database maintenance is always done within offline
> windows: application is shutdown, new code installed, database recycled,
> application started up. This is one of the reasons why we do have only a
> few windows per year.
>
> For smaller fixes, we are looking at using EBR (edition-based
> redefinition). Heard not so much about it in the past, anyone using it out
> there? Any comments about it? Or are there better alternatives to minimize
> application downtime?
>
>
>
> Thanks for listening
>
> Willy
>

--
http://www.freelists.org/webpage/oracle-l