The difference between Oracle Service name and SID

Source: Internet
Author: User

Database name (db_name), instance name (instance_name), and operating system environment variables (ORACLE_SID)   only database name (db_name) and DB instance name (Instance_) in ORACLE7, 8 database Name). A new parameter, the database domain name (db_domain), the service name (SERVICE_NAME), and the operating system environment variable (ORACLE_SID), appears in Oracle8i, 9i. These are identified in the same database and are used to differentiate the parameters of different databases.   What is the database name (db_name)?  The database name is the internal identity used to differentiate the data, is a parameter stored in a binary way in the database control file, and cannot be modified after the data is installed or created. After the database installation is complete, this parameter is written to the database parameter file Pfile, in the following format:  ......... db_name= "ORCL" # (not allowed to modify)  db_domain=dbcenter.toys.com  instance_name=orcl service_names=orcl.dbcenter.toys.com control_file= (...............  .........   has a database name (db_name) in each running Oracle8i database, and if two databases are created in a server program, there are two database names. Its control parameters are divided into different pfile to control the relevant database.    Ii. What is the database instance name (instance_name)    DB instance name used to connect to the operating system for use with external connections. In the operating system to obtain interaction with the database, the database instance name must be used. For example, to connect to a database server, you must know its database instance name, only the database name is useless, and unlike the database name, the instance name can be modified after the data is installed or the database is created. After the database installation is complete, the instance name is written to the database parameter file Pfile, in the following format:  db_name= "ORCL" # (not allowed to modify)  db_domain=dbcenter.toys.com instance _NAME=ORCL # (can be modified, can be the same or different from db_name)  service_names=orcl.dbcenter.toys.com control_file= (........................   the relationship between the database name and the instance name    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, there are two database names, two DB instance names, using these two identities (database name and instance name) to determine a database, the user and the instance are connected.   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, (a database corresponding to multiple instances, the same time the user only one instance of the connection, when an instance fails, other instances of automatic service, to ensure that the database safe operation. )    operating system environment variables (ORACLE_SID)    in practice, the description of the database instance name sometimes uses the instance name (instance_name) parameter, sometimes using the Oracle_sid parameter. These are both database instance names, what's the difference?    (ORACLE_SID)  OS<----------------> ORACLE Database <--------(instance_name (instance name))     represents the relationship between the instance name instance_name, Oracle_sid and the database and operating system, although the two parameters listed here are DB instance names, but the instance_name parameter is the parameter of the ORACLE database. This parameter can be queried in the parameter file, while the ORACLE_SID parameter is the operating system environment variable.   OS environment variable ORACLE_SID is used to interact with the operating system. In other words, to get the instance name in the operating system, you must use ORACLE_SID. 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. such as:  $ORACLE _base/admin/db_name/pfile/init$oracle_sid.ora.   Definition method:   export oracle_sid=orcl   If multiple databases are created on the same server, there must be multiple DB instances at the same time, you can repeat the above definition process to select different instances. &nBSP; You can also use   [[email protected]]$. oraenv  to switch between different oracle_sid to launch different instances via the operating system (instance)  =================================================== ================================  database name: db_name            DB instance name: instance_name   Operating system environment variables:oracle_sid  database service name:service_names  database domain name: db_domain         Global database name: Global_ db_name   only the database name and the instance name in the Oracle7, Oracle8 database, the database domain name, the service name, and the global database name appear in the Oracle8i and oracle9i.    database name (db_name): An internal identifier used to differentiate between one data, an internal token of Oracle, and a parameter stored in a binary way in a database control file.   Database name function: The database name is the internal management tag of the database, and data names are required to install the database, create a new database, create a database control file, modify the database structure, and use database backup and recovery tools for database backup.   After the database installation is complete, the database name is stored in the parameter file (pfile,spfile) and stored in the control file in binary mode, and both must match. If the two do not match, the database prompts for the ORA-01103 error at startup.   Data Name query:  1, query parameter file db_name value  2, select name from v$database 3, sql>show parameter db_name   Modify the database name (modify complex, we recommend not to modify)  1, modify the database names of the parameter file.  2. Rebuild the control file for the database.    DB instance Name (instance_name): Used to connect to the operating system.   The interaction between the operating system and the database must use the database instance name. DatabaseWhen the installation is complete, the DB instance name is stored in the parameter file and stored in the registry.   Database name and instance name are generally one by one corresponding relationships, namely: there is a database name has an instance name, and in the Oracle9i parallel server structure is a one-to-many relationship, that is, a database corresponding to multiple instances.   Data Name query:  1, query parameter file instance_name value  2, select instance_name from V$instance 3, sql>show Parameter instance_name   operating system environment variable (ORACLE_SID): used to correspond to the database instance name. Instance_name is the Oracle database parameter-ORACLE_SID is a system environment variable, that is, the operating system to get the instance name must be done by the system environment variables ORACLE_SID,ORACLE_SID and instance_name must be the same.   is the interface between the database and the operating system, which is used by the operating system, which is the basis for differentiating multiple databases. The oracle_sid is stored in the registry. If the oracle_sid of the database differs from the actual instance_name, all Oracle commands running under the operating system will produce an error.   Definition of Data instance name:  sql>set oracle_sid= DB instance name    database domain name (db_domain): Adds the domain name structure after the database name, The name of the database is unique in the whole network environment and is mainly used for remote replication of data in Oralce distributed environment. The database domain name is stored in the parameter file.   Consider using the database domain name in the following scenarios: 1. In an Oracle distributed environment, the data is transmitted remotely through the data link between two databases. 2, in the same network environment, two database names are the same.   Query of database domain name:  1, query parameter file db_domain value  2, select value from V$parameter where name= ' Db_domain '  3, SQL >show parameter db_domain  Modify database domain name:  1, close database  2, modify Db_domain, service_names in database parameters file.    Database service Name SerVice_names: Database name + database domain name, meaning the same as the global database name. The   database service name is stored in the parameter file.   Query for database service name:  1, query parameter file service_names value  2, select value from V$parameter where name= ' Service_names '  3, sql>show parameter service_names   global database name Global_db_name: Database name + database domain name.     DB instance name, service name and network connection  oracle Server version Client Network driver  oracle7   (support instance name) Sql_net (support instance name)  oracle8   (support instance name) NET8 (support instance name)  ORACLE8i  (support instance name, service name) net8i (support instance name, service name)  ORACLE9i  (support instance name, service name) ORACLE NET service  (Support instance name, service name)   for network connection string use instance name, service name problem, need to be determined according to client-driven version and Oracle Server version, that is, both support the instance name and service name use service name, Otherwise, the instance name is used.  ==================================================================================== 1. The SID is the instance name, and the instance name refers to the name of the database management system that responds to a database operation. The instance name is determined by the parameter instance_name of the initialization parameter file. If this parameter is not specified (that is, instance_name is not specified as any value), then the name of the instance is determined by the user's environment variable ORACLE_SID (note that this is capitalized). Under the Windows platform, the ORACLE_SID value in the registry is determined.   2. Service_Name refers to the global database name in listener: This name is determined by the Global_dbname parameter in Listener.ora. This name represents the client connection to the database when the Tnsnames.ora Service_nThe value corresponding to the AME parameter.  3. Connection Strings       When configuring Tnsname.ora, you can use SIDs or SERVICE_NAME.   Note that these two values are not necessarily the same, depending on the configuration in the database server.  connect_str =   (DESCRIPTION =     (address_list =       (ADDRESS = ( PROTOCOL = TCP) (HOST = xx.xx.xx.xx) (PORT = 1521))     )      (Connect_data =   & nbsp   (service_name = xxxx    )   )  connect_str =   (DESCRIPTION =      (address_list =       (ADDRESS = (PROTOCOL = TCP) (HOST = xx.xx.xx.xx) (PORT = 1521))  & nbsp  )      (Connect_data =       (SID = xxx)        (SERVER = Dedicated)     )   )

Differences between Oracle Service name and SID

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.