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
>
>
>
>

I don't know of any paper about this subject, but I can give you my 2 cents...

Against single database:
- If you have to do maintenance that requires downtime for one database, all will have to go down as well (downtime for one means downtime for all). - Resource usage may become tricky. One badly tuned system can bring your performance down in all databases (e.g., consuming all buffer cache). - Each system may have different resource consumption profiles so they would benefit from different init parameters (even better if it's in different servers).

For single databases:
- Data sharing becomes child's play.
- User management is simpler (for users that must connect to more than one system, a single account would do).
- Patch installation and upgrade is easier.

Even though there are benefits, I (my humble opinion) would avoid combining all different systems into a single database. A database link is not the only method of data sharing. I've been using Perl and it works perfectly for this kind of job (although you may have to develop the skills in your team).

Best regards. Received on Fri Jan 03 2003 - 21:06:41 CST