Re: Update schema while new data is coming in

Antje

If you want to keep an application running while it is being upgraded, you need to design the application in the first place - ie in the past :< - so that you can independently upgrade pieces of it. To be able to do this, you generally need to partition your application - certainly by data and posibly by function (or to put it another way, each functional silo needs to (be able to) operate independently and needs to be partitioned (or partitionable).

So in a web sales application (for example)

- your user interacts with the shopping cart app
- he/she is directed to a currently running instance of the app
- you can shut down each instance independently

Integration with other silos (eg accounting, fulfilment) is via loosely coupled interfaces (batch or background programs, or message based interfaces that can wait while the silo is down). Interface formats are either frozen or upwardly compatible (one of the alleged benefits of XML - and RDBMS for that matter - is that consumers of a message - or readers of a table - can ignore any new fields/columns). Note that the partitions are NOT RAC instances (or rather, need not be).You could have multiple schemas in a single database, or schemas spread across several databases (for load, and perhaps for geographic affinity).

This approach is no different in principle to the tyre and exhaust center taking a lift out of commission for replacement. Customers simply drive their autos to an alternative lift. No big deal... as long as you don't do it at peak time.

You should read up about how Amazon and Google deal with scaling and upgrades - all I could find in a hurry was this MS Research paper.

So all in all, to make upgrades work you have to design your application to expect them.

I think there was discussion on this list a while ago about the DBMS_REDEFINITION packages that were intended to make Oracle deal with evolving schemas (ie you can have both old and new schemas coexisting - at least for a while). Not to be confused with XML Schema Evolution in the 11g new features guide.

See http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tables007.htm#ADMIN01514 and http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_redefi.htm#sthref6772.

It's all done by monkeying around with materialised views apparently, so I don't think you'd probably want to use it when restructuring anything very large... My own experience of upgrades is that inevitably there will be times when the "simple" or automated solution doesn't work or isn't practical, and you have to come up with your own way of changing horses in mid stream without falling off. That will always be application specific. Sometimes there just may not be a (cost-effective) way apart from stop, upgrade, restart.

Good luck!

Regards Nigel

On Nov 12, 2007 3:47 AM, <Antje.Stejskal_at_ppimedia.de> wrote:
> Hi,
>
> we are having a web application that ist o be online availyble 7x24. We plan
> for the scenario that the application needs a software update (new tables ,
> modified tables et. al.) Management and customer requires that the
> application is still running while the upgrade goes on. ...

--
http://www.freelists.org/webpage/oracle-l