A collection of tutorials, code and tools to help you get better using Oracle
16 May 2011
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:
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!
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
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:
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:
As you can see, code patches for the middle-tier of an application can be handled quite easily without an outage in many cases.
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.
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:
If things are structured in this way, then it is possible to upgrade a PLSQL application by:
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.
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.
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.
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.
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.
Clearly, using this built in feature makes hot patching code much easier and faster: