Edition-Based Redefinition, Part 2

Our technologist continues to explore Edition-Based Redefinition, with a full schema change.

By Tom Kyte

March/April 2010

In the last issue of Oracle Magazine (January/February 2010), I began to describe the feature I consider to be the killer feature of Oracle Database 11g Release 2: Edition-Based Redefinition. As you may recall from the last issue, Edition-Based Redefinition is the ability to perform an online application upgrade of your application.

Oracle Database has historically allowed for many things to be done online, such as the following:

• Modify most parameters (only 90 out of 350 are not modifiable online)

• Reorganize objects—turn a nonpartitioned table into a partitioned one, reclaim free space, and so on

• Create indexes

• Apply database patches with Oracle Real Application Clusters (Oracle RAC)

• Upgrade Oracle Database from major release to major release

And now Oracle Database 11g Release 2 adds “upgrade your application” to that list of online operations.

Last time, in “A Closer Look at the New Edition,” we looked at a simple scenario. We had version 1.0 of our application installed in the database and wanted to patch this application; specifically, we needed to correct one of the PL/SQL units. Typically, the DBA or application administrator would have to schedule a maintenance window to perform this patching, because that person would be recompiling code. As most DBAs know, you cannot compile a PL/SQL unit while someone else is executing it. Additionally, even if you do succeed in recompiling that package, your application users will suddenly be telling you about the “ORA-04068: existing state of packages has been discarded” error messages they are getting. These circumstances make an online upgrade or patch of your application impossible in real-life production situations. This is what Edition-Based Redefinition changes: we can now patch an application online while end users are executing the old version of the code.

If you have not read the previous column in this series, I encourage you to do so now before reading this column. In this column, I’m going to assume that you have been introduced to Edition-Based Redefinition, understand the basic capability, and have walked through the previous column’s example.

In this installment, we are going to go a step further than we did last time. In the previous column, our application upgrade was a simple patch—we replaced and tested a PL/SQL unit in real time without our end users’ incurring downtime. This time we not only want to replace some objects whose type is editionable (PL/SQL units in last issue’s example) but would like to facilitate a physical schema change as well. Our goal this time around is to minimize downtime as much as is reasonable while implementing code and physical schema changes.

As you may recall, in Oracle Database 11g Release 2, all object types fall into one of two categories: editionable and noneditionable. The following object types are editionable:

• Synonyms

• Views (including editioning views, which we’ll define below)

• All PL/SQL object types (functions, procedures, packages, and so on)

All other types of database objects—such as tables, for example—are specifically noneditionable. But that fact does not prohibit us from creating the illusion (for our application) that noneditionable objects are “versionable.”

Let’s begin by laying out the example we’ll be looking at this time. The standard sample schema HR has an EMPLOYEES table. This master list of all employees of a company includes attributes such as their first names, last names, e-mail addresses, and phone numbers. We’ll pretend that this table has historically been part of a U.S.-based company’s application, so the phone numbers are stored in a format that a U.S.-based company would recognize easily. U.S. phone numbers are stored with just an area code and then the seven-digit number, such as 650.507.9876. International numbers are stored in a format that has the U.S. “escape” code (for dialing an international number), followed by the country code and then the phone number—for example, 011.44.1644.429262.

This fictitious company has been purchased and is now part of a global company. To adhere to the standards of the new parent company, the purchased company must change how it stores phone numbers. It will now store and display all numbers in two fields: a country code field and a phone number field. So for example, using two sample phone numbers, the before and after storage will be as follows:

Now this small change (but there is no such thing as a small change, really!) involves a couple of steps. Specifically, we have to

• Modify the schema so the EMPLOYEE table has two new columns: COUNTRY_CODE and PHONE#

• Modify the schema so the EMPLOYEE table doesn’t have a PHONE_NUMBER column anymore

• Create a new index on PHONE# for a search screen (and maybe other indexes as well, but we’ll stick with just one index)

• Mass-move the existing data from PHONE_NUMBER to COUNTRY_CODE and PHONE#

