"One" understanding of the Oracle_sid
--------------------------------------------------------------------------------
The SID in Oracle acts like a "switch variable"---directs Oracle to start the instance correctly by reading the appropriate parameter file and loading it in the default location when the instance starts.
We know that the boot of the instance needs to specify Pfile/spfile (oracle9i before using the pfile--as the init file, the default is to use the spfile--as a server-side parameter file starting from 9i the location of the two files is:
A.pfile: Name is Init<oracle_sid>.ora
B.spfile: Name is Spfile<oracle_sid>.ora
We see that both the init file and the SPFile file are labeled with Oracle_sid, which is what the SID does:
"1" When we start an instance, we first tell ORACLE the next instance to launch 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 that instance--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 boot process for instances such as background processes
"4" When this instance is successfully started, we can re-execute set oracle_sid = XXX or export oracle_sid = XXX command again to dial "switch to" another SID, and then repeat the above steps
From the above procedure we see that the Oracle SID function is an obvious "switch variable", which pulls to the side, then Oracle will find and its corresponding parameter file to launch the instance. The second SID also plays a role in 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. The background process is named through the SID.
The operating system must also deal with the Oracle instance through the SID, the operating system does not know what instance_name, only know Oracle_sid, in Oracle internally by Oracle to identify different instances of the SID. So Oracle_sid is more of "a window of Oracle and external operating system communication".
The understanding of "two" to instance_name
--------------------------------------------------------------------------------
Instance_name is a real parameter compared to a "switch variable" such as Oracle_sid. It is configured in the Init/pfile file (db_name). The default value for identifying the name of the DB instance is oracle_sid.
Instance_name is like a person's name, while SID is the person's ID number. Through the SID we find the corresponding Init/spfile file, and the Init/spfile file by instance_name This parameter tells us what the corresponding instance name. In this way, Oracle_sid, parameter files, and instance_name are linked together.
The cause of oracle_sid is different, but the instance_name is usually the same because the original parameter file has been copied, but the value of the instance_name parameter is forgotten. However, after 10G instance_name this parameter and disappeared from the init/spfile, so as not to cause confusion.
The comprehension 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, through which we can know the location of the control files, log files, and data files on the corresponding disk.
Db_name is specified when the database is created, as is the name of Oracle_sid by default. But the same as instance_name. Db_name cannot be modified once it is determined, as it will be stored in Init/spfie, control files, log files, data files. You can only modify the value of the db_name parameter in the Init/spfile file, but you cannot manually modify the values of other files. So once the modification causes several file values to be mismatched then the database startup 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 at a time.
B. A database can be mount and open on one or more instances (only in a RAC environment where a database can only be mount and opened by one instance at a time).
So how do you know if the instance should be attached to that database and open it? is to rely 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 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-- Complete mount and Open
Correspondence between "Four" oracle_sid, instance_name and db_name
--------------------------------------------------------------------------------
We already know that Oracle_sid is used to isolate different instances of the same oracle_home. But what about the different oracle_home? Can you have a SID with the same name?
In fact, this is the same as the file system rules: In the same directory is not allowed to have the same name of the file exists, but different directories can have the same name of the file. The same oracle_home under the same SID can not exist, and different oracle_home under the same name of the SID (that is, the same machine, as long as the oracle_home different can).
So what about instance_name? By default, instance_name and Oracle_sid are the same, or they 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 example of life to parse:
In a class class_1 inside, there are two students, their names are called Zhang San, but their school number must be different. Here "Zhang San" is instance_name, and the study number is SID. So instance_name repetition does not matter, as long as the SID difference can be distinguished.
But in another class class_2 inside, there are students with the same student number, this time there is the same SID we say the problem, then how to distinguish it? Don't forget we still have the concept of Class! This corresponds to Oracle, which is oracle_home.
As for the correspondence between instance_name and db_name, it's good to understand that different instances can be mounted to different db, or they can be mounted to the same db. This can be interpreted as "competing access to the same disk file by multiple processes simultaneously".
A. Under the same oracle_home, instance_name can be the same, but Oracle_sid must be unique. Distinguish different instances by oracle_sid
B. Under different oracle_home, Oracle_sid can be the same, by Oracle_home to differentiate different SIDs
C. Different instance_name can correspond to different db_name, can also correspond to the same db_name. However, if you are not in a RAC environment, you can only start the instance at the same time and not mount it simultaneously
Relevant parameter files for the "five" Oracle instance startup process
--------------------------------------------------------------------------------
As mentioned earlier, when the 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 that when the two files are present at the same time?
With Oracle 9i as an example, the order in which to start an instance by default is SPFile:
Spfile<oracle_sid>.ora---> Spfile.ora---> Init<oracle_sid>.ora
That is, the default load and sid corresponding parameter file, if not found the default parameter file, and can not find the old init file, if still cannot find the instance cannot start.
The difference between service name and SID
Database names (db_name), instance names (instance_name), and operating system environment variables (ORACLE_SID) have only database names (db_name) and DB instance names (instance_name) in ORACLE7, 8 databases. 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. First, what is the database name (db_name)? The database name is the internal identity used to differentiate the data, and it is a parameter stored in a binary way in a database control file that 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.cominstance_name= Orclservice_names=orcl.dbcenter.toys.comcontrol_file= (........................ There is a database name (db_name) in each running Oracle8i database, and two database names if two databases are created in a server program. 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.COMINSTANCE_NAME=ORCL # ( Can be modified, can be the same as db_name or different) service_names=orcl.dbcenter.toys.comcontrol_file= (........................ Between the database name and the instance nameRelationship database name and instance name is generally the relationship between the 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. However, 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? (often confused) (ORACLE_SID) os<----------------> ORACLE Database <--------(instance_name (instance name)) represents the instance name Instance _name, Oracle_sid and the database and operating system, although the two parameters listed here are the database instance name, but the instance_name parameter is the parameters of the ORACLE database, this parameter can be queried in the parameter file, and Oracle_ The SID parameter is the operating system environment variable. The operating system 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. 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 variable: Oracle_ Sid database Service Name: Service_names database Domain name: Db_domain Global Database name:global_db_name only the database name and instance name in the Oracle7, Oracle8 database, in Oracle8i, The database domain name, service name, and global database name appear in the oracle9i. database name (db_name): An internal identifier used to differentiate between one data, an internal token of Oracle, and a parameter that is stored in a binary way in a database control file. The role of the database name: 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, which must match. If the two do not match, the database prompts for the ORA-01103 error at startup. Query for Data name: 1, query parameter file db_name value 2, select name from V$DATABASE3, sql>show parameter db_name Modify database name (modify complex, recommend not modify) 1, Modify the database name 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. After the database installation is complete, the database instance name is stored in the parameter file and stored in the registry. The database name and instance name are generally one by one correspondence, namely: there is a database name has an instance name, and in Oracle9i's parallel server structure is a one-to-many relationship, that is, a database corresponding to multiple instances. Query for Data name: 1, query parameter file instance_name value 2, select instance_name from V$instance3, sql>show parameter instance_nameOperating 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 variable 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 the data instance name: Sql>set oracle_sid= DB instance Nameinstance_name and Oracle_sid are the same by default. In fact Oracle_sid and instance_name originally had no relationship. When the operating system interacts with the database, Oracle_sid is used, and when the external connection isinstance_name is used to interact with the library. When multiple databases are installed on the same server, the operating system uses ORACLE_SID to differentiate between different instances of the process, and when we connect to different databases of this server, we use instance_name to determine which database to link to.
Database domain name (db_domain): After the name of the database to increase the composition of the domain name, so that the name of the database is unique throughout the network environment, mainly for ORALCE distributed environment Data remote replication. The database domain name is stored in the parameter file. Consider using the database domain name in the following cases: 1. In an Oracle distributed environment, the data is transmitted remotely through a data link between two databases. 2, in the same network environment, two database names are the same. Query for 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 the database parameter file Db_domain, Service_names. 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) The problem of using instance name and service name for network connection string needs to be determined by client-driven version and Oracle Server version. That is, both support the use of the service name for the instance name and service name, otherwise use the instance name.
The relationship between Oracle Sid,instance_name,db_name,oracle_sid
