edition based redefinition joords
DESCRIPTION
Edition Based Redifinition, The key to onine application upgrade. When upgrading an application or implementation based on PL/SQL means in generally compile of the PL / SQL procedures. During this compilationfase end users can not use the application: they must wait until the entire change in the application is implemented. During the implementation can be a bit unpredictable behavior. In Oracle 11g Release 2, this problem is past: by utilizing Edition it is possible to live new code to implement and compile, in one stroke current is madeTRANSCRIPT
WWW.TRANSFER-SOLUTIONS.COM
Spreker(s) :
Datum :
E-mail :
Edition Based RedefinitionThe key to online application upgrade
Jos van den Oord
24 oktober 2012
[email protected] Academy
© COPYRIGHT TRANSFER SOLUTIONS B.V. 2
Competency Quiz
Does DBA_OBJECTS show all database objects?
Can you create a before insert table trigger on a view?
Can two different views with the same name exist in a schema?
Can you replace a PL/SQL object without down-time while it is in use?
Can all views be created with a WHERE clause?
Can you set the firing order of table triggers?
Do you know how to actualize a stored procedure?
What does it mean to grant USE to a schema?
© COPYRIGHT TRANSFER SOLUTIONS B.V.
High availability
3
Resource Solution
Data Center Data Guard
Server RAC
Storage ASM, RAID, Resumable Transactions
Network VLANs, Multiplexing, Bonding
Corruption RMAN
Software Rolling Patches
Tables & Indexes Online Redefinition/Create/Alter/Rebuild/Move
Parameters Changing of most Oracle parameters(only 90 of the 350 are not online to change)
PL/SQL Objects Not Possible
© COPYRIGHT TRANSFER SOLUTIONS B.V.
Application Upgrade
Creation of new objects
Changing existing objects (alter and create or replace)
Add, Modify or Drop columns or constraints
Change packages and stored procedures
Recompile
Drop redundant objects
Convert or migrate data
Resume normal operations
4A
pplication D
own
© COPYRIGHT TRANSFER SOLUTIONS B.V.
What we want for Application Upgrades
Elimination downtime during application role out
System continue without interrupting for users
Upgrade system code and immediately oprational
Easy roll forward and backward senario of the application
5
© COPYRIGHT TRANSFER SOLUTIONS B.V.
Edition Based Redefinition (EBR)
A single technology that provides high availability during Upgrades
No disturbing users
No corrupt data
upgrades that:Reflects pre-upgrade transactions after upgrade
Seamlessly rolls changes forward and backward
Is safe
Is secure
Is fully supported by Oracle
Is free (no extra licensing cost)
Operational on SE and EE
6
© COPYRIGHT TRANSFER SOLUTIONS B.V.
EBR - Parallel Universes
A revolutionary new capabilityCode changes are installed in the privacy of an edition
Editionable object typesPL/SQL objects of all kinds
Synonyms
Views
Three new object typesEdition
Editioning View
Crossedition Trigger
Mandatory for all Oracle databases [new|upgrade] 11gR2 editioning
7
© COPYRIGHT TRANSFER SOLUTIONS B.V.
Important Oracle RDBMS Changes
New Oracle Objects
Fine Graines Dependency Tracking
Editions – Oracle versioning
Solving exclusive lock on table by creating online indexes
Cost-Based optimalisations.
8
© COPYRIGHT TRANSFER SOLUTIONS B.V.
Editionable objects
Editionable
• PL/SQL Objects• Packages• Procedures• Functions• Triggers
• Views• Synonyms• Types
Not Editionable
• Tables• Materialized Views• DB Links• Public Synonyms
9
© COPYRIGHT TRANSFER SOLUTIONS B.V.
The Three New Object Types
10
•Editions are isolated environments.
•A single schema can have two or more versions of stored PL / SQL objects, types and views. [edition:owner:object => unique]
Edition
•Oracle New Tables
•Shield around the existing table.
•References to the table are replaced by references to the edition view.
Edition View
•Propagates data changes made by the parent edition into the child edition’s columns,
or (in hot-rollover) vice-versa
Crossedition Trigger
© COPYRIGHT TRANSFER SOLUTIONS B.V.
Edition
Introduced in release 11.2
Set compatibility 11.2
Version control system inside the database
New dimension or worlds for an Application
11
© COPYRIGHT TRANSFER SOLUTIONS B.V.
Edition views - The New Oracle Tables
The FROM LIST – only a single table
Select list is column named
Logical projection of a table structure
Edition view and table in same schema
One edition view for a table in given database edition
An editioning view can NEVER be more complex than this
No aggregation or concatenation, No Joins, No WHERE clause, No GROUP BY clause, No ORDER BY clause
12
© COPYRIGHT TRANSFER SOLUTIONS B.V.
Cross Edition Trigger
Temporary – drop after restructured completed
Propagating changes back and forward between two versions
Forward Crossedition Triggers
Reverse Crossedition Triggers
Crossedition Trigger Interaction with EditionsFiring Order
Follows and Precedes Clauses
Not visible to end-users
13
© COPYRIGHT TRANSFER SOLUTIONS B.V.
EBR exercise/planning
Application Upgrade
Impacted
Editions
Editions views
Forward crossedition trigers TRIGGERSTRIGERSriggersg triggerscrossedition triggers
Reverse crossedition triggers
Change only editioned objects
Make only additive table changes
Change only non-transaction tables
Change the structure of transaction tables non-additively
Support hot rollover
14
© COPYRIGHT TRANSFER SOLUTIONS B.V.
Schema design – NOT EBR
15
Table
IDX_1 IDX_2
Public synonyms
Views
Grants, Roles, Privs
VPD policies
VPD policies
Materialized views
Constraints
Triggers
AQ interfaces
© COPYRIGHT TRANSFER SOLUTIONS B.V.
Source Table
EditionViews
Schema design – EBR
16
IDX_1 IDX_2
Views
Grants, Roles, PrivsVPD policiesSynonyms
Materialized views
Triggers
AQ interfaces
Crossedition triggers
Auditing
Contraints
© COPYRIGHT TRANSFER SOLUTIONS B.V.
Edition Enabled Data Dictionary Views*_EDITIONS
*_EDITION_COMMENTS
*_VIEWS_AE
*_SOURCE_AE
*_OBJECTS_AE
*_ERRORS_AE
*_EDITIONING_VIEWS_AE
*_EDITIONING_VIEW_COLS_AE
AUD$ (obj$edition)
FGA_LOG$ (obj$edition)
DBA_OBJETCS (edition_name)
DBA_USERS (edition enabled)
V$SESSION (session_edition_id)AE = All Editions
17
© COPYRIGHT TRANSFER SOLUTIONS B.V.
Edition Enabled Packages
DBMS_EDITIONS_UTILITIES.SET_EDITIONING_VIEWS_READ_ONLY
DBMS_SESSION.SET_EDITION_DEFERRED(<EDITION_NAME> )
DBMS_METADATA_UTIL.GET_EDITIONID
DBMS_PARALLEL_EXECUTE.RESUME_TASK
DBMS_PARALLEL_EXECUTE.RUN_TASK
DBMS_SQL.PARSE
DBMS_UTILITY.VALIDATE
18
© COPYRIGHT TRANSFER SOLUTIONS B.V.
APEX and EBR (1)
19
None Options:Alter session set edition
DBMS_SESSION.set edition_deferred
Apex 4.0apex_util.set_edition()
dbms_epg.set_dad_attribute('APEX', 'database-edition', 'HR_RELEASE2');
Syntax error on line 48 of /home/oracle/OraHome_1/Apache/modplsql/conf/dads.conf:Invalid command 'PlsqlDatabaseEdition', perhaps mis-spelled or defined by a module not included in the server
© COPYRIGHT TRANSFER SOLUTIONS B.V.
APEX and EBR (2)
20
Build on – views on - Editioning Views
Four (!) -tier architecture
Edition your Views or PL/SQL Functions
Use Authorization Schemes
Table
Editioning View
(Regular) View
UI / APEX
© COPYRIGHT TRANSFER SOLUTIONS B.V.
Considerations by activating Editions
SCN and commitsSwitching/activating of editions
Inserts/updates delets full table LocksBulk inserts/updates,deletes on a table columns
Change column sizeImpacted on other objects
21
© COPYRIGHT TRANSFER SOLUTIONS B.V.
SQLDeveloper support
22
© COPYRIGHT TRANSFER SOLUTIONS B.V. 23
&QuestionsAnswers
CONSULTING | MANAGED SERVICES | EDUCATION
WWW.TRANSFER-SOLUTIONS.COM
Demo