• Replace the application code in the database that is reliant on the PHONE_NUMBER column

Any one of these steps could take a significant amount of time; the ones that will consume the vast preponderance of the time will be the index creation, data migration, and code replacement. Our goal in this example is to minimize any downtime incurred by this application upgrade, so we’d like to do all these steps while version 1.0 of the application is up and running and stage all the changes in the database for version 2.0 of the application. And we’d like to reduce downtime to just the amount of time it takes to switch over to the new application (an ALTER DATABASE operation will do that for us). In short, the downtime for this upgrade should be measured in seconds , not minutes or hours.

Let’s look at the challenges facing us, then. The first thing we need to do is add two new columns and remove an existing column. We need to add the columns in a manner that will not affect the existing application. What if the application code does a SELECT * FROM EMPLOYEES? That is a bad coding practice, but it might happen nonetheless. Or maybe the application has code such as INSERT INTO EMPLOYEES VALUES (. . . ) with no list of columns (another really bad practice). Adding two new columns could break the existing application. Likewise, we need to drop a column that the existing application is currently using (so obviously, we cannot really drop it).

To deal with conundrums of this nature, Oracle Database 11g Release 2 introduced the concept of an editioning view, which provides a buffer between the application code and the physical schema itself. An editioning view is a special kind of view that can only perform a SELECT on a single table and project onto that table a list of attributes it wants. There can be no WHERE clause and no joining—just SELECT and FROM. Think of an editioning view as being very much like a SYNONYM, but one that can not only rename what it is pointing to but also choose the columns and what to name them. Further, this editioning view can have triggers defined on it—not INSTEAD OF triggers like those a typical view would have, but all the regular table trigger types, such as BEFORE, BEFORE EACH ROW, and compound triggers. So, we can use these editioning views to hide the two new columns from the existing application, and we can use a new editioning view in our new application code base to hide the old column (as if we had dropped it) and expose the two new columns.

What other challenges face us for this simple “add some new columns and drop an existing column” scenario? Well, there is the issue of blocking and locking in the database. In the past, to add a new column to a table, we needed an exclusive lock on the table. If you have ever tried to add a new column to an active table, you may be intimately familiar with this issue:

SQL> alter table emp add resume blob;
alter table emp add resume blob
            *
ERROR at line 1:
ORA-00054: resource busy and acquire
with NOWAIT specified

It often used to be impossible to perform data definition language (DDL) operations on an active system. However, in Oracle Database 11g Release 1 and in some cases in Release 2, the ability to perform DDL on an active system has been greatly enhanced. Many DDL operations, such as adding a column, can now be done in a nonblocking fashion, or alternatively, if the DDL does need to be done in isolation, we can avoid the ORA-00054 error message by using DDL that waits (see “On Seeing Double in V$SQL,” Oracle Magazine, May/June 2008, for a write-up on that).

