Introduction to the nine _ names of Oracle

1. db_name Database Name

SQL> connect xys/manager as sysdba

Connected.

SQL> show useruser is "sys" SQL> show parameter db_namename type value ---- -------- ---------- db_name string test1

Db_name is the name of the database, and Oracle itself may be useful. It is of little use to us. db_name is recorded in controlfile, datafile_header, and redo, there are two ways to modify db_name: 1. re-create controlfile, And then you must open the database in reseglogs mode; 2. Use NID. In addition, db_name is limited to a maximum of 8 characters when creating a database. Although there is no error prompt when creating a 10 Gb database, you can see at most a few characters in the database, even if the input exceeds 8 characters, no error is returned, but it is finally truncated. Previously, I handled some problems caused by the truncation of db_name for an enterprise.

SQL> DESC v $ database; is the name blank? Type ----------- -------- --------- dbid numbername varchar2 (9) SQL>

Therefore, it is very important to specify the appropriate db_name when creating a database. Db_name also plays an important role in dynamically registering a listener. No matter whether SERVICE_NAME or the value of SERVICE_NAME is specified, pmon uses db_name to dynamically register the listener.

SQL> host LSNRCTL status LSNRCTL for 32-bit windows: Version 10.2.0.1.0-production on-2007 copyright (c) 1991,200 5, Oracle. All rights reserved.

The 9 _ names of Oracle are the summary of the course hours. This article provides an in-depth analysis of the nine _ names with instances.

Connecting to (address = (Protocol = TCP) (host =) (Port = 1521 ))

Listener status ---------------------- alias listener version tnslsnr for 32-bit windows: version 10.2.0.1.0-production start date:-2007 09:29:47 normal running time: 0 days, 0 hours, 49 minutes, 50 seconds tracking level off security on: Local OS authenticationsnmp off listener Log File E: /Oracle/product/10.2.0/db_1 etwork/log/listener. summary of log listening endpoints... (description = (address = (Protocol = TCP) (host = xys) (Port = 1521 )))

Service summary ..

The Service "test2.com" contains one routine.

Routine "inst_test", status ready, contains 1 handler of this service...

The Service "test3.com" contains one routine.

Routine "inst_test", status ready, contains 1 handler of this service...

The Service "test1.com" contains one routine.

Routine "inst_test", status ready, contains 1 handler of this service...

The Service "test10000xpt.com" contains one routine.

Routine "inst_test", status ready, contains 1 handler of this service...

Command executed successfully

SQL> show parameter service_namesname type value -------------------------------------- service_names string Test2, test3

We found that the service_names value is Test2 and test3, but the result displayed by LSNRCTL status contains"

The Service "test1.com" contains one routine.

Routine "inst_test", status ready, contains 1 handler of this service ..."

　　2. instnace_name

SQL> show parameter instance_namename type value -------------------------------------- ------------------------------------ instance_name string inst_test

Instance_name is used in addition to Dynamic Registration listening. So far, I have not found any other use. Maybe Oracle uses it to differentiate instances? However, I believe that only instance_name cannot be fully differentiated. At least Sid is also used. Let's take a look at the information displayed in the Dynamic Registration listener, where inst_test is instance_name.

　3. Sid: system identifier

The SID identifies the instance's shared memory on a host, but may not uniquelydistinguish this instancefrom other instances

In the doc, Sid is interpreted as marking the shared memory of the instance with Sid on the host. It can be seen that Sid mainly deals with OS.

Sid can be queried in the database using the following statement:

SQL> select instance_name from V $ instance; instance_name -------------- tsid

Although the instance_name field in V $ instance looks like the Instance name, it actually stores the SID. In win, the SID cannot be repeated, no matter whether ORACLE_HOME is the same or not, the differences here are mainly for Unix/Linux, in Unix/Linux, As long as oracle of different versions is installed in different ORACLE_HOME, instances with the same Sid can be created, but Windows cannot. This is not determined by Oracle, it is mainly restricted by Windows Services. There cannot be Oracle services with the same service name in the service. The service name is in the following format: oracleservicesid, because the service name includes Sid, therefore, if the SID is the same, the service name is the same, which is not allowed by windows. Therefore, different instances with the same SID cannot be created under Win.

