实战：解决一次离奇的Oracle DBLINK不通问题

1、报错现象

客户方的大哥找到我说，他们这有个A数据库上有个到B数据库的DBLINK过完年回来突然不通了，他们也测试了重建还是不好使，让我帮看看。

于是我也测试了一下，真的是不通了：

SQL> select sysdate from dual@jlsi_sspt;

select sysdate from dual@jlsi_sspt
*
ERROR at line 1:
ORA-02085: database link JLSI_SSPT connects to ORCL

我又去SQLDEVELOPER 工具上测试了一下，多了一点报错信息提示。

具体内容

ORA-02085: 数据库链接 JLSI_SSPT 连接到 ORCL
02085. 00000 - "database link %s connects to %s"
*Cause: a database link connected to a database with a different name.
The connection is rejected.
*Action: create a database link with the same name as the database it
connects to, or set global_names=false.

2、开始排查

为了排除网络上的问题，我在A服务器上创建1个服务名叫DB122到b数据库。

执行一下tnsping db122返回结果如下

oracle@XDB1:~$ tnsping DB122

TNS Ping Utility for Solaris: Version 12.2.0.1.0 - Production on 21-FEB-2024 15:12:48
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = XXXXXX)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

看起来应该是没有毛病，于是尝试用对应的用户名密码登录下B数据库。

oracle@XDB1:~$ sqlplus 用户名/密码@DB122

SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 21 15:12:57 2024
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Wed Feb 21 2024 15:27:48 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> exit

登录也能成功，这就离奇了，说明网络层面没有毛病。
于是我尝试自己创建个DBLINK试试。

CREATE DATABASE LINK "JLSI_SSPT_TEST2024"
CONNECT TO "用户名" IDENTIFIED BY '密码'
USING 'DB122';

在SQLDEVELOPER上测试连接还是一样的报错。

ORA-02085: 数据库链接 JLSI_SSPT_TEST2024 连接到 ORCL
02085. 00000 - "database link %s connects to %s"
*Cause: a database link connected to a database with a different name.
The connection is rejected.
*Action: create a database link with the same name as the database it
connects to, or set global_names=false.

命令行执行也是如此！！！

3、解决问题办法

这就奇怪了，顺着报错ORA-02085去查万能的MOS和国外大佬的BLOG吧，发现了两篇大佬写的BLOG见参考章节，确定了解决办法，就是要把global_names=false修改一下就好了。

alter system set global_names=TRUE scope=both;

以下是官方文档的TROUBLESHOOTING STEPS：

Database Links: Troubleshooting the ORA 2085 "database link %s connects to %s"
In the following explanation the SOURCE database houses the DBLINK.
The TARGET database is the destination database the DBLINK points to.

When the source database initialization parameter GLOBAL_NAMES is set to true, the

database link name must match the target database global name as it exists in the GLOBAL_NAME

view in the data dictionary.

The GLOBAL_NAME can be determined by logging in to the database with system privileges and issuing the following command:

SQL>Select * from global_name;

Additionally, if you do not specify the domain portion of the dblink name in the create statement, Oracle automatically qualifies the link name with the domain of the SOURCE database global name view.

Check the contents of ALL_DB_LINKS for the fully qualified link name.

For example, if you defined a database link in PROD.ORACLE.COM to connect to target instance TEST.WORLD.COM in the following manner:

SQL>Create public database link TEST connect to userid identified by password using ‘test’;

SQL>select * from tablename@TEST;

This select would yield the following error:

ORA-2085 "database link TEST.ORACLE.COM connects to TEST.WORLD.COM"

The correct syntax for defining the link would be:

SQL>Create public database link TEST.WORLD.COM connect to userid identified by password using ‘test’;

SQL>select * from tablename@TEST.WORLD.COM;

Would yield the desired result.

It is possible to alter the GLOBAL_NAME table so that the domain portion of both SOURCE and TARGET global names are identical. This would eliminate the need to include the domain in the create database link statement.

In the above example, we could alter the GLOBAL_NAME of TEST.WORLD.COM in the following manner:

Login to TEST with system privileges and issue:

SQL>alter database rename global_name to TEST.ORACLE.COM;

Now, the create database link statement could also be changed.

Login to PROD.

SQL>create public database link TEST connect to userid identified by password using ‘test’;

A database link would be defined in ALL_DB_LINKS as TEST.ORACLE.COM.

SQL>select * from tablename@TEST;

This would yield the desired result.

 

The domain portion of the GLOBAL_NAME setting is usually set at db creation time and is derived from the value DB_DOMAIN.  So the GLOBAL_NAME setting would be DB_NAME.DB_DOMAIN unless changed after the database creation time.
 

这里我就大意了，SQLDEVELOPER工具弹出提示时，己经写了action（工具比较强大！）。

*Action: create a database link with the same name as the database it connects to, or set global_names=false.

这个参数默认值就是false，这个库肯定是节后被人手动修改了，这里由于是A库有别的应用厂商在使用，我就不随意修改库的参数，告诉客户让他提交给应用方，让他们评估修改。

出于对global_names这个参数的不解，后面我又查了oracle的官方文档和大佬的BLOG。

4、关于参数GLOBAL_NAME=FALSE

先看看官网的说明

GLOBAL_NAMES specifies whether a database link is required to have the same name as the database to which it connects.

Property	Description
Parameter type	Boolean
Default value	false
Modifiable	ALTER SESSION, ALTER SYSTEM
Modifiable in a PDB	Yes
Range of values	true | false
Basic	No

If the value of is , then no check is performed. If you use or plan to use distributed processing, then Oracle recommends that you set this parameter to to ensure the use of consistent naming conventions for databases and links in a networked environment. GLOBAL_NAMESfalsetrue

他这个官方就这么写的，最后的解释貌似是官方文档格式有问题没有显示对

再看大佬的说明：

Normally, we turned off GLOBAL_NAMES to prevent additional check on remote database before connecting to it. This is because we know where we are going to very clearly.

But what if we are in a complicated distributed environment, how to prevent connecting to the wrong destination? In such situation, Oracle recommends to turn on GLOBAL_NAMES to prevent us from connecting to wrong databases.

结合起来说就是两个值：

true要求DBLINK名要与对端库的global_names一样，否则不好使（为了防止连到错误的库）；

False不做上面的这个强制检查；

在我这个环境设置为true的情况下，先去对端数据库B上查询global_name

SQL> select * from global_name;
GLOBAL_NAME
---------------------------------------------------------------
ORCL

我做了下测试，如果重新创建DBLINK名为ORCL，那么测试就OK没毛病。

参考

Database Links: Troubleshooting ORA-2085: database link %s connects to %s (Doc ID 210630.1)

How to Resolve ORA-02085: database link SOURCE_LINK connects to SOURCE_DATABASE - Ed Chen Logic

What GLOBAL_NAMES do to DB Links - Ed Chen Logic

也欢迎关注我的公众号【徐sir的IT之路】，一起学习！

————————————————————————————
公众号：徐sir的IT之路
CSDN ：徐sir（徐慧阳）-CSDN博客
墨天轮：徐sir的个人主页 - 墨天轮
PGFANS：PGFans问答社区:全球唯一的PostgreSQL中文技术交流社区

————————————————————————————