Relationship and functions of db_name/SERVICE_NAME/Sid in Oracle

Show parameter service_names

select instance_name from v$instance;

 

Db_name is a real physical name. SERVICE_NAME carries the domain name, because if the two databases do not have the same domain, they can have the same name. The SID is the database instance name, which consists of SGA and background processes. A database has only one db_name but can have two instances. Each instance has its own SGA and background processes.
Db_name: Unique identifier of a database (Oracle database. This expression is sufficient for a single database, but with the popularization of distributed databases composed of multiple databases, this method of Command database creates a certain burden on database management, because the names of various databases may be the same, resulting in management confusion. To solve this problem, the db_domain parameter is introduced. In this way, the database ID is determined by the two parameters db_name and db_domain, avoiding management confusion caused by database name duplication. This is similar to managing machine names on the Internet. We connect the parameters db_name and db_domain with '.' to indicate a database, and the database name is global_name, that is, it extends db_name. The db_name parameter can only contain letters, numbers, '_', '#', and '$', and can contain a maximum of 8 characters.
Db_domain: defines the domain where a database is located. The domain name has nothing to do with the 'region' on the Internet. It is determined by the database administrator in order to better manage the distributed database. Of course, to facilitate management, it can be equal to the domain of the Internet.
Global_name: the unique identifier of a database (Oracle Database). We recommend that you use this method to command the database. This value is determined when you create a database. The default value is db_name. db_domain. Any modifications to the parameters of db_name and db_domain In the parameter file will not affect the value of global_name. To modify global_name, you can only use the alter database rename global_name to command and then modify the corresponding parameters.
SERVICE_NAME: this parameter is newly introduced by Oracle8i. Before 8i, we used Sid to identify an instance of the database. However, in the parallel environment of Oracle, a database corresponds to multiple instances, so that multiple network service names are required, configuration is cumbersome. To facilitate the setting in the parallel environment, the SERVICE_NAME parameter is introduced, which corresponds to a database rather than an instance, and has many other advantages. The default value of this parameter is db_name. db_domain, which is equal to global_name. A database can correspond to multiple service_names for more flexible configuration. This parameter has no direct relationship with Sid, that is, the service name must be the same as the SID.
Instance_name: name of the database instance. Used for external connection to the operating system. To obtain interaction with databases in the operating system, you must use the database instance name. For example, to connect to a database server, you must know its database instance name. It is useless to know only the database name. Unlike the database name, after the data is installed or the database is created, the instance name can be modified. The relationship between database names and instance names is generally a one-to-one correspondence. A database name has an Instance name. If you create two databases on a server, there are two database names, two database instance names are used to identify a database. The user and instance are connected. However, in the 8i and 9i Parallel Server structures, there is no one-to-one correspondence between the database and the instance, but a one-to-many relationship (one database corresponds to multiple instances, within the same time, the user is associated with only one instance. When an instance fails, other instances are automatically served to ensure the safe operation of the database .)
Oracle_sid: operating system environment variable. In practice, the description of the database instance name sometimes uses the Instance name (instance_name) parameter, and sometimes uses the oracle_sid parameter. The two are database instance names. What are their differences? (Frequent obfuscation)
(Oracle_sid)
OS <---------------->; Oracle database <-------- (instance_name (Instance name ))
The preceding example shows the relationship between the Instance name instance_name and oracle_sid and the database and the operating system. Although the two parameters listed here are database instance names, the instance_name parameter is a parameter of the Oracle database, this parameter can be queried in the parameter file, while oracle_sid is the operating system environment variable.
The operating system environment variable oracle_sid is used to interact with the operating system. That is to say, to get the Instance name in the operating system, you must use oracle_sid. This parameter is used in the same way as oracle_base and ORACLE_HOME. After the database is installed, oracle_sid is used to define the name of the database parameter file. For example:
========================================================
Db_name, instance_name, SERVICE_NAME in init. ora
Db_name is the name of the database, which has been set during dB installation. It cannot be modified here, and it feels the location of the Database Installation File.
Instance_name is the Instance name and the name in the database running. In OO, db_name is equivalent to the class and instance_name is oriented to the object. It also represents the memory and process in the database running, at the same time, the names of these processes are affected. For example, if a database named db_name = Cus and its instance named instance_name = aking, the process name of the database may be pmon_aking_1. The instance name must match files such as pwdsid. ora and initsid. ora. Otherwise, an error is reported when the database is started. It can be seen from this that db_name is a class name and cannot be modified after definition. For instance_name, the Instance name is oriented to the object, so we can set our favorite object name. However, after changing instance_name, it is recommended that you do not change the instance_name because the default value is the same as that of db_name.
SERVICE_NAME I think it should be the name of the database network connection, which will be considered in the Listener Configuration. This value can also be changed at will, and there can be multiple values. Alter system set SERVICE_NAME = serv1, serv2 scope = both;

Sid_name and global_dbname are available in listener. ora.
Sid_name indicates the name of the running instance of the database, which should be consistent with instance_name.
Global_dbname is the name of the external network connection configured by listener. We will consider this parameter When configuring tnsname. ora. This parameter can be set at will.
In addition, you need to note that the Listener Configuration of the database instance is manually configured in listener. ora. However, Oracle supports automatic registration through the pmon process. The name of the automatically registered external network connection will use init. SERVICE_NAME in the ora file. If there are multiple values, multiple registrars will be registered. For the above example, the listener services serv1 and serv2 will be registered here. If you manually configure a listening service with global_dbname = serv3, there will be three listening services for instance instance_name = aking.

SERVICE_NAME, Sid in tnsname. ora
Configure tnsname. ora of the Client
For the configuration here, the IP address of the database to be connected and the instance or service to be connected are provided.
The name of the external network connection is mentioned in the Listener Configuration. If SERVICE_NAME is used here, SERVICE_NAME = (global_dbname or SERVICE_NAME is required to be automatically registered in the listener here ), for SID = (instance_name), for example:
SERVICE_NAME = serv1, serv2, serv3, or
SID = aking

The last one is the oracle_sid parameter, which is used in the operating system. It describes the database instance to be connected by default. If there are multiple instances on one machine, after modification, you can connect to the database through Conn/As sysdba, because the default Instance name is used here.
========================================================== =====

I have been not very clear about the various names in Oracle. I have made some research over the past two days, which is a little achievement. Haha!
SERVICE_NAME: the database is logically represented as a service. The service name can be retrieved at will, and a database can have multiple service names. The name is determined by the service_names parameter. If this parameter is null, the default service name is global database name, that is, db_name.db_domain.

Instance_name: The ing between an instance and a database is many-to-one. It is specified by the instance_name parameter. When the system instance and the database are in one-to-one relationship, the Instance name is usually the database name.

Db_name: db_name and db_domain form the global database name, which uniquely identifies an Oracle database. db_name cannot be changed after the database is created.

Oracle_sid: Oracle system identifier, which is an environment variable used to differentiate instances. Therefore, oracle_sid is actually the name of the instance to be connected. Because the instance and the database are many-to-one, determining the instance is equivalent to determining the database to be connected.

Connection identifier: The following is the basic content of a tnsnames. ora file:
Oracle =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = 192.168.0.111) (Port = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10g)
)
)
Oracle is the connection identifier. When an application (such as SQL * Plus) connects to Oracle using a local name, it uses the connection identifier, as shown in figure
"Sqlplus Scott/tiger @ oracle"
The content on the right of the equal sign of the connection identifier is the connection descriptor, which is the information required to connect to Oracle using a local name.
SERVICE_NAME is the value in service_names. It must correspond to the value of this parameter. If the parameter is null, SERVICE_NAME can only be db_name.db_domain; otherwise, it cannot connect to Oracle.
====================================
Protocol = TCP) (host = 192.168.0.6) (Port = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = epolice

Protocol-meaning
Host -- Database Server IP Address
Port -- database server port
SERVICE_NAME -- database Sid