Edition-Based Redefinition: Revolutionizing Online Application Upgrades

In today's fast-paced digital landscape, businesses need to adapt and upgrade their applications without interrupting their operations. Traditional application upgrades often require downtime, causing inconvenience for users and potential revenue loss for companies. However, with edition-based redefinition (EBR), a revolutionary feature introduced in Oracle Database 11g Release 2, businesses can now perform online application upgrades seamlessly, ensuring uninterrupted availability and a superior user experience.

In this comprehensive guide, we will explore the concept of edition-based redefinition and its significance in achieving high availability during application upgrades. We will delve into the capabilities offered by EBR, understand how it works, and discuss its benefits and use cases. So, let's embark on this journey to discover how edition-based redefinition is transforming the landscape of online application upgrades.

Understanding Edition-Based Redefinition (EBR)

Unlike traditional upgrade methods that require application downtime, EBR allows businesses to maintain uninterrupted availability by utilizing the concept of editions, editioning views, and crossedition triggers.

The Components of EBR

EBR introduces several key components that work together to facilitate online application upgrades:

• Edition: An edition is a version of an application that exists concurrently with other editions. It serves as an isolation mechanism, allowing pre-upgrade and post-upgrade schemas to coexist seamlessly.
• Editioning View: An editioning view is a view that exposes a different projection of a table in each edition. By using editioning views, each edition can see only its own columns, ensuring data privacy and integrity during upgrades.
• Crossedition Trigger: A crossedition trigger propagates data changes made by one edition into the columns of another edition. This ensures that transactions performed by users of the pre-upgrade application are reflected in the post-upgrade application and vice versa during hot rollover periods.

Benefits of EBR

Edition-based redefinition offers several significant benefits for businesses looking to upgrade their applications seamlessly:

• Uninterrupted Availability: EBR enables businesses to perform online application upgrades without interrupting user sessions or causing downtime. Users of the old application can continue their ongoing sessions, while new users can start using the upgraded application simultaneously.
• Safe Code and Data Changes: With EBR, code changes are installed in the privacy of a new edition, ensuring that the pre-upgrade application remains unaffected. Data changes are made safely by writing only to new columns or tables not seen by the old edition, preserving data integrity.
• Isolation and Privacy: Editions and editioning views provide isolation and privacy, allowing each edition to operate independently. This ensures that users can access and interact with only the columns and data relevant to their specific edition, enhancing security and performance.

Implementing EBR: A Case Study

To illustrate the power and effectiveness of edition-based redefinition, let's explore a case study involving the HR sample schema, as shipped by Oracle Corp. In the pre-upgrade application, the schema assumes that the United States is the only country in the world. We will walk through the steps involved in upgrading the HR application to include country codes and within-country phone numbers for all employees.

• Starting Point: The pre-upgrade application is in normal use, with the HR schema lacking country codes and within-country phone numbers.
  PLSQL

   

  $ sqlplus / as sysdba
  
  SQL> ALTER USER HR ENABLE EDITIONS;
  
  create table ebr_test
  (
    taskkey           number generated always as identity not null
   ,task              varchar2(20)   NOT NULL
   ,oname             varchar2(32)   
   ,otype             varchar2(32)
   ,status            varchar2(20)
   ,message           varchar2(128)
   ,op_datetime       date
   ,required_conv     varchar2(1) default 'Y'
  )
  /
  
  BEGIN
     --
     FOR t IN ( select table_name from user_tables
                where table_name not like 'BIN$%' 
                and   table_name != 'EBR_CONVERSION')
     LOOP
        --
        insert into ebr_conversion(task,oname,otype,status,message,op_datetime)
        values('Table Rename',t.table_name,'RENAME','New','Table added in list for rename',sysdate)
        ;
        --
        insert into ebr_conversion(task,oname,otype,status,message,op_datetime)
        values('Editioning View',t.table_name,'CREATE','New','Editioning view after table rename',sysdate)
        ;
        --
     END LOOP;
     --
     commit;
     --
  EXCEPTION when others THEN
  	   rollback;
  	   raise;
  END;
  /

  
  

