ORACLE數據庫中Global Database Name與DB LINKS的關系還真是有點糾纏不清,在說清楚這個關系前,我們先來了解一下Global Database Name的概念
Global DataBase Name 概念
1. What is a global database name?
-------------------------------------------------------------------------------
The global database name is the unique name of the database. In a distributed
database system (a set of databases stored on multiple computers that typically
appears to applications as a single database) the global database name ensures
that each database is distinct from all other databases in the system. Oracle
forms a database's global database name by prefixing the database's network
domain with the individual database's name. For example: sales.us.oracle.com
and sales.uk.oracle.com.
The global database name defaults to DB_NAME.DB_DOMAIN and this value is marked
at database creation time. If you change the DB_NAME or DB_DOMAIN after the
database has been created, the value for the global database name (GLOBAL_NAME)
will not change.
說簡單一點,global database name就是用來唯一標識數據庫的東東。global database name由兩部分組成,DB_NAME和DB_DOMAIN。在創建db link的時候,Oracle會自動將db_domain作為後綴添加上去。而且一旦加入就很難變更。所以在進行高級復制、Streams復制等配置時,最好首先將多個節點的global_name規劃好。
如何查看Global Database Name
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM
SQL>
如下所示,可以知道global_name為一個視圖。
SQL> select object_name,object_type from dba_objects where object_name=upper('global_name');
OBJECT_NAME OBJECT_TYPE
---------------------------------------- -------------------------------
GLOBAL_NAME VIEW
GLOBAL_NAME SYNONYM
視圖global_name的定義可以從DBA_VIEWS裡面查看,如下所示,它來源於sys.props$內部表
select value$ from sys.props$ where name = 'GLOBAL_DB_NAME'
那麼參數global_name與Global Database Name又沒有區別呢? 其實參數global_name僅僅表示指定數據庫的鏈接是否需要和它所連接的數據庫相同的名稱。
GLOBAL_NAMES specifies whether a database link is required to have the same name as the database to which it connects.
SQL> show parameter global_name
NAME TYPE VALUE
---------------------------------- ----------- -----------------
global_names boolean FALSE
SQL>
所以兩者完全是兩個不同的概念,但是global_name也很重要,因為它的值會影響DB LINK.接下來,我們來創建一個DB Link,如下所示
SQL> select * from global_name;
GLOBAL_NAME
-------------------------------------
KKK.REGRESS.RDBMS.DEV.US.ORACLE.COM
SQL> CREATE PUBLIC DATABASE LINK "TEST"
CONNECT TO "test" IDENTIFIED BY test1111
USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =
(PROTOCOL = TCP)(HOST = 192.168.27.109)(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = QWR)))';
Database link created.
SQL>
如上所示,我創建的DB Link名字為TEST,但是你查詢DBA_DB_LINKS時,你會發現鏈接服務器自動加上了域名,變成了TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM, 當你查詢時,使用TEST或TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM都OK。
SQL> SELECT * FROM DUAL@TEST;
D
-
X
SQL> SELECT * FROM [email protected];
D
-
X
SQL>
當Global Database Name只有DB_NAME,但是沒有DB_DOMAIN時,此時,如果創建的DB Link是沒有db_domain作為後綴添加上去的,你查詢時,就必須使用全名TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM,不能像上面那樣使用TEST,否則會報ORA-02019: connection description for remote database not found 錯誤。
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
EPPS
SQL> CREATE PUBLIC DATABASE LINK "TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM"
2 CONNECT TO "test" IDENTIFIED BY test1111
3 USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =
(PROTOCOL = TCP)(HOST = 192.168.27.109)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = QWR)))';
Database link created.
SQL> SELECT * FROM DUAL@TEST;
SELECT * FROM DUAL@TEST
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
SQL> SELECT * FROM [email protected];
D
-
X
SQL>
DB_LINK與GLOBAL_NAMES參數的關系
當GLOBAL_NAMES為TURE時,影響的是創建數據庫鏈接的那個庫對數據庫鏈接的使用。而不是鏈接到該數據庫的鏈接服務器的使用。也就是說,如果一個庫(實例)的global_names參數設值為TRUE,則該庫連接其他庫的數據庫鏈接,其名稱必須要與被連接的庫的global_name相同,是否有點繞口,那麼從下面實驗看看。
服務器B
SQL> show parameter global_names;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
KKK.REGRESS.RDBMS.DEV.US.ORACLE.COM
服務器A
SQL> show parameter global_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
SCM2.REGRESS.RDBMS.DEV.US.ORACLE.COM
在服務器B上創建鏈接服務器,鏈接到服務器A
SQL> CREATE PUBLIC DATABASE LINK "LINK1"
2 CONNECT TO "test" IDENTIFIED BY test1111
3 USING 'SERVER_A';
Database link created.
SQL> SELECT * FROM DUAL@LINK1;
D
-
X
在服務器A上啟用參數global_names,設置為TRUE。
SQL> show parameter global_names;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE
SQL> alter system set global_names=true;
System altered.
SQL>
然後在服務器B上,再次創建鏈接服務器LINK2,如下所示
SQL> CREATE PUBLIC DATABASE LINK "LINK2"
2 CONNECT TO "test" IDENTIFIED BY test1111
3 USING 'SERVER_A';
Database link created.
SQL> SELECT * FROM DUAL@LINK2;
D
-
X
從上可以看出,服務器B上的global_names設置為TRUE是不影響服務器A上的DB LINK的,那麼在服務器A上,將參數global_names設置為TRUE呢?
SQL> alter system set global_names=true;
System altered.
SQL> SELECT * FROM DUAL@LINK1;
SELECT * FROM DUAL@LINK1
*
ERROR at line 1:
ORA-02085: database link LINK1.REGRESS.RDBMS.DEV.US.ORACLE.COM connects to
SCM2.REGRESS.RDBMS.DEV.US.ORACLE.COM
SQL> SELECT * FROM DUAL@LINK2;
SELECT * FROM DUAL@LINK2
*
ERROR at line 1:
ORA-02085: database link LINK2.REGRESS.RDBMS.DEV.US.ORACLE.COM connects to
SCM2.REGRESS.RDBMS.DEV.US.ORACLE.COM
此時我們來創建一個與被鏈接數據庫global_name一致的DB Link,如下所示
SQL> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
KKK.REGRESS.RDBMS.DEV.US.ORACLE.COM
SQL>
SQL>
SQL> CREATE PUBLIC DATABASE LINK SCM2
2 CONNECT TO "test" IDENTIFIED BY test1111
3 USING 'SERVER_A';
Database link created.
SQL> SELECT * FROM DUAL@SCM2;
D
-
X
另外,我們來看看如何修改Global Database Name,如下所示
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
KKK.REGRESS.RDBMS.DEV.US.ORACLE.COM
SQL> alter database rename global_name to TEST;
Database altered.
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM
SQL>
如上所示,修改rename global_name只能修改DB_NAME部分,不能修改DB_DOMAIN,沒有常規的辦法去掉"."分隔符後面部分,要去掉只能用update語句
update global_name set global_name='xxxx';
但是網上很多大神的建議都是不要更新global_name. 例如AskTom裡面How to change the value in GLOBAL_NAME table就提到
you don't UPDATE a global_name like that. You NEVER update any data dictionary table -- never.
另外在老熊的博客Oracle數據庫的global_name裡面,提到不要直接用update global_name set global_name=''將global_name設置為空,否則數據庫不能啟動,會報ORA-00600[18061] 或 ORA-00600[18062]這樣的錯誤。除非萬不得已,不要去更新global_name視圖,即使更新,也不要去更新global_name的基表props$,更不要將global_name更新為空。
參考資料:
http://www.laoxiong.net/oracle_global_name.html
http://www.laoxiong.net/database_link_global_names.html
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1392538395678
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=265322928260367&parent=DOCUMENT&sourceId=742140.1&id=115499.1&_afrWindowMode=0&_adf.ctrl-state=1hwssfmxb_203