oracle_Edition-Based-Redefinition数据库版本_孙成子子

http://blog.sina.com.cn/u/2199343467

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

oracle_Edition-Based-Redefinition数据库版本

(2018-07-10 23:11:47)

标签：

upgrade

ddl

edition

view

rolling

分类： oracleops

（同志们，好久不见。）

本文章内容主要针对灰度发布针对数据库端表级别不同版本对应用的不兼容性问题。

在11gR1之前,当业务系统需要升级的时候,我们常常需要停下,从而保证版本的一直性和访问的连续性,对于一些APP变更频繁但又不允许经常停应用的IT系统, 无法做到兼顾,从11gR2开始,Oracle引入edition(版本)的概念,以实现app的online upgrade. 同时该特性允许pre-upgrade application and the post-upgrade application并存, 等我们确认post-upgrade app没问题的时候再把pre-upgrade app切换下来, 在这期间整个APP的访问是不受影响的.从而可以最大程度的减少application down time.实际上这个特性，在11gR1中已经提供了，通过隐藏参数来控制_edition_based_redefinition 11gR1中该参数默认是false. 11gR2中，该参数已经被废弃，默认为启用。

edition(版本)的特性解决了升级前后的兼容性问题，但未实现两套系统的数据同步问题，通过cross-edition trigger来实现，CROSSEDITION触发器可以其他的版本中生效，使得登陆到其他版本的会话执行的DML可以正确的将数据应用到当前版本。如果是升级版本，使用FORWARD CROSSEDITION触发器，这个触发器在当前版本的父版本中触发。如果是降级版本，使用REVERSE CROSSEDITION触发器，这个触发器在当前版本和子版本中触发。

下面关于的edition-based redefinition的介绍： edition 可以理解成“版本”。

一个database至少有一个缺省的edition

CONN / AS SYSDBA

SELECT property_value FROM database_properties WHERE property_name = 'DEFAULT_EDITION'; PROPERTY_VALUE

--------------------------------------------------------------------------------

ORA$BASE

创建一个edition，子句as child of缺省继承上一个edition,即父集，一个edition只能有一个父集和子集，也就是不加子句的话默认继承上一个,进行删除时必须保证子集被删；

create edition release_v1 as child of ora$base;

create edition release_v2 ;

create edition release_v3;

SQL> SELECT * FROM dba_editions;

EDITION_NAME PARENT_EDITION_NAME USA

------------------------------ ------------------------------ ---

ORA$BASE YES

RELEASE_V3 RELEASE_V2 YES

RELEASE_V1 ORA$BASE YES

RELEASE_V2 RELEASE_V1 YES

设置数据库的缺省edition

alter database DEFAULT EDITION = release_v1;

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

----------------- ---------------- ------------------------------------

DEFAULT_EDITION RELEASE_V1 Name of the database default edition

与edition相关的系统权限有

SQL> select name from system_privilege_map where name like 'ITION%';

NAME

------------------

ALTER ANY EDITION

DROP ANY EDITION

CREATE ANY EDITION

与edition相关的数据字典

*_editions -->列出了当前数据库中的所有的EDITIONS,缺省情况下ORA$BASE

SQL> select * from dba_editions;

EDITION_NAME PARENT_EDITION_NAME USABLE

-------------- -------------------- ------

ORA$BASE YES

*_objects -->描述当前版本下对应的objects的可见性（当前edition下的实际对象或者继承对象）

SQL> select owner,object_name,edition_name from dba_objects;

*_objects_ae -->描述当前数据库中每个真实的objects(所有版本 ae=all editiion)

SQL> select owner,object_name,edition_name from dba_objects_ae;

*_EDITION_COMMENTS

*_OBJECTS

*_OBJECTS_AE

*_ERRORS

*_ERRORS_AE

*_USERS

*_VIEWS

*_EDITIONING_VIEWS

*_EDITIONING_VIEWS_AE

*_EDITIONING_VIEW_COLS

*_EDITIONING_VIEW_COLS_AE

通过不同的edition连接数据库

从11gR2开始，SQL*Plus这样的工具也有对edition的连接支持

is: {[/][@] | / } [AS {SYSDBA | SYSOPER | SYSASM}] [EDITION=value]

默认情况下EDTION=DEFAULT_EDITION,也就是对应当前数据库的缺省版本

11gR2中editions支持的objects type有

Synonym

View

Function

Procedure

Package (specification and body)

Type (specification and body)

Library

Trigger