　　4. service_names service name

The service name is a complex number. After reading it, it means that service_names can be multiple values. The service name here is not used in Dynamic Registration listening, but has no other usage, we recommend that you use the same service_names on primary and standby in dataguard, so that you can implement transparent switching as much as possible, provided that static listening is not configured, of course, if the listener with static registration is configured in primary, standby must also ensure that the input service name is the same in listener. In that case, implement transparent switching as much as possible. The following query shows service_names:

SQL> show parameter service_namesname type value -------------------------------------- service_names string Test2, test3

Here I have specified two values: Test2 and test3. Let's take a look at how a dynamically registered listener uses the service name. Some status information of the listener is as follows:

The Service "test2.com" contains one routine.

Routine "inst_test", status ready, contains 1 handler of this service...

The Service "test3.com" contains one routine.

Routine "inst_test", status ready, contains 1 handler of this service...

Here, we can see that the service name is suffixed with com because I set db_domain

　　5. db_domain database Domain Name

SQL> show parameter db_domainname type value ------------------------------ db_domain string com

Doc says it is ". "segmentation, including a maximum of 128 characters at the time of the period, has not been changed so long, do not know, has not been verified, who wants to verify it, the role of db_domain is mainly used in distributed databases, each database of distributed transactions should have db_domain, but they are required to be the same. I didn't mention it in the doc, and I didn't know it. The previous Unit developed a distributed environment, but I didn't pay attention to it at the time, however, the database of the object to be synchronized in the Advanced Replication requires db_domain in any way. It is not clear whether the requirements are the same, I set db_domain to the same during configuration replication. Another purpose of introducing db_domain is to create a database with the same name db_name in the same OS domain. If you want to create a database with the same name db_name, we recommend that you set the database with the same db_name to a different db_domain, to ensure that global_name is unique in the same domain. This is also recommended on the DOC: Oracle recommends that you specify db_domain as a unique string for all databases in a domain

