1 / 19

The powerhouse PL/SQL upgrade option: Edition-Based Redefinition (EBR)

The powerhouse PL/SQL upgrade option: Edition-Based Redefinition (EBR). Tim Hall Oracle ACE Director Oracle ACE of the Year 2006 OakTable Network OCP DBA (7, 8, 8i, 9i, 10g, 11g) OCP Advanced PL/SQL Developer Oracle Database: SQL Certified Expert http://www.oracle-base.com Books

kamin
Download Presentation

The powerhouse PL/SQL upgrade option: Edition-Based Redefinition (EBR)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. The powerhouse PL/SQL upgrade option:Edition-Based Redefinition (EBR) Tim Hall Oracle ACE Director Oracle ACE of the Year 2006 OakTable NetworkOCP DBA (7, 8, 8i, 9i, 10g, 11g) OCP Advanced PL/SQL Developer Oracle Database: SQL Certified Expert http://www.oracle-base.com Books Oracle PL/SQL Tuning Oracle Job Scheduling http://www.oracle-base.com

  2. Why should we care about EBR? • Application upgrades with no downtime. • Run multiple versions of apps simultaneously. • Gradual ramp-up. http://www.oracle-base.com

  3. Editions • Version label (sort-of) for some objects in schema. • Only apply to: • PL/SQL Objects: Functions, Libraries,Packages, Procedures, Triggers,TypesAlso: Synonyms, Views • Objects identified by: • owner • object_name • object_type • edition http://www.oracle-base.com

  4. New and Amended Dictionary Views • *_EDITIONS • *_EDITION_COMMENTS • *_OBJECTS • *_OBJECTS_AE • *_ERRORS • *_ERRORS_AE • *_USERS • *_VIEWS • *_EDITIONING_VIEWS • *_EDITIONING_VIEWS_AE • *_EDITIONING_VIEW_COLS • *_EDITIONING_VIEW_COLS_AE* = DBA / ALL / USER http://www.oracle-base.com

  5. What about tables? • Tables NOT editionable. • Use “Editioning Views” to see subsets and rename columns.CREATE OR REPLACEEDITIONING VIEW view-name ASSELECT col1, col2, col4 AS new_nameFROM table-name; • Apps access EViews, not tables. http://www.oracle-base.com

  6. Editioning Views Schema Edition: release_v1 Edition: release_v2 http://www.oracle-base.com

  7. Performance • Oracle guarantee execution plan will match for same query against editioning view and table. • No performance impact. http://www.oracle-base.com

  8. Do base tables have missing data? • Yes, but there is a solution. • Use Cross-Edition Triggers tokeep base table consistent. • Similar to regular triggers. • Transient. http://www.oracle-base.com

  9. Demo setup.sql http://www.oracle-base.com

  10. Managing Editions • Editions are created using CREATE EDITION.CREATE EDITION ed-name;CREATE EDITION ed-name AS CHILD OF ed-name; • Currently only 1 child per parent. • Attempt to branch results in error.ORA-38807: Implementation restriction:an edition can have only one child v1 v2 v3 v1.5 http://www.oracle-base.com

  11. Managing Editions (continued) • Unreferenced leaf editions removedusing DROP EDITION. • Attempt to drop non-leaf edition results in errorORA-38810: Implementation restriction: cannot drop edition that has a parent and a child • Query DBA_EDITIONS. • manage_editions.sql v1 ✗ v2 v3 ✔ http://www.oracle-base.com

  12. User Setup • Editioning must be turned on for a user.ALTER USER username ENABLE EDITIONS; • No off switch! • Specific editions must be associated with users.GRANT USE ON EDITION ed-name TO username; • user_setup.sql http://www.oracle-base.com

  13. Switching between editions • Any Session: (switch_editions.sql)ALTER SESSION SET EDITION = ed-name;ALTER DATABASE DEFAULT EDITION = ed-name; • SQL*Plus:$ export ORA_EDITION=ed-nameC:> set ORA_EDITION=ed-name$ sqlplus username/password@service edition=ed-nameSQL> CONNECT username/password@service EDITION=ed-name http://www.oracle-base.com

  14. Switching between editions (continued) • Services: Editions associated with services (11.2.0.2)BEGIN DBMS_SERVICE.modify_service( service_name => 'DB11G.WORLD', edition => 'RELEASE_V1', modify_edition => TRUE);END;/ • When you switch to a new edition, all editionable objects in schema are automatically inherited from the previous edition. • Altering or dropping an object breaks inheritance for that object. http://www.oracle-base.com

  15. Preparing existing app for editions. • Rename base tables. • Create editioning views matching original table structures and names. PL/SQL objects will recompile against editioning view. • Drop any triggers and compile them against editioning views, rather than base tables. • If necessary, apply VPD policies against editioning views, rather than base tables. • Revoke all privileges against tables andrecreate them against the editioning views. http://www.oracle-base.com

  16. Create new application • Build editioning views over tables. • Code references editioning views, not tables. • Triggers and VPD policiesreference editioning views. • (create_app.sql) http://www.oracle-base.com

  17. Upgrade application • Amend base tables. • Switch edition. • Build new editioning views andnew app. • Build cross edition triggersand update missing data. • (upgrade_app.sql) http://www.oracle-base.com

  18. Summary • Application upgrades with no downtime. • Run multiple versions of apps simultaneously. • Gradual ramp-up. • Not just a PL/SQL feature. Javaapps use tables too. http://www.oracle-base.com

  19. The End… • Download slides at:http://www.oracle-base.com/workshops • Article:http://www.oracle-base.com/articles/11g/EditionBasedRedefinition_11gR2.php http://www.oracle-base.com

More Related