This discussion is archived
11 Replies Latest reply: Feb 8, 2010 12:12 PM by 680087 RSS

11g r2 Edition-based redefinition

126955 Newbie
Currently Being Moderated
I have been reading on the new feature edition-based redefinition in 11g r2, but still do not know if i have a post-upgraded Java app and a pre-upgraded Java app, how will the post-upgraded app use the new edition and the pre-upgrade app use the old edition.

The purpose is to try to upgrade an app in production. For most application users they will keep using the pre-upgrade app connecting to the old edition by default, while at the same time some UAT users will test out the post-upgraded app on the new edition. The question is how will the Java app connect to a user schema to user the new edition. ( I understand if we login from sqlplus, we can use Alter session command to switch to the new edition after login )
  • 1. Re: 11g r2 Edition-based redefinition
    Hoek Guru
    Currently Being Moderated
    Interesting question. I've been reading about it as well.
    Unfortunatly I don't have an 11.2g db at my disposal...

    This is about: making an edition available to some users:
    http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10471/adfns_editions.htm#BABJFDCH
    Whole chapter:
    http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10471/adfns_editions.htm

    The part that's not clear to me is how you've implemented eb-redef, or are you just asking theoretically?
  • 2. Re: 11g r2 Edition-based redefinition
    126955 Newbie
    Currently Being Moderated
    hoek,
    thanks for the link.
    We are still in 10g, but just reading on this new feature, and 11g r2 is not yet available for Windows, so i can't have it installed on local neither.

    I still do not understand how to connect to the new edition. It is correct that we can make both edition available to the user, but there is only one default edition for the entire database. Let's say user scheme S can use both edition V1 and V2. When we connect to S, it use the default edition V1. If we login from sqlplus, we can use Alter Session to switch to V2, but from Java web app connecting thru app server connection pool, it will be using the default edition V1, how do we switch to session V2.

    ( Currently we are implementing multiple application versions with different user schema S1 and S2 in production with duplicating packages/procedures, and causing quite a bit of confusion sometimes )
  • 3. Re: 11g r2 Edition-based redefinition
    damorgan Oracle ACE Director
    Currently Being Moderated
    I have done a substantial amount of work with edition based redefinition and the one thing that I would hope would be clear from the docs as well as Tom Kyte's excellent article in the latest Oracle Magazine is that the nature of your application is irrelevant to EBR.

    When you log on you are assigned your default edition based on user-id and password.
    That is the edition you will be using for the life of your session unless you do an ALTER SESSION SET EDITION
    to a different edition.

    http://www.morganslibrary.org/reference/editions.html
  • 4. Re: 11g r2 Edition-based redefinition
    Hoek Guru
    Currently Being Moderated
    Here's an Oracle White Paper you might like to ( should ;) ) read as well:
    http://www.oracle.com/technology/deploy/availability/pdf/edition_based_redefinition.pdf
    It is correct that we can make both edition available to the user, but there is only one default edition for the entire database.
    Some quotes from the WP:

    "• Every database from 11.2 onwards, whether brand new or the result of an upgrade from an
    earlier version, non-negotiably has at least one edition. Immediately on creation or upgrade
    to 11.2, there is exactly one edition with the name Ora$Base."

    "• A new not null database property, Default_Edition, listed in Database_Properties, specifies the
    edition that a session will use immediately on connection if the connect syntax does not
    nominate an explicit edition. Code_1 shows the SQL statement to set this:
    -- Code_1 alter database default edition = Some_Edition
    A side effect of making an edition the default is to grant Use on it to public."

    Footnote 10, page 7 states:

    "10. The OCI and JDBC programmatic interfaces have been enhanced to allow an edition to be specified at
    session-creation time; and tools like SQL*Plus expose this new optional degree of freedom with appropriate
    syntax. However, in 11.2, the connect string specification (i.e. the item for which an alias can be established in
    tnsnames.ora) does not allow the edition to be specified. This means that a database link always connects to the
    target database’s default edition."
    ( Currently we are implementing multiple application versions with different user schema S1 and S2 in production with duplicating
    packages/procedures, and causing quite a bit of confusion sometimes )
    Yes, I know the confusion it can cause, tell me about it...especially when the confusion spans developers living in multiple countries in multiple timezones ;)
  • 5. Re: 11g r2 Edition-based redefinition
    damorgan Oracle ACE Director
    Currently Being Moderated
    Bryn Lewellyn's White Paper is a great resource, and a "must read" for anyone contemplating using EBR.
  • 6. Re: 11g r2 Edition-based redefinition
    Hoek Guru
    Currently Being Moderated
    Yes, and for me this is a new feature that can solve and save a lot of troubles. When I fully understand it, that is.
    The docs and white papers are starting points #1.
    However, working with 11.2 on daily basis is a utopia for me, cst's have their priorities as well, 11.1 is the max for now.
    Coincidentally I was talking about eb-redef earlier today with a DBA/MVA collegue ("oh, an 11.2 VM seems to be up and running, didn't you know that?" ;) ) and one thing is for sure: we can't wait to dive into it and explore and learn.


    Offtopic (at least for Dan ;) ) , but whoever else may stumble and click upon this link; please do read this other great WP:
    http://www.oracle.com/technology/tech/pl_sql/pdf/doing_sql_from_plsql.pdf
  • 7. Re: 11g r2 Edition-based redefinition
    damorgan Oracle ACE Director
    Currently Being Moderated
    I have links to both of Bryn's most recent White Papers on my home page
    www.morganslibrary.org
    His PL/SQL paper is the best I have ever read (Sorry Steven).

    The most important thing to remember if you are going to EBR is that you need to first define what you are going to redefine. If only tables and such then stick with DBMS_REDEFINITION. If going further then you may be able to incrementally ... use editions, use editions with editioning views, and finally use editions with editioning views and crossedition triggers.

    I sure wouldn't be writing crossedition triggers if I didn't need to.

    But, and this is very important, your API from this point forward will very likely be views not tables or materialized views. This change may be more challenging politically than technically.
  • 8. Re: 11g r2 Edition-based redefinition
    damorgan Oracle ACE Director
    Currently Being Moderated
    I have links to both of Bryn's most recent White Papers on my home page
    www.morganslibrary.org
    His PL/SQL paper is the best I have ever read (Sorry Steven).

    The most important thing to remember if you are going to EBR is that you need to first define what you are going to redefine. If only tables and such then stick with DBMS_REDEFINITION. If going further then you may be able to incrementally ... use editions, use editions with editioning views, and finally use editions with editioning views and crossedition triggers.

    I sure wouldn't be writing crossedition triggers if I didn't need to.

    But, and this is very important, your API from this point forward will very likely be views not tables or materialized views. This change may be more challenging politically than technically.
  • 9. Re: 11g r2 Edition-based redefinition
    126955 Newbie
    Currently Being Moderated
    hoek,
    "10. The OCI and JDBC programmatic interfaces have been enhanced to allow an edition to be specified at
    session-creation time; and tools like SQL*Plus expose this new optional degree of freedom with appropriate
    syntax. However, in 11.2, the connect string specification (i.e. the item for which an alias can be established in
    tnsnames.ora) does not allow the edition to be specified. This means that a database link always connects to the
    target database’s default edition."

    Sounds like EBR is not ready to replace the multiple schema implementation yet, unless the App Server is upgraded to be able to login to different Editions, and database link is upgraded to be able to connect to different Editions. Am i getting this right?
  • 10. Re: 11g r2 Edition-based redefinition
    damorgan Oracle ACE Director
    Currently Being Moderated
    At any point in time you can do an ALTER SESSION which allows you to change editions at will.
  • 11. Re: 11g r2 Edition-based redefinition
    680087 Pro
    Currently Being Moderated
    11gR2 JDBC driver recognizes a connection property named oracle.jdbc.editionName, which I believe is for setting current edition during connect/login phase.

    Edited by: Timur Akhmadeev on Feb 8, 2010 11:11 PM
    Corrected property name. It's working, I've tested it.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points