Re: Schema/Change Manager product for 100+ cloned instances.

From: Mark Bole <makbo_at_pacbell.net>
Date: Fri, 20 Aug 2004 02:27:30 GMT
Message-ID: <mkdVc.4874$QJ3.2861@newssvr21.news.prodigy.com>

JEDIDIAH wrote:

> Sybrand Bakker <sybrandb_at_hccnet.nl> wrote in message news:<tio9i0ticu2ujmtvgdr3b8fktln3p478om_at_4ax.com>...
> 

>>On 19 Aug 2004 10:01:43 -0700, jedithezealot_at_yahoo.com (JEDIDIAH)
>>wrote:
>>
>>
>>>Where can I find a schema management tool efficient and automated
>>>enough to manage changes between 100+ databases all running the
>>>same application (and thus the same schema)?
>>
>>
>>I don't think running 100+ databases running the same application and
>>same schema has anything to do with database management.
>>You can't blame Oracle for not being capable to maintain an ultimate
>>mess.

> 
> 
>      A small shell script would do better than what Oracle sells for the task.

No direct experience with Change Management Pack here, but I am inclined to agree with the above comment.

Imagine an environment where, due to a "work harder, not smarter" philosophy, there are, not 100+, but nevertheless many copies of an application schema used for quality assurance of all sorts (load testing, system integration, user acceptance testing, beta user training, regression testing, small case testing, large case testing, post-production bug fixes, and so on). As a result, each of these schemas must be at a well-known version that matches the application code (apparently certain Java application servers do quite a bit of "strict typing" when it comes to matching schema objects in the database).

A simple approach that can work well is to maintain a "schema_version" table in your application schema which is used to record each new release (unique version string) as it is applied. The script(s) which correspond to the new release consult the table and decide whether or not to attempt schema DDL for the new release based on what they find. So, using Jedidiah's example, if a column has already been added, there won't be an attempt (and subsequent error) to add it again.

This implies that you can "refresh" the various test databases back to a previous schema version at any time and re-apply the new changes (many ways to do this exist). It also implies that your developers are managing their schema changes the same way they manage their other source code (well, I hope).

--Mark Bole Received on Thu Aug 19 2004 - 21:27:30 CDT