I'm really confuse with the term instance and database. So, my first question is how database and Instance are related in Oracle, I mean, whether an instance is associated with one/multiple database(s) or a database associated with single/multiple instance(s)?
And, my second question is, one of my friend says we can't have multiple instances in oracle. is it really valid a statement ?
The documentation explains it very well:
An Oracle database server consists of an Oracle database and an Oracle instance. Every time a database is started, a system global area (SGA) is allocated and Oracle background processes are started. The combination of the background processes and memory buffers is called an Oracle instance.
An oracle database is the physical files that make up the database itself (control files, data files etc) - Documentation link.
A database is associated with one or more instances, with multiple instances making a RAC setup - Documentation link. As far as your second question is concerned, multiple instances of a single database is a RAC setup.
You can have multiple instances on one machine that both mount the same database (a RAC configuaration), but this would be unusual. The multiuple instances of a RAC system would generally be on different machines except for special cases such as development or test systems.
You can have multiple non-RAC systems on a single machine, so of course each of those would have its own instance. This is common but not necessarily as efficient as combining the multiple databases into one.
