We know EBS 12.2 uses EBR(Edition Based Redefinition) to support the online patching mechanism in the database tier. EBS 12.2 online patching uses Cross Edition Triggers, editioned objects , VPDs and Editioned Views to supply the online patching in the DB Tier. It is a little complicated in the first look, but it become a straigthforward routine once you get the idea and make the practices.
In this post, we 'll make a demo to get the idea about what EBS actually does with EBR.
Suppose we want to change a column definition of a table and modify it to be VARCHAR(10) and suppose we want to do this operation online without affecting the running application.
So, just like EBS 12.2 online patching, we will use EBR for this.
Editioning used in online patching is very easy for the editioned object, such as procudure and packages , but it starts to be a little harder when it comes to the non-editioned objects such as Tables.
Anyways, to demonstrate a table defitinion change, first we set our run time edition and create the table to work with.
We use ORA$BASE as the runtime environment.
SQL> alter session set edition=ORA$BASE;
Session altered.
SQL> create table erman as select * from dual;
Table created.
So we created a table named "erman" by using the description and the contents of the "dual" table.
SQL> desc erman;
Name Null? Type
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)
Next, we create an editioning view named OLDERMAN in the ORA$BASE edition to select the dummy column from the table ERMAN..
SQL>CREATE OR REPLACE EDITIONING VIEW OLDERMAN AS
SELECT DUMMY
FROM ERMAN
;
2 3 4
View created.
Next , we create a procudure to use the editioning view to reach the table erman, as we want to show that this procedure will not get affected by our patching work.
SQL> create or replace procedure ermanp1(p_st number, p_end number) is
cursor c1 is
select * from olderman;
k number;
begin
for i in p_st..p_end
loop
insert into olderman(dummy) values (i);
end loop;
end;
2 3 4 5 6 7 8 9 10 11
12 /
Procedure created.
SQL> select status from dba_objects where object_name='ERMANP1';
STATUS
-------
VALID
As you see , we created the procedure above, and saw its status is valid.
Now we modify that the table that the procedure ERMANP1 uses..
First , we try to modify the column directly.
SQL> ALTER TABLE ERMAN MODIFY
(
DUMMY varchar2(10)
); 2 3 4
Table altered.
SQL> select status from dba_objects where object_name='ERMANP1';
STATUS
-------
INVALID
As you see , its status became invalid as we changed the definition of it.
Actually, this is an expected behaviour, as we have changed directly the column that was in use by the editioning view.
So, lets compile the procedure and get back to the point where we start.
SQL> alter procedure ERMANp1 compile;
Procedure altered.
SQL> select status from dba_objects where object_name='ERMANP1';
STATUS
-------
VALID
Let's make this alter in another edition and see what happens.
First, we create a new edition named erman_edition.
SQL> create edition erman_edition;
Edition created.
We change our session to use the erman_edition.
SQL> alter session set edition=erman_edition;
Session altered.
We modify the column dummy in the table ERMAN in the erman_edition.
SQL> ALTER TABLE ERMAN MODIFY
(
DUMMY varchar2(10)
); 2 3 4
Table altered.
SQL> select status from dba_objects where object_name='ERMANP1';
STATUS
-------
INVALID
Ooops.. Again, we see the status of the procedure is invalid..
Why? Altough we changed the definition of the column DUMMY in a different edition, it didnt matter, as the editioning view in the run edition (ORA$BASE) was using the same column. Note that: tables are not editioning objects. That means changing the definition of a table affects all the editions.
So, We need to use another method. A method that EBS 12.2 uses too.
So , we start it again..
We set our edition to ORA$BASE, which is actually our run edition.
SQL> alter session set edition=ORA$BASE;
Session altered.
SQL> select status from dba_objects where object_name='ERMANP1';
STATUS
-------
INVALID
we compile the procedure to get back where we start.
SQL> alter procedure ERMANp1 compile;
Procedure altered.
This time , rather than modifying the column DUMMY directly, we will add a column named DUMMY2 with desired description in to erman table
We set the erman_edition in our session.
SQL> alter session set edition=erman_edition;
We add a colum named dummy2 in to the erman table.
SQL> ALTER TABLE erman ADD (dummy2 varchar2(50));
Table altered.
We create an editioning view in the erman_edition and make it reach the dummy2 column but display it with the column name dummy.
SQL> CREATE OR REPLACE EDITIONING VIEW OLDERMAN AS
SELECT DUMMY2 as dummy
FROM ERMAN
; 2 3 4
That 'is it.. Now we can use olderman edition view in the erman_edition to query the table and it will select the newly created dummy2 column but display it as dummy .
ERMAN_EDITION ( PATCH/OLD EDITION)
SQL> select * from erman;
DUMMY DUMMY2
---------- --------------------------------------------------
X
SQL> select * from olderman;
DUMMY
--------------------------------------------------
select status from dba_objects where object_name='ERMANP1';
STATUS
-------
VALID
ORA$BASE EDITION (RUN EDITION)
SQL> select * from erman;
DUMMY DUMMY2
---------- --------------------------------------------------
X
SQL> select * from olderman;
DUMMY
----------
X
This way, when we 'll make the erman_edition to be the default edition of the database, the newly created session 's plsql codes which uset he olderman editionin view will not see any difference.
This actually means that, the alter can be done online..
But what will happen to the rows, which may be inserted to the real dummy column during our patching operation? That is , as this operation is online, the application will use the ORA$BASE edition until we switch the database 's default edition, so any insert, update or delete done by the application will use the real dummy column, so there is a need for syncronizing the real DUMMY and newly created DUMMY2 columns then..
In order to do this sync, we use a forward cross edition trigger.
We create the fwd cross edition trigger in the erman_edition as follows..
SQL> create or replace trigger erman_trg
before insert or update or delete on erman
for each row
forward crossedition
disable
begin
if inserting or updating then
:new.dummy2 := :new.dummy;
end if;
end;
/ 2 3 4 5 6 7 8 9 10 11
Trigger created.
SQL>alter trigger erman_trg enable;
Once we enable the trigger, we set our environment to ORA$BASE and make a test.
SQL> alter session set edition=ORA$BASE
We insert a row into the table erman using the editioning view , just like a EBR aware application does.
SQL> insert into olderman values('ERMAN');
1 row created.
SQL> commit;
Commit complete.
We see the row is inserted into the REAL DUMMY column.
SQL> select * from olderman;
DUMMY
----------
X
ERMAN
Next ,we change our session's edition to be the erman_edition, and check if the newly inserted row is there.
SQL> alter session set edition=erman_edition;
Session altered.
SQL> alter session set edition=erman_edition;
Session altered.
SQL> SQL>
SQL>
SQL> select * from olderman;
DUMMY
--------------------------------------------------
ERMAN
Yes. The row is there. So the trigger is working.
But What about the old rows, I mean 'X' ?
In order to sync the old rows which are created before the creation of fwd edition trigger, we update the rows in the column with the same values and make the fwd trigger triggered for all rows...
SQL> alter session set edition=ORA$BASE;
Session altered.
SQL> update olderman set dummy=dummy;
2 rows updated.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> alter session set edition=ERMAN_EDITION;
Session altered.
SQL> select * from olderman;
DUMMY
--------------------------------------------------
X
ERMAN
That is it , an easy update triggered the trigger and we are in completely in sync and procedure ermanp1 does not know anything about it..
SQL> select status from dba_objects where object_name='ERMANP1';
STATUS
-------
VALID
SQL> alter session set edition=ORA$BASE;
SQL> select status from dba_objects where object_name='ERMANP1';
STATUS
-------
VALID
In this post, we 'll make a demo to get the idea about what EBS actually does with EBR.
Suppose we want to change a column definition of a table and modify it to be VARCHAR(10) and suppose we want to do this operation online without affecting the running application.
So, just like EBS 12.2 online patching, we will use EBR for this.
Editioning used in online patching is very easy for the editioned object, such as procudure and packages , but it starts to be a little harder when it comes to the non-editioned objects such as Tables.
Anyways, to demonstrate a table defitinion change, first we set our run time edition and create the table to work with.
We use ORA$BASE as the runtime environment.
SQL> alter session set edition=ORA$BASE;
Session altered.
SQL> create table erman as select * from dual;
Table created.
So we created a table named "erman" by using the description and the contents of the "dual" table.
SQL> desc erman;
Name Null? Type
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)
Next, we create an editioning view named OLDERMAN in the ORA$BASE edition to select the dummy column from the table ERMAN..
SQL>CREATE OR REPLACE EDITIONING VIEW OLDERMAN AS
SELECT DUMMY
FROM ERMAN
;
2 3 4
View created.
Next , we create a procudure to use the editioning view to reach the table erman, as we want to show that this procedure will not get affected by our patching work.
SQL> create or replace procedure ermanp1(p_st number, p_end number) is
cursor c1 is
select * from olderman;
k number;
begin
for i in p_st..p_end
loop
insert into olderman(dummy) values (i);
end loop;
end;
2 3 4 5 6 7 8 9 10 11
12 /
Procedure created.
SQL> select status from dba_objects where object_name='ERMANP1';
STATUS
-------
VALID
As you see , we created the procedure above, and saw its status is valid.
Now we modify that the table that the procedure ERMANP1 uses..
First , we try to modify the column directly.
SQL> ALTER TABLE ERMAN MODIFY
(
DUMMY varchar2(10)
); 2 3 4
Table altered.
SQL> select status from dba_objects where object_name='ERMANP1';
STATUS
-------
INVALID
As you see , its status became invalid as we changed the definition of it.
Actually, this is an expected behaviour, as we have changed directly the column that was in use by the editioning view.
So, lets compile the procedure and get back to the point where we start.
SQL> alter procedure ERMANp1 compile;
Procedure altered.
SQL> select status from dba_objects where object_name='ERMANP1';
STATUS
-------
VALID
Let's make this alter in another edition and see what happens.
First, we create a new edition named erman_edition.
SQL> create edition erman_edition;
Edition created.
We change our session to use the erman_edition.
SQL> alter session set edition=erman_edition;
Session altered.
We modify the column dummy in the table ERMAN in the erman_edition.
SQL> ALTER TABLE ERMAN MODIFY
(
DUMMY varchar2(10)
); 2 3 4
Table altered.
SQL> select status from dba_objects where object_name='ERMANP1';
STATUS
-------
INVALID
Ooops.. Again, we see the status of the procedure is invalid..
Why? Altough we changed the definition of the column DUMMY in a different edition, it didnt matter, as the editioning view in the run edition (ORA$BASE) was using the same column. Note that: tables are not editioning objects. That means changing the definition of a table affects all the editions.
So, We need to use another method. A method that EBS 12.2 uses too.
So , we start it again..
We set our edition to ORA$BASE, which is actually our run edition.
SQL> alter session set edition=ORA$BASE;
Session altered.
SQL> select status from dba_objects where object_name='ERMANP1';
STATUS
-------
INVALID
we compile the procedure to get back where we start.
SQL> alter procedure ERMANp1 compile;
Procedure altered.
This time , rather than modifying the column DUMMY directly, we will add a column named DUMMY2 with desired description in to erman table
We set the erman_edition in our session.
SQL> alter session set edition=erman_edition;
We add a colum named dummy2 in to the erman table.
SQL> ALTER TABLE erman ADD (dummy2 varchar2(50));
Table altered.
We create an editioning view in the erman_edition and make it reach the dummy2 column but display it with the column name dummy.
SQL> CREATE OR REPLACE EDITIONING VIEW OLDERMAN AS
SELECT DUMMY2 as dummy
FROM ERMAN
; 2 3 4
That 'is it.. Now we can use olderman edition view in the erman_edition to query the table and it will select the newly created dummy2 column but display it as dummy .
ERMAN_EDITION ( PATCH/OLD EDITION)
SQL> select * from erman;
DUMMY DUMMY2
---------- --------------------------------------------------
X
SQL> select * from olderman;
DUMMY
--------------------------------------------------
select status from dba_objects where object_name='ERMANP1';
STATUS
-------
VALID
ORA$BASE EDITION (RUN EDITION)
SQL> select * from erman;
DUMMY DUMMY2
---------- --------------------------------------------------
X
SQL> select * from olderman;
DUMMY
----------
X
select status from dba_objects where object_name='ERMANP1';
STATUS
-------
VALID
This way, when we 'll make the erman_edition to be the default edition of the database, the newly created session 's plsql codes which uset he olderman editionin view will not see any difference.
This actually means that, the alter can be done online..
But what will happen to the rows, which may be inserted to the real dummy column during our patching operation? That is , as this operation is online, the application will use the ORA$BASE edition until we switch the database 's default edition, so any insert, update or delete done by the application will use the real dummy column, so there is a need for syncronizing the real DUMMY and newly created DUMMY2 columns then..
In order to do this sync, we use a forward cross edition trigger.
We create the fwd cross edition trigger in the erman_edition as follows..
SQL> create or replace trigger erman_trg
before insert or update or delete on erman
for each row
forward crossedition
disable
begin
if inserting or updating then
:new.dummy2 := :new.dummy;
end if;
end;
/ 2 3 4 5 6 7 8 9 10 11
Trigger created.
SQL>alter trigger erman_trg enable;
Once we enable the trigger, we set our environment to ORA$BASE and make a test.
SQL> alter session set edition=ORA$BASE
We insert a row into the table erman using the editioning view , just like a EBR aware application does.
SQL> insert into olderman values('ERMAN');
1 row created.
SQL> commit;
Commit complete.
We see the row is inserted into the REAL DUMMY column.
SQL> select * from olderman;
DUMMY
----------
X
ERMAN
Next ,we change our session's edition to be the erman_edition, and check if the newly inserted row is there.
SQL> alter session set edition=erman_edition;
Session altered.
SQL> alter session set edition=erman_edition;
Session altered.
SQL> SQL>
SQL>
SQL> select * from olderman;
DUMMY
--------------------------------------------------
ERMAN
Yes. The row is there. So the trigger is working.
But What about the old rows, I mean 'X' ?
In order to sync the old rows which are created before the creation of fwd edition trigger, we update the rows in the column with the same values and make the fwd trigger triggered for all rows...
SQL> alter session set edition=ORA$BASE;
Session altered.
SQL> update olderman set dummy=dummy;
2 rows updated.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> alter session set edition=ERMAN_EDITION;
Session altered.
SQL> select * from olderman;
DUMMY
--------------------------------------------------
X
ERMAN
That is it , an easy update triggered the trigger and we are in completely in sync and procedure ermanp1 does not know anything about it..
SQL> select status from dba_objects where object_name='ERMANP1';
STATUS
-------
VALID
SQL> alter session set edition=ORA$BASE;
SQL> select status from dba_objects where object_name='ERMANP1';
STATUS
-------
VALID
