Shulou(Shulou.com)05/31 Report--

This article shows you what is the difference between Database name, Instance Name and ORACLE_SID in Oracle. The content is concise and easy to understand. It will definitely make you shine. I hope you can gain something from the detailed introduction of this article.

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

I. Database name

1 What is a database name?

A database name is an identifier for a database, just like a person's ID number. The DB_NAME parameter indicates that if multiple full databases are installed on a machine, each database has a database name. After the database installation or creation is complete, the parameter DB_NAME is written to the parameter file. Format:

DB_NAME=orcl

When creating a database, you should consider the database name, and after creating the database, the database name should not be modified, even if you want to modify it, it will be very troublesome. Because the database name is also written to the control file, which is stored in binary form, 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 DB_NAME. However, when Oracle starts, the database fails to start because DB_NAME in the parameter file is inconsistent with the database name in the control file, and ORA-01103 error is returned.

2. The role of database name

Database names are used when installing databases, creating new databases, creating database control files, modifying data structures, and backing up and restoring databases.

There are many Oracle installation directories that are associated with database names, such as:

winnt: e:/oracle/product/10.1.0/oradata/DB_NAME/...

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

Parameter file pfile:

winnt: e:/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

Track file directory:

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

If the control file is damaged or lost, the database will not be loaded, then to recreate the control file, the method is to start the instance in nomount mode, and then create the control file with the create controlfile command, of course, this command also refers to DB_NAME. Also, when backing up or restoring a database, you need to use the database name. In short, the database name is very important, to accurately understand its role.

3. Query the current data name

Select name from v$database;

Method 2: Show parameter db

Method 3: Check the parameter file.

4, modify the database name

Suggestion: The database name should be determined when creating the database, and the database name should not be modified, because modifying the database name is a more complicated matter. So let's explain how to modify the database name after the data has been created. The procedure is as follows:

1. Close the database.

2. Modify the DB_NAME parameter value in the database parameter file to the new database name.

3. Start the instance in NOMOUNT mode and build the control file (refer to oracle documentation for command syntax of creating control file)

II. Database instance name

1 What is a database instance name?

An instance is a portion of computer memory and auxiliary processing background processes required to access the oracle database. It is a collection of processes and memory used by these processes (SGA). It does not need a database associated with it at all. An instance can be started without any data file.

The start-up process is as follows:

sqlplus / as sysdba

sql>startup nomount #started an instance, now SGA allocated, process running, everything started except database

sql>alter database mount #Find and load the control file (specified in the init.ora file), then use the control file to locate redo log files, data files, temporary files

SQL>alter database open #oracle opens all online data files and redo log files for user access.

Everyone accessing oracle is accessing an instance, but if this instance is associated with a database file, it can be accessed. If not, you will get an error that the instance is unavailable.

Instance name refers to the name of the database management system used to respond to a database operation, which is also called sid. The instance name is determined by the instance_name parameter. If this parameter is not specified (i.e., instance_name is not specified), then the instance name is determined by the user's environment variable ORACLE_SID(note that this is capitalized).

2. Examples and databases

Difference between instance and database:

1) Instance is temporary, it exists only as long as the associated process and memory collection exist, database is permanent, it exists as long as the file exists.

2) An instance can install and open a single database during its lifetime; databases can be installed and opened by many instances, or one instance after another, each opening the same database.

Database instance names are used for external connections. In order to contact the database in the operating system, you must use the database instance name. To connect to a database server, you must know its database instance name, only knowing the database name is useless. Unlike database names, instance names can be modified after the database is installed or created. The database name and instance name can be the same or different. In general, database name and instance name are one-to-one relationship, but if in oracle parallel server architecture (oracle real-time application cluster), database name and instance name are one-to-many relationship (one database corresponds to multiple instances, users only have one instance to contact at the same time, and when one instance fails, other instances automatically serve to ensure safe operation of the database).

Query the current database instance name

Select instance_name from v$instance;

Method 2: Show parameter instance;

ORALCE_SID

In practice, the identification of database instances sometimes uses the instance name and sometimes uses ORACLE_SID. What is the difference between them?

(ORACLE_SID)

OS; ORACLE database

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.