Re: How do you handle Database Configuration Management?

Further to Niall's answer:

There are a number of related aspects for you to manage:
-- creating a clean new schema

• populating a clean new schema with clean, new seed (reference) data
• upgrading an existing schema
• upgrading the seed data
• upgrading the non-seed (customer, transaction) data within an existing schema.

For an internal (bespoke) application you only need to maintain one or two upgrade steps at a time, and you should be able to test extensively on a clone of production (unless you are working for a moronic corporation... which has been known).

For a product with multiple client installs (eg a vendor's application) you will certainly need to maintain an upgrade path across several versions. (eg 1.0 to 1.1. to 1,2 to 2,0 ... and any patches in between). Your testing of each step has to be much more thorough, as you obviously don't have access to all the target schemas to be upgraded.

I always recommend that you provide an upgrade set for each version step, and then a thin wrapper that identifies the start version and applies all necessary upgrades in sequence until it reaches the latest version (or stops on serious error). This means you should record the schema version and upgrade history in the schema itself. I have worked on products that retained the ability to upgrade from any start version 1.0 and up right through 30 plus point versions, over a 5 year life cycle.

Within each upgrade, some operations can be executed in parallel (maybe - either by parallelising specific CTAS steps, or by running independent steps in parallel). Only the application designer knows whether that is (a) worthwhile and (b) predictable across all client sites (which may have different data skewing, partitioning etc).

Sadly I am not aware of any tools which provide everything you need to automate this process. I have used Oracle Designer as an aid to generation of "new schema" and "database upgrade" scripts; but Designer version management (IMHO) is to be avoided like the plague, and certainly Designer won't help you update seed and customer data. However it is relatively simple to provide automated scripts to extract the necessary SQL from your source code control system (eg ClearCase, Synergy, SCCS, whatever) and package it up into a deliverable (eg a tar file or zip file that you can ship to your DBA, client, or other 3rd party who will actually execute the upgrade).

If you have branching application versions, then you will eventually need to control the merging of these upgrade scripts. Try to organise your concurrent developments to be independent of each other (so merge is simply additive). Try and keep this under control of a single team (call them DB designers, DBAs, Design Authorities, or what you like - just so long as they can get and keep a grip on things).

Make sure there is a clear framework for building upgrade scripts; remember to log progress, any errors or warnings, etc, in a consistent and reliable way. For example, personally I prefer to avoid SQL*Plus SPOOL command for the simple reason that a misplaced SPOOL OFF can cause some error output to be lost, and if you are using nested scripts it's not always clear which level is responsible for logging and error detection.

Finally, good luck. Data upgrade is often the Cinderella of the project - but screwing it up can wreck your corporate data assets (or better, your clients' assets). If you're lucky, the upgrade will fail, you'll restore from a backup (that's in your upgrade plan, right?) and you'll just have a red face. If you're unlucky, it all goes through, everyone loves the new version, and a couple of weeks later the accounts don't balance because your CTAS accidentally migrated only 98% of the rows of some table. Test, test and test again!

HTH Regards Nigel

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