Another issue that arose in the past with DDL modifications such as “add column” was that dependent objects became invalid: any view on the table would become invalid, any PL/SQL unit dependent on the table would become invalid, and so on. This prevented you from adding the column in an online fashion. But, given the new Oracle Database 11g Release 1 fine-grained dependency tracking (see the “Reason #13134213 Not to Use SELECT *” section in “On Popularity and Natural Selection,” Oracle Magazine, July/August 2009), we have tackled the first issue—how to add the two new columns safely—without invalidating any existing code. We hide the columns from the existing application with editioning views, which are protected from invalidation by fine-grained dependency tracking. In addition, we can now virtually drop the existing column, using the editioning view, while exposing the new columns to the latest version of the application, also by using editioning views.

What about creating an index on the PHONE# column now? There are two issues: creating the index and having to worry about this index affecting the query performance of the existing system. Adding a new index could improve the response times of some queries, not affect other queries at all, and have a negative impact on the performance of yet a third group of queries. In the past, prior to Oracle Database 11g Release 1, a CREATE INDEX statement always required an exclusive lock on a table—even a CREATE INDEX ONLINE operation would need such a lock momentarily at the beginning of the index creation. Starting in Oracle Database 11g Release 1, however, creating an online index is entirely nonblocking. It requires no locks at all, so the locking/blocking issue is obviated. (Note: CREATE INDEX ONLINE operations are a feature of Oracle Database, Enterprise Edition.) Additionally, an index can now be created as invisible —meaning that it will exist and be maintained by modifications but will not be used as an access path unless a session explicitly asks for invisible indexes to be used. Therefore, we can now add an index on the PHONE# column without blocking/locking issues and can ensure that the addition of any new index will not have an impact on the performance of an existing query, a query we perhaps haven't tested with this new index in place. So we can add this index safely without affecting the existing version of the application.

Next we have the really hard job: the mass data migration. We need to move the data from the PHONE_NUMBER column into the two new columns. Additionally, we need to consider what happens to rows that are inserted and updated by the existing version of the application while we are doing this application upgrade. It will not work just to do a single mass update to the EMPLOYEES table to move the data over, for two reasons:

• A single mass update would lock the entire table, which would not be very “online.”

• If the single mass update was complete and the existing application inserted a new row or modified the PHONE_NUMBER column in an existing record, those changes would not be reflected in the new columns, so we would lose the changes.

Fortunately, we can resolve both problems. The first one—the locking of the entire table with a single mass update—will be dealt with by a new package, DBMS_PARALLEL_EXECUTE (we looked at that feature, which I nicknamed “do-it-yourself parallelism” in “Looking at the New Edition,” Oracle Magazine, November/December 2009). Using DBMS_PARALLEL_EXECUTE, we’ll be able to update every row in the table in tiny chunks, using whatever degree of parallelism we desire—anywhere from parallel 1 (serial) to 1,000. This will reduce the amount of data involved and the time any chunk of the table is locked. If we broke the table up into 100 chunks and used parallel 1, we would lock no more than 1 percent of the table at a time; if we broke the table into 1,000 chunks, we would lock no more than 0.1 percent of the table at a time; and so on.

The second problem—continuously migrating data from the existing application to the new application—is more problematic. We have to somehow teach the existing application to maintain the data for the new version, but we have to do so in a manner that doesn’t require changing the existing version! Sounds like we need “magic”—so magic it is. This magic is in the form of a new type of trigger: a crossedition trigger. Crossedition triggers will be used only during the application upgrade process (and we’ll drop them as soon as possible after the upgrade process is complete). Crossedition triggers can be used to “forward” a change from the old version of the application to the new version. They can also be used to “reverse” a change made by the new application back to the old application schema. (We’ll use a reverse crossedition trigger in the next and final installment of this Edition-Based Redefinition series.)

And last we have to replace the existing code of the application to take advantage of the new schema—without disturbing the existing application. Fortunately, we saw how easy that is to accomplish last time, when we did just that. We’ll repeat that process again for completeness.

Before we can begin the process of adding and removing columns, we must introduce the editioning views—the buffer between the application and the physical schema. This will involve an outage, a one-time outage that will never need to be repeated for your application.

TIP: In the future, as you develop your new applications, you can avoid this outage entirely by using editioning views in your application code from the very beginning. This strategy will allow you to rename and reorder columns in your table in a very trivial fashion and will permit online application upgrades coupled with physical schema changes.

We need a one-time maintenance window for sliding in the editioning view. The general steps for the process are as follows:

• Rename the existing table, because the editioning view will be taking over the old name.

• Create the editioning view, giving it the name of the original table.

• Drop any triggers on the existing table, and move them to the editioning view. This step is recommended because the CREATE TRIGGER statements you have refer to the table by name directly. You’ll want the triggers on the editioning view that now has the name of the original table.

• Re-create the triggers on the editioning view.

• Revoke privileges from the base table, and re-grant them on the editioning view.

• Take other steps, such as moving a fine-grained access control policy from the base table to the editioning view.

Our application has no triggers or grants, so we’ll be able to skip a few steps. But in general, the above process is what you’ll execute to get ready to use Edition-Based Redefinition to perform not only application code patches but also physical schema updates.

We’ll assume that we have the DEMO account that was set up and used in the last column. As you may recall, the DEMO account was a normal schema with just CREATE SESSION and CREATE PROCEDURE as well as the ability to create editions of its schema (ALTER USER DEMO ENABLE EDITIONS) and to use an existing edition named VERSION2 (GRANT USE ON EDITION VERSION2 TO DEMO). In this example, we’ll grant the DEMO account the ability to create a table and a sequence so we can copy the EMPLOYEES table and we’ll grant the ability to create views and triggers, but otherwise the set of privileges will remain the same.

Let’s start with a look at the existing version 1.0 application setup:

SQL> create table
  2    employees
  3    as
  4    select *
  5      from hr.employees;
Table created.
SQL> create sequence emp_seq
  2    start with 500;
Sequence created.

That is the copy of the data we’ll be using; the sequence was created to start with a value higher than any existing value in the EMPLOYEES table for this demonstration. The existing application code will perform two functions: show a report of EMPLOYEES with their phone numbers and e-mails when given a search string and hire a new employee—modifying the existing table. The specification for the package is shown in Listing 1.

Code Listing 1: emp_pkg specification for application version 1.0

SQL> create or replace package emp_pkg
  2    as
  3      procedure show
  4      ( last_name_like in employees.last_name%type );
  5
  6      function add
  7      ( FIRST_NAME in employees.FIRST_NAME%type := null,
  8        LAST_NAME in employees.LAST_NAME%type,
  9        EMAIL in employees.EMAIL%type,
 10        PHONE_NUMBER in employees.PHONE_NUMBER%type := null,
 11        HIRE_DATE in employees.HIRE_DATE%type,
 12        JOB_ID in employees.JOB_ID%type,
 13        SALARY in employees.SALARY%type := null,
 14        COMMISSION_PCT in employees.COMMISSION_PCT%type := null,
 15        MANAGER_ID in employees.MANAGER_ID%type := null,
 16        DEPARTMENT_ID in employees.DEPARTMENT_ID%type := null  )
 17      return employees.employee_id%type;
 18    end;
 19    /
Package created.

And the implementation is rather straightforward: a simple “show” routine and a simple “add a new employee” transaction, as shown in Listing 2.

Code Listing 2: emp_pkg body for application version 1.0

SQL> create or replace package body emp_pkg
  2    as
  3
  4    procedure show
  5    ( last_name_like in employees.last_name%type )
  6    as
  7    begin
  8        for x in
  9        ( select first_name, last_name,
 10                 phone_number, email
 11            from employees
 12           where last_name like
 13                 show.last_name_like
 14           order by last_name )
 15        loop
 16            dbms_output.put_line
 17            ( rpad( x.first_name || ' ' ||
 18                      x.last_name, 40 ) ||
 19              rpad( nvl(x.phone_number, ' '), 20 ) ||
 20              x.email );
 21        end loop;
 22    end show;
 23
 24    function add
 25    ( FIRST_NAME in employees.FIRST_NAME%type := null,
 26      LAST_NAME in employees.LAST_NAME%type,
 27      EMAIL in employees.EMAIL%type,
 28      PHONE_NUMBER in employees.PHONE_NUMBER%type := null,
 29      HIRE_DATE in employees.HIRE_DATE%type,
 30      JOB_ID in employees.JOB_ID%type,
 31      SALARY in employees.SALARY%type := null,
 32      COMMISSION_PCT in employees.COMMISSION_PCT%type := null,
 33      MANAGER_ID in employees.MANAGER_ID%type := null,
 34      DEPARTMENT_ID in employees.DEPARTMENT_ID%type := null
 35    )
 36    return employees.employee_id%type
 37    is
 38        employee_id  employees.employee_id%type;
 39    begin
 40        insert into employees
 41        ( EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
 42          EMAIL, PHONE_NUMBER, HIRE_DATE,
 43          JOB_ID, SALARY, COMMISSION_PCT,
 44          MANAGER_ID, DEPARTMENT_ID )
 45        values
 46        ( emp_seq.nextval, add.FIRST_NAME, add.LAST_NAME,
 47          add.EMAIL, add.PHONE_NUMBER, add.HIRE_DATE,
 48          add.JOB_ID, add.SALARY, add.COMMISSION_PCT,
 49          add.MANAGER_ID, add.DEPARTMENT_ID )
 50        returning employee_id into add.employee_id;
 51
 52        return add.employee_id;
 53    end add;
 54
 55    end;
 56    /
Package body created.

Now we can review how this application works in SQL*Plus, as shown in Listing 3.

Code Listing 3: Running application version 1.0 in SQL*Plus

SQL> exec emp_pkg.show( '%K%' );
Payam Kaufling   650.123.3234        PKAUFLIN
Alexander Khoo   515.127.4562        AKHOO
Steven King      515.123.4567        SKING
Janette King     011.44.1345.429268  JKING
Neena Kochhar    515.123.4568        NKOCHHAR
Sundita Kumar    011.44.1343.329268  SKUMAR
PL/SQL procedure successfully completed.
SQL> begin
  2      dbms_output.put_line
  3      ( emp_pkg.add
  4        ( first_name      => 'Tom',
  5          last_name       => 'Kyte',
  6          email           => 'TKYTE',
  7          phone_number    => '703.123.9999',
  8          hire_date       => sysdate,
  9          job_id          => 'IT_PROG' ) );
 10    end;
 11    /
500
PL/SQL procedure successfully completed.
SQL> exec emp_pkg.show( '%K%' );
Payam Kaufling   650.123.3234        PKAUFLIN
Alexander Khoo   515.127.4562        AKHOO
Janette King     011.44.1345.429268  JKING
Steven King      515.123.4567        SKING
Neena Kochhar    515.123.4568        NKOCHHAR
Sundita Kumar    011.44.1343.329268  SKUMAR
Tom Kyte         703.123.9999        TKYTE
PL/SQL procedure successfully completed.

Let’s prepare our schema to allow for an online application upgrade that includes physical schema updates. Remember, this involves one outage—the last outage ever—to put the editioning views in place. In this case, creating the editioning view involves the following:

SQL> alter table employees
  2    rename to employees_rt;
Table altered.
SQL> create editioning view employees
  2    as
  3    select
  4      EMPLOYEE_ID, FIRST_NAME,
  5      LAST_NAME, EMAIL, PHONE_NUMBER,
  6      HIRE_DATE, JOB_ID, SALARY,
  7      COMMISSION_PCT, MANAGER_ID,
  8      DEPARTMENT_ID
  9    from employees_rt
 10   /
View created.

That’s it. Once that is done, we are online again. Furthermore, the existing application will be 100 percent unaffected by this; the editioning view we put in place looks and behaves just like a table. The existing application runs as before.

Now we are ready to add and index our columns in preparation for the new application release:

SQL> alter table employees_rt
  2    add
  3    ( country_code varchar2(3),
  4      phone# varchar2(20)
  5    )
  6    /
Table altered.
SQL> create index employees_phone#_idx
  2    on employees_rt(phone#)
  3    ONLINE INVISIBLE
  4    /
Index created.

The addition of the columns is an online operation, starting with Oracle Database 11g Release 1. Further, note how the index is created as ONLINE (truly online in Oracle Database 11g Release 1 and above) and INVISIBLE. In this particular example, the INVISIBLE setting probably isn’t technically necessary, because the PHONE# column is never referenced in any way, shape, or form by the existing application—it is included for completeness here.

Now we have the task of migrating the data from the existing edition to the new edition. To accomplish this, we will rely on a forward cross-edition trigger, the trigger we’ll use to make sure that data that is inserted or updated by the existing edition makes its way to the new edition. We’ll use this trigger not only to capture the changes made by the legacy application but to do the mass move as well. That is, we’ll code the logic to maintain the COUNTRY_CODE and PHONE# columns from the legacy PHONE_NUMBER column (we have to do that for updates in the existing application to forward the changes to the new schema already) and use that logic to move the data. Once the forward crossedition trigger is in place, it will be a simple matter to issue “update employees set phone_number = phone_number,” using the DBMS_PARALLEL_EXECUTE package to move the data. Listing 4 shows the forward crossedition trigger.

Code Listing 4: The crossedition trigger

SQL> alter session set edition = version2;
Session altered.
SQL> create or replace trigger employees_fwdxedition
  2    before insert or update of phone_number on employees_rt
  3    for each row
  4    forward crossedition
  5    declare
  6        first_dot  number;
  7        second_dot number;
  8    begin
  9        if :new.phone_number like '011.%'
 10       then
 11            first_dot
 12               := instr( :new.phone_number, '.' );
 13            second_dot
 14               := instr( :new.phone_number, '.', 1, 2 );
 15            :new.country_code
 16               := '+'||
 17                  substr( :new.phone_number,
 18                            first_dot+1,
 19                          second_dot-first_dot-1 );
 20            :new.phone#
 21               := substr( :new.phone_number,
 22                            second_dot+1 );
 23        else
 24            :new.country_code := '+1';
 25            :new.phone# := :new.phone_number;
 26        end if;
 27    end;
 28    /
Trigger created.

A couple of things to note about this trigger are that

• We created this in our “to be” edition, the new application schema (VERSION2). The ALTER SESSION was necessary for that. Our goal is to not disturb the existing application, so we’ll do our editioning work in the new edition only. You have to create crossedition triggers in the “to be” edition—never in the existing legacy application edition.

• On line 4 of the trigger, you see the “magic”: FORWARD CROSSEDITION. That clause means that this trigger will be fired only by INSERT, UPDATE, or DELETE statements whose current edition is the legacy edition—in other words, statements issued by the legacy application.

Now we are ready to do our mass move. It can be done via a simple update:

SQL> update employees
set phone_number = phone_number;
109 rows updated.

But remember, that will tend to lock the entire table, because it updates every row, and that is one thing we are trying to avoid. In any event, however, any modifications—any inserts or updates performed by the existing legacy application—will now forward their changes into the new columns for us.

In the next issue, we’ll finish off this example by demonstrating how to

• Perform the above mass update piece by piece, in chunks, so as to not lock very much of the table at any given time—how to do an online mass update, so to speak

• Install the new application code, including the new editioning view for the new application install

• Perform the cutover to the new application and clean up the remnants of the legacy application—the code and columns that are no longer necessary

After that I’ll give an alternative ending to this example. Instead of simply “shut off the old version and start using the new version”—a warm cutover —we’ll perform a hot rollover , whereby we’ll enable the legacy application to run concurrently with the new application until the legacy application is no longer needed. This hot rollover capability reduces downtime from seconds to nonexistent. When we are done with that, we’ll have seen how to use Edition-Based Redefinition to

• Patch a live system without downtime (from the first installment)

• Minimize downtime—down to seconds—during an application upgrade, inclusive of physical schema changes and bulk update operations

• Remove downtime entirely

If you simply cannot wait for the final installment in the next issue, I encourage you to check out otn.oracle.com/deploy/availability/pdf/edition_based_redefinition.pdf for Edition-Based Redefinition, a white paper by Bryn Llewellyn. Llewellyn continues to be Oracle’s PL/SQL product manager and now adds Edition-Based Redefinition to his list of responsibilities. Chapter 19 of Oracle Database Advanced Application Developer’s Guide 11g Release 2 (11.2) contains a complete Edition-Based Redefinition tutorial as well.

ASK Tom
Tom Kyte answers your most difficult technology questions. Highlights from that forum appear in this column.
asktom.oracle.com

READ more Tom
Expert Oracle Database Architecture 9i and 10g Programming Techniques and Solutions
Oracle Database Concepts 11g Release 2 (11.2)

READ more about
Edition-Based Redefinition
Edition-Based Redefinition
“A Closer Look at the New Edition”
Oracle Database Advanced Application Developer’s Guide 11g Release 2 (11.2)

DOWNLOAD Oracle Database 11g Release 2