When db_domain is specified, db_domain (DOC:

If you omit the domains from the name of a database link, Oracle expands the name by qualifying the database with the domain of your local database as it currently exists in the data dictionary, and then stores the Link name in the data dictionary. the characters valid in a database domainname are: alphanumeric characters, underscore (_), and number sign (#).)

9i seems to remember this, but I did not verify it for 10 GB:

SQL> CREATE DATABASE LINK dbl_test using 'orcl ';

The database link has been created.

SQL> select db_link from dba_db_links; DB_LINK-----------------------------------DBLDBL_TESTORCL

Another important thing is: you must set this parameter for every instance, and multiple instances must have the same value in real Application Clusters

　　6. global_name Global Database Name

Global_name is composed of db_name.db_domain. The doc is as follows:

The following is a reference clip:

Understanding how global database names are formed a global database name is formed from two components: a database name and a domain. the database name and the domain name are determined by the following initialization parameters at database creation: component parameter requirements example database name db_name must be eight characters or less. sales domain containing the database db_domain must follow standard Internet conventions. levels in domain names must be separated by dots and the order of domain names is from leaf to root, left to right. us.acme.com

However, it is found through verification that Oracle has not synchronized db_name.db_domain and global_name. Why? Global_name Oracle provides a view, SYS. global_name, Which is originated from props $. You can view the script for creating a view. Finally, we access a public synonym global_name:

The following is a reference clip:

SQL> select * From global_name; global_name --------------------------------------------- test1

According to the doc, the query result I saw above should be test1.com. This is my question. How long has it been?

However, we can also modify global_name:

SQL> alter database rename global_name to test1.com;

The database has been changed.

The following is a reference clip:

SQL> select * From global_name; global_name ------------------------------------------------------ test1.com SQL> alter database rename global_name to test123.com;

The database has been changed.

The following is a reference clip:

SQL> select * From global_name; global_name ----------------------------- test123.com SQL>

Note that once a domain is added, the preceding command cannot be removed, for example:

SQL> alter database rename global_name to test123.com;

The database has been changed.

The following is a reference clip:

SQL> select * From global_name; global_name ---------------------------------------------------- test1.com SQL> alter database rename global_name to test123;

The database has been changed.

The following is a reference clip:

SQL> select * From global_name; global_name ---------------------------------------------------- test1.com SQL> alter database rename global_name to test123;

The database has been changed.

The following is a reference clip:

SQL> select * From global_name; global_name ---------------------------- test123.com SQL>

However, you can directly update global_name or props $ to remove the Suffix:

SQL> Update global_name set global_name = 'test1 ';

Update 1 line.

SQL> commit;

Submitted.

The following is a reference clip:

SQL> select * From global_name; global_name ------------------------------ test1 SQL>

Global_name is mainly used in distributed database. I only used global_name in advanced replication.

For details, refer to the following connection:

[Url = login... dmin.htm # sthref4096 [/url]

Why do you mention it? global_names and global_name look very similar. global_names is used to determine whether to force the global_name of the remote database when creating the DB link. If global_names = true, the DB Link name must be global_name of the remote database. Otherwise, the DB link cannot be used together after creation. The test is as follows. The default value is false.

The following is a reference clip:

SQL> show parameter global_names name type value -------------------------------------- global_names Boolean true SQL> select count (*) from t_emp @ DBL; select count (*) from t_emp @ DBL *

Row 3 has an error:

ORA-02085: database connection DBL to orclsql> Col db_link format A10 SQL> Col host format A10 SQL> Col owner format A10 SQL> Col username format A10 SQL> select * From dba_db_links; owner db_link username host created ---------- ------------------ sys DBL test orcl 01-12-07 sys orcl test orcl 01-12-07 SQL> select count (*) from t_emp @ orcl; count (*) ---------- 4 SQL>

8. What should I enter for the global database name (global_dbname) When configuring static listening registration through netmanager? What is the service name (SERVICE_NAME) required to configure the network service name (TNS) through netmanager?

The content of listener. ora is as follows:

Sid_list_listener = (sid_list = (sid_desc = (global_dbname = test) (ORACLE_HOME = E:/Oracle/product/10.2.0/db_1) (sid_name = tsid ))) listener = (description = (address = (Protocol = TCP) (host = xys) (Port = 1521) tnsnames. ora contains the following content: test = (description = (address_list = (address = (Protocol = TCP) (host = xys) (Port = 1521 ))) (CONNECT_DATA = (SERVICE_NAME = test )))

　　This clearly tells you that When configuring static listening registration, you need to enter the global database name (global_dbname) to input everything, as long as the listerner is guaranteed. global_dbname and tnsnames in ora. the SERVICE_NAME in ora must be consistent,Let's test the effect:

It is worth noting that global_dbname = test, and the db_name, global_name, and service_names of the database are shown as follows:

SQL> show parameter db_namename type value ------------------------------------ --------- db_name string test1sql> select * From global_name; global_name revoke test1sql> alter database rename global_name to ABCD. Yu;

The database has been changed.

) SQL> select * From global_name; GLOBAL_NAME----------------------------------ABCD.YUSQL> SQL> show parameter service_namesname type value ----------------------------------- service_names string Test2, test3sql>

However, the test, db_name, globl_name, and SERVICE_NAME provided when I configure listener and tnsnames do not have any relationship. Then, let's look at the effect of tnsping:

C:/> tnsping Test

Alias resolved using tnsnames Adapter

Attempting to contact (description = (address_list = (address = (Protocol = TCP) (host = xys) (Port = 1521) (CONNECT_DATA = (SERVICE_NAME = test ))) OK (30 ms) C:/>

　　9. db_unique_name

Db_unique_name is a 10G parameter. When configuring the dataguard environment, you must set a unique value for each database in the DG Environment. Before the db_unique_name parameter is absent, when building DG on the same machine, the lock_name_space parameter must be used. In the standby parameter file, 10 GB

Db_unique_name and lock_name_space have been deprecated!