Relationship between Oracle Database Name and Oracle Instance name [basic concepts are involved]

1. Database Name 1.1 The Concept Database Name (db_name) is the identifier of a database, just like the ID card number of a person. 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_HOMEa

1. Database Name 1.1 The Concept Database Name (db_name) is the identifier of a database, just like the ID card number of a person. 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/

1 Database Name 1.1 concept of Database Name

The Database Name (db_name) is the ID of a database, just like the ID card number of a person. 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 <实例名> . ORA) File

######################################## ###
# Database Identification
######################################## ###
Db_domain = ""
Db_name = orcl

When creating a database, you should consider the database name, and after the database is created, the database name should not be modified, even if you want to modify it will be very troublesome. 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 roles of database names

The database name must be used when installing the database, creating a new database, creating a database control file, modifying the data structure, backing up and restoring the database (note that sid cannot be used in these cases, also, the database name is used for alter database ).

Many Oracle installation file directories are related to database names, such:

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 damaged 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 modify Database Name

Suggestion: 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 procedure is 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 build 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 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 Startup Process of Oralce is as follows:

Sqlplus/as sysdba
SQL> startup nomount # started an instance. Now the SGA instance is allocated, the process is running, and everything except the database is started.
SQL> alter database mount # use the control file (specified in the init. ora file) to locate and rebuild log files, data files, and temporary files
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 Method 1: select instance_name from v $ instance;
Method 2: show parameter instance
3 ORACLE_SID (environment variable)

In reality, what are the differences between database instance identifiers, sometimes using instance names and ORACLE_SID? (Very confusing)

OS (ORACLE_SID) <----------------> ORACLE database <-------- (instance_name (Instance name ))

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 the parameter of the ORACLE database, this parameter can be queried in the parameter file, while the ORACLE_SID parameter is an operating system environment variable. It is used in the same way as ORACLE_BASE and ORACLE_HOME for interaction 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, there must be multiple database instances at the same time. At this 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 different ORACLE_SID to start different instances through the operating system)

4 database domain name and global database name 4.1 concepts and Contacts

Using the Database Name (da_name) to uniquely identify a database is sufficient for a single database, but with the popularity of distributed databases composed of multiple databases, this puts a certain burden on database management, because the names of each database 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 alter database rename global_name Command, and then modify the corresponding parameters.

4.2 method 1 for querying database domain names: 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
These are the database domain names. The database domain names exist in the parameter file, and their parameters are db_domain.
Global Database Name = database name + database domain name. For example, the Global Database Name of the preceding Fujian node is orcl. jl. jtgis.
5. Database Service name (service_name) 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.

Query the database service name (method 2 is feasible)

Method 1: select value from v $ parameter where name = 'service _ name'; (10 Gb is not feasible)

Method 2: show parameter service_name