RE: Rule of thumb for new schema vs. new database?

Dennis,

        Allow me to disagree. Yes you could upgrade Oracle for one application without the others if you have separate Oracle_Homes for each instance which requires a lot more disk space. Yes you can bring down one instance for maintenance without affecting the others, but your probably bringing it down to increase some parameter that will ultimately affect the other instances. And I've never had a problem moving an application out of an instance and into another even when it's sharing on both sides. That was what export/import was designed for.

        Other problems that I've had with separate instances are:

1. The development manager will ultimately want his/her own server. In one instance the manager wanted his own LAN and ups to boot.
2. Many users will need access to this application as well as others. Keeping passwords in sync is a true pain in the .
3. Applications sooner or later need data from other applications, which means linking your databases together. This is a performance problem from the start, and makes troubleshooting a true pain. It also kills the ability to take one app down for maintenance without affecting anyone else. Also codifying inter-instance data validation is one road you really don't want to head down.

        Now there are good reasons for having dedicated instances, but those I've seen have been causes simply by a lazy development effort or an effort that was misdirected in the first place. I'll take one that shall forever remain nameless. The application at startup for each client needs to look at priviledges granted for the entire database. Yes they created a number of view definitions based on data dictionary tables that HAD to reside in the SYS account. Now while it would share the instance, doing so caused the logon process to extend from it's normal 2 minutes (yes that is the normal expected logon time from the vendor) to as much as half an hour. The bottom line is install the app in a shared test instance. If it behaves badly, think before you leap. The problem could be the database or it could be the app.

        BTW: If it's the app & it was developed in house I've a well used baseball bat that is open for rental. ;-)

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dennis Williams Sent: Wednesday, October 05, 2005 5:16 PM To: brian.peasey_at_gmail.com
Cc: Oracle-L
Subject: Re: Rule of thumb for new schema vs. new database?

Brian

> When would it be preferable to create a new db vs. just a schema in an
> existing db?

Advantages of same instance (db)

• Simpler maintenance, only one instance to keep backed up. This means less DBA time.

Advantages of separate instances (db)

• Can upgrade each application to a new Oracle version separately.
• Can bring an instance down for maintenance without affecting all users.
• Can more easily move one appplication to another server.

Dennis Williams

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