The relationship between Oracle Sid,instance_name,db_name,oracle_sid __oracle

The understanding of "one" to Oracle_sid

--------------------------------------------------------------------------------
The role of SIDS in Oracle is similar to that of a "switch variable"---directs Oracle to read and load the appropriate parameter file at the default location at instance startup to properly start the instance.

We know that the startup of the instance needs to be specified Pfile/spfile (oracle9i is used pfile--the init file, the default is to use the spfile--, the server-side parameter file, starting at 9i. The location of these two files is:

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

We see that both the init file and the SPFile file have a oracle_sid identity, which is what the SID does:

"1" When we are going to start an instance, we first tell ORACLE the next instance to start 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 the instance--that is, 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 startup process for instances such as background processes

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

From the above process we see that the Oracle SID function is an obvious "switch variable", it pulls to that side, then Oracle will find and its corresponding parameter file to start the instance. The second SID also acts as a 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. Name the background process through the SID.

The operating system also has to deal with Oracle instances through SIDS, and the operating system knows nothing about the instance_name, only the ORACLE_SID, where Oracle itself identifies different instances within Oracle. So Oracle_sid is more of a window for Oracle and external operating systems to communicate.

The understanding of "two" to instance_name
--------------------------------------------------------------------------------
Instance_name is a real parameter compared to the "switch variables" such as ORACLE_SID. It is configured in the Init/pfile file (db_name). The name used to identify the database instance, and its default value is Oracle_sid.

Instance_name is like a person's name, and Sid is the person's ID number. We find the corresponding Init/spfile file through SID, and the Init/spfile file tells us the name of the corresponding instance by instance_name this parameter. This is the Oracle_sid, parameter file, instance_name link up.

Causes Oracle_sid to be different, but instance_name the same reason is usually because the original parameter file was copied, but forgot to modify the value of the instance_name parameter. However, after 10G instance_name this parameter and disappeared from the init/spfile, so as not to cause confusion.

The understanding 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, and through this db_name we know the location of the control files, log files, and data files on the corresponding disks.

Db_name is specified when the database is created and is the same as the Oracle_sid name by default. But it's the same as instance_name. Once db_name is identified, it cannot be modified because it is stored in Init/spfie, control files, log files, and data files. You can only modify the value of the db_name parameter in the Init/spfile file, but you cannot manually modify the value of the other file. So once the changes cause a few file values do not match then the database startup will fail.

A. An instance can mount and open any database (db_name parameters are configured through Init/spfile), but an instance of the same time can open only one database.
B. A database can be mount and open by one or more instances (only in a RAC environment, a database can only be mount and open at the same time in a common environment).

So how do you know that the instance should be mounted to that database and open? It is relying 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 the final form of a complete chain:

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

"Four" oracle_sid, instance_name, db_name correspondence relations
--------------------------------------------------------------------------------
We already know that Oracle_sid is used to isolate different instances of the same oracle_home. But for the different oracle_home. Can I have a SID with the same name?

This is actually the same as the rules of the file system: Files with the same name are not allowed in the same directory, but different directories can have files with the same name. Similarly, under the same oracle_home cannot have the same SID existence, and different oracle_home can have the name of the SID (that is, the same machine, as long as the oracle_home can be different).

So what about instance_name. Instance_name and Oracle_sid are the same or different by default, 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 resolve:

In a class class_1, there are two students, their names are called John, but their school number is certainly different. Here "John" is instance_name, and the study number is SID. So instance_name repetition doesn't matter, as long as the SIDs are different.

But in another class class_2, there are students with the same number, and then there are the same as we say the SID of the same problem, then how to distinguish it. Don't forget we have the concept of a class. That corresponds to Oracle is Oracle_home.

As for the correspondence between instance_name and db_name, this is well understood, different instances can be mounted to different db, or can be mounted to the same db. This can be interpreted as "multiple processes competing access to the same disk file simultaneously."

A. Under the same oracle_home, instance_name can be the same, but Oracle_sid must be unique. To differentiate between different instances by Oracle_sid
B. Under different oracle_home, Oracle_sid can be the same, by Oracle_home to distinguish between different SIDs
C. Different instance_name can correspond to different db_name, or they can correspond to the same db_name. But if you are not in a RAC environment, you can only start the instance at the same time and not mount the

Related parameter files for the "five" Oracle instance startup process
--------------------------------------------------------------------------------
Earlier, when 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 the two files when they exist at the same time?

Take Oracle 9i for example, because SPFile is used by default to start an instance, the order of lookups is:

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

That is, the default load and SID corresponding to the parameter file, if you can not find the default parameter file, and then can not find the old init file, if you still cannot find the instance could not start.