Complex Schema Changes

The previous sections explained how to perform a hot patch on a running PLSQL application, and then explored some techniques to facilitate online schema changes without affecting end users. Generally these two techniques allow for many types of upgrades, but there are still a few scenarios that can spoil the online party.

The Unsolved Problem

It was previously mentioned that it is not possible to change the size of an existing column online

There is also the tricky issue of how the old and new versions of the application can seamlessly interact despite referencing a slightly different schema.

The solution here invariably involves another new 11gR2 feature called Cross Edition Triggers.

Growing a Column Online

We know you cannot issue a simple alter table command to re-size a column online as it will invalidate any views and PLSQL units that reference the table. If you have been following along this far, then you already know that to enable your application for online upgrades, the application must only ever access Editioning Views and never looks at the underlying tables directly, and this should offer a clue about how to re-size a column online. There are two choices:

• Add a new column to the existing table
• Create a totally new table

No matter which option is used, an Editioning View can be used to give a consistent view of the table and column names to the application, even though different underlying columns or even tables are being accessed. Infact, once the upgrade is complete and the old version of the application has been disabled the old table can be dropped, or the old column can be marked unusable to tidy things up.

Consider the following simple example. In the current edition (ORA$BASE) create a simple table to hold comments made by users and an Editioning View to reference it.

SQL11G> create table user_comments (user_id integer not null,
                                    comment_txt varchar2(10));

SQL11G> insert into user_comments 
select level, 'Entry '||level
from dual
connect by level <= 100;

SQL11G> commit;

SQL11G> create or replace editioning view user_comments_ev
as
select user_id as user_id, 
       comment_txt as comment_txt 
from user_comments;

Now consider a simple stored procedure that is used to insert rows into this table:

SQL11G> create or replace procedure add_comment(i_user in integer,
                                                i_comment in varchar2)
as
begin
  insert into user_comments_ev (user_id, comment_txt) 
  values (i_user, i_comment);
end;
/

After this "application" is deployed, a problem is noted:

SQL11G> exec add_comment(1, 'too_big_to_fit_in_the_column');

*
ERROR at line 1:
ORA-12899: value too large for column "SAMPLE"."USER_COMMENTS"."COMMENT_TXT" (actual: 28, maximum: 10)
ORA-06512: at "SAMPLE.ADD_COMMENT", line 5
ORA-06512: at line 1

Basically, instead of a bug in the easily patched application code, we have a bug in the schema, as the column is not big enough, causing some transactions to fail. Assuming this is a critical system and an outage is not an option, we need to find a way to widen this column.

Start by creating a new Edition - even though we are not going to change the PLSQL, we will need a new Editioning View.

SQL11G> create edition upgrade_v2;

Now we must remember to switch the session to use the new Edition:

SQL11G> alter session set edition = upgrade_v2;

SQL11G> select sys_context('Userenv', 'Current_Edition_Name')
from dual;

SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------
UPGRADE_V2

Now, add a new column to the existing table. As we saw earlier, this will not affect the current application or lock the table in any way:

SQL11G> alter table user_comments 
        add comment_txt_v2 varchar2(100);

Going forward, this new column is going to become the only place to hold the comment text, and the existing column will cease to be used. Next, create a new Editioning view to expose this new column to the application in place of the old one:

SQL11G> create or replace editioning view user_comments_ev
        as
        select user_id as user_id, 
               comment_txt_v2 as comment_txt
        from user_comments;

Note how the new Editioning view has the same name as the old one, but this time it aliases COMMENTTEXTV2 to the same name as the old column, making this change invisible to the application when the new Edition is enabled.

Now, so long as the application runs in the new edition, it will be able to insert the new longer values into the table, at the same time as the existing application cannot.

SQL11G> alter session set edition = ORA$BASE;

Session altered.

SQL11G> exec add_comment(1, 'too_big_to_fit_in_the_column');

*
ERROR at line 1:
ORA-12899: value too large for column "SAMPLE"."USER_COMMENTS"."COMMENT_TXT" (actual: 28, maximum: 10)
ORA-06512: at "SAMPLE.ADD_COMMENT", line 5
ORA-06512: at line 1

SQL11G> alter session set edition = UPGRADE_V2;

Session altered.

SQL11G> exec add_comment(1, 'too_big_to_fit_in_the_column');

PL/SQL procedure successfully completed.

It would seem that we have successfully fixed this application without an outage, but if you have been paying attention, you will notice a significant problem. Assuming this application is more complex than the single addcomment procedure, any part of it that uses the new Edition will not be able to see any comments inserted by the old Edition and vise-versa. As the two versions of the application need to work together during the cut over, this is not acceptable. Somehow we need to copy all the values from COMMENTTXT into the new column COMMENTTXTV2, without locking the entire table and without missing any rows. If the original version of the application inserts a new row, it needs to insert the new value into both COMMENTTXTV2 and COMMENTTXT. Similarly, the new version needs to backport any inserts into COMMENTTXTV2 to COMMENTTXT where possible.

