Problem solve Get help with specific problems with your technologies, process and projects.

Creating more than one instance for a database

Can I create more than one instance for a database? If so, what is the use of it? Can you throw some light on this topic, like when it is useful, why do we need to create it and things like that?

Before I can answer this question, I should probably clear up some Oracle terminology. This differs from other RDBMS vendor's terminology.

In Oracle-speak, a database is basically the datafiles on physical disk. An instance is a started database connected to those datafiles. While the datafiles may exist on the server, you have not instantiated the database until you start it. You do not access the datafiles directly, rather you connect to the database instance to gain access to the data. So a database and an instance are two different things.

For most of the Oracle databases that run in the world, one instance connects to one database and that's it. But in special configurations, you can have multiple instances accessing the same database. In other words, you can start Oracle running on multiple servers accessing the same data. In order to accomplish this in Oracle 9i, you need to use a product called Real Application Clusters (RAC).

RAC is used for a number of reasons. First, you can spread out the load on the database among multiple servers. This is one of the ways that databases can handle an extreme volume of users and transactions. So better performance is one of the reasons. The second reason to use RAC is for high availability. In normal configurations (without RAC), if the instance goes down, you no longer have access to your data. In RAC environments, if an instance goes down other instances can still access the data. User sessions can failover to the remaining instances in these environments. If you do not have a requirement for high availability and/or high performance, then you do not need RAC. Most Oracle databases in the world do not use RAC, but that does not mean that RAC is never used.

For More Information

  • What do you think about this answer? E-mail the editors at [email protected] with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle database design and architecture