oracle dblink问题

最新推荐文章于 2024-12-07 20:03:54 发布

ckth47350

最新推荐文章于 2024-12-07 20:03:54 发布

阅读量153

点赞数

文章标签：开发工具网络数据库

本文详细介绍了在Oracle环境下配置tnsnames.ora文件以实现远程数据库连接，并通过示例解决tnsping失败的TNS-12532错误。进一步探讨了在设置GLOBAL_NAMES参数为TRUE时，使用DBLink连接远程数据库时需确保DBLink名称与目标库的GLOBAL_NAME一致，以避免出现ORA-02085错误。提供了具体的解决步骤与配置修改建议。

oracle dblink常见问题及解决方案

192.168.1.10连接192.168.1.20数据库

1

[oracle@card admin]$ vim tnsnames.ora
20 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.20)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

[oracle@card admin]$ tnsping 20

[oracle@card admin]$ tnsping 20
TNS Ping Utility for Linux: Version 9.0.1.0.0 - Production on 29-APR-2015 09:49:52

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:
/home/oracle/OraHome1/network/admin/sqlnet.ora

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SID=*)(SERVICE_NAME=20.))(ADDRESS=(PROTOCOL=TCP)(HOST=20)(PORT=1521)))
TNS-12532: TNS:invalid argument

出现TNS-12532
解决办法：
注释掉默认域名
[oracle@card admin]$ vim sqlnet.ora
#NAMES.DEFAULT_DOMAIN = DEV.CN.TLAN
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

[oracle@card admin]$ tnsping 20

TNS Ping Utility for Linux: Version 9.0.1.0.0 - Production on 29-APR-2015 09:51:50
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
/home/oracle/OraHome1/network/admin/sqlnet.ora
/home/oracle/OraHome1/network/admin/tnsnames.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.20)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (0 msec)

2 创建dblink

create database link orcl connect to scott identified by tiger using '20';

select * from test@orcl;

ERROR at line 1:
ORA-02085: database link FGOS.REGRESS.RDBMS.DEV.US.ORACLE.COM connects to ORCL

出现错误ORA-02085
解决办法：
当GLOBAL_NAMES参数设置为TRUE时，使用DATABASE LINK时，DATABASE LINK的名称必须与被连接库的GLOBAL_NAME一致。

SQL> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE

SQL> alter system set global_names=false scope=both;

SQL> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE

select * from test@orcl;

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

转载于:http://blog.itpub.net/29785807/viewspace-1609985/