The Cross Edition Trigger

The Cross Edition Trigger is the final part of the Edition Based Redefinition puzzle. Its job is to solve this exact problem, propagating changes back and forward between two versions of a schema during a hot rollover.

There are quite a few finer details around Cross Edition Triggers, but I will leave them until later and explain the main points now.

There are two types of Cross Edition Trigger:

• Forward Cross Edition Trigger - This 'forwards' changes from the old version of the application to the new version.
• Reverse Cross Edition Trigger - This 'reverses' changes in the new version of the schema back to the old version.

In our example, it is clear we are going to need one of each of these.

The next thing to note is that both Cross Edition Triggers are always installed in the new edition - this makes sense, as we don't want to disturb any part of the old application.

Even though the forward Cross Edition Trigger is in the new Edition, it is only fired by database statements running in older Editions.

The reverse Cross Edition Trigger is only fired by database statements running in the new or newer Editions.

An example should make this clear, so lets create a couple of triggers and see what happens:

SQL11G> create or replace trigger user_comments_fwd_xed_trg
before insert or update or delete on user_comments
for each row
forward crossedition
disable
begin
  if inserting or updating then
    :new.comment_txt_v2 := :new.comment_txt;
  end if;
end;
/

SQL11G> create or replace trigger user_comments_rev_xed_trg
before insert or update or delete on user_comments
for each row
reverse crossedition
disable
begin
  if inserting or updating then
    -- can only insert a max of 10 characters into the
    -- old column.
    :new.comment_txt := substr(:new.comment_txt_v2, 1, 10);
  end if;
end;
/

There are a few things to note:

• The Cross Edition Triggers are created on the table, not on the Editioning View. Infact, they must be created on the table, otherwise an error will occur.
• They look just like a normal trigger, only with the special reverse/forward crossedition keywords.
• They are initially created as disabled

Finishing The Upgrade

Now we have added the new column to the table, added a new Edition to the database containing a new Editioning View and Cross Edition Triggers to propagate changes between each version of the application, but there is still a significant problem.

In a real live application, the usercomments table will contain lots of rows. If the application accesses an existing row using the original live Edition, it will find values in COMMENTTXT. However, if the application accesses the same row via the new Edition, it will find nulls in COMMENTTXT (as COMMENTTXT is exposed to the application via the Editioning View and is actually COMMENTTXTV2). Clearly this is unacceptable, and before the new version can be enabled this needs to be solved.

So how do we copy all the existing COMMENTTXT values into COMMENTTXT_V2 and ensure that none are missed? This is not as easy as it sounds, as the application is live, and new comments are being added all the time, so we need to be careful. The first idea that comes to mind is a simple update statement:

SQL11G> update user_comments
set comment_txt_v2 = comment_txt;

However, it will miss rows that were inserted but not committed when the transaction started. Also, if this is a big table, the statement will run for quite a long time, and any new records added after it started will also get missed.

The new records are easily solved - that is what the Cross Edition Triggers are for, so step 1 is to enable the forward Cross Edition Trigger. As nothing is using the new UPGRADE_V2 Edition as yet, we may as well enable the reverse Cross Edition trigger too:

SQL11G> alter trigger user_comments_fwd_xed_trg enable;

SQL11G> alter trigger user_comments_rev_xed_trg enable;

So now, anything inserted into USERCOMMENTS.COMMENTTXT by the live application will be copied into COMMENTTXTV2.

What about the inserted but uncommitted transactions? Luckily, Oracle 11G introduced yet another new feature - DBMSUTILITY.WAITONPENDINGDML(). If you execute this procedure against the table, it will block until all uncommitted transactions that began before the procedure call was made commit. When that happens, we can safely copy COMMENTTXT to COMMENTTXT_V2 for all rows knowing none will be missed. It may seem like the best way to do this is using the update statement above, but our example is very simple and in reality the transformation may be more complex - the best thing is to use the forward Cross Edition trigger to do this, which can be done by simply updating every row in the table to the same value, eg:

SQL11G> declare
  v_scn integer default null;
begin
  if dbms_utility.wait_on_pending_dml('USER_COMMENTS', null, v_scn) then
    null;
  else
    raise_application_error(-20001, 'Timeout waiting for transactions to complete');
  end if;
end;
/

PL/SQL procedure successfully completed. 

SQL11G> update user_comments
set comment_txt = comment_txt;

101 rows updated.

SQL11G> select user_id, comment_txt, comment_txt_v2
        from user_comments
        where rownum <= 10;

