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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- 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 PL/SQL, database administration and data warehousing gurus are waiting to answer your toughest questions.