• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1084
  • Last Modified:

Problems with a Database link

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
bencouve2
Asked:
bencouve2
  • 6
  • 3
  • 2
  • +1
3 Solutions
 
vishal68Commented:
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
 
bencouve2Author Commented:
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
 
vishal68Commented:
Hi

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

Vishal
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
bencouve2Author Commented:
Sorry I put all_dba_links instead of all_db_links. Will let you know
0
 
bencouve2Author Commented:
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
 
actonwangCommented:
>>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
 
linuxoCommented:
check ur database link syntax and your connection string...
0
 
bencouve2Author Commented:
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
 
linuxoCommented:
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
 
bencouve2Author Commented:
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
 
vishal68Commented:
please post the contents of your sqlnet.ora file.

Vishal
0
 
bencouve2Author Commented:
Thank you all for your help. This turns out to be an incorrect figure for the port number in the tnsnames.ora.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now