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

database link not working

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
suhinrasheed
Asked:
suhinrasheed
1 Solution
 
Daniel StanleyCommented:
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
 
Daniel StanleyCommented:
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
 
suhinrasheedAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
syed555Commented:
Check names in your sqlnet.ora file.
0
 
suhinrasheedAuthor Commented:
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
 
Daniel StanleyCommented:
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
 
Daniel StanleyCommented:
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
 
Daniel StanleyCommented:
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
 
Daniel StanleyCommented:
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
 
Daniel StanleyCommented:
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
 
suhinrasheedAuthor Commented:
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
 
suhinrasheedAuthor Commented:
open_links=4
open_links_per_instance=4
0
 
Daniel StanleyCommented:
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
 
suhinrasheedAuthor Commented:
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
 
Daniel StanleyCommented:
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
 
suhinrasheedAuthor Commented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
suhinrasheedAuthor Commented:
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
 
Daniel StanleyCommented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now