Zero Downtime PL/SQL Changes with Edition Based Redefinition

An overview of how Edition-based redefinition enables you to do zero-downtime deployments of PL/SQL

Transcript

2. I ♥ PL/SQL
3. How the @#!% do we get it live?!
4. Zero-Downtime PL/SQL Changes with Edition-Based Redefinition chris.saxon@oracle.com @ChrisRSaxon & @SQLDaily blogs.oracle.com/sql www.youtube.com/c/TheMagicofSQL Chris Saxon, Oracle Developer Advocate
5. The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation. Statements in this presentation relating to Oracle’s future plans, expectations, beliefs, intentions and prospects are “forward-looking statements” and are subject to material risks and uncertainties. A detailed discussion of these factors and other risks that affect our business is contained in Oracle’s Securities and Exchange Commission (SEC) filings, including our most recent reports on Form 10-K and Form 10-Q under the heading “Risk Factors.” These filings are available on the SEC’s website or on Oracle’s website at http://www.oracle.com/investor. All information in this presentation is current as of September 2019 and Oracle undertakes no duty to update any statement in light of new information or future events. Safe Harbor
6. alter package … compile;
7. alter package … compile; Errors: check compiler log
8. PRO start release create table … <stuff> update … commit; PRO release complete ORA-04068 PLS-00905 ORA-04061
9. alter package … compile;
10. alter package … compile;
11. alter package … compile;
12. alter package … compile; No users executing!
13. Rolling Release?
14. Rolling Release?
15. Rolling Release?
16. Rolling Release?
17. shutdown immediate
18. Emergency Rollback?
19. Solutions pre 11g Release 2
20. PROCEDURE_3 VIEW_2 Rename Your Objects! PROCEDURE PROCEDURE_2 PACKAGE PACKAGE_2 VIEW
21. VIEW_2 Dependency Hell PROC PROC_2 PACK PACK_2 PACK_3 VIEW VIEW_3 PROC_3a PACK_3a VIEW_3b PROC_4 VIEW_2a
22. PROCEDURE_3 VIEW_2 Rename Your Objects! PROCEDURE PROCEDURE_2 PACKAGE PACKAGE_2 PACKAGE_3 VIEW VIEW_3
23. Multiple PL/SQL Schemas DATA PL/SQL 1 PL/SQL 2
24. Replicate Your Database $$$ &
25. Copyright © 2019 Oracle and/or its affiliates. No Database Compiled PL/SQL!!
26. EBR to the Rescue!
27. PROCEDURE VIEW EDITION_1 EDITION_2 EDITION_3 EBR to the Rescue! PROCEDURE PACKAGE VIEW
28. How do I use EBR?
29. DEMO
30. create edition … alter session set edition … @do_your_release @recompile_all grant use on edition to …
31. That was too easy! Surely there must be a catch?
32. alter user … enable editions;
33. You can’t editions disable a user
34. Not Everything is Editionable MY_PACKAGE FUNCTION-BASED INDEX MATERIALIZED VIEW Addressed in 12c!
35. create noneditionable function … as begin … end; /
36. alter table t add … as ( your_func ) evaluate using current edition
37. Many editions >> risk of missteaks
38. Specify edition in connect string Ethernet plug grey / Elembis / CC-BY-SA-3.0
39. oracle.jdbc.editionName export ORA_EDITION = … conn usr/pwd edition = …
40. alter database default edition = …
41. Minimize Live Editions Image by tswedensky from Pixabay
42. revoke use on edition release_001 from ebr_user;
43. conn ebr_user alter session set edition = release_001; ORA-38802: edition does not exist
44. Wait, There’s More! EBR
45. Further Reading 11gR2 Documentation: http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_editions.htm#ADFNS020 12cR1 Documentation: http://docs.oracle.com/database/121/ADFNS/adfns_editions.htm Oracle-BASE article: http://oracle-base.com/articles/11g/edition-based-redefinition-11gr2.php Tom Kyte’s Oracle Magazine article, Edition-Based Redefinition, Part 1: http://www.oracle.com/technetwork/issue-archive/2010/10-jan/o10asktom-172777.html Edition-based redefinition whitepaper, Bryn Llewellyn July 2009: http://www.oracle.com/technetwork/database/features/availability/edition-based- redefinition-1-133045.pdf Oren Nakdimon's EBR blog post series
46. Summary Use EBR to deploy PL/SQL with zero downtime …or just shrink outage windows Requires 11g Release 2+; Easier from 12c
47. asktom.oracle.com #MakeDataGreatAgain Ryan McGuire / Gratisography