11 Replies Latest reply: Feb 8, 2010 2:12 PM by Timur Akhmadeev RSS

    11g r2 Edition-based redefinition

    pkchan
      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
          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
            pkchan
            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
              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
                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
                  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
                    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
                      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
                        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
                          pkchan
                          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
                            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
                              Timur Akhmadeev
                              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.