以上数据库对象支持edition，但是发现没有，表没有被包括在内。对于不同表结构的升降级需求，就需要editioning views出现，解决这个问题。 editioning views 也是和普通视图一样，选择需要的列进行返回，cross trigger建立在ev上用于新老版本的数据同步。forward crossediton trigger 用于升级操作，将老版本操作数据更新到新版本中，reverse crossedition 则相反，两个触发器可同时使用用于保障数据一致。

用户要想使用edition，必须先Enable editions for a schema。

注意：这是一个不可逆的动作，一旦enable了就不能disable。

比如：conn /as sysdba

alter user sun enable editions;

select username,editions_enabled from dba_users WHERE username='SUN';

USERNAME E

------------------------------ -

SUN Y

对用户授权对edition的使用权限

SQL> grant use on edition release_v1 to sun;

Grant succeeded.

SQL> grant use on edition release_v2 to sun;

Grant succeeded.

SQL> grant use on edition release_v3 to sun;

Grant succeeded.

SQL> SELECT sys_context('USERENV','SESSION_EDITION_NAME') FROM dual;

SYS_CONTEXT('USERENV','SESSION_EDITION_NAME')

---------------------------------------------

RELEASE_V1

SQL> SELECT sys_context('USERENV','SESSION_EDITION_NAME') FROM dual;

SYS_CONTEXT('USERENV','SESSION_EDITION_NAME')

---------------------------------------------

RELEASE_V3

SQL> alter session set edition=ora$base;

Session altered.

SQL> SELECT sys_context('USERENV','SESSION_EDITION_NAME') FROM dual;

SYS_CONTEXT('USERENV','SESSION_EDITION_NAME')

---------------------------------------------

ORA$BASE

一个存在的应用将要使用edition需要以下准备：

准备使用editon需要一点停机时间，但是接下来的升级操作不需要停机，步骤如下：

对基表进行改名操作，会对与表相关的PL/SQL和视图等对象失效。

在基表上创建editioning view，与原表的结构相同，同时对失效对象进行编译，使之有效。

删除ev上的触发器（也就是基表上的触发器），并编译，不对基表操作。

如果需要，应用VPD策略在ev上。

回收基表上的权限，并实施在ev上。

不同edition间的切换

alter session set edition=newedition;

使用环境变量ORA_EDITION

export ORA_EDITION=ora\$base / set ORA_EDITION=ora$base

通过sqlplu客户端指定edition登录

$ sqlplus username/password@service edition=ora\$base

sqlplud中使用connect关键字连接

SQL> CONNECT username/password@service EDITION=ora$base

数据库服务和editions

使用modify_service修改指定service的默认edition

BEGIN

DBMS_SERVICE.modify_service(

service_name => 'DB11G.WORLD',

edition => 'RELEASE_V1',

modify_edition => TRUE);

END;

SQL>SELECT name, edition FROM dba_services;

NAME EDITION

-------------------- ------------------------------

SYS$BACKGROUND

SYS$USERS

DB11GXDB

DB11G.WORLD RELEASE_V1

登录时，即选择了指定的edition。

$SQLPLUS edition_test/edition_test@//localhost:1521/DB11G.WORLD

SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;

EDITION

--------------------

RELEASE_V1

实战1：升降级没有数据传输

本部分，将在基表上添加一列，老系统将忽略更改继续工作，新系统将应用新列。

整体思路，是在两个edition中v1,v2分别基于基表创建不同列的ev,从而实现通过editioning view达到新老系统的兼容。

SQL> conn sun/sun

Connected.

SQL> alter session set edition=release_v1;

Session altered.

SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;

EDITION

--------------------------------------------------------------------------------

RELEASE_V1

SQL> CREATE TABLE employees_tab (

2 employee_id NUMBER(5) NOT NULL,

3 name VARCHAR2(40) NOT NULL,

4 date_of_birth DATE NOT NULL,

5 CONSTRAINT employees_pk PRIMARY KEY (employee_id)

6 );

Table created.

SQL> CREATE SEQUENCE employees_seq;

Sequence created.

SQL> CREATE OR REPLACE EDITIONING VIEW employees AS

2 SELECT employee_id,

3 name,

4 date_of_birth

5 FROM employees_tab;

View created.

SQL> CREATE OR REPLACE PROCEDURE create_employee (p_name IN employees.name%TYPE,

2 p_date_of_birth IN employees.date_of_birth%TYPE) AS

3 BEGIN

4 INSERT INTO employees (employee_id, name, date_of_birth)

5 VALUES (employees_seq.NEXTVAL, p_name, p_date_of_birth);

6 END create_employee;

7 /

Procedure created.

SQL> BEGIN

