【DBLINK】“ORA-02085: database link %s connects to %s”故障排查及处理策略两则

  今天在使用连接远程数据库实例的database link时遭遇“ORA-02085: database link DBLINK_TO_SECGC connects to SECGC”报错，导致远程数据无法获取。本文给出ORA-02085报错过程、以及两种处理方法。该问题处理过程具有普遍参考意义。

  约定：虽然这里secdb实例和secgc实例都在一套物理服务器上。我们在此假设secdb作为本地数据库，secgc作为远程数据库

1.在远程secgc数据库实例user_secgc用户中创建基表T并初始化数据
secdb@secdb1 /home/oracle$ export ORACLE_SID=secgc
secgc@secdb1 /home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 19 20:24:43 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@secgc> create user user_secgc identified by user_secgc;

User created.

sys@secgc> grant connect,resource to user_secgc;

Grant succeeded.

sys@secgc> conn user_secgc/user_secgc
Connected.
user_secgc@secgc> create table t (x varchar2(10));

Table created.

user_secgc@secgc> insert into t values ('secooler');

1 row created.

user_secgc@secgc> commit;

Commit complete.

user_secgc@secgc> select * from t;

X
----------
secooler

2.在本地secdb实例上创建database link连接到secgc实例的user_secgc用户
1）在本地secdb实例上创建到secgc实例的连接串
secdb@secdb1 /home/oracle$ vi $ORACLE_HOME/network/admin/tnsnames.ora
SECGC =
  (DESCRIPTION =
    (address = (protocol = tcp)(host = secdb1)(port = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = secgc)
    )
  )

2）在本地secdb数据库实例上创建database link
secdb@secdb1 /home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 19 20:31:06 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@secdb> create public database link dblink_to_secgc connect to user_secgc identified by user_secgc using 'SECGC';

Database link created.

3.测试database link是否可用，出现ORA-02085错误
sys@secdb> select * from t@dblink_to_secgc;
select * from t@dblink_to_secgc
                *
ERROR at line 1:
ORA-02085: database link DBLINK_TO_SECGC connects to SECGC

4.报错原因
1）获取ORA-02085错误的参考信息
secdb@secdb1 /home/oracle$ oerr ora 2085
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）从报错信息的描述上已经可以得到问题的原因和处理方法
错误原因：使用的dblink名字与要连接的数据库名字不相同
处理方法：有两种处理方法
第一种处理方法：创建与对方数据库名字相同的dblink
第二种处理方法：调整数据库参数global_names值为false，取消这种限制

5.故障处理方法两则
按照上面给出的两种处理方法进行验证。
1）第一种处理方法：使用与远程数据库名字相同的dblink
（1）获取远程数据库名字
sys@secgc> select * from global_name;

GLOBAL_NAME
----------------------
SECGC

（2）创建与远程数据库名字相同的dblink
sys@secdb> create public database link secgc connect to user_secgc identified by user_secgc using 'SECGC';

Database link created.

（3）在global_names参数为TRUE的时候测试dblink可用性
注意：仅需调整使用dblink端的数据库参数即可。
sys@secdb> show parameter global_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
global_names                         boolean     TRUE

sys@secdb> select * from t@secgc;

X
----------
secooler

成功。

2）第二种处理方法：调整数据库参数global_names值为false
sys@secdb> show parameter global_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
global_names                         boolean     FALSE

sys@secdb> alter system set global_names=FALSE;

System altered.

sys@secdb> show parameter global_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
global_names                         boolean     FALSE

sys@secdb> select * from t@dblink_to_secgc;

X
----------
secooler

可见，此时dblink连接远程数据库实例获取数据成功。

6.10g官方文档中关于global_names参数的描述
官方文档参考链接：http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams077.htm#REFRN10065

GLOBAL_NAMES

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

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

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

7.小结
  本文围绕global_names参数对dblink使用过程中出现的ORA-02085错误展开的分析和验证。建议对数据库常用参数的功能和用法谙熟于心。

Good luck.

secooler
11.05.19

-- The End --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-695851/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-695851/