Oracle Edition Based Redefinition Demo 7
Version 11.2.0.3
 
Editioning for Patching 1: Preparation
Edition Based Redefinition is a game changer for Oracle if it is adopted. It has the ability to bring to Oracle-based applications real-time online patching. The following demonstration is designed to illustrate the ease of solving a challenge not otherwise solved without taking an outage.

The following demonstration assumes that you have previously run EBR Demo1 (link at page bottom) so the required infrastructure exists. Let's start by creating a stored procedure that runs continuously.
Session 1 Session 2
conn ebradmin/ebradmin

CREATE TABLE cancel (
flag_column NUMBER);

INSERT INTO cancel VALUES (0);

CREATE OR REPLACE PROCEDURE patchProc AUTHID DEFINER IS
 x cancel.flag_column%TYPE;
BEGIN
  FOR i IN 1 .. 100001 LOOP -- 8-10 sec on my laptop
    SELECT flag_column
    INTO x
    FROM cancel;

    IF x = 1 THEN
      EXIT;
    END IF;
  END LOOP;
END PatchProc;
/

set serveroutput on

exec patchProc;
conn ebradmin/ebradmin

UPDATE cancel SET flag_column = 1;
  COMMIT;
 
Editioning for Patching 2: Failed Patching
The following code demonstrates how you can not patch (replace) PL/SQL code while it is in use. You will note how, only after the procedure in session 1 completes does the compilation take place and the source code change can be viewed in USER_SOURCE.
Session 1 Session 2
conn ebradmin/ebradmin conn ebradmin/ebradmin

CREATE OR REPLACE PROCEDURE patchProc AUTHID DEFINER IS
 x cancel.flag_column%TYPE;
BEGIN
  FOR i IN 1 .. 100002 LOOP -- 8-10 sec on my laptop
    SELECT flag_column
    INTO x
    FROM cancel;

    IF x = 1 THEN
      EXIT;
    END IF;
  END LOOP;
END PatchProc;
exec patchProc;  
  /
SELECT text
FROM user_source
WHERE name = 'PATCHPROC';
 
 
Editioning for Patching 3
First Session 2, in the default ORA$BASE edition moves to the new edition and, as above, replaces the procedure but this time does it while it is running without interfering with production users for forcing an outage.

Then the DBA, silently, alters the SYS$USERS service used to connect to the application causing subsequent EBRADMIN logins to log in using the new, DEMO_ED, edition.
Session 1 Session 2
conn ebradmin/ebradmin

SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME')
FROM dual;
conn ebradmin/ebradmin

SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME')
FROM dual;

ALTER SESSION SET EDITION=demo_ed;

SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME')
FROM dual;

CREATE OR REPLACE PROCEDURE patchProc AUTHID DEFINER IS
 x cancel.flag_column%TYPE;
BEGIN
  FOR i IN 1 .. 100003 LOOP -- 8-10 sec on my laptop
    SELECT flag_column
    INTO x
    FROM cancel;

    IF x = 1 THEN
      EXIT;
    END IF;
  END LOOP;
END PatchProc;
exec patchProc;  
  /
SELECT text
FROM user_source
WHERE name = 'PATCHPROC';
SELECT text
FROM user_source
WHERE name = 'PATCHPROC';
  conn / as sysdba

exec dbms_service.modify_service('SYS$USERS', edition=>'DEMO_ED', modify_edition=>TRUE);
SELECT text
FROM user_source
WHERE name = 'PATCHPROC';

conn ebradmin/ebradmin

SELECT text
FROM user_source
WHERE name = 'PATCHPROC';
 
 
Editioning for Patching 4
Clean up both for the ebradmin user and SYS returning the edition for SYS$USER to the installation default.
Session 1 Session 2
conn ebradmin/ebradmin

ALTER SESSION SET EDITION=demo_ed;

DROP PROCEDURE patchProc;

ALTER SESSION SET EDITION=ora$base;

DROP PROCEDURE patchProc;

DROP TABLE cancel PURGE;
conn / as sysdba

exec dbms_service.modify_service('SYS$USERS', edition=>'ORA$BASE', modify_edition=>TRUE);
I hope this small amount of code, written for ODTUG KScope 11 helps you move forward with EBR.
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: © 2012 Daniel A. Morgan All Rights Reserved