The relationship between Instance_name,db_name,oracle_sid

A "understanding of the Oracle_sid

--------------------------------------------------------------------------------
The SID in Oracle acts like a "switch variable"---directs Oracle to start the instance correctly by reading the appropriate parameter file and loading it in the default location when the instance starts.

We know that the boot of the instance needs to specify Pfile/spfile (oracle9i before using the pfile--as the init file, the default is to use the spfile--as a server-side parameter file starting from 9i the location of the two files is:

A.pfile: Name is Init<oracle_sid>.ora
B.spfile: Name is Spfile<oracle_sid>.ora

We see that both the init file and the SPFile file are labeled with Oracle_sid, which is what the SID does:

"1" When we start an instance, we first tell ORACLE the next instance to launch by: set oracle_sid = XXX or export ORACLE_SID = XXX

"2" When we execute startup Nomount, because the SID has been specified, Oracle knows how to go to the default directory to find the corresponding parameter file for that instance--by Sid to match each init/spfile file

"3" When Oracle finds the file (Init/spfile), it reads the contents of the file, uses a series of parameters to allocate memory space, and builds the boot process for instances such as background processes

"4" When this instance is successfully started, we can re-execute set oracle_sid = XXX or export oracle_sid = XXX command again to dial "switch to" another SID, and then repeat the above steps

From the above procedure we see that the Oracle SID function is an obvious "switch variable", which pulls to the side, then Oracle will find and its corresponding parameter file to launch the instance. The second SID also plays a role in process isolation-that is, the SID is reflected in a series of background processes in the instance, such as Ora_dbw0_paullin,ora_dbw0_boblin. The background process is named through the SID.

The operating system must also deal with the Oracle instance through the SID, the operating system does not know what instance_name, only know Oracle_sid, in Oracle internally by Oracle to identify different instances of the SID. So Oracle_sid is more of "a window of Oracle and external operating system communication".

The understanding of "two" to instance_name
--------------------------------------------------------------------------------
Instance_name is a real parameter compared to a "switch variable" such as Oracle_sid. It is configured in the Init/pfile file (db_name). The default value for identifying the name of the DB instance is oracle_sid.

Instance_name is like a person's name, while SID is the person's ID number. Through the SID we find the corresponding Init/spfile file, and the Init/spfile file by instance_name This parameter tells us what the corresponding instance name. In this way, Oracle_sid, parameter files, and instance_name are linked together.

The cause of oracle_sid is different, but the instance_name is usually the same because the original parameter file has been copied, but the value of the instance_name parameter is forgotten. However, after 10G instance_name this parameter and disappeared from the init/spfile, so as not to cause confusion.

The comprehension of "three" to db_name
--------------------------------------------------------------------------------
We already know that Oracle_sid is a "switch variable" and instance_name is used to describe the instance. Then Db_name is the name of the database that describes the instance mount, through which we can know the location of the control files, log files, and data files on the corresponding disk.

Db_name is specified when the database is created, as is the name of Oracle_sid by default. But the same as instance_name. Db_name cannot be modified once it is determined, as it will be stored in Init/spfie, control files, log files, data files. You can only modify the value of the db_name parameter in the Init/spfile file, but you cannot manually modify the values of other files. So once the modification causes several file values to be mismatched then the database startup will fail.

A. An instance can mount and open any database (by configuring the db_name parameter in Init/spfile), but only one database can be opened at a time.
B. A database can be mount and open on one or more instances (only in a RAC environment where a database can only be mount and opened by one instance at a time).

So how do you know if the instance should be attached to that database and open it? is to rely on the "control_files" parameter in Init/spfile to tell Oracle to go there to read the control file, while the control file also records the location of the data file. So eventually a complete chain is formed:

Oracle_sid-Init/spfile file--instance_name, db_name, Control_files--instance name, database name, control file--data file-- Complete mount and Open

Correspondence between "Four" oracle_sid, instance_name and db_name
--------------------------------------------------------------------------------
We already know that Oracle_sid is used to isolate different instances of the same oracle_home. But what about the different oracle_home? Can you have a SID with the same name?

In fact, this is the same as the file system rules: In the same directory is not allowed to have the same name of the file exists, but different directories can have the same name of the file. The same oracle_home under the same SID can not exist, and different oracle_home under the same name of the SID (that is, the same machine, as long as the oracle_home different can).

So what about instance_name? By default, instance_name and Oracle_sid are the same, or they can be different, and different instances can have the same instance name. That sounds a little confusing, doesn't it? In fact, we can use a simple example of life to parse:

In a class class_1 inside, there are two students, their names are called Zhang San, but their school number must be different. Here "Zhang San" is instance_name, and the study number is SID. So instance_name repetition does not matter, as long as the SID difference can be distinguished.

But in another class class_2 inside, there are students with the same student number, this time there is the same SID we say the problem, then how to distinguish it? Don't forget we still have the concept of Class! This corresponds to Oracle, which is oracle_home.

As for the correspondence between instance_name and db_name, it's good to understand that different instances can be mounted to different db, or they can be mounted to the same db. This can be interpreted as "competing access to the same disk file by multiple processes simultaneously".

A. Under the same oracle_home, instance_name can be the same, but Oracle_sid must be unique. Distinguish different instances by oracle_sid
B. Under different oracle_home, Oracle_sid can be the same, by Oracle_home to differentiate different SIDs
C. Different instance_name can correspond to different db_name, can also correspond to the same db_name. However, if you are not in a RAC environment, you can only start the instance at the same time and not mount it simultaneously

Relevant parameter files for the "five" Oracle instance startup process
--------------------------------------------------------------------------------
As mentioned earlier, when the Oracle instance starts, you need to specify the Pfile/spfile parameter, which corresponds to the init file and the SPFile file. So how does Oracle decide to read that when the two files are present at the same time?

With Oracle 9i as an example, the order in which to start an instance by default is SPFile:

Spfile<oracle_sid>.ora---> Spfile.ora---> Init<oracle_sid>.ora

That is, the default load and sid corresponding parameter file, if not found the default parameter file, and can not find the old init file, if still cannot find the instance cannot start.

The relationship between Instance_name,db_name,oracle_sid