Performing Online Application Upgrade Using the Edition-Based Redefinition Feature

Purpose

This tutorial shows you how to perform an online upgrade of a database application using a new Oracle Database 11g Release 2 feature called Edition-Based Redefinition.

Time to Complete

Approximately 1 hour

Topics

This tutorial covers the following topics:

Overview
Prerequisites
Setting Up The Environment
Prepare For Edition-Based Redefinition
Creating a New Edition
Making The New Edition Your Current Edition
Modifying the Underlined Table
Replacing the Editioning View
Defining a Forward Crossedition Trigger
Defining a Reverse Crossedition Trigger
Bulk Forward Propagation
Verifying The Result Of the Forward Crossedition Trigger
Verifying The Result Of the Reverse Crossedition Trigger
Making the Post-Upgrade Edition Available To All Users
Cleanup
Summary

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

Oracle Database provides you with high availability, with a broad suite of capabilities that help businesses to maintain continuous operations both during unexpected failures and scheduled maintenance activities. To support online application upgrade, Oracle Database 11g Release 2 has enhanced the naming mechanism of database objects by introducing a new database object type called an edition.

Edition is a nonschema database object. Every database session is associated with an edition. When you are upgrading the application, a new edition is created as a child of the existing edition and the child edition inherits all objects from its parent edition. It is in the privacy of this new edition that you apply your upgrade. Edition based redefinition gives you the flexibility to continue referring to objects by the existing schema qualified naming mechanism. The edition name of an object is resolved automatically by Oracle Database.The pre-upgrade application continues to work on the database objects that are associated with the old edition, absolutely unaware of the upgrade. Data changes that the upgrade application makes is made safely by writing only to the new columns or new tables that are not seen by the old edition.

Oracle Database 11g Release 2 introduces a specialized view, editioning view that exposes a different projection of a table into each edition to allow each to see just its own columns. To synchronize post-upgrade editions with the data changes taking place in the pre-upgrade edition (and vice-versa) Oracle Database 11g Release 2 introduces specialized triggers, the crossedition triggers that propagates data changes made by the old edition into the new edition’s columns and vice-versa.

This tutorial displays how editioning views and crossedition triggers facilitate online application upgrade. In this tutorial you split the column PHONE_NUMBER of the employees table into two columns COUNTRY_CODE and PHONE_NUMBER_WITHIN_COUNTRY.

Note: As a developer of an upgrade process, you would be delivering a mechancial way to implement the application upgrade by writing sql scripts. In this tutorial, you use SQL Developer to step through the process.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install Oracle Database 11g.
2.

Install Oracle SQL Developer.
3.

Download and unzip the ebr.zip file into your working directory which contains the files needed to perform this tutorial.

Back to Topic List

Setting Up the Environment

To execute this OBE you need to perform the following steps to set up the environment:

1.

Open SQL Developer. Click on New Connection Icon in the Connection Navigator.

 
2.

Create the sys connection with the following details:

Connection Name: sys
Username: sys
Password: <your_password>
Check the Save Password checkbox
Role: SYSDBA
Connection Type: Basic
Hostname: localhost
Port: 1521
SID: orcl

Click the Test button. Note the Status as Success.



To save the connection, click Save. 


 
3.

Create the hr connection with the following details:

Connection Name: hr
Username: hr
Password: hr
Check the Save Password checkbox
Role: Default
Connection Type: Basic
Hostname: localhost
Port: 1521
SID: orcl

Click the Test button. Note the Status as Success.



To save the connection, click Connect. 


 

Back to Topic List

Prepare For Edition-Based Redefinition

Before you start online application upgrade, you should prepare the application for it using the following steps:

1. Connect to the newly created connection sys. Right click the sys connection and click Connect.

 
2. As the sys user, enable editions for the user hr. Enter the following command and click the Run Script icon.

ALTER USER hr ENABLE EDITIONS;

 
3.

To perform edition-based redefinition, you need to rename all of the tables for a given user. For convention purposes, you can give them names that are similar to the former names. For this tutorial, you can add an underscore to the end of the table name. For example, rename the table employees to employees_.

Connect to the hr user. Right click the hr connection and click Connect.

 
4.

As the hr user, rename the table employees to employees_. Enter the following command and click the Run Script icon.

ALTER TABLE employees RENAME TO employees_;

 
5. As the hr user, Create editioning views on all tables. Name the views with the former name of their base tables. For example, the editioning view on the table employees_ can be renamed to its former actual name employees.

CREATE EDITIONING VIEW employees AS
SELECT
employee_id, first_name, last_name, email, PHONE_NUMBER, hire_date, job_id, salary, commission_pct, manager_id, department_id
FROM employees_;

 

Back to Topic List

Create a New Edition

Create a new edition using the following steps:

1. Select the sys tab to go to the SQL Worksheet for the sys connection.

 
2.

When you create or upgrade to Oracle Database 11g Release 2, you get an edition by the name of Ora$Base. As the sys user, create a new edition, post_upgrade as the child of the current edition, Ora$Base.

CREATE EDITION post_upgrade AS CHILD OF Ora$Base;

 
3.

As the sys user, grant USE on the edition post_upgrade to the user hr.

GRANT USE ON EDITION post_upgrade TO hr;

 

Back to Topic List

Making The New Edition Your Current Edition

Now that your script has created the child edition, make the child edition your current edition using the following steps:

