Shulou(Shulou.com)06/01 Report--

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

These are several concepts that confuse many beginners. I believe that many beginners are as confused as I am by these concepts in the title. Let's figure them out now.

1 Database name

What is the database name?

The database name is the identity of a database, just like the human × × number. He uses the parameter DB_NAME to indicate that if multiple databases are installed on a machine, then 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 creating the database, the database name should not be modified, even if you want to change it will be very troublesome. Because the database name is also written to the control file, the control file is stored in binary format, and the user cannot modify the contents of the control file. Suppose the user modifies the database name in the parameter file, that is, the value of DB_NAME. However, when Oracle starts, the database startup fails because the DB_NAME in the parameter file is inconsistent with the database name in the control file, and an ORA-01103 error will be returned.

Job with database name

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

There are many Oracle installation file directories that are related to database names For example: 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 creating data, the database name in the careatedatabase command should also be the same as the value of the DB_NAME parameter in the parameter file, otherwise an error will occur.

Similarly, the statement alterdatabase that modifies the database structure, of course, indicates the name of the database to be modified.

If the control file is corrupted or missing, the database cannot be loaded, so recreate the control file by starting the instance in nomount mode, and then create the control file with the create controlfile command, which also refers to DB _ NAME.

And you need to use the database name when backing up or restoring the database.

In short, the database name is very important, to accurately understand its role.

Query the current database name

Method 1: select name from vault database; method 2: showparameter db method 3: check the parameter file.

Modify the database name

The previous suggestion: you should determine the database name when you create the database, and the database name should not be modified, because changing the database name is a complicated thing. So now let's explain how to change the database name after the data has been created. The steps are as follows:

1. Shut down the database.

two。 Change the value of the DB_NAME parameter in the database parameter file to the new database name.

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

2 Database instance name

What is the database instance name?

The database instance name is the identity used to contact the operating system, which means that the interaction between the database and the operating system uses the database instance name. The instance name is also written to the parameter file, which is instance_name, and on the win 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, oracle real-time application cluster), the database name and instance name are one-to-many. This point is illustrated by a legend in the first article.

Query the current database instance name

Method 1: selectinstance_name from instance; method 2: showparameter instance method 3: query in the parameter file.

Database instance name and ORACLE_SID

Although both tables are oracle instances, there is a difference. Instance_name is the oracle database parameter. ORACLE_SID is the environment variable of the operating system. ORACLD_SID is used to interact with the operating system, that is, the instance name must be accessed through ORACLE_SID from the operating system's point of view. On win, ORACLE_SID also needs to exist in the registry.

And ORACLE_SID must be consistent with the value of instance_name, otherwise, you will receive an error, on unix platform, it is "ORACLE not available", on win 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 to identify the oracle server of the network connection. When you configure the oracle host connection string, you need to specify the instance name. Of course, network components later than 8i require the service name SERVICE_NAME. This concept is explained next.

3 database domain name

What is a database domain name?

In distributed database systems, different versions of database servers can be replicated remotely through database links between different versions of database servers, no matter which operating system is unix or windows. Database domain names are mainly used for replication in oracle distributed environment. Examples such as:

The distributed database of the national transportation administration system, among 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 its parameter is db_domain.

Query database domain name

Method 1: selectvalue from v$parameter where name = 'db_domain'; method 2: showparameter domain method 3: query in the parameter file.

Global database name

Global database name = database name + database domain name. As mentioned above, the global database name of 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, has been introduced. 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 name

Method 1: selectvalue from v$parameter where name = 'service_name'; method 2: showparameter service_name method 3: query in the parameter file.

Database service name and network connection

Starting from oracle8i, the oracle network component uses the database service name as the connection host string between the database and the client. I used to use ORACLE_SID, that is, the database instance name

The above content is sorted out from the network, and if there are too many specific original posts, the source will not be posted. In short, I hope that the students can really master it only after more practice, so don't just read it. Here is my little addition:

Check the listening status-- lsnrctl

Service "elvis" has 2 instance (s). Instance "elvis", status UNKNOWN, has 1 handler (s) for this service... 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

You can see that two service names have been occupied by the system, while there can be 64 service names, but the system occupies two, that is, 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.

Service "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 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

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.