What are the advantages and disadvantages of having a single instance compared to multiple instances when multiple databases are intended to be created?
+2
A:
You may want to browse the Oracle concept guide, especially if you're more familiar with other DBMS.
- A database is a set of files, located on disk, that store data. These files can exist independently of a database instance.
- An instance is a set of memory structures that manage database files. The instance consists of a shared memory area, called the system global area (SGA), and a set of background processes. An instance can exist independently of database files.
A single instance (set of processes) can mount at most one database (set of files). If you need to access multiple databases, you will need multiple instances. More on the difference between instances and databases on askTom.
Ideally, you only want one instance per server (the server may be a logical server -- i.e a virtual server). This will allow Oracle to know exactly what is going on. This implies one database per server.
If your databases are really independent, going with multiple instances/databases would make sense, since you have greater control over DB version, administration, etc.
If however your databases are not really independent (you frequently share data across them, you need some common data accessible to all of them), it may be more efficient (and simpler) to go with a single consolidated database. Each original database would have its own set of schemas. In this case cross-schema referential integrity would be easy, you wouldn't need to duplicate the data that needs to be shared.
Vincent Malgrat
2010-08-25 14:19:45
You did say *ideally*, but keep in mind, one instance per server will likely cost you more in licensing fees.
DCookie
2010-08-25 16:47:35
I would never, ever run multiple non-internally developed applications in the same Oracle database. I've upgrade/support conflict amongst applications requiring different patchsets / configuration settings, and I've also witnessed the 18-month projects that happen when one has to upgrade, for example, mainframe DB2 from v7 to v8. If you need to share data among applications in such a case, look at Streams/Golden Gate or consider database links.
Adam Musch
2010-08-25 17:13:11
@Adam Musch: agreed, I would also likely run a non-internally developped application on its own database -- support contracts often require this.
Vincent Malgrat
2010-08-25 19:29:47
@DCookie: Yes, *ideally* :). For some databases, performance is not an issue (the server is clearly oversized for example), in that case sharing the server among multiple instances does make sense. The problems arise when performance degrades: most diagnostic reports will be hard to interpret when there is more than one instance on a server. In this case, it may be more interesting to move an instance in its own server rather than add processors (there would be additional lisence cost in both cases)
Vincent Malgrat
2010-08-25 19:37:37
Virtualization (or at least officially-Oracle-blessed virtualization, like Solaris containers) can help mitigate licensure costs. However, it does make tuning the system more challenging when multiple hosted VMs are competing for system resources and people are blaming the database for being slow. One machine. One instance. Unless you've got a big enough, smart enough, well instrumented enough team to manage the infrastructure.
Adam Musch
2010-08-26 12:17:28