Global database name oracle что это
Может кто-нибудь объяснить мне, в чем разница SID, имени БД, домена БД, глобального имени базы данных, имени службы, псевдонима службы и имени экземпляра в Oracle?
SID = идентифицирует экземпляр базы данных (имя базы данных + номер экземпляра). Поэтому, если имя вашей базы данных - somedb, а номер вашего экземпляра - 3, тогда ваш SID - somedb3.
Имя БД = Имя базы данных (база данных может совместно использоваться несколькими экземплярами)
Имя службы = «соединитель» для одного или нескольких экземпляров. Часто полезно создавать дополнительные имена служб в среде RAC, поскольку служба может быть модифицирована для использования определенных SID в качестве первичных или вторичных соединений или для того, чтобы вообще не использовать определенные SID.
Псевдоним службы = псевдоним имени службы (как CNAME и т. Д.). Скажем, вы делаете имя своего сервиса чем-то значимым для dba, но, возможно, это немного эзотерично Создайте псевдоним службы и назовите его так, чтобы он имел смысл для пользователя.
Имя экземпляра = такое же как SID
То, как вы описываете SID, - это только поведение DEFAULT в конфигурации RAC. SID (== имя_экземпляра) - это просто имя вашего экземпляра.
Я всегда смотрю на это так: Экземпляр, это экземпляр программного обеспечения СУБД. Экземпляр монтирует контрольный файл (изменяет монтирование базы данных). В этом контрольном файле записывается расположение файлов данных. коллекция файлов данных (хорошо, и контрольный файл (ы)) == база данных.
База данных имеет имя, db_name и (необязательно) домен (db_domain) -> вместе global_db_name. Теперь представьте, что вы реплицируете (DataGuard) свою базу данных. Вы хотели бы оставить имя DB_name таким же, верно? (Я имею в виду: с точки зрения данных, это ЖЕ база данных) Но тогда как определить две «версии» вашей базы данных? Введите 'DB_UNIQUE_NAME' . Да, это сбивает с толку .
Моя личная практика - называть INSTANCE именем db_unique_name в настройке DataGuard и придерживаться RAC-имен (db_name + Instance_Number) в настройке RAC. Затем, db_unique_names, которые я составляю, обычно похожи на db_name + 1-буквенный суффикс (MYDBa MYDBb и т. Д.)
SID является экземпляром. Лучше избегать использования термина «экземпляр базы данных», просто «экземпляр».
«SID = идентифицирует экземпляр базы данных (имя базы данных + номер экземпляра)» неверно. «Экземпляр, это экземпляр программного обеспечения СУБД» неправильно. Удаленная или установленная СУБД - это просто СУБД.
«Домен БД = обычно такой же, как домен вашей компании» следует избегать. У меня возникла проблема с использованием домена, и проблемы исчезли, если не использовать домен.
«Глобальное имя базы данных = имя базы данных + домен базы данных» также неверно. Глобальное имя базы данных - это имя службы. Это так просто.
«SID = идентифицирует экземпляр базы данных (имя базы данных + номер экземпляра). Поэтому, если имя вашей базы данных - somedb, а номер вашего экземпляра - 3, то ваш SID - somedb3». неправильно. Нет такой привязки личности или имени.
In a distributed database system, each database should have a unique global database name . Global database names uniquely identify a database in the system. A primary administration task in a distributed system is managing the creation and alteration of global database names.
This section contains the following topics:
Changing a Global Database Name: Scenario
In this scenario, you change the domain part of the global database name of the local database. You also create database links using partially specified global names to test how Oracle Database resolves the names. You discover that the database resolves the partial names using the domain part of the current global database name of the local database, not the value for the initialization parameter DB_DOMAIN .
You query the V$PARAMETER view to determine the current setting for the DB_DOMAIN initialization parameter:
You then create a database link to a database called hq , using only a partially-specified global name:
The database expands the global database name for this link by appending the domain part of the global database name of the local database to the name of the database specified in the link.
You query USER_DB_LINKS to determine which domain name the database uses to resolve the partially specified global database name:
You query V$PARAMETER again and discover that the value of DB_DOMAIN is not changed, although you renamed the domain part of the global database name:
This result indicates that the value of the DB_DOMAIN initialization parameter is independent of the ALTER DATABASE RENAME GLOBAL_NAME statement. The ALTER DATABASE statement determines the domain of the global database name, not the DB_DOMAIN initialization parameter (although it is good practice to alter DB_DOMAIN to reflect the new domain name).
You create another database link to database supply , and then query USER_DB_LINKS to see how the database resolves the domain part of the global database name of supply :
You query V$PARAMETER to again check the setting for the parameter DB_DOMAIN :
The result indicates that the domain setting in the parameter file is exactly the same as it was before you issued either of the ALTER DATABASE RENAME statements.
Finally, you create a link to the warehouse database and again query USER_DB_LINKS to determine how the database resolves the partially-specified global name:
Again, you see that the database uses the domain part of the global database name of the local database to expand the partial link name during link creation.
In order to correct the supply database link, it must be dropped and re-created.
Oracle Database Reference for more information about specifying the DB_NAME and DB_DOMAIN initialization parameters
Managing a distributed database includes tasks such as managing global names, managing database links, and creating location and statement transparency.
In a distributed database system, each database should have a unique global database name . Global database names uniquely identify a database in the system. A primary administration task in a distributed system is managing the creation and alteration of global database names.
To support application access to the data and schema objects throughout a distributed database system, you must create all necessary database links.
Every application that references a remote server using a standard database link establishes a connection between the local database and the remote database. Many users running applications simultaneously can cause a high number of connections between the local and remote databases. Shared database links enable you to limit the number of network connections required between the local server and the remote server.
Managing database links includes tasks such as closing them, dropping them, and limiting the number of active connections to them.
The data dictionary of each database stores the definitions of all the database links in the database. You can use data dictionary tables and views to gain information about the links.
After you have configured the necessary database links, you can use various tools to hide the distributed nature of the database system from users. In other words, users can access remote objects as if they were local objects.
In a distributed database, some SQL statements can reference remote tables.
Examples illustrate managing database links.
Db_name, service_names, instance_name, oracle_sid, global_dbname в ORACLE
В Oracle есть много параметров, связанных с именами, которые иногда могут сбивать с толку.Какова функция этого параметра и в чем разница между ними?
Давайте сначала посмотрим, какие параметры связаны с именем
В файле параметров есть db_name, instance_name, service_names, db_unique_name
В переменной окружения есть oracle_sid
В listener.ora есть sid_name, global_dbname (в случае статической регистрации)
В tnsnames.ora есть service_name, sid
Каковы их значения, давайте познакомимся с ними по порядку:
db_name:
Уникальный идентификатор базы данных (база данных Oracle). Этого представления достаточно для одной базы данных, но для распространения
DB_UNIQUE_NAME:
instance_name:
Перед изменением параметра экземпляра, как показано на рисунке ниже
После изменения параметра instance_name:
Сравнение двух изображений показывает, что instance_name играет роль указания имени при регистрации монитора.
Кроме того, некоторые динамические представления производительности, содержащие столбец instance_name или inst_name, выводятся из переменной среды ORACLE_SID (а не этого параметра).
service_names:
Перед изменением service_names, service_names согласуется с отслеживаемой службой:
После изменения service_names в мониторинг добавляется заданное имя службы:
Вы также можете подключиться к базе данных, используя только что установленное имя службы:
oracle_sid:
Идентификатор экземпляра существует в форме переменной среды и используется для соответствия сегменту разделяемой памяти. Экземпляр Oracle состоит из SGA и группы фоновых процессов. Для создания и запуска экземпляра требуется файл параметров, а имя файла параметров определяется ORACLE_SID. оф. Для файла инициализации имя файла по умолчанию - init .ora, для файла spfile имя файла по умолчанию - spfile .ora. Устанавливая разные значения ORACLE_SID, вы можете запускать разные экземпляры базы данных с разными файлами параметров по умолчанию.
Иными словами, ORACLE_SID определяет имя экземпляра локально запущенного экземпляра базы данных. Пока существует соответствующий init .ora или spfile .ora, его можно запустить, или он может быть запущен, указав pfile при запуске, если параметр Другие параметры, установленные в файле, могут запускать базу данных.
В нормальных условиях ORACLE_SID = test, запустите базу данных, как показано на рисунке ниже, и сгенерированный процесс идентифицируется тестом.
Закройте базу данных, измените ORACLE_SID = mydb, вы также можете запустить базу данных, но вам нужно вручную указать файл параметров, сгенерированный процесс идентифицируется mydb.
Даже если процессы связаны с mydb, имя базы данных и конфигурация мониторинга все еще тестируются.
Также изменились instance_name и inst_name в соответствующем динамическом представлении производительности:
service_name и sid в tnsnames.ora:
32.2 Creating Database Links
To support application access to the data and schema objects throughout a distributed database system, you must create all necessary database links.
A database link is a pointer in the local database that lets you access objects on a remote database. To create a private database link, you must have been granted the proper privileges.
When you create a database link, you must decide who will have access to it.
A database link defines a communication path from one database to another. When an application uses a database link to access a remote database, Oracle Database establishes a database session in the remote database on behalf of the local application request. When you create a private or public database link, you can determine which schema on the remote database the link will establish connections to by creating fixed user, current user, and connected user database links.
In some situations, you may want to have several database links of the same type (for example, public) that point to the same remote database, yet establish connections to the remote database using different communication pathways.
32.2.1 Obtaining Privileges Necessary for Creating Database Links
A database link is a pointer in the local database that lets you access objects on a remote database. To create a private database link, you must have been granted the proper privileges.
The following table illustrates which privileges are required on which database for which type of link:
CREATE DATABASE LINK
Creation of a private database link.
CREATE PUBLIC DATABASE LINK
Creation of a public database link.
Creation of any type of database link.
To see which privileges you currently have available, query ROLE_SYS_PRIVS . For example, you could create and execute the following privs.sql script (sample output included):
32.2.2 Specifying Link Types
When you create a database link, you must decide who will have access to it.
Use the CREATE DATABASE LINK statement to create private database links.
Use the CREATE PUBLIC DATABASE LINK statement to create public database links.
You can use a directory server in which databases are identified by net service names. In this document, these are what are referred to as global database links.
32.2.2.1 Creating Private Database Links
Use the CREATE DATABASE LINK statement to create private database links.
To create a private database link, specify the following (where link_name is the global database name or an arbitrary link name):
Following are examples of private database links:
A private link using the global database name to the remote supply database.
The link uses the userid/password of the connected user. So if scott (identified by password ) uses the link in a query, the link establishes a connection to the remote database as scott/ password .
CREATE DATABASE LINK link_2 CONNECT TO jane IDENTIFIED BY password USING 'us_supply';
A private fixed user link called link_2 to the database with service name us_supply . The link connects to the remote database with the userid/password of jane/ password regardless of the connected user.
CREATE DATABASE LINK link_1 CONNECT TO CURRENT_USER USING 'us_supply';
A private link called link_1 to the database with service name us_supply . The link uses the userid/password of the current user to log onto the remote database.
Note: The current user may not be the same as the connected user, and must be a global user on both databases involved in the link (see "Users of Database Links" ). Current user links are part of the Oracle Advanced Security option.
32.2.2.2 Creating Public Database Links
Use the CREATE PUBLIC DATABASE LINK statement to create public database links.
To create a public database link, use the keyword PUBLIC (where link_name is the global database name or an arbitrary link name):
Following are examples of public database links:
A public link to the remote supply database. The link uses the userid/password of the connected user. So if scott (identified by password ) uses the link in a query, the link establishes a connection to the remote database as scott/ password .
CREATE PUBLIC DATABASE LINK pu_link CONNECT TO CURRENT_USER USING 'supply';
A public link called pu_link to the database with service name supply . The link uses the userid/password of the current user to log onto the remote database.
Note: The current user may not be the same as the connected user, and must be a global user on both databases involved in the link (see "Users of Database Links" ).
A public fixed user link to the remote sales database. The link connects to the remote database with the userid/password of jane/ password .
Oracle Database SQL Language Reference for CREATE PUBLIC DATABASE LINK syntax
32.2.2.3 Creating Global Database Links
You can use a directory server in which databases are identified by net service names. In this document, these are what are referred to as global database links.
See the Oracle Database Net Services Administrator's Guide to learn how to create directory entries that act as global database links.
32.2.3 Specifying Link Users
A database link defines a communication path from one database to another. When an application uses a database link to access a remote database, Oracle Database establishes a database session in the remote database on behalf of the local application request. When you create a private or public database link, you can determine which schema on the remote database the link will establish connections to by creating fixed user, current user, and connected user database links.
When an application uses a fixed user database link, the local server always establishes a connection to a fixed remote schema in the remote database. The local server also sends the fixed user's credentials across the network when an application uses the link to access the remote database.
Connected user and current user database links do not include credentials in the definition of the link. The credentials used to connect to the remote database can change depending on the user that references the database link and the operation performed by the application.
32.2.3.1 Creating Fixed User Database Links
When an application uses a fixed user database link, the local server always establishes a connection to a fixed remote schema in the remote database. The local server also sends the fixed user's credentials across the network when an application uses the link to access the remote database.
To create a fixed user database link , you embed the credentials (in this case, a username and password) required to access the remote database in the definition of the link:
Following are examples of fixed user database links:
A public link using the global database name to the remote supply database. The link connects to the remote database with the userid/password scott/ password .
CREATE DATABASE LINK foo CONNECT TO jane IDENTIFIED BY password USING 'finance';
A private fixed user link called foo to the database with service name finance . The link connects to the remote database with the userid/password jane/ password .
32.2.3.2 Creating Connected User and Current User Database Links
Connected user and current user database links do not include credentials in the definition of the link. The credentials used to connect to the remote database can change depending on the user that references the database link and the operation performed by the application.
For many distributed applications, you do not want a user to have privileges in a remote database. One simple way to achieve this result is to create a procedure that contains a fixed user or current user database link within it. In this way, the user accessing the procedure temporarily assumes someone else's privileges.
To create a connected user database link, omit the CONNECT TO clause in the CREATE DATABASE LINK statement.
To create a current user database link, use the CONNECT TO CURRENT_USER clause in the CREATE DATABASE LINK statement.
Related Topics
32.2.3.2.1 Creating a Connected User Database Link
To create a connected user database link, omit the CONNECT TO clause in the CREATE DATABASE LINK statement.
The following syntax creates a connected user database link, where dblink is the name of the link and net_service_name is an optional connect string:
For example, to create a connected user database link, use the following syntax:
32.2.3.2.2 Creating a Current User Database Link
To create a current user database link, use the CONNECT TO CURRENT_USER clause in the CREATE DATABASE LINK statement.
Current user links are only available through the Oracle Advanced Security option.
The following syntax creates a current user database link, where dblink is the name of the link and net_service_name is an optional connect string:
For example, to create a connected user database link to the sales database, you might use the following syntax:
To use a current user database link, the current user must be a global user on both databases involved in the link.
Oracle Database SQL Language Reference for more syntax information about creating database links
32.2.4 Using Connection Qualifiers to Specify Service Names Within Link Names
In some situations, you may want to have several database links of the same type (for example, public) that point to the same remote database, yet establish connections to the remote database using different communication pathways.
A remote database is part of an Oracle Real Application Clusters configuration, so you define several public database links at your local node so that connections can be established to specific instances of the remote database.
Some clients connect to the Oracle Database server using TCP/IP while others use DECNET.
To facilitate such functionality, the database lets you create a database link with an optional service name in the database link name. When creating a database link, a service name is specified as the trailing portion of the database link name, separated by an @ sign, as in @sales . This string is called a connection qualifier .
Notice in the first two examples that a service name is simply a part of the database link name. The text of the service name does not necessarily indicate how a connection is to be established; this information is specified in the service name of the USING clause. Also notice that in the third example, a service name is not specified as part of the link name. In this case, just as when a service name is specified as part of the link name, the instance is determined by the USING string.
To use a service name to specify a particular instance, include the service name at the end of the global object name:
The DB_NAME database parameters determines the local name component of the database's name, while the DB_DOMAIN parameter indicates the domain (logical location) within a network structure. The combination of the settings for these two parameters must form a database name that is unique within a network.
Viewing a Global Database Name
Use the data dictionary view GLOBAL_NAME to view the database global name. For example, issue the following:
32.1 Managing Global Names in a Distributed System
In a distributed database system, each database should have a unique global database name . Global database names uniquely identify a database in the system. A primary administration task in a distributed system is managing the creation and alteration of global database names.
A global database name is formed from two components: a database name and a domain.
The name that you give to a link on the local database depends on whether the local database enforces global naming.
Use the data dictionary view GLOBAL_NAME to view the database global name.
Use the ALTER DATABASE statement to change the domain in a database global name.
A scenario illustrates changing a global database name.
32.1.1 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:
Must be 30 characters or less.
Domain containing the database
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.
These are examples of valid global database names:
The DB_DOMAIN initialization parameter is only important at database creation time when it is used, together with the DB_NAME parameter, to form the database global name. At this point, the database global name is stored in the data dictionary. You must change the global name using an ALTER DATABASE statement, not by altering the DB_DOMAIN parameter in the initialization parameter file. It is good practice, however, to change the DB_DOMAIN parameter to reflect the change in the domain name before the next database startup.
32.1.2 Determining Whether Global Naming Is Enforced
The name that you give to a link on the local database depends on whether the local database enforces global naming.
If the local database enforces global naming, then you must use the remote database global database name as the name of the link. For example, if you are connected to the local hq server and want to create a link to the remote mfg database, and the local database enforces global naming, then you must use the mfg global database name as the link name.
"Using Connection Qualifiers to Specify Service Names Within Link Names" for more information about using services names in link names
To determine whether global naming is enforced on a database, either examine the database initialization parameter file or query the V$PARAMETER view. For example, to see whether global naming is enforced on mfg , you could start a session on mfg and then create and execute the following globalnames.sql script (sample output included):
32.1.3 Viewing a Global Database Name
Use the data dictionary view GLOBAL_NAME to view the database global name.
For example, issue the following:
32.1.4 Changing the Domain in a Global Database Name
Use the ALTER DATABASE statement to change the domain in a database global name.
After the database is created, changing the initialization parameter DB_DOMAIN has no effect on the global database name or on the resolution of database link names.
The following example shows the syntax for the renaming statement, where database is a database name and domain is the network domain:
Use the following procedure to change the domain in a global database name:
- Determine the current global database name. For example, issue:
32.1.5 Changing a Global Database Name: Scenario
A scenario illustrates changing a global database name.
In this scenario, you change the domain part of the global database name of the local database. You also create database links using partially specified global names to test how Oracle Database resolves the names. You discover that the database resolves the partial names using the domain part of the current global database name of the local database, not the value for the initialization parameter DB_DOMAIN .
The database expands the global database name for this link by appending the domain part of the global database name of the local database to the name of the database specified in the link.
This result indicates that the value of the DB_DOMAIN initialization parameter is independent of the ALTER DATABASE RENAME GLOBAL_NAME statement. The ALTER DATABASE statement determines the domain of the global database name, not the DB_DOMAIN initialization parameter (although it is good practice to alter DB_DOMAIN to reflect the new domain name).
The result indicates that the domain setting in the parameter file is the same as it was before you issued either of the ALTER DATABASE RENAME statements.
Again, you see that the database uses the domain part of the global database name of the local database to expand the partial link name during link creation.
In order to correct the supply database link, it must be dropped and re-created.
Oracle Database Reference for more information about specifying the DB_NAME initialization parameter
Oracle Database Reference for more information about specifying the DB_DOMAIN initialization parameter
Articles Related
Recommended Pages
Oracle Database - Global Database Name (GLOBAL_NAME)
Oracle Database - Global Database Name (GLOBAL_NAME) About The Oracle Database (11g) software identifies a database by its global database name. A global database name consists of the database name ".
Oracle Database - DB_DOMAIN
Oracle Database - DB_DOMAIN About The DB_NAME initialisation parameters determines the local name component of the database's name, while the DB_DOMAIN parameter indicates the domain (logical locati ".
Oracle Database - Service name
Oracle Database - Service name About service name is an identifier of a database service. The service name is specified by the SERVICE_NAMES initialization parameter. sidglobal database name ".
Oracle Database - DB_UNIQUE_NAME parameter
DB_UNIQUE_NAME = DB_UNIQUE_NAME is a database parameter which specifies a globally unique name for the database. Databases with the same DB_NAME within the same DB_DOMAIN (for example, copies of a da ".
Oracle Database - SERVICE_NAMES parameter
The service name is specified by the SERVICE_NAMES initialization parameter. If you do not specify a service name for a database, the service name will default to the database's global name: a na ".
Changing the Domain in a Global Database Name
Use the ALTER DATABASE statement to change the domain in a database global name. Note that after the database is created, changing the initialization parameter DB_DOMAIN has no effect on the global database name or on the resolution of database link names.
The following example shows the syntax for the renaming statement, where database is a database name and domain is the network domain:
Use the following procedure to change the domain in a global database name:
Determine the current global database name. For example, issue:
Rename the global database name using an ALTER DATABASE statement. For example, enter:
Query the GLOBAL_NAME table to check the new name. For example, enter:
Determining Whether Global Naming Is Enforced
The name that you give to a link on the local database depends on whether the remote database that you want to access enforces global naming. If the remote database enforces global naming, then you must use the remote database global database name as the name of the link. For example, if you are connected to the local hq server and want to create a link to the remote mfg database, and mfg enforces global naming, then you must use the mfg global database name as the link name.
"Using Connection Qualifiers to Specify Service Names Within Link Names" for more information about using services names in link names
To determine whether global naming on a database is enforced on a database, either examine the database initialization parameter file or query the V$PARAMETER view. For example, to see whether global naming is enforced on mfg , you could start a session on mfg and then create and execute the following globalnames.sql script (sample output included):
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:
These are examples of valid global database names:
The DB_DOMAIN initialization parameter is only important at database creation time when it is used, together with the DB_NAME parameter, to form the database global name. At this point, the database global name is stored in the data dictionary. You must change the global name using an ALTER DATABASE statement, not by altering the DB_DOMAIN parameter in the initialization parameter file. It is good practice, however, to change the DB_DOMAIN parameter to reflect the change in the domain name before the next database startup.
Читайте также: