Re: One vs many databases

John Hunter wrote:
> Hi Gang,
>
> I'm looking at submitting a business case to management that will justify
> changing from our current structure of many oracle databases to one big
> database. We currently run many separate databases (financial, sales,
> purchases etc...) all based on functional areas. These are all inhouse
> written systems. My problem with having all these instances is with trying
> to link data together. We need to have realtime data shared amonst the
> systems. Dblinks are quite slow and although materialized views have lots
> to offer they consume a fair amount of overhead.
>
> Anyway, I've done some web searches looking for the pros and cons of many
> instances vs. one instance and have yet to find a good whitepaper on this
> subject. I did read through the long (70 or so posts) when someone said they
> were going to install 50 instances on one host, but it didn't really answer
> the question.
>
> Thanks,
> -John

IMHO there isn't one - and only one - clear answer to this question. There are a lot of pros and cons - not all of which are technical. Some are simply the way things are in the company and no one is going to set aside a pot of money to fix. I don't think other shops have our requirements ... so what I say is very site-specific ... YMMV.

For example, we keep our data warehouse in a separate database because it has vastly different resource requirements (in SGA size, batch reports, inelegant user-defined ad-hoc queries, etc). We also have a bunch of formerly unused NT workstations that were sitting around with nothing to do, but that do quite nicely with "departmental" databases (that is, databases that don't need a lot of data from other databases). Management didn't want to see them idle. We also have some databases that don't need to be backed up ... and ones that we'd have our fingers chopped off if we didn't. Setting up RMAN scripts to back up only the pieces we want of a single, large db might be more difficult than setting up RMAN scripts only for a few small, whole databases.

If you have an unlimited budget in your company, then go for the big RAC database on a SAN. Kinda reminds me of all the arguments 30 years ago when we were considering switching from the big iron mainframes to DEC VAXen minicomputers. It was almost a religious war between the folks on the single or multiple side of the fence. Now, our Sun box with a couple dozen CPUs can really handle the database we did decide to make "big." Received on Fri Jan 03 2003 - 11:07:37 CST