Solved

database link not working

Posted on 2006-11-21
19
1,380 Views
Last Modified: 2008-03-17
Hi

I have created the following database links

create public database link TEST.WORLD
  connect to CUBE_DATA
  using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = PISARS)(PORT = 1521))
    )
    (CONNECT_DATA = (SERVICE_NAME = acorntst))
  )';

create public database link TEST
  connect to CUBE_DATA
  using 'TEST.WORLD';

when i do select * from products@test.world,i get data retreieved,but when i do

select * from products@test

i get ora-12154 tns could not resolve the service name.Why is so,isn't it possible to create a database link referencing another database link
0
Comment
Question by:suhinrasheed
19 Comments
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 17993118
the using clause has to either have a sqlnet qualified host string as you have done for your dblink test.world or an entry from your server side tnsnames.ora file.  you cannot point a db link to another database link.

for your db link 'TEST' it's giving you the error because it cannot find a 'TEST.WORLD' entry in the tnsnames.ora file located on your db server.

hope this clears it up for you,
daniels
0
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 17993124
actually, just found a note. i might be wrong.. never used the method you are attempting. never had a need to do so..

good luck,
daniels
0
 

Author Comment

by:suhinrasheed
ID: 17993222
I had made a mistake in tns.ora sorry.But i have a more important question which is of concern which is needed an immediate attention

i have created the following dblinks

CREATE PUBLIC DATABASE LINK BENPROD
CONNECT TO BEN_IF
IDENTIFIED BY <pwd>
USING 'BENPROD.WORLD';

i am able to use this link successfull.But when i try to select using the below link

CREATE PUBLIC DATABASE LINK TESTPROD1
CONNECT TO BEN_IF
IDENTIFIED BY <pwd>
USING 'BENPROD.WORLD';

i get ora-12535 tns operation timed out.Why is this.Help me
0
 
LVL 2

Expert Comment

by:syed555
ID: 17993231
Check names in your sqlnet.ora file.
0
 

Author Comment

by:suhinrasheed
ID: 17993252
There is no problem with sqlnet.ora.Please notice that above both the scripts are same both connect to same user,only database link name is different one is BENPROD(is working fine) and the other is TESTPROD1.WORLD---.WORLD is prefixed automatically .TESTPROD1.WORLD is not working
0
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 17993321
just a question but why would you want to do what you're trying to do?  that is, have a 2nd database link that points to another instead of just having one db link?

daniels
0
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 17993325
i cannot find any documentation that supports what you're trying to do?  where did you come up with the idea for this?  just curious

daniels
0
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 17993339
fyi, this may not be your problem but just because you can create a db link successfully does not mean that it working  or will work.

SQL> create database link db_prod
  2  connect to cust_prof
  3  identified by cust_prof
  4  using 'hello';

Database link created.

SQL> desc cust_prof.db_prod
ERROR:
ORA-04043: object cust_prof.db_prod does not exist


daniels
0
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 17993350
SQL> create database link db_prod
  2  connect to cust_prof
i  3  dentified by cust_prof
  4  using 'hello';

Database link created.

SQL> desc customer@db_prod
ERROR:
ORA-12154: TNS:could not resolve service name


this is the result of a bad service_name reference; which means that there is not such database named 'hello'

hope this helps,
daniels
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 17993373
check these two init parameters, make sure they are set to appropriate settings to do what you're doing.

open_links and open_links_per_instance

regards,
daniels
0
 

Author Comment

by:suhinrasheed
ID: 17993389
please all let me try to correct myself i am not trying to create now a database link referencing a database link

i have created the following dblinks

CREATE PUBLIC DATABASE LINK BENPROD
CONNECT TO BEN_IF
IDENTIFIED BY <pwd>
USING 'BENPROD.WORLD';

Here BENPROD.WORLD is a connection string which is been properly included in tnsnames.ora.
This database link is working fine,i.e i am able to select data from a remote table using this link.

Now my second dblink is created as

