Recall the differences and relationships between Oracle instance names, service names, and other concepts

Source: Internet
Author: User

Recently, when sorting out content related to the test environment, students often ask the following terms:

Database Name, Instance name, database domain name, Global Database Name, service name,

This is a few confusing concepts for many beginners. I believe that many beginners are confused by these concepts on the title just like me. This blog will let us understand them. To put it one sentence in advance, you only need to understand it at last, and you do not need to deliberately recite the concept.

I. Database Name

What is the database name?

The database name is the ID of a database, just like the ID card number of a person. He uses the DB_NAME parameter to indicate that if multiple full 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 format is as follows:

DB_NAME = myorcl


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.

Database Name

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

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

Winnt: d: \ oracle \ product \ 10.1.0 \ oradata \ DB_NAME \...

Unix:/home/app/oracle/product/10.1.0/oradata/DB_NAME /...


Winnt: d: \ oracle \ product \ 10.1.0 \ admin \ DB_NAME \ pfile \ ini. ora

Unix:/home/app/oracle/product/10.1.0/admin/DB_NAME/pfile/init $ ORACLE_SID.ora

Tracking file directory:

Winnt:/home/app/oracle/product/10.1.0/admin/DB_NAME/bdump /...

In addition, when creating data, the database name in the careate database command must be consistent with the value of the DB_NAME parameter in the parameter file. Otherwise, an error will occur.

Similarly, the alter database statement for modifying the database structure also specifies the name of the database to be modified.

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, this command also refers to DB_NAME.

You also need to use the database name to back up or restore the database.

In short, the database name is very important and it must be understood accurately.

Query the current data name

Method 1: select name from v $ database;

Method 2: show parameter db

Method 3: view the parameter file.

Modify Database Name

We recommend that you determine the database name when creating a database. The database name should not be modified because it is complicated to modify the database name. Now let's take a look at how to modify the database name after the data has been created. 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)

Ii. database instance name

What is the database instance name?

The database instance name is the identifier used to contact the operating system, that is, the interaction between the database and the operating system uses the database instance name. The instance name is also written to the parameter file. The parameter is instance_name. On the winnt platform, the Instance name is also written to the Registry.

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. This is illustrated in the first article.

Query the name of the current database instance

Method 1: select instance_name from v $ instance;

Method 2: show parameter instance

Method 3: query in the parameter file.

Database instance name and ORACLE_SID

Although both tables are oracle instances, the two are different. Instance_name is an oracle database parameter. ORACLE_SID is the environment change volume of the operating system. ORACLD_SID is used to interact with the operating system. That is to say, the Instance name accessed from the operating system must be accessed through ORACLE_SID. There are no winnt instances, and ORACLE_SID must also exist in the registry.

And ORACLE_SID must be consistent with the value of instance_name. Otherwise, you will receive an error. On the unix platform, it is "ORACLE not available". On the winnt platform, it is "TNS: protocol adapter error ".

Database instance name and network connection

In addition to interacting with the operating system, the database instance name is also used as the oracle server ID for network connection. When configuring an oracle host connection string, you need to specify the Instance name. Of course, for network components later than 8i, the service name SERVICE_NAME is required. This concept is explained later.

Iii. Database Domain Name

What is a database domain name?

In the distributed database system, database servers of different versions can be remotely copied between servers, regardless of whether the operating system is unix or windows, the database domain name is mainly used for replication in the oracle distributed environment. Example:

The distributed database of the National Transportation Administration System, where:

Fujian node: fj. jtyz

Xiamen node in Fujian: xm. fj. jtyz

Jiangxi: jx. jtyz

Shangrao, Jiangxi: sr. jx. jtyz

This is the database domain name.

The database domain name exists in the parameter file. Its parameter is db_domain.

Query database domain names

Method 1: select value from v $ parameter where name = 'db _ domain ';

Method 2: show parameter domain

Method 3: query in the parameter file.

Global Database Name

Global Database Name = database name + database domain name. For example, the Global Database Name of the preceding Fujian node is oradb. fj. jtyz.

Iv. Database Service name

What is the database service name?

A new parameter, that is, the Database Service name, is introduced from oracle9i. The parameter name 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.

Query database service names

Method 1: select value from v $ parameter where name = 'service _ name ';

Method 2: show parameter service_name

Method 3: query in the parameter file.

Database Service name and network connection

The oracle network component opened from oracle8i. The connection host string between the database and the client uses the Database Service name. Previously, ORACLE_SID was used, that is, the database instance name.

Create an Oracle database using Oracle10g as an example)

There are two ways to create a database: one is to create a database manually using a command line script; the other is to create a database using the Database Configuration Wizard provided by Oracle. This document describes how to create an Oracle database using a command line script on Unix and Windows.

A complete database system should contain a physical structure, a logical structure, a memory structure, and a process structure. If you want to create a new database, all these structures must be completely created.

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: 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.