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

Starting new instance on same database?

According to my understanding, a database is a set of data files, and an instance is (SGA+BG-processes). For a database (name=temp), there is one instance already running (sid=temp). If I need to start another instance (different sid,say,temp2) for the same database, how do I do it? I think creating another database on the same machine is different from this.

In Oracle terms, a "database" is the collection of datafiles. An "instance" is the allocated memory and running processes. In Oracle there is a one to one correspondence between instance and database. One instance can attach to only one database. And one database can only have one instance attached to it.

People coming from a SQL Server background have difficulties with this because "database" means something totally different to SQL Server. The closest thing to a SQL Server database in Oracle is the "tablespace". Just like SQL Server can have multiple databases, Oracle can have multiple tablespaces.

If you want to create a new instance on your server, then you certainly can. But keep in mind that it needs its own database to attach to. You can't attach the second instance to the same database.

The exception to the above rule is to run an Oracle add-on product called Real Application Clusters, or RAC. Before Oracle 9i, this product was called Oracle Parallel Server. RAC lets you attach multiple instances to one database. But you'll want to put each instance on a separate server connecting to the database on a shared disk subsystem.

For More Information

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.