1. Select the hr tab to go to the SQL Worksheet for the hr connection.

 
2.

Make the child edition, post_upgrade your current edition.

ALTER SESSION SET EDITION = post_upgrade;

 

Back to Topic List

Modifying The Underlined Table

Once you are in the post_upgrade edition, your script needs to modify the definition of the table employees_ and add the replacement columns using the following steps:

1.

In your hr connection, use the following ALTER statement to add columns COUNTRY_CODE and PHONE_NUMBER_WITHIN_COUNTRY.

ALTER TABLE employees_ ADD
(COUNTRY_CODE VARCHAR2(5),
PHONE_NUMBER_WITHIN_COUNTRY VARCHAR2(20));

Note : The existing column (PHONE_NUMBER), of the edition Ora$Base is retained in the post_upgrade edition.

 

Back to Topic List

Replacing The Editioning View

Replace the editioning view created in the parent edition by adding the newly created replacement columns to it using the following steps:

1.

In your hr connection, in the child edition, add the columns COUNTRY_CODE and PHONE_NUMBER_WITHIN_COUNTRY to the editioning view employees using the following code:

CREATE OR REPLACE EDITIONING VIEW employees AS
SELECT employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, phone_number_within_country, country_code
FROM employees_;

 

Back to Topic List

Defining a Forward Crossedition Trigger

A forward crossedition trigger updates the post-upgrade columns on the basis of the DML changes that occur in pre-upgrade columns. Use the following steps to create a forward crossedition trigger empl_forward on the table employees_ which is fired when the edition Ora$Base issues a DML on the table employees_.

1.

In SQL Developer, open the fwd_ce.sql file.

 
2.

Press F5 or click the Run Script button to execute the script.

 
3.

In the Select Connection dialog, select the connection as hr.

 

Back to Topic List

Defining a Reverse Crossedition Trigger

A reverse crossedition trigger updates the pre-upgrade columns on the basis of the DML changes made by the post-upgrade application. Use the following steps to create a reverse crossedition trigger empl_reverse on the table employees_ which is fired when the edition post_upgrade issues a DML on the table employees_.

1.

In SQL Developer, open the rev_ce.sql file.

 
2.

Press F5 or click the Run Script button to execute the script.

In the Select Connection dialog, select the connection as hr.

 

Back to Topic List

Bulk Forward Propagation

Use the following steps to split the column of the existing rows in the table.

1.

In SQL Developer, open the bulk_fwd.sql file.

 
2.

Press F5 or click the Run Script button to execute the script and apply the forward crossedition trigger on every row.

In the Select Connection dialog, select the connection as hr.

 

Back to Topic List

Verifying The Result Of the Forward Crossedition Trigger

Use the following steps to verify the result of the forward crossedition trigger.

1.

n SQL Developer, open the file /home/oracle/wkdir/ebr/ver_fwd.sql.

 
2.

Press F5 or click the Run Script button to execute the script.

In the Select Connection dialog, select the connection as hr.

Before inserting or updating the table employees_, empl_forward uses the NEW value of the PHONE_NUMBER column to update the columns COUNTRY_CODE and PHONE_NUMBER_WITHIN_COUNTRY. Therefore, in the post_upgrade edition, when you select PHONE_NUMBER, COUNTRY_CODE, and PHONE_NUMBER_WITHIN_COUNTRY, you see the updated values of PHONE_NUMBER_WITHIN_COUNTRY.

 

Back to Topic List

Verifying The Result Of the Reverse Crossedition Trigger

Use the following steps to verify the result of the reverse crossedition trigger.

1.

In SQL Developer, open the ver_rev.sql file.

 
2.

Press F5 or click the Run Script button to execute the script.

In the Select Connection dialog, select the connection as hr.

When a DML is fired to insert a row or update the PHONE_NUMBER_WITHIN_COUNTRY column of the employees_ table in the post_upgrade edition, the DML results in firing of the empl_reverse trigger. The trigger empl_reverse uses the NEW value of the PHONE_NUMBER_WITHIN_COUNTRY column to update the pre-upgrade column PHONE_NUMBER . Therefore, in the post_upgrade edition, when you SELECT PHONE_NUMBER, COUNTRY_CODE, and PHONE_NUMBER_WITHIN_COUNTRY, you see the updated values of PHONE_NUMBER.

 

Back to Topic List

Making the Post-Upgrade Edition Available To All Users

After the post_upgrade edition starts functioning properly, you need to make it available to all the users using the following step.

1. Select the sys connection tab.

 
2.

Execute the following command:

GRANT USE ON EDITION post_upgrade to PUBLIC;

 

Back to Topic List

Cleanup

This section has been provided to start the processes that you stopped at the beginning of the tutorial.

1.

Open a terminal window and execute the following to recreate the HR user and all its associated database objects:

cd <location of files>/ebr
sqlplus / as sysdba
@hr_main
exit

 

Back to Topic List

Summary

In this tutorial, you learned how to:

Create Editions to facilitate online application upgrade.
Create editioning views to have different logical project of a non-editionable tables.
Create crossedition triggers to properly record transactional data changes that take place during an online application upgrade, across pre and post upgrade edition.
Make the pre-upgrade edition, UNUSABLE. so that all the users use the post-upgrade edition from then on.

Back to Topic List

Place the cursor over this icon to hide all screenshots.