USER_ID    COMMENT_TX COMMENT_TXT_V2
---------- ---------- --------------
      1
      2
      3
      4
      5
      6
      7
      8
      9
     10

10 rows selected.

Well, something certainly went wrong! All the comments have been wiped out, so a rollback is required:

SQL11G> rollback;

The problem, is that we executed the update statement in the new Edition. Remember that a forward cross edition trigger will only fire for DML issued by a session running an older Edition, while the reverse Cross Edition Trigger will fire in the current or newer Edition. In this case, the reverse trigger fired, copying the null value in COMMENTTXTV2 into the COMMENT_TXT column. So, to complete the upgrade switch to the live edition and run the update again:

SQL11G> alter session set edition = ORA$BASE;

SQL11G> update user_comments
set comment_txt = comment_txt;

101 rows updated.

SQL11G> select user_id, comment_txt, comment_txt_v2
        from user_comments
        where rownum <= 10;
   USER_ID COMMENT_TX COMMENT_TXT_V2
---------- ---------- ----------------------
         1 Entry 1    Entry 1
         2 Entry 2    Entry 2
         3 Entry 3    Entry 3
         4 Entry 4    Entry 4
         5 Entry 5    Entry 5
         6 Entry 6    Entry 6
         7 Entry 7    Entry 7
         8 Entry 8    Entry 8
         9 Entry 9    Entry 9
        10 Entry 10   Entry 10

10 rows selected.

Now, and only now can we enable the new version of the application and roll over to the new version.

More Observations

If you are really paying attention, you might have noticed that between enabling the Cross Edition Triggers and doing the table update, some rows may already have been inserted and have both COMMENTTXT and COMMENTTXT_V2 populated. Then the update statement will cause them to be updated by the trigger a second time. This is a key point about Cross Edition Triggers. They must always be designed so that if the same row gets forwarded or reversed many times, it will not corrupt the data.

If you think about it carefully, you will notice there is a bug in the forward Cross Edition Trigger. If the new application inserts a long comment, it is stored in full in COMMENTTXTV2, but is truncated in COMMENTTXT - there is no choice but to truncate it as it will not fit in the column. If the old version of the application then updates the same row, the truncated value will overwrite the longer version. Depending on the application, this could be a tricky problem to solve. A simple solution is to only update COMMENTTXTV2 if :new.COMMENTTXT != :old.COMMENT_TXT, knowing that the old application will still fail to insert longer comments. A revised forward Cross Edition Trigger might be:

SQL11G> create or replace trigger user_comments_fwd_xed_trg
before insert or update or delete on user_comments
for each row
forward crossedition
disable
begin
  if inserting then
    :new.comment_txt_v2 := :new.comment_txt;
  end if;
  if updating then
    -- conditional update of comment_txt_v2
    if :new.comment_txt != :old.comment_txt then
      :new.comment_txt_v2 := :new.comment_txt;
    end if;
  end if;
end;
/

These sort of subtleties demonstrate that running an application against two versions of a schema at once can be difficult, and the best solution is to make the hot rollover period as short as possible.

If the USERCOMMENTS table is large, then the update statement will take a long time to run, and as it is updating every row, it will eventually lock every row in the table, which will surely impact the existing application. There are ways to do this update in chunks, only locking a small number of rows for a short time each. Another new 11G feature can actually make this very simple with the DBMSPARALLEL package.

Switching Over

Once the transformation of the old data to the new format has completed, it is time to initiate the hot rollover. To do this, alter the database so the default Edition is the new one:

SQL11G> alter database default edition = upgrade_v2;

Now all new sessions on the database will use the new Edition, so all that is left is to cause the application servers to reconnect, which is an application dependent step.

Cleaning Up

After all the application sessions have reconnected and are using the new Edition, it is important to clean things up to avoid problems in the future.

First, revoke access to the old edition from all users on the system. If the Edition was only ever granted to public, access can be removed by simply running:

SQL11G> revoke use on edition ORA$BASE from public;

If more detailed grants were given, the query below can find them:

SQL11G> SELECT GRANTEE, PRIVILEGE
        FROM DBA_TAB_PRIVS
        WHERE TABLE_NAME = 'ORA$BASE';

Then disable and perhaps drop the Cross Edition Triggers created in UPGRADE_V2, as they are no longer needed:

SQL11G> alter trigger user_comments_fwd_xed_trg disable;

SQL11G> alter trigger user_comments_rev_xed_trg disable;

In this example, you may also want to consider reclaiming the space used by the now unused column in the table by:

• Updating the old column to null, taking care of locking again.
• Alter the old column to be unusable
• Shrink the table using the ALTER TABLE SHRINK SPACE command.

More on Cross Edition Triggers

The example above outlines almost everything you need to know about Cross Edition Triggers, and if you make sure that a maximum of two Editions are in use at once and remember to disable old Cross Edition Triggers, things will remain quite simple. There are, as usual, some more important details that are good to know.

