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@xxxxxxxxxxx> 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
