Relationship between Oracle Database Name and Oracle Instance name

Relationship between Oracle Database Name and Oracle Instance name 1 Database Name 1.1 Concept Database Name db_name is a database ID, just like a person's ID card number. If multiple databases are installed on a machine, each database has a database name. After the database is installed or created, the parameter DB_NAME is written to the parameter file. The database name is in $ ORACLE_HOME/admin/db_name/pfile/init. ora (or $ ORACLE_BASE/admin/db_name/pfile/init. ora or $ ORACLE_HOME/dbs/SPFILE <Instance name>. ORA) file ###################################### ###### Database Identification ################################ ########## db_domain = "" db_name = orcl when creating a database, you should consider the database name, the database name cannot be modified after the database is created. It may be difficult to modify the database name. Because the database name is also written into the control file and the control file is stored in binary format, you cannot modify the control file content. Assume that you have modified the database name in the parameter file, that is, the value of DB_NAME. But at Oracle startup, because DB_NAME In the parameter file is inconsistent with the database name in the control file, causing database startup failure, a ORA-01103 error is returned. 1.2 database name function the database name must be used when installing the database, creating a new database, creating a database control file, modifying the data structure, and backing up and restoring the database (note that these cannot be used use sid, also, the database name is used for alter database ). There are many Oracle installation file directories related to database names, such as: winnt: F: \ oracle \ product \ 10.2.0 \ oradata \ DB_NAME \... another example is the parameter file pfile: winnt: F: \ oracle \ product \ 10.2.0 \ admin \ DB_NAME \ pfile \ init. ora.54200885729 if the control file is corrupted or lost, the database cannot be loaded. In this case, you need to re-create the control file by starting the instance in nomount mode, and then run the create controlfile command to create the control file, of course, DB_NAME must be specified in this command. 1.3 Recommended database name modification: the database name should be determined when the database is created. The database name should not be modified because it is complicated to modify the database name. It is the internal identifier for distinguishing data (that is, used internally by the Oracle database), because it is saved in multiple places in the database and saved in the control file is binary, so it is difficult to change its name. After the data has been created, modify the database name. The steps are as follows: (1) shut down the database. (2) modify the value of DB_NAME in the database parameter file to the new database name. (3) Start the instance in NOMOUNT mode and construct the control file (for the command syntax for creating the control file, refer to the oracle document) 1.4 method 1 for querying the current database name: select name from v $ database; Method 2: show parameter db method 3: view the parameter file, $ ORACLE_HOME/admin/db_name/pfile/init. ora (or $ ORACLE_BASE/admin/db_name/pfile/init. ora) 2 database instance name (instance name) 2.1 concept of database instance name (instance_name) the instance name indicates the memory structures + Background process (MEM + BGP) used to load or open a database ). In unix, the Instance name is defined by the ORACLE_SID environment variable and by the service name in windows. It can be used in the connection string. This method is used before the 8i version. Although 10 Gb is still supported, it is not recommended because it lacks support for some new features. An instance is a part of the computer memory and auxiliary processing background processes required to access the Oracle database. It is a collection of processes and the memory used by these processes (SGA, it does not need a database associated with it, and can start the instance without any data files. The following is the Oralce startup process: sqlplus/as sysdba SQL> startup nomount # Starts an instance. Now the SGA instance is allocated and the process is running, all except the database, SQL> alter database mount # use the control file (init. (specified in the ora file) Locate and rebuild the log file, data file, temporary file SQL> alter database open # The database can be accessed by anyone through this instance. So it can be understood that accessing Oracle accesses an instance. However, if the instance is associated with a database file, it can be accessed. If it is not, the instance may be unavailable. Instance name refers to the name of the database management system used to respond to a database operation, also known as SID. The instance name is determined by the instance_name parameter. If this parameter is not specified (instance_name is not specified as any value), the Instance name is determined by the user's environmental variable ORACLE_SID (note that this parameter is in upper case. On windows, the oracle_sid value in the registry is determined. The instance name and SID are one-to-one correspondence, with the same name but different storage locations. 2.2 The differences between an instance and a database are as follows: (1) the instance is temporary and exists only when the related process and memory set exist. The database is permanent, as long as the file exists. (2) A single database can be installed and opened for an instance within its lifetime. A database can be installed and opened by many instances, or installed and opened by one instance, or multiple instances can be installed and enabled simultaneously (RAC ). In short, RAC is a system that enables multiple instances to open a database file at the same time. It is structured on multiple machines, and each machine runs one instance, each instance opens the same database (which is implemented by the disk sharing technology) and needs to synchronize high-speed cache between these instances to ensure that multiple instances are completely consistent, they do not conflict with each other or even overwrite each other. The database instance name (instance_name) is used for external connections. To contact the database 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 and only know the database name. Different from the database name, the Instance name can be modified after the data is installed or the database is created. The Database Name and Instance name can be the same or different. In general, the database name and Instance name are one-to-one, but in the oracle Parallel Server Architecture (that is, the oracle Real-time application cluster, the Database Name and Instance name are one-to-multiple relationships (one database corresponds to multiple instances, and users are only associated with one instance during the same time period. When an instance fails, other instances will automatically serve, to ensure the safe operation of the database ). An instance is a group of operating system processes (or multi-threaded processes) and some memory. These processes can operate databases; the database is just a collection of files (including data files, temporary files, redo log files, and control files ). At any time, an instance can only have a group of related files (associated with a database ). In most cases, the opposite is true: only one instance in a database operates on it. However, Oracle's Application cluster (RAC) is an exception. This is an option provided by Oracle that allows operations on multiple computers in the cluster environment, in this way, multiple instances can simultaneously load and open a database (located on a group of shared physical disks ). Therefore, we can access the database from multiple computers at the same time. Oracle RAC supports highly available systems and can be used to build highly scalable solutions. 2.3 query the current database instance name Method 1: select instance_name from v $ instance; Method 2: show parameter instane3 ORACLE_SID (environmental variable) In practice, what are the differences between the instance names and ORACLE_SID for database instance identifiers? (Very confusing) OS (ORACLE_SID) <----------------> 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 database instance names, the instance_name parameter is an ORACLE database parameter, which can be queried in the parameter file, while the ORACLE_SID parameter is an operating system environment variable, similar to ORACLE_BASE and ORACLE_HOME, it 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. And ORACLE_SID must be consistent with the value of instance_name. Otherwise, you will receive an error "TNS: protocol adapter error" on winnt platform ". After the database is installed, ORACLE_SID is used to define the name of the database parameter file. For example, $ ORACLE_BASE/admin/DB_NAME/pfile/init $ ORACLE_SID.ora. If multiple databases are created on the same server, multiple database instances must exist at the same time. You can repeat the definition process (export ORACLE_SID = orcl) to select different instances. You can also use the oraenv command line to switch between different ORACLE_SID to start different instances through the operating system. 4. Database domain name and global database name 4.1 concept and contact the database name (da_name) uniquely identifies a database. This indicates that it is sufficient for a single database. However, with the popularization of distributed databases composed of multiple databases, this puts 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, so that 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. Database domain name (db_domain): defines the domain where a database is located. The domain name has nothing to do with the "Domain" of the Internet, it is determined by the database administrator based on the actual situation to better manage distributed databases. For ease of management, You can regard it as a domain of the Internet. Global database Name (global_name): uniquely identifies a database (Oracle database). We recommend that you use this method to identify a 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 <db_name.db_domain> command to modify it, modify the parameters. 4.2 database domain name query method 1: select value from v $ parameter where name = 'db _ domain '; (10 Gb is not feasible) Method 2: show parameter domain method 3: query In the parameter file, $ ORACLE_HOME/admin/db_name/pfile/init. ora (or $ ORACLE_BASE/admin/db_name/pfile/init. ora) 4.3 example: the distributed database of the National Transportation GIS system, where: Jilin node: jl. jtgis Jilin Changchun node: cc. jl. jtgis Hebei node: hb. jtgis Hebei Shijiazhuang node: sjz. hb. jtgis is the database domain name. The database domain name exists in the parameter file. Her parameter is db_domain. global Database Name = database name + database domain name. For example, the Global Database Name of the preceding Fujian node is: orcl. jl. jtgis5 Database Service name (service_na Me) this parameter is newly introduced by oracle8i. Before 8i, we used SID to represent an instance of the database. However, in the parallel environment of Oracle, a database corresponds to multiple instances, which requires multiple network service names and is cumbersome to set. The service_name parameter is introduced to facilitate settings in the parallel environment. This parameter corresponds to a database, not an instance, and has many other benefits. The default value of this parameter is db_name.db_domain, which is equal to global_name. If the database has a domain name, the Database Service name is the global database name; otherwise, the Database Service name is the same as the database name. A database can correspond to multiple service_names for more flexible configuration. This parameter has no direct relationship with SID, that is, service_name does not have to be the same as SID. For oracle network components starting with Oracle8i, Database Service names are recommended for host strings connected between databases and clients. The SID is used before, that is, the database instance name. SID = the unique name of your DB instance, ServiceName = the alias used when connecting. If the database is registered with listener in this way, you can use SERVICE_NAME parameter in the tnsnames. ora file; otherwise, SID is required. Method 1: select value from v $ parameter where name = 'service _ name'; (10 Gb is not feasible) Method 2: show parameter service_name 6 network service name (net_service_name), also known as database alias (database alias), is required when client programs access the database, it shields the details of how the client connects to the server and achieves the location transparency of the database. The Network Service name is recorded in the tnsnames. ora file. You can use netmgr to create a network service name. The Network Service name is from the client perspective. When the client connects to a remote database or other services, you can specify the network service name. Therefore, you must use one or more naming methods to resolve the Net service name to the connection descriptor of the connected database or other services. The naming method is as follows: [local] -- tnsnames stored on the local client. the Network Service name in the ora file is parsed as the connection descriptor. [Oracle Names]-The Oracle name server provides Resolution Methods for each Oracle Net service on the network [host name]-connects to the Oracle Database Service through the host alias in the TCP/IP Environment [Sun NIS ]/[dce cds] -- used by dedicated systems, not applicable in Windows 2000