The Execution Edition

Cross Edition Triggers are Editionable objects, but they can be fired by DML running in a different Edition, infact a forward Cross Edition Trigger must be executed by DML in older Editions. It is important to note that no matter what Edition the DML is executed in, the trigger always executes in the Edition in which it is actual (UPGRADE_V2 in the example here). This is important to know, as if the trigger makes calls to other PLSQL modules it is understood which version of the module it is actually calling.

Trigger Family Trees

Before I mentioned that it is a good idea to disable and remove Cross Edition triggers when a hot roll over has completed. This is because things can rapidly get confusing if the database has many Editions and many Cross Edition Triggers.

The definition of when a forward Cross Edition Trigger will fire is

When DML is executed in ANY ancestor (older) Edition than the Edition the trigger it is actually created in.

Similarly, the definition of when a reverse Cross Edition Trigger will fire is:

When DML is executed in the Edition the trigger is actually created in and ANY child (newer) Editions.

This means that if the old Cross Edition Triggers are left enabled after a hot rollover has completed, then it is easy for confusing things to happen. In general, forward Cross Edition Triggers will not be a problem, as the old Editions should never be used, and should be revoked from use to ensure that is the case. However, any reverse Cross Edition triggers will continue to fire many Editions later and could easily cause unexpected problems with future changes or maybe even cause performance problems.

If many Cross Edition Triggers are in scope in different Editions, then it is important to know the firing order.

Forward Cross Edition triggers fire first in the oldest Edition

This makes sense, as the each change to the schema must be migrated forwards one stage at a time, starting with the oldest transformation.

Reverse Cross Edition Triggers fire first in the newest Edition

The diagram shows four different Editions on the database, and any triggers defined in that Edition are shown in the boxes. If a DML statement is executed against that Edition, the text below the box shows what triggers will fire and in what order. It can get confusing fast, so another rule of thumb is:

A best practice is to ensure a maximum of only two Editions are ever in use in a database to keep things simple.

Advanced Firing Rules and Two Kinds of DML

This is where things get really confusing! Where Cross Edition Triggers are involved, there are actually two types of DML.

DML that is not issued by a Cross Edition Trigger is known as 'normal DML'.

DML that is issued by a Cross Edition Trigger is known as Cross Edition DML (XED DML).

To really confuse things, if a Cross Edition trigger calls a PLSQL helper procedure, any DML issued by the helper procedure becomes normal DML. This includes dynamic SQL generated using DBMS_SQL.

The advanced firing rules are:

• Normal DML will cause regular and Cross Edition triggers to fire.
• XED DML generated by a forward Cross Edition Trigger that is actual in an Edition e will cause further forward Cross Edition triggers to fire in newer Editions, but it will never cause reverse Cross Edition or regular triggers to fire.
• Similarly, XED DML generated by a reverse Cross Edition Trigger that is actual in Edition e, will cause further Reverse Cross Edition triggers to fire in older Editions, but never cause forward Cross Edition or regular triggers to fire.
• XED DML from either a forward or reverse trigger in Edition e does not normally fire other Cross Edition triggers in Edition e.

Expanding on the last rule - you can get XED DML from a Cross Edition trigger in Edition e to fire other cross Edition triggers in Edition e by setting up an ordering relationship between the triggers, if you want to get really confused!

Forcing a Cross Edition Trigger to Fire

Earlier we encountered a problem in the USERCOMMENTS example, in that to get our update statement to fire the Forward Cross Edition Trigger in the UPGRADEV2 Edition (which is required to transform the data), we had to switch to the earlier Edition of the online application.

The DBMSPARSE and the DBMSPARALLEL APIs both provide a parameter called 'applycrosseditiontrigger', which allow for example, a forward Cross Edition trigger which is actual in the current Edition to be specified and hence fired by the SQL statement when it normally wouldn't be.

Cross Edition Trigger Advice

My advice with Cross Edition Triggers and Online Upgrades is:

• Keep it as simple as possible
• Limit the time both applications are running as much as possible
• Try to avoid chains of triggers, which can get confusing fast
• Ensure that only two Editions are ever in use on the database at any time.
• Test, test and test some more away from production

Summing Up

That is it for Edition Based Redefinition. We learned how easy it is to perform an 'hot patch' on a runnning PLSQL application when it requires no schema changes.

Then we explored the constraints around making online schema changes, and how to architect an application to allow it to happen using Editioning Views. Using hot patches and Editioning Views, a lot of changes can be managed online quite easily.

Finally we looked at Cross Edition Triggers. Used sensibly, they can allow almost any change to happen online, but with such power comes great responsibility. It is important to clean up after each upgrade, otherwise things can get very confusing.