| 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. |