2 create_employee('Peter Parker', TO_DATE('01-JAN-2010', 'DD-MON-YYYY'));

3 COMMIT;

4 END;

5 /

PL/SQL procedure successfully completed.

SQL>

SQL> SELECT * FROM employees;

EMPLOYEE_ID NAME DATE_OF_B

----------- ---------------------------------------- ---------

1 Peter Parker 01-JAN-10

COLUMN object_name FORMAT A20

SELECT object_name, object_type, edition_name

FROM user_objects_ae

ORDER BY object_name;

OBJECT_NAME OBJECT_TYPE EDITION_NAME

-------------------- ------------------- ------------------------------

EMPLOYEES_PK INDEX

EMPLOYEES_TAB TABLE

EMPLOYEES_SEQ SEQUENCE

EMPLOYEES VIEW RELEASE_V1

CREATE_EMPLOYEE PROCEDURE RELEASE_V1

##可以看到table,index,sequence是没有editon属性的

SQL> ALTER SESSION SET EDITION = release_v2;

Session altered.

SQL> BEGIN

2 create_employee('Clark Kent', TO_DATE('02-JAN-2010', 'DD-MON-YYYY'));

3 COMMIT;

4 END;

5 /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM employees;

EMPLOYEE_ID NAME DATE_OF_B

----------- ---------------------------------------- ---------

2 Peter Parker 01-JAN-10

3 Clark Kent 02-JAN-10

SQL> ALTER TABLE employees_tab ADD (

2 postcode VARCHAR2(20)

3 );

Table altered.

SQL> ALTER SESSION SET EDITION = release_v1;

Session altered.

SQL>

SQL> BEGIN

2 create_employee('Flash Gordon', TO_DATE('03-JAN-2010', 'DD-MON-YYYY'));

3 COMMIT;

4 END;

5 /

PL/SQL procedure successfully completed.

SQL>

SQL> SELECT * FROM employees;

EMPLOYEE_ID NAME DATE_OF_B

----------- ---------------------------------------- ---------

2 Peter Parker 01-JAN-10

3 Clark Kent 02-JAN-10

4 Flash Gordon 03-JAN-10

SQL> ALTER SESSION SET EDITION = release_v2;

Session altered.

SQL> CREATE OR REPLACE EDITIONING VIEW employees AS

2 SELECT employee_id,

3 name,

4 date_of_birth,

5 postcode

6 FROM employees_tab;

View created.

SQL> CREATE OR REPLACE PROCEDURE create_employee (p_name IN employees.name%TYPE,

2 p_date_of_birth IN employees.date_of_birth%TYPE,

3 p_postcode IN employees.postcode%TYPE) AS

4 BEGIN

5 INSERT INTO employees (employee_id, name, date_of_birth, postcode)

6 VALUES (employees_seq.NEXTVAL, p_name, p_date_of_birth, p_postcode);

7 END create_employee;

8 /

Procedure created.

SQL> BEGIN

2 create_employee('Mighty Mouse', TO_DATE('04-JAN-2010', 'DD-MON-YYYY'), 'AA1 2BB');

3 COMMIT;

4 END;

5 /

PL/SQL procedure successfully completed.

SQL> SET LINESIZE 100

SQL> SELECT * FROM employees;

EMPLOYEE_ID NAME DATE_OF_B POSTCODE

----------- ---------------------------------------- --------- --------------------

2 Peter Parker 01-JAN-10

3 Clark Kent 02-JAN-10

4 Flash Gordon 03-JAN-10

5 Mighty Mouse 04-JAN-10 AA1 2BB

SELECT object_name, object_type, edition_name

FROM user_objects_ae

ORDER BY object_name, edition_name;

OBJECT_NAME OBJECT_TYPE EDITION_NAME

-------------------- ------------------- ------------------------------

EMPLOYEES_TAB TABLE

EMPLOYEES_PK INDEX

EMPLOYEES_SEQ SEQUENCE

EMPLOYEES VIEW RELEASE_V2

EMPLOYEES VIEW RELEASE_V1

CREATE_EMPLOYEE PROCEDURE RELEASE_V2

CREATE_EMPLOYEE PROCEDURE RELEASE_V1

##可以看到table,index,sequence是没有editon属性的

SQL> ALTER SESSION SET EDITION = release_v1;

Session altered.

SQL> SELECT * FROM employees;

EMPLOYEE_ID NAME DATE_OF_B

----------- ---------------------------------------- ---------

2 Peter Parker 01-JAN-10

3 Clark Kent 02-JAN-10

4 Flash Gordon 03-JAN-10

5 Mighty Mouse 04-JAN-10

