Edition Based Redefinition 1 - Hot Patching PLSQL

Patching a PLSQL application - traditionally this is one thing you are guaranteed to need to do that will incur an application outage.

Anyone who has worked with Oracle for any length of time knows the problems:

• When a PLSQL unit is executing, you cannot compile a new version of it as it will invariably timeout waiting for a lock on the PLSQL object.
• When a PLSQL unit is compiling you cannot run it.
• If you recompile a PLSQL unit then existing sessions will get an "ORA-04068: existing state of packages has been discarded" error when they next try to use it, which is far from ideal. Also, any dependent objects may become invalid, and anything that uses them will get the same error.

The third problem can be managed with careful exception handling, but the first two issues are much more difficult to deal with.

Some time ago, I designed and helped implement a large PLSQL based application consisting of hundreds of PLSQL units and tables. 100% uptime was never a primary concern for this application, but still, upgrading the PLSQL components became a long and difficult process, even for the simplest of changes. Pushing a PLSQL patch onto the live system was impossible without a full outage. After living with this application for a several years, I had reached a worrying conclusion - building a complex application in PLSQL is madness if you require zero downtime during application upgrades!

Application Servers

Thinking more about this, I was being a little harsh. Consider a traditional web application.

Here, a load balancer distributes incoming application requests to a pool of live application servers which implement the application logic and persist information into the database.

Here we have three layers in the application

• The Load Balancer
• The Application Server Pool, perhaps running Java servers
• The database cluster - probably Oracle RAC for a highly available Oracle cluster.

Normally there would be a pool of web servers in front of the application servers but for simplicity they have been left out of the diagram. In this model, application changes can fall into several categories:

• A change to the load balancer setup (I am going to ignore this)
• An application logic change, known as a patch.
• An application upgrade, that will normally involve both code and database changes.

Generally application upgrades are major events planned well in advance and at some sites a full application outage can be permitted.

A more frequent problem is bugs in the application code, or minor upgrades that need to be fixed quickly, and preferably without an application outage. Often these changes don't require any physical database changes, only the application logic needs to be patched.

Working in a traditional web application setup, there are several options to patch the running code:

• Shutdown the application, deploy the new code and restart - this requires a full outage
• Using the load balancer, take one application node out of service and upgrade it and bring it back online. Repeat for each application server. This is known as a rolling upgrade and requires no outage, but does require that the old and new application coexist for a short period and hence can work together.
• Keep the current application running and simultaneously deploy a new set of application server instances. Once they are ready, and perhaps after some sanity testing, use the load balancer to cut the application over to the application servers. This may require a short outage, but normally it can be done with no impact to users.

As you can see, code patches for the middle-tier of an application can be handled quite easily without an outage in many cases.

What about PLSQL?

Now consider PLSQL. The problems mentioned above prevent new PLSQL code from being deploy while the system is running. To allow for patching a running system (known as hot patching), there are at least two solutions, depending if you are running Oracle 11gR2 or not.

Pre 11gR2

Consider the third example of upgrading an application server, where a set of new application servers are built alongside the existing deployment. The same thing can be achieved with PLSQL, provided the application is designed in the correct way:

• The PLSQL and tables used by the application are created in different schemas.
• Within the PLSQL schema, a set of private synonyms may be created to reference each table in the TABLE schema.
• All the application PLSQL must be in the same schema or private synonyms should reference any units which are not. This is so that one PLSQL unit never needs to prefix calls to other units with the schema name and hence the application should not care what schema it is installed in.

If things are structured in this way, then it is possible to upgrade a PLSQL application by:

• Creating a new schema
• Granting all relevant privileges to it
• Creating all required synonyms
• Building the complete PLSQL application in the new schema

The upgrade problem then becomes how to switch to the new schema. For batch jobs, this should be fairly easy - just stop the jobs in the old schema and start them in the new schema. For online users, it may be more tricky, and largely depends on the application. In the web application example above, a rolling restart of the application servers may be required after modifying their configuration to connect to the new database user.

While this sounds like a sound and simple strategy, it largely depends on the application. Some applications are so complex, that frequently switching schema could cause unforeseen problems. However, if you are working with a small to medium sized application, and can design an upgrade strategy like this from day 1, it will probably give good results.

11gR2 - The Future

Oracle 11gR2 has totally changed the game around application upgrades by adding what really is a new killer feature. Enter a new database object known as an Edition and Edition Based Redefinition.

An Edition is like a version control system inside the database, and certain Editionable objects can be attached to one or more Editions. It is important to note that not all database objects are Editionable. Tables and indexes are not, but PLSQL, views, triggers, synonyms and grants are.

Pre 11gR2, any PLSQL unit was uniquely identified by a schema name and object name, eg user1.plsql_proc_1.

Now, a third dimension has been added to editionable objects, in that they are uniquely identified by schema, object name and an edition, allowing two objects of the same name to exist in the same database and schema simultaneously, and this enables edition based redefinition.

Using Edition Based Redefinition

The best way to explore Editions is by way of some examples. First, create a user that is able to use Editions

SQL11G> create user sample
        identified by sample
        temporary tablespace temp
        default tablespace data;

SQL11G> grant connect, resource, alter session, create table, 
create procedure, create any edition to sample;

SQL11G> alter user sample enable editions;

Now login as sample. By default, all new databases will have one Edition created, called ORA$BASE:

SQL11G> select * from dba_editions;

