The relationship and function of Db_name/service_name/sid in Oracle __oracle

Show Parameter Service_names

Select instance_name from V$instance;

Db_name is a real physical name. Service_Name with a domain name, because if two databases no longer have the same name as the same domain. The SID is the instance name of the database, which consists of a SGA and a background process. A database has only one db_name but can have two instances. Each instance has its own SGA and background processes. Db_name: Unique identification of a database (Oracle database). This representation is sufficient for a single database, but with the spread of distributed databases made up of multiple databases, the method of this command database poses a certain burden to the management of the database, as the names of each database may be the same, resulting in management confusion. In order to solve this situation, the Db_domain parameter is introduced, so that the identity of the database is determined by db_name and db_domain two parameters, which avoids the confusion of management because of the duplicate database. This is similar to the management of machine names on the Internet. We will db_name and db_domain two parameters using '. ' Connects to represent a database, and the name of the database is called Global_name, that is, it extends the db_name. The db_name parameter can only consist of letters, numbers, ' _ ', ' # ', ' $ ', and up to 8 characters. Db_domain: Defines the domain in which the database is located, and the name of the domain has nothing to do with the ' domain ' of the Internet, but the database administrator decides on the basis of the actual situation to better manage the distributed database. Of course, in order to manage conveniently, it can be equal to the domain of the Internet. Global_name: The unique identification of a database (Oracle database), Oracle recommends that you use this method to command the database. The value is determined when the database is created, and the default value is Db_name. Db_domain. Any subsequent modification of the db_name and Db_domain parameters in the parameter file does not affect the Global_name value, and if the global_name is to be modified, it can only be modified with the ALTER DATABASE RENAME to command, then modify the corresponding parameters. SERVICE_NAME: This parameter is newly introduced by Oracle8i. Before 8i, we used SIDS to represent an instance of the identity database, but in Oracle's parallel environment, one database corresponded to multiple instances, requiring multiple network service names and cumbersome settings. To facilitate settings in a parallel environment, the SERVICE_NAME parameter is introduced, which corresponds to a database rather than an instance, and has many other benefits. The default value for this parameter is db_name. Db_domain is equal to Global_name。 A database can correspond to multiple service_name in order to achieve a more flexible configuration. This parameter is not directly related to the SID, which means that the service name must be the same as the SID. Instance_name: database instance name. Used for contact with the operating system for external connections. In the operating system to obtain interaction with the database, you must use the database instance name. For example, to connect to a database server, you must know its database instance name, only that the database name is not used, and unlike the database name, the instance name can be modified after the data is installed or the database is created. The relationship between the database name and the instance name is typically one by one correspondence, there is a database name has an instance name, if you create two databases in a server, then there are two database names, two database instance names, two identities to determine a database, user and instance connection. But in 8i, 9i parallel server structure, there is no one by one correspondence between the database and the instance, but a one-to-many relationship, (one database corresponds to multiple instances, the user is only one instance in the same time, and when an instance fails, other instances are automatically serviced to ensure the safe operation of the database.) ) ORACLE_SID: Operating system environment variables. In practice, the description of the database instance name sometimes uses the instance name (instance_name) parameter and sometimes the Oracle_sid parameter. These two are database instance names, what's the difference? (often confused) (ORACLE_SID) os<---------------->; The ORACLE Database <--------(instance_name (instance name)) example indicates the relationship between the instance name instance_name, Oracle_sid and the database and the operating system, although the two parameters listed here are database instance names. However, the instance_name parameter is an Oracle database parameter that can be queried in the parameter file, while the ORACLE_SID parameter is the operating system environment variable. The operating system environment variable ORACLE_SID is used to interact with the operating system. That is, you must use ORACLE_SID to get the instance name in the operating system. This parameter is the same as Oracle_base, Oracle_home, and so on. After the database is installed, ORACLE_SID is used to define the name of the database parameter file. For example: ======================================= in Init.ora has db_name,instance_name,service_name db_name is the name of the database, It was set up when DB was installedIt is not modifiable here, it feels the location of the database installation files. Instance_name is the instance name, is the name of the database running, in fact, in OO db_name the equivalent class and instance_name to the object, it is also representative of the database running memory and its processes, while affecting the names of these processes, such as: a database db_ Name=cus, and in fact the example instance_name=aking, then the database up, its process name may be: pmon_aking_1. Here the instance name and Pwdsid.ora and Initsid.ora file matching, otherwise, DB start error. It can be seen from here that Db_name is the class name, defined is not modifiable, and for the instance_name instance name to the object, so we can set their favorite object names. However, although this said, but changed the instance_name, involved in many other settings, or the best not to move him, the default and Db_name is the same, so much better. Service_Name I think it should refer to the name of the database network connection, which will be considered in the listener configuration. This value can also be changed arbitrarily, and can also have multiple values. Alter system set SERVICE_NAME=SERV1,SERV2 Scope=both; In Listener.ora there are sid_name,global_dbname here Sid_name the instance name of the database running, which should be consistent with instance_name and for Global_ DBName is the listener configuration of the external network connection name, we will consider this parameter when configuring Tnsname.ora. This parameter can be set arbitrarily. Another point to note is that in general we will manually configure the listener configuration of the database instance in Listener.ora. However, Oracle can support autoenrollment through the Pmon process, when the automatic registration of the external network connection name will be used in the Init.ora file service_name, there are multiple values will register multiple, for the above example, Here you will register the SERV1 and serv2 two listening services. If you also manually configure a GLOBAL_DBNAME=SERV3 listening service, there will be three listening services for the instance instance_name=aking. In Tnsname.ora, there are service_name,sid to configure the client Tnsname.ora for this configuration is primarily to give the database to connect to the IP and its connection to the instance or service in the listening configuration we mentioned the external network connection name,Here if we use service_name, we need service_name= (Global_dbname or service_name here requires Oracle to be automatically registered to the listener), for sid= (instance_ Name), such as: Service_name=serv1,serv2,serv3 can, or sid=aking the last is the Oracle_sid parameter, which is used in the operating system, it is a description of the default connection we want to the database instance. For cases where there are multiple instances on a machine, you can modify them to connect through the Conn/as SYSDBA because the default instance name is used here. ============================================= has been on the various names of Oracle is not very clear, these two days to do a more detailed study, but also a small achievement bar, haha. SERVICE_NAME: The database is logically represented as a service, the service name can be arbitrarily taken, and a database can have more than one service name, the name is determined by the parameter service_names, if the parameter is NULL, the default service name is the global database name, namely Db_name.db_ Domain instance_name: The corresponding relationship between the instance and the database is Many-to-many, specified by the parameter instance_name, when the instance of the system is a one-to-one relationship to the database, the instance name is usually the database name. Db_name:db_name and Db_domain make up the global database name, uniquely identifying an Oracle database that db_name can no longer change after the database is created. Oracle_sid: The full name is the Oracle System Identifier, which appears as an environment variable to differentiate between different instances. So Oracle_sid is actually the name of the instance you want to connect to, because the instance is a Many-to-many relationship to the database, so the instance is determined to determine which database to connect to. 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)) where Oracle is the connection identifier, when using a locally named method, when an application (such as Sql*plus) connects to Oracle, theIs the connection identifier, such as the "Sqlplus scott/tiger@oracle" Connection identifier, to the right of the equal sign, is the connection descriptor, which is the information needed to connect Oracle with a local name. Service_Name is the value in the parameter service_names, which must correspond to the value of the parameter, and if the argument is empty, the service_name here can only be db_name.db_domain, otherwise the Oracle cannot be connected. ============================== PROTOCOL = TCP) (HOST = 192.168.0.6) (PORT = 1521)) (Connect_data = (service_name = Epolice PROTOCOL--Co-host--Database server IP Port--database server port service_name--database SID