实战2：升降级需要互相进行数据传输

实验将name列分为first_name和last_name,我们知道三列之间的关系，从而通过cross trigger实现数据的互相同步。

在新系统通过创建forward crossediton trigger ，reverse crossedition trigger 实现三列的转换，新老系统操作ev触发对基表的三列的不同更新；

SQL> conn sun/sun

Connected.

SQL>

SQL> ALTER TABLE employees_tab ADD (

2 first_name VARCHAR2(20),

3 last_name VARCHAR2(20)

4 );

Table altered.

SQL> UPDATE employees_tab

2 SET first_name = SUBSTR(name, 1, INSTR(name, ' ')-1),

3 last_name = SUBSTR(name, INSTR(name, ' ')+1)

4 WHERE first_name IS NULL;

4 rows updated.

SQL>

SQL> ALTER TABLE employees_tab MODIFY (

2 first_name VARCHAR2(20) NOT NULL,

3 last_name VARCHAR2(20) NOT NULL

4 );

Table altered.

SQL> SELECT * FROM employees_tab ;

EMPLOYEE_ID NAME DATE_OF_B POSTCODE FIRST_NAME LAST_NAME

----------- --------------- --------- -------------------- -------------------- --------------------

2 Peter Parker 01-JAN-10 Peter Parker

3 Clark Kent 02-JAN-10 Clark Kent

4 Flash Gordon 03-JAN-10 Flash Gordon

5 Mighty Mouse 04-JAN-10 AA1 2BB Mighty Mouse

SQL> CREATE OR REPLACE EDITIONING VIEW employees AS

2 SELECT employee_id,

3 first_name,

4 last_name,

5 date_of_birth,

6 postcode

7 FROM employees_tab;

View created.

SQL> SQL> CREATE OR REPLACE PROCEDURE create_employee (p_first_name IN employees.first_name%TYPE,

2 p_last_name IN employees.last_name%TYPE,

3 p_date_of_birth IN employees.date_of_birth%TYPE,

4 p_postcode IN employees.postcode%TYPE) AS

5 BEGIN

6 INSERT INTO employees (employee_id, first_name, last_name, date_of_birth, postcode)

7 VALUES (employees_seq.NEXTVAL, p_first_name, p_last_name, p_date_of_birth, p_postcode);

8 END create_employee;

9 /

Procedure created.

SQL> CREATE OR REPLACE TRIGGER employees_fwd_xed_trg

2 BEFORE INSERT OR UPDATE ON employees_tab

3 FOR EACH ROW

4 FORWARD CROSSEDITION

5 DISABLE

6 BEGIN

7 :NEW.first_name := SUBSTR(:NEW.name, 1, INSTR(:NEW.name, ' ')-1);

8 :NEW.last_name := SUBSTR(:NEW.name, INSTR(:NEW.name, ' ')+1);

9 END employees_fwd_xed_trg;

10 /

Trigger created.

SQL> CREATE OR REPLACE TRIGGER employees_rvrs_xed_trg

2 BEFORE INSERT OR UPDATE ON employees_tab

3 FOR EACH ROW

4 REVERSE CROSSEDITION

5 DISABLE

6 BEGIN

7 :NEW.name := :NEW.first_name || ' ' || :NEW.last_name;

8 END employees_rvrs_xed_trg;

9 /

Trigger created.

SQL> ALTER TRIGGER employees_fwd_xed_trg ENABLE;

Trigger altered.

SQL> ALTER TRIGGER employees_rvrs_xed_trg ENABLE;

Trigger altered.

SELECT object_name, object_type, edition_name

FROM user_objects_ae

ORDER BY object_name, edition_name;

OBJECT_NAME OBJECT_TYPE EDITION_NAME

------------------------- ------------------- ------------------------------

EMPLOYEES_TAB TABLE

EMPLOYEES_PK INDEX

EMPLOYEES_SEQ SEQUENCE

EMPLOYEES VIEW RELEASE_V2

EMPLOYEES VIEW RELEASE_V3

EMPLOYEES VIEW RELEASE_V1

CREATE_EMPLOYEE PROCEDURE RELEASE_V2

CREATE_EMPLOYEE PROCEDURE RELEASE_V1

CREATE_EMPLOYEE PROCEDURE RELEASE_V3

EMPLOYEES_FWD_XED_TRG TRIGGER RELEASE_V3

EMPLOYEES_RVRS_XED_TRG TRIGGER RELEASE_V3

## 在对表first_name和last_name列进行not null操作前，先确保老系统没有对基表的DML操作，pending后再使用employees_fwd_xed_trg对name做更新。