EDITION_NAME                   PARENT_EDITION_NAME            USA
------------------------------ ------------------------------ ---
ORA$BASE                                                      YES

1 row selected.

If you create a PLSQL object in a schema that has Editions enabled, you will be able to see the Edition in the EDITIONNAME column of the ALLOBJECTS view.

SQL11G> create or replace procedure sample_1
as
begin
  null;
end;
/

SQL11G> select object_name, edition_name from all_objects
where object_name = 'SAMPLE_1';

OBJECT_NAME                    EDITION_NAME                  
------------------------------ ------------------------------
SAMPLE_1                       ORA$BASE                      

1 row selected.

If the user sample had not been enabled to use Editions, then the EDITION_NAME column would have been blank.

What is the current Edition?

The next question is, how do you know what Edition your session is currently using?

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

SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')                                   
----------------------------------------------------
ORA$BASE                                                                        

1 row selected.

If there are more Editions defined on the database, you can switch your session to use them with a simple alter session command:

SQL11G> alter session set edition = new_edition_name;

The database will always have a default Edition, and if no special action is taken when a user logs in, that is the Edition the users session will use. The log on process can of course override this with connection parameters, or a simple log on trigger. Determining the default database Edition is simple too:

SQL11G> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE 
PROPERTY_NAME = 'DEFAULT_EDITION';

Now, lets create a slightly more useful procedure:

SQL11G> create or replace procedure print_version
as
begin
  dbms_output.put_line('I am version 1');
end;
/

And then create a new edition:

SQL11G> create edition upgrade_v2;

If you again check what the current edition is, it will still be ORA$BASE at this point. To create a new version of our print_version procedure, first switch to the new edition:

SQL11G> alter session set Edition = upgrade_v2;

Then create a new version of the procedure:

SQL11G> create or replace procedure print_version
as
begin
  dbms_output.put_line('I am version 2');
end;
/

Now, if someone logs in as SAMPLE and runs printversion, they will run version 1 of the procedure, but if they then alter their session to set the edition to UPGRADEV2, they will get the second version:

SQL11G> exec print_version;  

I am version 1

SQL11G> alter session set Edition = upgrade_v2; 

SQL11G> exec print_version;  

I am version 2

Even better, adding the new version of the procedure did not affect the existing version in any way. An application currently running it is not locked or interrupted in anyway when the new version is compiled. This allows a completed upgrade of a PLSQL application to be staged and tested in isolation, while the old version is still running.

Once you are satisfied all the changes are correct, it is time to switch the live version of the application. There are two parts to this operation. First, the easy part is to change the default Edition of the database over to the new version, which can be done with a simple alter database command (executed by a DBA user):

SQL11G> alter database default edition = upgrade_v2;

At this point, all sessions that were connected to the database before the switch are still running using the old Edition. New sessions to connect will however use the new Edition. To complete the upgrade, you need to somehow get the existing application sessions to disconnect and reconnect from the database. The method used to do this is very much application dependent. In the traditional web application model above, a rolling bounce of the application servers may be the simplest option. However, if you are designing a new application to make use of Edition Base Redefinition from day 1, then it may be possible to design a disconnect/reconnect admin option for this purpose.

As the application will gradually start using the new version of the patched PLSQL, it is important to remember that two versions can be executing at the same time, and the application needs to be able to handle that scenario.

At this point, it is still possible to roll the application back to a previous version with a further alter database command, which is another powerful feature when things go wrong.

Actual and Inherited Objects

When an object is created in an Edition, it is said to be 'actual' in that edition. In the example above, PRINTVERSION is actual in both editions. If we had simply created the new upgradev2 edition and switched to it without creating any new objects in it, we can still run PRINT_VERSION even though it is only actual in the old edition - the new edition inherits what it needs from older editions.

If we extended the example, and had two procedures in ORA$BASE, PR1 and PR2, such that PR1 calls PR2 (ie PR1 depends on PR2):

SQL11G> alter session set edition = ORA$BASE;

SQL11G> create or replace procedure PR2
as
begin
  null;
end PR2;
/

SQL11G> create or replace procedure PR1
as
begin
  PR2;
end PR1;
/

Then switch to the new Edition. Remember that PR1 calls PR2, so if we create and compile a new version of PR1 in upgradev2, it can simply inherit PR2 from the older edition where it has not changed, and only PR1 will be actual in upgradev2.

However, if instead we created a new version of PR2 in upgradev2 - PR1 depends on it (because it calls it). Without Editions this would make PR1 become invalid, but PR1 in the old edition cannot be allowed to become invalid, so PR1 must be actualized into edition upgradev2 in an invalid state. This happens automatically when PR2 is compiled.

Now here is the really interesting part. If a PLSQL unit in a newer edition is inheriting from one in an older edition, what happens if the object in the older edition gets recompiled? Well unfortunately the object in the newer edition becomes invalid.

In general this should not be a problem, as you should only be changing objects in newer editions, but it is important to know it can happen. It is also important to be aware of how PLSQL units inherit from each across editions. If you are really worried about it, you can force all dependent objects to be actualized in the current edition by recompiling all of them using 'alter ... compile'. Note that dbmsutility.compileschema() will not actualize objects that are not already actual in the current edition.

Summary

Clearly, using this built in feature makes hot patching code much easier and faster:

• Only the code that needs to be changed needs to be reinstalled, unlike with the pre 11gR2 solution using a new schema
• Rollback is trivial if required
• The current version is not affected in any way.
• The new version of the application can be sanity tested in isolation from the original version.