CREATE PUBLIC DATABASE LINK TESTPROD1.world
CONNECT TO BEN_IF
IDENTIFIED BY <pwd>
USING 'BENPROD.WORLD';

The above script has only difference that the name of the dblink is different,it is created the same way as benprod.But this link is not working and i am getting ora-12535:tns operation timed out.

Hope all are clear with my problem now.
0
 

Author Comment

by:suhinrasheed
ID: 17993420
open_links=4
open_links_per_instance=4
0
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 17993484
i tried your exact example and could not get it to break on my end.  :-(

i know this is not what you want to hear but why would you want to complicate the db_link name by adding a .world extention to it? in my opinion the real beauty of db_links is that you can hide the complexity of the connection details from the users to make it easier and less cumbersome...

regards,
daniels
0
 

Author Comment

by:suhinrasheed
ID: 17993508
Hi Daniel

i created the dblink with the name testprod1,but a suffix of .WORLD get's automatically suffixed to it.This i suppose is due to the global database name i suppose.The global database name is now INCA.WORLD.

When BENPROD was created it might have been just INCA.
0
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 17993546
yes, i could not understand why you were throwing a domain suffix into the naming mix for your db_links.  glad you got it worked out..

regards,
daniels
0
 

Author Comment

by:suhinrasheed
ID: 17993588
so do you mean i have to change the global name if i want the link to work,is that you are suggesting,why is this timed out error throwing in,just because of .world in link name
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 17995872
Yes, I suspect the problem is the ".world" that you added to the end of the dblink name.  I agree with daniel, if a link works without this extension, why complicate things by adding the extension?

The value in your sqlnet.ora file for:
NAMES.DEFAULT_DOMAIN =
determines whether you need to add the domain name (or not) when you create a database link.  And it also depends on whether your tnsnames.ora entries include the ".[domain_name]" syntax or not.
0
 

Author Comment

by:suhinrasheed
ID: 18025467
But i still am puzzled by the following scenario.I have the following link
CREATE PUBLIC DATABASE LINK BENPROD.WORLD
CONNECT TO BEN_IF
IDENTIFIED BY <pwd>
using '(DESCRIPTION =  (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = hbbenp1.us.db.com)(PORT = 1927)) ) (CONNECT_DATA =  (SID = HBBENP1)   ) )';

This link works fine.

I have a second link

CREATE PUBLIC DATABASE LINK TESTPROD1.WORLD
CONNECT TO BEN_IF
IDENTIFIED BY <pwd>
USING 'BENPROD.WORLD';

When i try to select data from a table using this link,it gives me ora-12535:tns timed out error.After referring some documents,i identified that this may be due to a Firewall problem and may need the network adminstrator to open the port referred in the connection string details of 'BENPROD.WORLD' remote database.
But what is confusing me is this i have one more link
CREATE PUBLIC DATABASE LINK BENPROD
CONNECT TO BEN_IF
IDENTIFIED BY <pwd>
USING 'BENPROD.WORLD';
The above dblink is also referring BENPROD.WORLD remote db and is working fine.So if it is a firewall issue then it should effect both BENPROD and TESTPROD1.WORLD dblinks.

Why is this strange problem happening

0
 
LVL 7

Accepted Solution

by:
Daniel Stanley earned 500 total points
ID: 18025692
i'm sticking with my comments where i stated that you should take the '.world' suffix out of your naming conventions, in my humble opinion it is a poor coding/naming methedology and above all else, has overcomplicated an otherwise simple solution and is causing you grief.. i can tell you that i would not let my developers get something like this by me, and nor would any of the other dba's that i work with. i can understand why your solution is failing and that it can be a very frustrating time when something that should work, isn't; what i fail to understand is why you see the need to continue down the path of the MOST resistance...  if that is not what you want to hear i would suggest you open a tar with oracle support on metalink, but i can take a pretty good guess at what they will tell you; they may very likely tell you that the '.world' suffix in a db link that you are attempting is not a supported naming convention because the object in question involves interaction with the sql*net layer


hope this has helped you,
daniels
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now