SQL> DECLARE

2 l_scn NUMBER := NULL;

3 l_timeout CONSTANT INTEGER := NULL;

4 BEGIN

5 IF NOT DBMS_UTILITY.wait_on_pending_dml(tables => 'employees_tab',

6 timeout => l_timeout,

7 scn => l_scn)

8 THEN

9 RAISE_APPLICATION_ERROR(-20000, 'Wait_On_Pending_DML() timed out. CETs were enabled before SCN: ' || l_scn);

10 END IF;

11 END;

12 /

PL/SQL procedure successfully completed.

SQL> DECLARE

2 l_cursor NUMBER := DBMS_SQL.open_cursor();

3 l_return NUMBER;

4 BEGIN

5 DBMS_SQL.PARSE(

6 c => l_cursor,

7 Language_Flag => DBMS_SQL.NATIVE,

8 Statement => 'UPDATE employees_tab SET name = name',

9 apply_crossedition_trigger => 'employees_fwd_xed_trg'

10 );

11 l_return := DBMS_SQL.execute(l_cursor);

12 DBMS_SQL.close_cursor(l_cursor);

13 COMMIT;

14 END;

15 /

PL/SQL procedure successfully completed.

SQL> ALTER TABLE employees_tab MODIFY (

2 first_name VARCHAR2(20) NOT NULL,

3 last_name VARCHAR2(20) NOT NULL

4 );

SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;

EDITION

-------------------------

RELEASE_V3

SQL> BEGIN

2 create_employee('Wonder', 'Woman', TO_DATE('01-JAN-2010', 'DD-MON-YYYY'), 'A11 2BB');

3 COMMIT;

4 END;

5 /

PL/SQL procedure successfully completed.

-- Check the editioning view.

SQL> SET LINESIZE 100

SQL> SELECT * FROM employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME DATE_OF_B POSTCODE

----------- -------------------- -------------------- --------- --------------------

2 Peter Parker 01-JAN-10

3 Clark Kent 02-JAN-10

4 Flash Gordon 03-JAN-10

5 Mighty Mouse 04-JAN-10 AA1 2BB

6 Wonder Woman 01-JAN-10 A11 2BB

-- Check the base table.

EMPLOYEE_ID NAME DATE_OF_B POSTCODE FIRST_NAME LAST_NAME

----------- -------------------- --------- ---------- -------------------- --------------------

2 Peter Parker 01-JAN-10 Peter Parker

3 Clark Kent 02-JAN-10 Clark Kent

4 Flash Gordon 03-JAN-10 Flash Gordon

5 Mighty Mouse 04-JAN-10 AA1 2BB Mighty Mouse

6 Wonder Woman 01-JAN-10 A11 2BB Wonder Woman

SQL> ALTER SESSION SET EDITION = release_v2;

Session altered.

SQL> BEGIN

2 create_employee('Inspector Gadget', TO_DATE('01-JAN-2010', 'DD-MON-YYYY'), 'A12 2BB');

3 COMMIT;

4 END;

5 /

PL/SQL procedure successfully completed.

SQL> -- Check editioning view.

SQL> COLUMN name FORMAT A25

SQL>

SQL> SELECT * FROM employees;

EMPLOYEE_ID NAME DATE_OF_B POSTCODE

----------- ------------------------- --------- ----------

2 Peter Parker 01-JAN-10

3 Clark Kent 02-JAN-10

4 Flash Gordon 03-JAN-10

5 Mighty Mouse 04-JAN-10 AA1 2BB

6 Wonder Woman 01-JAN-10 A11 2BB

7 Inspector Gadget 01-JAN-10 A12 2BB

6 rows selected.

SQL>

SQL> -- Check base table.

SQL> SELECT * FROM employees_tab;

EMPLOYEE_ID NAME DATE_OF_B POSTCODE FIRST_NAME LAST_NAME

----------- ------------------------- --------- ---------- -------------------- --------------------

2 Peter Parker 01-JAN-10 Peter Parker

3 Clark Kent 02-JAN-10 Clark Kent

4 Flash Gordon 03-JAN-10 Flash Gordon

5 Mighty Mouse 04-JAN-10 AA1 2BB Mighty Mouse

6 Wonder Woman 01-JAN-10 A11 2BB Wonder Woman

7 Inspector Gadget 01-JAN-10 A12 2BB Inspector Gadget

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：oracle_没有控制文件备份下的恢复过程

后一篇：oracle_SQL_EXEC_ID和SQL_RESTART

新浪BLOG意见反馈留言板　欢迎批评指正