EBR – Fix The Old Data

EBR is great for implementing application and data structures upgraded with no downtime. But what about the data itself? There are two main areas where data requires fixing:

New Data From The Old Edition

This is the data created in the old edition (ORA$BASE in these blog entries) that is not aware of the data structure and code changes in the new edition (NEW_COL in these blog entries). The code running in ORA$BASE is happily creating records in the DEMO table but it is not putting anything the the NEW_COL column because it is not even aware it exists.

Old Data From The Old Edition

This is the data created before the new edition was even added. This blog entry shows how to handle that.

Fixing The Old Data

The data that was created before the new edition (NEW_COL) was even introduced needs to be updated to be synchronized with new records arriving. This ensures all records will have data in the newly added column. The simplest way to do this is with DBMS_SQL using the APPLY_CROSSEDITION_TRIGGER parameter as shown below.

Remember the cross edition trigger we defined fires for updates.

We’ll run this in the ORA$BASE edition.

SQL> DECLARE
 2     v_curs NUMBER;
 3     v_ret_val NUMBER;
 4   BEGIN
 5     v_curs := DBMS_SQL.OPEN_CURSOR;
 6     DBMS_SQL.PARSE( c => v_curs,
 7                     language_flag => DBMS_SQL.NATIVE,
 8                     statement => 'UPDATE demo SET ts = ts where new_col is null',
 9                     apply_crossedition_trigger => 'set_new_col' );
 10    v_ret_val := DBMS_SQL.EXECUTE(v_curs);
 11    DBMS_OUTPUT.PUT_LINE('Updated ' || v_ret_val || ' Rows');
 12    DBMS_SQL.CLOSE_CURSOR(v_curs);
 13  END;
 14 /
Updated 3572 Rows
PL/SQL procedure successfully completed.

SQL> COMMIT;
Commit complete.
SQL> SELECT COUNT(*)
 2     FROM demo
 3    WHERE new_col IS NULL;
COUNT(*)
----------
         0

The APPLY_CROSSEDITION_TRIGGER parameter made DBMS_SQL fire the cross edition trigger (SET_NEW_COL) for every record returned by the SQL statement provided. This gets the old data in sync with the new!

Thanks for reading.

EBR – Fix The New Data

All of the previous blog entries in this series have focused on getting the application and data structures upgraded with no downtime. Now its time to look at the data itself. There are two main areas where data requires fixing:

New Data From The Old Edition

This is the data created in the old edition (ORA$BASE in these blog entries) that is not aware of the data structure and code changes in the new edition (NEW_COL in these blog entries). The code running in ORA$BASE is happily creating records in the DEMO table but it is not putting anything the the NEW_COL column because it is not even aware it exists. This blog entry shows how to handle that.

Old Data From The Old Edition

This is the data created before the new edition was even added. I’ll explain how to handle that in the next blog entry.

Fixing The New Data

At this point one record has been created in the NEW_COL edition that puts a value in the NEW_COL column.

SQL> SELECT COUNT(*)
 2     FROM demo
 3    WHERE new_col = 1;
COUNT(*)
----------
         1

But the record count getting created in the ORA$BASE edition without a value is steadily increasing.

SQL> SELECT COUNT(*)
 2     FROM demo
 3    WHERE new_col IS NULL;
COUNT(*)
----------
         9

SQL> SELECT COUNT(*)
 2     FROM demo
 3    WHERE new_col IS NULL;
COUNT(*)
----------
        11

We need a way to populate the NEW_COL column even though the ORA$BASE edition is not fully aware of it. This is done using a cross edition trigger as shown below.

-- ensure we are working with the ORA$BASE edition
ALTER SESSION SET EDITION = ORA$BASE;

-- create a trigger to populate the NEW_COL column
CREATE OR REPLACE TRIGGER set_new_col
BEFORE INSERT OR UPDATE ON demo
FOR EACH ROW
REVERSE CROSSEDITION
ENABLE
BEGIN
 :NEW.new_col := 1;
END;

Why did I include UPDATE in the trigger? Check the next blog entry for an explanation.

Now we’ll see the number of records with a value in NEW_COL start to increase.

SQL> SELECT COUNT(*)
 2     FROM demo
 3    WHERE new_col = 1;
COUNT(*)
----------
         7
SQL> SELECT COUNT(*)
 2     FROM demo
 3    WHERE new_col = 1;
COUNT(*)
----------
        11

Thanks for reading.

EBR – Moving To The New Edition

There are a few ways to switch between editions. In this post I’ll present two of them.

After Logon Triggers

This option creates a trigger in the applicable schemas (or the whole database) that specifies the edition to choose. Here is an example:

CREATE OR REPLACE TRIGGER SET_EDITION
AFTER LOGON
ON SCHEMA
BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET EDITION = new_column';
END;

Default Editions

This option sets the default edition for all subsequent logins to the database. It uses the ALTER DATABASE sytnax.

ALTER DATABASE DEFAULT EDITION = new_column;

Remember these only take effect for subsequent logins. Existing sessions are not affected.

In a later post I’ll explain how to set the edition name at the database service level.

Thanks for reading.