Forum Stats

  • 3,838,691 Users
  • 2,262,394 Discussions
  • 7,900,734 Comments

Discussions

use edition based redefinition to upgrade without downtime

berx
berx Member Posts: 219 Silver Badge
edited Jan 11, 2016 6:19PM in Database Ideas - Ideas

At the moment for every Database upgrade a downtime is needed. a startup RESTRICT or MIGRATE is required to update objects belonging to SYS.

But Oracle introduced a feature which should overcome this limitation:

With Edition Based Redefinition 2 different versions of Views and Packages can exist at the same time supporting different setups during a migration.

In a RAC environment, the new edition could be loaded before the first instance is moved from the old to the new version. Then every instance with higher binaries uses the new edition, whereas the old instances are using the old views and packages.

After the last instance is upgraded, a post-script makes the new edition default and removes the old one.

It's not easy, but HA rarely is.

berxGeeky NerdmanUser259623 -OracleLothar FlatzDer BaboHans-Martin Ruffvinaykumar2pankajrangaArpit Jain -OracleManish Chaturvediuser10212775Brian BakulactriebPravin TakpirebhagatsinghborneselSven W.top.gunDear DBA Frankitshaksdstuber
24 votes

Active · Last Updated

Comments

  • Tmicheli-Oracle
    Tmicheli-Oracle Member Posts: 24 Red Ribbon

    We, Oracle are working on our internal process as to how to evaluate and prioritize the IDEAS submitted.  But the more votes obviously the more priority we will put on the request.  However votes/popularity alone will not determine the priority.

    As we move through the process the IDEA will change stages: (not in flow order)

    - Active

    - Already Offered

    - Archived

    - Coming Soon

    - For Future Consideration

    - in Progress

    - Partially Implemented

    - Under Review

  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge

    evidently useful

  • Pravin Takpire
    Pravin Takpire Member Posts: 1,762 Gold Trophy

    with this one you can reduce the downtime but cant avoid the downtime.

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond

    How does Edition Based Redefinition "overcome this limitation" of downtime? Even with EBR, there is still a small window (can be very small) where the schema is unavailable when switching to the new edition. At this point, all database activity must cease because all database activity performed by end users will require recursive SQL which will be impossible while SYS is being moved to the new edition.


    Also, an upgrade or a patch can involve more than just SYS-owned objects. It can be any other objects that support database features as well. APEX, CTXSYS, MDSYS, etc.


    I don't remember, but isn't EBR an EE feature? If so, then this wouldn't apply to non-EE databases.


    All that being said, I do like the idea of Oracle using their own feature to significantly shorten the downtime window.

    Cheers,
    Brian

  • Sven W.
    Sven W. Member Posts: 10,541 Gold Crown

    How does Edition Based Redefinition "overcome this limitation" of downtime? Even with EBR, there is still a small window (can be very small) where the schema is unavailable when switching to the new edition. At this point, all database activity must cease because all database activity performed by end users will require recursive SQL which will be impossible while SYS is being moved to the new edition.


    Also, an upgrade or a patch can involve more than just SYS-owned objects. It can be any other objects that support database features as well. APEX, CTXSYS, MDSYS, etc.


    I don't remember, but isn't EBR an EE feature? If so, then this wouldn't apply to non-EE databases.


    All that being said, I do like the idea of Oracle using their own feature to significantly shorten the downtime window.

    Cheers,
    Brian

    EBR is for all editions.

    In theory you can do an downtimeless application upgrade with that. But it is pretty complex to do that. Sessions continue to work in the old edition. Only after a reconnect you will be switch to the new edition. The complex part is how to handle data that is entered in the old and in the new edition (can be technically done with cross edition triggers, but might not always be logically done).  I also doubt it would be possible for sys-objects or things like v$views . However that would be task of oracle to evaluate.

  • Dear DBA Frank
    Dear DBA Frank Member Posts: 177 Bronze Badge

    Edition-Based Redefinition is a great feature;  But don't be lured: for minimum dowtime, the process is quite complex (involves forward edition trigggers, views, invisible indexes, and several steps).  See a good explanation by Tom Kyte on OTN: I hope Oracle will make it more user-friendly.