?
Solved

Problems with a Database link

Posted on 2006-05-11
12
Medium Priority
?
1,058 Views
Last Modified: 2008-03-03
I have a user who has been granted dba rights, has connect and resource roles, and has created a database link to another db. However, when trying to do a select as the same user this is the error that is generated.

                             
ERROR at line 1:

ORA-02019: connection description for remote database not found

They can ping the database, and connect with Toad. What could be doing this. I have some data from doing a trace too but am still stuck. Can anyone help, please.

0
Comment
Question by:bencouve2
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 7

Accepted Solution

by:
vishal68 earned 800 total points
ID: 16656761
Hi

Most likely you have created a dblink without the connect string or a wrong connect string. Run the following query

select * from all_db_links;

Check the entry in the Host column. If it is coming as blank, then you have created a dblink without a connect string. You need to drop and recreate the link with proper connect string. If there is an entry, check that the value in the Host column matches with the connect string name in the tnsnames.ora on the server.

HTH
Vishal
0
 

Author Comment

by:bencouve2
ID: 16657484
Hello vishal68,

A select has been done on this table and it does not exist for this user. Why would this happen if the user has dba rights??? Any suggestions?
0
 
LVL 7

Expert Comment

by:vishal68
ID: 16657505
Hi

Is it table does not exist or there are no rows in this table.

Vishal
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:bencouve2
ID: 16657598
Sorry I put all_dba_links instead of all_db_links. Will let you know
0
 

Author Comment

by:bencouve2
ID: 16657840
Hello vishal68,

these are the entries in that table

PUBLIC      SI2.US.ORACLE.COM                      other_user      SI2P.CSS.EU.INT      05/05/2006 17:14:11
MY_USER      BOB4SI.US.ORACLE.COM      DWHMAST_FSA      SI2P.CSS.EU.INT      05/10/2006 10:02:46
MY_USER      SI2BOB.US.ORACLE.COM      DWHMAST_FSA      SI2T.CSS.EU.INT      05/10/2006 18:26:15
0
 
LVL 19

Assisted Solution

by:actonwang
actonwang earned 600 total points
ID: 16658360
>>ORA-02019: connection description for remote database not found

the tns name which you used to create this db link is invalid. You need to verify the tns name for that db link is valid. HOST column gives you which tns name you have used.


For example, if you refer to dblink SI2, then try to verify if you have entry called "other_user" in your tnsnames.ora.

acton
0
 
LVL 4

Assisted Solution

by:linuxo
linuxo earned 600 total points
ID: 16659466
check ur database link syntax and your connection string...
0
 

Author Comment

by:bencouve2
ID: 16659870
OWNER      DB_LINK                                      USERNAME      HOST                      CREATED
PUBLIC      SI2.US.ORACLE.COM                      DWHMAST_FSA      SI2P.CSS.EU.INT      05/05/2006 17:14:11
BOB      BOB4SI.US.ORACLE.COM      DWHMAST_FSA      SI2P.CSS.EU.INT      05/10/2006 10:02:46
BOB      SI2BOB.US.ORACLE.COM      DWHMAST_FSA      SI2T.CSS.EU.INT      05/10/2006 18:26:15

OK, these are the enties. I accidentally pasted and then submited the above entriies but they are there now for the world to see so there we go. Here are the entries in the tnsnames.ora

SI2P.CSS.EU.INT =
  (DESCRIPTION=
    (SOURCE_ROUTE=on) .....

SI2T.CSS.EU.INT =
(DESCRIPTION=
 (SOURCE_ROUTE=on) ...

Now, I am sure that all is correct with these entries. The host column gives us the entries that are found in the tnsnames.ora. Remembering that tnsping and connection from these strings is fine. So, a little confusing.
0
 
LVL 4

Expert Comment

by:linuxo
ID: 16663620
Dear,

PUBLIC     SI2.US.ORACLE.COM  ----------------> SI2P.CSS.EU.INT

pleasemake sure that ur complete database link name is exist exactly in your TNSNAMES.ORA

-----------------------------------------------------------------------------------------------------
SQL> create database link dblink1
connect to scott identified by tiger
using 'HQ';

// your TNSNAMES should look as

HQ=(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)
(PORT=1521))
(CONNECT DATA=
(SERVICE_NAME=HQ.HOST1)
)
)
-----------------------------------------------

remains
0
 

Author Comment

by:bencouve2
ID: 16665031
Linuxo, thanks for the input. This is exactly how the db link has been created.

create database link SI2S.US.ORACLE.COM
connect to DWHMAST_FSA
identified by <pwd>
using 'SI2T.CSS.EU.INT';

Now, I cannot find anything wrong with what has been done at the command line, what is in the all_db_links, and what is in the tnsnames.ora. And all normal connections can be made with a ping and with Toad.
0
 
LVL 7

Expert Comment

by:vishal68
ID: 16665092
please post the contents of your sqlnet.ora file.

Vishal
0
 

Author Comment

by:bencouve2
ID: 16666111
Thank you all for your help. This turns out to be an incorrect figure for the port number in the tnsnames.ora.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question