• Create a New Edition: In EBR, the first step is to create a new edition as the child of the existing one. This new edition will serve as the isolation mechanism for the upgrade.

create table ebr_test
(
  taskkey           number generated always as identity not null
 ,task              varchar2(20)   NOT NULL
 ,oname             varchar2(32)   
 ,otype             varchar2(32)
 ,status            varchar2(20)
 ,message           varchar2(128)
 ,op_datetime       date
 ,required_conv     varchar2(1) default 'Y'
)
/

BEGIN
   --
   FOR t IN ( select table_name from user_tables
              where table_name not like 'BIN$%' 
              and   table_name != 'EBR_CONVERSION')
   LOOP
      --
      insert into ebr_conversion(task,oname,otype,status,message,op_datetime)
      values('Table Rename',t.table_name,'RENAME','New','Table added in list for rename',sysdate)
      ;
      --
      insert into ebr_conversion(task,oname,otype,status,message,op_datetime)
      values('Editioning View',t.table_name,'CREATE','New','Editioning view after table rename',sysdate)
      ;
      --
   END LOOP;
   --
   commit;
   --
EXCEPTION when others THEN
	   rollback;
	   raise;
END;
/

• Create Replacement Columns: Next, we create the replacement columns in the underlying table, ensuring that the editioning view shields the application from this change.
• Modify Application Code: We update the application code to select the new columns and implement the new behavior required for displaying country codes and phone numbers.
• Create Crossedition Triggers: To propagate data changes between editions, we create forward and reverse crossedition triggers. These triggers ensure that transactions performed in one edition are reflected in the corresponding columns of the other edition during hot rollover periods.

BEGIN
   --
   FOR p IN (select object_name,object_type from user_objects 
             where object_type in ('PROCEDURE','FUNCTION','PACKAGE'))
   LOOP
      --
      begin
         --
	 execute immediate 'alter '||p.object_type||' '||p.object_name||' compile' ;
	 --
         insert into ebr_conversion(task,oname,otype,status,message,op_datetime)
	 values('PLSQL Compile',p.object_name,'COMPILE','Success',p.object_type||' Compiled',sysdate);
	 --
      exception when others then
	 --
         insert into ebr_conversion(task,oname,otype,status,message,op_datetime)
	 values('PLSQL Compile',p.object_name,'COMPILE','Error',p.object_type||' Error during recompile',sysdate);
         --
      end;
      --
   END LOOP;
   --
   commit;
   --
END;
/

• Apply Data Transformations: Finally, we apply the necessary data transformations to enable the new application to utilize the upgraded data effectively.
• Complete the Upgrade: Once the hot rollover period is complete, the pre-upgrade edition is retired, and the edition-based redefinition exercise is considered complete.

Achieving High Availability With EBR

High availability is a critical goal in any application upgrade process. EBR, with its seamless online application upgrade capabilities, enables businesses to maintain uninterrupted availability throughout the upgrade process. Unlike traditional high-availability features that are implemented by administrators at the site of the deployed application, EBR empowers application vendors to incorporate high availability directly into their application design.

Conclusion

Edition-based redefinition (EBR) is transforming the way businesses upgrade their applications by enabling seamless online upgrades with uninterrupted availability. By leveraging editions, editioning views, and crossedition triggers, businesses can perform upgrades without interrupting user sessions and ensure the privacy and integrity of data. EBR offers a high-availability solution that empowers application vendors to incorporate upgrade capabilities directly into their application design. By choosing EBR or GoldenGate based on their specific needs, businesses can achieve their upgrade goals while minimizing conflicts and maximizing the user experience. Embrace the power of edition-based redefinition and revolutionize your online application upgrades today.