7

I came across the following statement in this article:

An instance is the software (and memory) that Oracle uses to manipulate the data in the database. In order for the instance to be able to manipulate that data, the instance must open the database. A database can be opened (or mounted) by more than one instance, however, an instance can open at most one database.

Why would one create multiple instances of same database?

Mehdi Charife
  • 131
  • 1
  • 12
Parag Meshram
  • 378
  • 2
  • 3
  • 11

4 Answers4

5

For availability (one instance goes down, another instance can continue to work), handling load between nodes (adding more nodes on as needed basis) and possibility to dynamically allocate resources based on needs. That is achieved by using Oracle RAC (Real Application Cluster) where instances are running on separate machines.

In Oracle for version up to and including 11.2, one database can be used by one instance (basic setup) or more instances (Oracle RAC).

It's expected to see paradigm shift for instance vs database with soon-to-be-released Oracle 12c (having more databases inside one instance).

igr
  • 274
  • 1
  • 4
0

Multiple instances against single (same) database is required in such environment where that database is distributed among different systems (either on LAN or WAN) forming cluster so users can access the data transparently.

It is used for high availability of data, providing more security to the users (as each instance may not be shared with other user at some point), load-sharing of data if network traffic becomes high.

Mazhar Bukhari
  • 1
-2

Multiple Oracle Instances are often needed to separate Development, User Testing, and Production. There may also be an instance dedicated for 'training' and /or for special development that cannot be refreshed. Hope this helps

David McCloud
  • 1
-3

There are many reasons to have multiple database instances.

You can tune instances for different stages of development and for production.

Having separate instances allows you to apply specific security requirements or configuration to an instance and re-configuring may require only a restart of that instance instead of the whole system.

The benefits are obviously only proportional to it's neccesity.