Oracle - side-by-side schema update technology...is there any?

Question

Is there any technology out there that will allow you to do side-by-side updates of production schemas?

The goal is to have zero down time when applying updates to a schema in production. Weblogic 10 has a similar feature for their JEE apps where by you deploy the new version of the app and new connections go to the new app, while the existing connections continue to the old app. When all the old connections complete/timeout, the old app is retired and the new app continues on...zero down time.

Is there something similar in Oracle?

Answer 1

Yes. There is online redefinition package.

DBMS_Redefinition

But I doubt this will give you zero downtime, this doesn't account for every possible change to a schema. This lets you do some table changes. I think you need to define zero and how extensive the changes you want to make. Usually if you change the database, you have to change your client as well. If you changed your database, how would the client switch automatically from the old proc signature to the new proc signature - Instantaneously?

Databases don't work like apps. There either is a FK from tableA to tableB or there isn't... it can't not be there for current connection and exist only for new connection in the same manner as your application can. Databases just aren't the same.

That being said, there is rumor that Oracle is working on package versioning... so you could connect to a specific version of a package to make such a migration simpler. But again... that would work for packages, DBMS_redef would work for tables... but that's not the sum total of your database.

Answer 2

Depends what you mean, or include, in "schema". If you want to add or drop an index, that can be done "in-flight", although it will require a lock which may halt activity for a time. In the latest Oracle versions, it doesn't need to hold the lock for the entire time it takes to build the index, just for a moment to lock in the change. If you have short-duration transactions it shouldn't be noticeable. In some cases that applies to tables as well (eg adding a nullable or default column).

If you use PL/SQL (especially packages), things can be a little more complicated. Enhancements were mooted for 11gR1 to enable the in-flight application upgrade, but it got pushed out and is now expected in 11gR2 (probably out first half next year). In the meantime, a workaround is a multi-schema solution. Say your data sits in one schema ("yellow") and your current application code is running in "blue" schema, you load your new application into "green schema". You switch your connections, one by one, from blue to green. Once your connections are all using "green", you can retire "blue" until your next upgrade (when "blue" becomes the new app and "green" is retired).

If you have a genuine 24/7 system, you'll probably always have to stage some upgrades. For example, add a new column as optional, upgrade the application to set it, then make it mandatory (possibly with some data change script for pre-existing rows).

Answer 3

Oracle release today 11gr2, it has edition-based redefinition: http://download.oracle.com/docs/cd/E11882%5F01/server.112/e10881/chapter1.htm#NEWFTCH1