Shulou(Shulou.com)06/01 Report--

[1] understanding of ORACLE_SID

The function of SID in Oracle is similar to a "switch variable"-guiding oracle how to read the appropriate parameter file and load it at the default location when the instance is started to start the instance correctly.

We know that pfile/spfile needs to be specified to start the instance (pfile-- or init file was used before Oracle9i, and spfile-- is used by default since 9i, that is, the location of server-side parameter files is:

A.pfile: name is init.ora

B.spfile: name is spfile.ora

We see that both the init file and the spfile file bear the ORACLE_SID logo, which is what SID is for:

[1] when we want 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, since SID has been specified, Oracle knows how to find the parameter file corresponding to the instance in the default directory-- that is, SID to match each init/spfile file

[3] when Oracle finds the file (init/spfile), it reads the contents of the file and uses a series of parameters to allocate memory space and build the startup process of instances such as background processes.

[4] when the instance is started successfully, we can re-execute the command set ORACLE_SID = XXX or export ORACLE_SID = XXX to "switch" to another SID again, and then repeat the above steps

From the above process, we can see that the role of Oracle SID is an obvious "switch variable". If it is pulled to that side, then Oracle will find its corresponding parameter file to start the instance. Secondly, SID also plays the role of process isolation-that is, SID is reflected in a series of background processes of the instance, such as ora_dbw0_paullin,ora_dbw0_boblin. The background process is named through SID.

The operating system must also deal with Oracle instances through SID. The operating system knows nothing about INSTANCE_NAME, only ORACLE_SID. Within Oracle, it is up to Oracle to identify different instances according to this SID. So ORACLE_SID is more of a window for Oracle to communicate with external operating systems.

[2] understanding of INSTANCE_NAME

Compared with the "switch variable" such as ORACLE_SID, INSTANCE_NAME is a real parameter. It is configured in the init/pfile file (db_name). The name used to identify the database instance, whose default value is ORACLE_SID.

INSTANCE_NAME is like a person's name, and SID is that person's ID number. Through SID we find the corresponding init/spfile file, and the init/spfile file uses the parameter instance_name to tell us the name of the corresponding instance. This connects ORACLE_SID, parameter files, and INSTANCE_NAME.

The reason why the ORACLE_SID is different but the INSTANCE_NAME is the same is usually because you copied the original parameter file but forgot to change the value of the INSTANCE_NAME parameter in it. However, the INSTANCE_NAME parameter disappeared from the init/spfile after 10G to avoid confusion.

[3] understanding of DB_NAME

We already know that ORACLE_SID is a "switch variable" and INSTANCE_NAME is used to describe instances. Then DB_NAME is the name of the database that describes the instance to be mounted. Through this DB_NAME, we can know the location of the control file, log file, and data file on the corresponding disk.

DB_NAME is specified when creating the database, which is the same as the name of ORACLE_SID by default. But just like INSTANCE_NAME. Once DB_NAME is determined, it cannot be modified because it is stored in init/spfie, control files, log files, and data files at the same time. You can only modify the value of the DB_NAME parameter in the init/spfile file, but you cannot modify the value of other files manually. So once the modification causes the values of several files to mismatch, then the startup of the database 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 by an instance at a time.

b. A database can be MOUNT and opened by one or more instances (only in a RAC environment, a database can only be opened by one instance at the same time in a normal environment).

So how do you know which database the instance should be mounted to and open? It is the "control_files" parameter in init/spfile that tells Oracle to read the control file, and the location of the data file is recorded in the control 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-- > finish mounting and opening

[IV] corresponding relations among ORACLE_SID, INSTANCE_NAME and DB_NAME

We already know that ORACLE_SID is used to isolate different instances under the same ORACLE_HOME. But what about different ORACLE_HOME? Can I have a SID with the same name?

In fact, this is the same as the rule of the file system: files with the same name are not allowed in the same directory, but there can be files with the same name in different directories. The same SID cannot exist under the same ORACLE_HOME, while different ORACLE_HOME can have a SID with the same name (even on the same machine, as long as the ORACLE_HOME is different).

What about INSTANCE_NAME? By default, INSTANCE_NAME and ORACLE_SID are the same or 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 life example to analyze:

In a class Class_1, there are two students whose names are Zhang San, but their student numbers must be different. Here "Zhang San" is INSTANCE_NAME, and the student number is SID. So it's okay to repeat INSTANCE_NAME, as long as the SID is different.

But in another class Class_2, there are also students with the same student number, at this time there is what we call the same problem of SID, so how to distinguish? Don't forget that we still have the concept of class! Corresponding to ORACLE is ORACLE_HOME.

As for the correspondence between INSTANCE_NAME and DB_NAME, it is easy to understand that different instances can be mounted to different DB or to the same DB. This can be understood as "competitive access by multiple processes to the same disk file at the same time".

a. Under the same ORACLE_HOME, INSTANCE_NAME can be the same, but ORACLE_SID must be unique. Distinguish different instances through ORACLE_SID

b. Under different ORACLE_HOME, ORACLE_SID can be the same, and different SID can be distinguished by ORACLE_HOME.

c. Different INSTANCE_NAME can correspond to different DB_NAME or the same DB_NAME. However, if it is not in the RAC environment, you can only start the instance at the same time and not mount it at the same time.

[5] Parameter files related to the startup process of Oracle instance

As mentioned earlier, when an Oracle instance is started, 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 both files exist at the same time?

Take Oracle 9i as an example. Since spfile is used by default to start the instance, the search order is as follows:

Spfile.ora-- > spfile.ora-> init.ora

That is, the parameter file corresponding to SID is loaded by default. If it cannot be found, the default parameter file is used. If it cannot be found, the old init file is used. If it is still not found, the instance cannot be started.

=

DB_NAME:

① is the name of the database and cannot exceed 8 characters in length. It is recorded in datafile, redolog and control file.

② has the same DB_NAME but different DB_UNIQUE_NAME in DataGuard environment

③ in a RAC environment, the DB_NAME of each node is the same, but the INSTANCE_NAME is different

④ DB_NAME also plays a role in dynamic registration listening, using DB_NAME dynamic registration listening regardless of whether the SERVICE_NAME,PMON process is defined or not

DBID:

① DBID can be seen as the internal representation of DB_NAME in the database, which is calculated by combining DB_NAME with algorithm when the database is created.

② exists in datafile and control file and is used to indicate the attribution of data files, so DBID is unique. For different databases, DB_NAME can be the same, but DBID must be unique. For example, in DataGuard, the DB_NAME of the master and standby database is the same, but the DBID must be different (I have seen a very vivid example, that is, you can have someone with the same name, but the ID card number must be different).

DB_UNIQUE_NAME:

① in DataGuard, the master and slave libraries have the same DB_NAME. In order to distinguish, there must be different DB_UNIQUE_NAME.

② DB_UNIQUE_NAME will affect the SERVICE_NAME of dynamic registration in DG, that is, if dynamic registration is used, the registered SERVICE_NAME is DB_UNIQUE_NAME, but the instance is still INSTANCE_NAME, that is, SID

INSTANCE_NAME:

The name of the ① database instance. The default value of INSTANCE_NAME is SID, which is generally the same as the database name (DB_NAME), but can also be different.

② initSID.ora and orapwSID files should be consistent with INSTANCE_NAME

③ INSTANCE_NAME affects the name of the process

SID:

① is an environment variable in the operating system and is used the same as ORACLE_HOME,ORACLE_BASE

② must use ORACLE_SID if it wants to get an instance name in the operating system. And the value of ORACLE_SID must be consistent with that of INSTANCE_NAME

SERVICE_NAME:

The name of the service used to connect the ① database to the client

② in DataGuard, if dynamic registration is used, it is recommended to use the same service_names in the master / slave database.

If ③ is registered statically in DataGuard, it is recommended to enter the same service name (service_name) in the listener on the master / slave database.

④ if static registration is used for listening, then SERVICE_NAME is equal to the value of GLOBAL_DATABASE_NAME in the Listener.ora file

GLOBAL_DATABASE_NAME:

① GLOBAL_DATABASE_NAME is the name of the external network connection configured by listener, which can be any value

It is OK for ② to keep the service_name in the tnsnames.ora file monitored by the client configuration consistent with this GLOBAL_DBNAME.

When ③ configure static snooping registration, you need to enter SID and GLOBAL_NAME

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.