oracle dblink問題

chenoracle發表於2015-04-29
Oracle
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;

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！

oracle dblink問題

oracle dblink問題








來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-1609985/，如需轉載，請註明出處，否則將追究法律責任。

相關文章