I created a link from 817 to 805. The remote global_name has db_domain as WORLD;however, the local one(v. 817) db_domain is null. tnsping works fine but the link failed to do a simple desc on a table. Global_name is set to TRUE on both databases. The remote tnsnames entry is
(ADDRESS = (PROTOCOL= TCP)(Host= estwhp02)(Port= 1521))
(CONNECT_DATA = (SID = PSTEST))
On the local, run the followings.
1 create database link PSTEST
2 CONNECT TO SYSADM IDENTIFIED BY PEANUT
3* USING 'PSTEST'
Database link created.
SQL_PSDEV8>select * from global_name@PSTEST;
select * from global_name@PSTEST
ERROR at line 1:
ORA-02085: database link PSTEST connects to PSTEST.WORLD
Any good ideas.
You need to set up tnsnames.ora to include domain name.
Please try some thing like:
(CONNECT_DATA = (SID = PSTEST)
(SERVICE_NAME = PSTEST.WORLD) )
Quick way to fix this problem is set GLOBAL NAMES = FALSE in init.ora
Mike73 has the point. But remember changing would require a bounce on the instance.
Before making new changes, I did some connection testing to the remote database such as sqlplus connection and it seemed to work fine. I don't think we need to change anything in tnsnames.ora on the remote database. Are there any other verifications we can do to clarify the problem? It seems like Oracle database link process got confused.
On local host, PSDEV8:
$ sqlplus sysadm/peanut@pstest
SQL*Plus: Release 184.108.40.206.0 - Production on Tue Aug 28 15:14:40 2001
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Oracle8 Enterprise Edition Release 220.127.116.11.2 - Production
With the Partitioning and Objects options
PL/SQL Release 18.104.22.168.1 - Production
You can do a tnsping to check that you are able to reach the remote database. But what you have confirmed is good enough to go.
I aleady did tnsping and it worked fine.
I also named my link the same as GLOBAL_NAMES but it still didn't work. I am not sure changing GLOBAL NAME is false will help. But I'll play with it.
You would want to set the GLOBAL_NAMES=False, if you are giving your link a name. On the other hand GLOBAL_NAMES=TRUE would require the link name to be the same as the DB name for which the link was created.
When you say remote tnsnames.ora do you mean the database you are connecting to with the DB link or the database you are connecting from
If it is the database you are trying to connect to having the tnsnames entry won't help, you need it on the server you are connecting from.
DB1 on server1 has a db link that connects to DB2 on server2
The tnsnames entry for DB2 has to be in server1's tnsnames.ora file
A good test is to add the entry and log onto the server. Once on the server try to connect using SQL plus to the database you want the db link to connect to.
Hope this helps
I did created a entry for the remote db, PSTEST, on my local machine. Like I said earlier, I did sqlplus sysadm/sysadm@pstest and it worked fine. tnsping pstest worked. I tried naming my link as both PSTEST and PSTEST.WORLD on different time but it still failed. This shouldn't be that difficult to do but maybe I missed something trivial. I have created lots of links in the past but haven't come across this one.