Oracle global database name与db link的纠缠关系

  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 [e-mail];

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 [e-mail];

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更新为空。