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;
