Database name, instance name, database domain name, global database name, service name

database name, instance name, database domain name, global database name , service name,

This is a few concepts that make many beginners easily confused. I believe many beginners are as confused as I am in the title of these concepts. Let's get them all figured out right now.

1 Database name

What is a database name?
The database name is the identity of a database, just like a person's ID number. He uses parameter db_name to indicate that if a machine is loaded with multiple databases, then each database has a database name. After the database installation or creation is complete, the parameter db_name is written to the parameter file. The format is as follows:
Db_name=myorcl
...
When you create a database, you should consider the name of the database, and after you create the database, the database name should not be modified, even if you want to modify it will be cumbersome. Because the database name is also written to the control file, the control file is stored in binary mode, and the user cannot modify the contents of the control file. Suppose the user modifies the database name in the parameter file, that is, modifies the value of the db_name. However, when Oracle starts, a ORA-01103 error is returned because the db_name in the parameter file is inconsistent with the database name in the control file, causing the database to fail to start.

Job for database name

The database name is required to install the database, create a new database, create a database control file, modify the data structure, and back up and restore the database.

There are many Oracle installation file directories associated with database names, such as: win:d:\oracle\product\10.1.0\oradata\db_name\. Unix:/home/app/oracle/product/10.1.0/oradata/db_name/...pfile:win:d:\oracle\product\10.1.0\admin\db_name\pfile \ini.oraunix:/home/app/oracle/product/10.1.0/admin/db_name/pfile/init$oracle_sid.ora trace file directory: win:/home/app/ oracle/product/10.1.0/admin/db_name/bdump/...

In addition, when you create data, the database name in the Careatedatabase command also matches the value of the db_name parameter in the parameter file, otherwise an error is generated.
Similarly, the statement that modifies the structure of the database alterdatabase, of course, the name of the database to be modified.
If the control file is corrupted or missing, the database will not load and the control file must be recreated by starting the instance in Nomount and then creating the control file with the Create Controlfile command, of course, in this commandalso refers to db_name.
There are also database names that need to be used when backing up or recovering a database.
In short, the database name is important to understand exactly what it does.

Querying the current database name

Method One: select name from V$database; method Two: Showparameter db method Three: View the parameter file.

Modify Database name
The previous recommendation: the database name should be determined when the database is created, and the database name should not be modified, because modifying the database name is a relatively complex thing. Now, let's explain how to modify the database name after the data has been created. The steps are as follows:
1. Close the database.
2. Modify the value of the db_name parameter in the database parameter file to the new database name.
3. Launch the instance in Nomount mode and build the control file (refer to Oracle Documentation for command syntax for creating control files)

2 DB Instance Name

What is a DB instance name?
The database instance name is the identity used to contact the operating system, meaning the database instance name is used for interaction between the database and the operating system. The instance name is also written to the parameter file, which is instance_name, and the instance name is also written to the registry in the win platform.
The database name and instance name can be the same or different.
In general, database names and instance names are a one-to-many relationship, but if you are in an Oracle Parallel server architecture (that is, Oracle real-time application clusters), the database name and instance name are a one-to-numerous relationship. This is illustrated in the first article in the legend.

Querying the current DB instance name

Method One: Selectinstance_name from V$instance; method Two: Showparameter instance method Three: query in the parameter file.

DB Instance name and Oracle_sid
Although both tables are Oracle instances, there is a difference between the two. Instance_name is an Oracle database parameter. ORACLE_SID is an environment variable for the operating system. Oracld_sid is used to interact with the operating system, that is, to access the instance name from the operating system's perspective and must pass ORACLE_SID. Oracle_sid also needs to be present in the registry in the win console.
And Oracle_sid must be consistent with the value of instance_name, otherwise you will receive an error on the UNIX platform, which is "ORACLE not available" on the win platform, which is "TNS: Protocol Adapter Error".

DB instance name and network connection
The database instance name is also used for the Oracle server identity of the network connection in addition to interacting with the operating system. When you configure the Oracle host connection string, you need to specify the instance name. Of course, the network components of 8i later require the service name service_name. This concept is explained next.

3 Database Domain name

what is a database domain name?
in a distributed database system, between different versions of a database server, whether the operating system is UNIX or Windows, Remote replication is possible between servers through a database link, and the database domain name is primarily used for replication in an Oracle distributed environment. Examples include:
distributed database of national transport systems, of which:
Fujian node: Fj.jtyz
Fujian xiamen node: Xm.fj.jtyz
Jiangxi: Jx.jtyz
Jiangxi Shangrao: Sr.jx.jtyz
This is the database domain name.
the database domain name exists in the parameter file, and his parameter is Db_domain.

Querying database domain names

Method One: Selectvalue from v$parameter where name = ' Db_domain '; method two: Showparameter domain method Three: query in the parameter file.

Global database name
Global Database name = database name + database domain name, as described above the global database name for the Fujian node is: Oradb.fj.jtyz

4 Database service Name

What is the database service name?
Starting with the Oracle9i version, a new parameter, the database service name, was introduced. The name of the parameter is service_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.

Querying database service Names

Method One: Selectvalue from v$parameter where name = ' service_name '; method Two: Showparameter Service_Name method Three: Query in the parameter file.

Database service name and network connection
Oracle network components starting from Oracle8i, database and client connection host strings use the database service name. Previously used is ORACLE_SID, which is the DB instance name

The above content from the network, the specific original post too much will not stick out the source, in short, I hope the students are still a lot of practice can really master, so do not light to see OH. Here are my little additions:

Look at the Listening status--lsnrctl

Service "Elvis" has 2 instance (s).  Instance "Elvis", Status UNKNOWN, have 1 handler (s) for the This service ... Instance "Elvis", Status ready, have 1 handler (s) for the This service ...  Service "Elvisxdb" has 1 instance (s). Instance "Elvis", Status ready, have 1 handler (s) for the This service ... The command completed successfully

You can see that the service name has been accounted for two of the system, and the service name can be 64, but the system occupies two, that is to say we can manually configure 62 service names

Database service Name change

alter system set SERVICE_NAMES=B;

After the change, the corresponding Tnsname.ora file also needs to be modified accordingly.

Servic e  "B"  has 1 instance (s).   instance "Elvis",  status ready, has  1 handler (s)  for this service ... service  "A"  has 1 instance (s).   instance "Elvis", status unknown,  Has 1 handler (s)  for this service ... service  "Elvis"  has 1 instance (s).   instance "Elvis",  status ready,  has 1 handler (s)  for this service ... service  "Elvisxdb"  has 1 instance (s).   instance "Elvis",  status ready,  has 1 handler (s)  for this service ... The command completed successfully 

Modification of the database instance name

Alter system set INSTANCE_NAME=TEST2 Scope=spfile;

This is a dynamic parameter, so you need to specify SPFile, and then restart the database to take effect

Database name, instance name, database domain name, global database name, service name