I am reading Oracle's administrative guide and I need a few things clarified. From my understanding multiple database instances can access one database, that is, all instances are connected to the same datafiles, therefore same data. So, a group of users can access one database by either a single instance or multiple instances and the difference to the users will be transparent.
If right, in what situations are multiple instances better than one?
I'm not sure if it is possible for two Oracle instances to share their datafiles (I think I once saw this, but in the special case of two cluster nodes which share datafiles and represent one instance).
Usually - at least from my experience - every database instance will have its own datafiles. Datafiles are bound to tablespaces and since a database has more than one tablespace there will be more than one datafile per instance.
One of the reasons for using more than one database instance on a server may be an improve in maintainability. If you have to shut down a database instance applications running on other instances are not affected.
Another example may be that one application will require a newer Oracle version, but another application running on this server is not compatible with this new Oracle version (or at least not yet tested against it), then the use of two instances may be the solution for this.
I'm not quite sure what you mean with:
So, a group of users can access one database by either a single instance or multiple instances and the difference to the users will be transparent.
One technique for transparent connecting to multiple instances is a failover definition in the tnsnames.ora file of the client. There you can bind several connection descriptions to one "connection name". The client then will be able to connect to a failover system if the main system is not available at startup.
I hope I could help you a little bit...
Joined: Jan 16, 2009
I really appreciate your reply, but I am more confused now. I am actually trying to understand the difference between an instance and a database. I know from reading documentation they are not the same thing.
An instance, as I understand, is the background processes, sga and pga. The data is written to data files which are the tables, rows etc. Are the datafiles the database, then?
Joined: Feb 12, 2009
yes, I was afraid that my post may be a little confusing when I read it myself. Sorry for that..
Yes I would follow your description, an instance is the set of background processes (or threads on a Windows system) providing the environment.
At least in my company both expressions are used without much difference, e.g. a db admin may ask if it's ok to "shut down database xy", where xy is surely an instance...
So I really don't see a difference between a database and a database instance.
It's along time since I read the Oracle documentation myself, so I don't know what's the difference between a database and an instance described there.
The only difference I could imagine is that they speak of a database as the installed set of files and of an instance for the processes/threads running.
What I want to say, if you have a server, you can have two databases installed, e.g. one of version 10g and one 11g. And on this server you may have 4 running database instances, 3 running under 11g, 1 running under 10g.
But as I said above, this is just a wild guess what may be the difference. Your impression that the database is the set of datafiles belonging to one instance may be true, too. After all this is the base where the data is comming from.
Ah, i just googled "Oracle difference instance database" and it looks like you are right, e.g. here: