Overview of Editions feature on Oracle:
Large mission-critical applications may experience downtime for tens of hours, or even longer, while the application’s database components are updated during an application upgrade.
Oracle Database introduced Edition-Based Redefinition (EBR), a revolutionary capability that allows online application upgrades with uninterrupted application availability.
EBR functions by maintaining two versions of the application simultaneously. When the installation of the upgrade is complete, the pre-upgrade application and the post-upgrade application can be used at the same time. Therefore, an existing session can continue using the pre-upgrade application until its user decides to end it; all new sessions can use the post-upgrade application. The pre-upgrade application can be retired after all sessions have disconnected from it. In other words, the application as a whole enjoys a hot rollover1 from the pre-upgrade version to the post-upgrade version.
The application’s database backend must be enabled to use EBR by making one-time schema changes to take advantage of the capability. Also, the script that performs the application upgrade must be written in such a way as to use EBR’s features. Therefore, EBR adoption and subsequent use is the prerogative of the development shop.
Oracle Edition concept:
First I would like to explain what do you mean by edition . Simple definition is same object name you can store multiple times. This will useful during upgradation or versioning control of ddls.
For example, as per your not sure whether object is required extra structure or not, in this case create same object name with different edition. Once you finalised then you can drop the unused object name .
Editions are nonschema objects; as such, they do not have owners.
Editions are created in a single namespace, and multiple editions can coexist in the database.
The database must have at least one edition. Every newly created or upgraded Oracle Database starts with one edition named ora$base.
Edition-based redefinition enables you to upgrade the database component of an application while it is in use, thereby minimizing or eliminating down time.
To upgrade an application while it is in use, you copy the database objects that comprise the application and redefine the copied objects in isolation. Your changes do not affect users of the application—they continue to run the unchanged application. When you are sure that your changes are correct, you make the upgraded application available to all users.
Using edition-based redefinition means using one or more of its component features. The features you use, and the down time, depend on these factors:
📍 What kind of database objects you redefine
📍How available the database objects must be to users while you are redefining them
📍Whether you make the upgraded application available to some users while others continue to use the older version of the application
You always use the edition feature to copy the database objects and redefine the copied objects in isolation; that is why the procedure that this chapter describes for upgrading applications online is called edition-based redefinition.
If the object type of every object you will redefine is editionable, the edition is the only feature you use.
Table is not an editionable type. If you change the structure of one or more tables, you also use the editioning view feature.
If other users must be able to change data in the tables while you are changing their structure, you also use forward crossedition triggers.
If the pre- and post-upgrade applications will be in ordinary use at the same time (hot rollover), you also use reverse crossedition triggers.
Crossedition triggers are not a permanent part of the application—you drop them when all users are using the post-upgrade application.
An editioned object is a schema object that has both an editionable type and an editions-enabled owner.
(A schema object that has an editionable type but not an editions-enabled owner is potentially editioned.)
An edition can have its own copy of an editioned object, in which case only the copy is visible to the edition.
A noneditioned object is a schema object that has a noneditionable type. An edition cannot have its own copy of a noneditioned object. A noneditioned object is identical in, and visible to, all editions.
The objects are limited to some set of objects which is not having the storage.
Editionable and Noneditionable Schema Object Types:
These schema objects types are editionable:
- SYNONYM
- VIEW
- All PL/SQL object types:
- FUNCTION
- LIBRARY
- PACKAGE and PACKAGE BODY
- PROCEDURE
- TRIGGER
- TYPE and TYPE BODY
All other schema object typeare noneditionable.
Table is an example of an noneditionable type.
A schema object of an editionable type is editioned if its owner is editions-enabled; otherwise, it is potentially editioned.
A schema object of a noneditionable type is always noneditioned, even if its owner is editions-enabled.
A table is an example of an noneditioned object.
1: Enabling Editions for a User
To enable editions for a user, use the ENABLE EDITIONS clause of either the CREATE USER or ALTER USER statement. The EDITIONS_ENABLED column of the static data dictionary view DBA_USERS or USER_USERS shows which users have editions enabled.
$ sqlplus / as sysdba
SQL> GRANT CREATE ANY EDITION, DROP ANY EDITION to user1;
SQL> alter user user1 enable editions;
SQL> grant create any view to user1;
SQL> select USERNAME,EDITIONS_ENABLED from dba_users where USERNAME='USER1';
USERNAME E
--------------- ---------
USER1 Y
2: Check the current & new edition in User1
$ sqlplus test1/pass
SQL> create edition e2;
--creating new edition
SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM DUAL;
-- check the current edition
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
---------------------
ORA$BASE
SQL> create editioning view ed_emp_view_orabase as select EMPNO,ENAME from emp;
SQL> desc ed_emp_view_orabase
Name Null? Type
----------------- -------- -------- -------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
SQL> alter session set edition=e2;
---moving to new edition
SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM DUAL;
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
----------------------------
E2
SQL> CREATE or replace EDITIONING VIEW ed_emp_view as
select EMPNO,ENAME,SAL from emp;
SQL> desc ed_emp_view
Name Null? Type
------------------------ -------- ---------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
SAL NUMBER(7,2)
3: Take the export
$ expdp dumpfile=test.dmp schemas=test include=view directory=dir1 source_edition=e2
4: drop the view from any edition
[oracle@ ~]$ sqlplus test/test
SQL> alter session set edition=ORA$BASE;
SQL> desc ed_emp_view_orabase
Name Null? Type
------------------------ -------- -----
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
SQL> drop view ed_emp_view_orabase;
Now you lost object in ORA$BASE edition. Now we have dumpfile backup but its in new edition.
5: IMPORT THE DUMPFILE
$ impdp dumpfile=test.dmp directory=dir1 target_edition=ORA\$BASE
6: Verify it
$ sqlplus test1/pass
SQL> ALTER SESSION SET EDITION =ORA$BASE;
SQL> desc ed_emp_view_orabase
Name Null? Type
---------------------- --------- ---------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
SAL NUMBER(7,2)
Note: Now you got the object from dumpfile which is target edition dumpfile. Now source edition and target edition objects are same.
AS CHILD OF Clause
If you use this clause, then the new edition is created as a child of parent_edition. If you omit this clause, then the new edition is created as a child of the leaf edition. At the time of its creation, the new edition inherits all editioned objects from its parent edition.
Restriction on Editions
An edition can have only one child edition. If you specify for parent_edition an edition that already has a child edition, then an error is returned.
Sample:
The following very simple examples are intended to show the syntax for creating and working with an edition.
In the following statements, the user HR is given the privileges needed to create and use an edition:
SQL> GRANT CREATE ANY EDITION, DROP ANY EDITION to HR;
Grant succeeded.
SQL>ALTER USER hr ENABLE EDITIONS;
User altered.
HR creates a new edition TEST_ED for testing purposes:
SQL>CREATE EDITION test_ed;
HR then creates an editioning view ed_view in the default edition ORA$BASE for testing purposes, first verifying that the current edition is the default edition:
SQL>SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM DUAL;
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
----------------------------------
ORA$BASE
1 row selected.
SQL> CREATE EDITIONING VIEW e_view AS
SELECT last_name, first_name, email
FROM employees;
View created.
DESCRIBE e_view
Name Null? Type
-------------- -------- -------------
LAST_NAME NOT NULL VARCHAR2(25)
FIRST_NAME VARCHAR2(20)
EMAIL NOT NULL VARCHAR2(25)
The view is then actualized in the TEST_ED edition when HR uses the TEST_ED edition and re-creates the view in a different form:
SQL>ALTER SESSION SET EDITION = TEST_ED;
Session altered.
SQL>CREATE OR REPLACE EDITIONING VIEW e_view AS
SELECT last_name, first_name, email, salary FROM employees;
View created.
The view in the TEST_ED edition has an additional column:
DESCRIBE e_view
Name Null? Type
------------------ -------- ---------------
LAST_NAME NOT NULL VARCHAR2(25)
FIRST_NAME VARCHAR2(20)
EMAIL NOT NULL VARCHAR2(25)
SALARY NUMBER(8,2)
The view in the ORA$BASE edition remains isolated from the test environment:
SQL>ALTER SESSION SET EDITION = ora$base;
Session altered.
DESCRIBE e_view;
Name Null? Type
----------- -------- --------------
LAST_NAME NOT NULL VARCHAR2(25)
FIRST_NAME VARCHAR2(20)
EMAIL NOT NULL VARCHAR2(25)
Even if the view is dropped in the test environment, it remains in the ORA$BASE edition:
SQL>ALTER SESSION SET EDITION = TEST_ED;
Session altered.
SQL>DROP VIEW e_view;
View dropped.
ALTER SESSION SET EDITION = ORA$BASE;
Session altered.
DESCRIBE e_view;
Name Null? Type
---------------- -------- ------------------
LAST_NAME NOT NULL VARCHAR2(25)
FIRST_NAME VARCHAR2(20)
EMAIL NOT NULL VARCHAR2(25)
When the testing of upgrade that necessitated the TEST_ED edition is complete, the edition can be dropped:
SQL>DROP EDITION TEST_ED;
Making an Edition Available to Some Users
As the creator of the edition, you automatically have the USE privilege WITH GRANT OPTION on it. To grant the USE privilege on the edition to other users, use the SQL statement GRANT USE ON EDITION.
Making an Edition Available to All Users
To make an edition available to all users, either:
- Grant the USE privilege on the edition to PUBLIC:
SQL> GRANT USE ON EDITION edition_name TO PUBLIC
- Make the edition the database default edition:
SQL>ALTER DATABASE DEFAULT EDITION = edition_name
- This has the side effect of allowing all users to use the edition, because it effectively grants the USE privilege on edition_nameto PUBLIC.
Edition-Based Redefinition (EBR)
Edition-based redefinition (EBR) enables online application upgrade with uninterrupted availability of the application. When the installation of an upgrade is complete, the pre-upgrade application and the post-upgrade application can be used at the same time. Therefore, an existing session can continue to use the pre-upgrade application until its user decides to end it; and all new sessions can use the post-upgrade application. When there are no longer any sessions using the pre-upgrade application, it can be retired. In this way, EBR allows hot rollover from from the pre-upgrade version to the post-upgrade version, with zero downtime.
EBR enables online application upgrades in the following manner:
- Code changes are installed in the privacy of a new edition.
- Data changes are made safely by writing only to new columns or new tables not seen by the old edition. An editioning view exposes a different projection of a table into each edition to allow each to see just its own columns.
- Crossedition triggers propagate data changes made by the old edition into the new edition’s columns, or (in hot-rollover) vice-versa.
EBR is available for use in all editions of Oracle Database without the need to license it.
Oracle data pump option for Edition:
TARGET_EDITION=name
If you specify TARGET_EDITION=name, then Data Pump Import creates all of the objects found in the dump file. Objects that are not editionable are created in all editions.
For example, tables are not editionable, so if there is a table in the dump file, then the table is created, and all editions see it.
Objects in the dump file that are editionable, such as procedures, are created only in the specified target edition.
If this parameter is not specified, then Import uses the default edition on the target database, even if an edition was specified in the export job. If the specified edition does not exist, or is not usable, then an error message is returned.
Restrictions
This parameter is only useful if there are two or more versions of the same versionable objects in the database.
The following is an example of using the TARGET_EDITION parameter:
# impdp hr DIRECTORY=dpump_dir1 DUMPFILE=exp_dat.dmp TARGET_EDITION=exp_edition
This example assumes the existence of an edition named exp_edition on the system to which objects are being imported. Because no import mode is specified, the default of schema mode will be used.
More info:
https://docs.oracle.com/en/database/oracle/oracle-database/19/adfns/editions.html
https://docs.oracle.com/en/database/oracle/oracle-database/23/adfns/editions.html
https://www.oracle.com/docs/tech/ebr-technical-deep-dive-overview.pdf
Alireza Kamrani
Database Box is a technical group on LinkedIn.
No comments:
Post a Comment