Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have 2 oracle databases (db1 and db2) on different machines, lets suppose: db1 on 192.168.1.1 and db2 on 192.168.1.2

What I want to achieve is to create a link on db1 that will allow me to perform queries on db2.

This is the way I created the link on db1:

create public database link db2Link connect to db2username identified by db2password
using 'db2';

I think this should work if both that bases are on the same machine, but as I want to connect to a database that is on a different machine, how can I do that link?

Thanks Best Regards

share|improve this question

migrated from stackoverflow.com Nov 30 '13 at 0:57

This question came from our site for professional and enthusiast programmers.

1  
You need to setup a TNS entry on the db1 machine so that it resolve 'db2' See think link for example -msutic.blogspot.com/2009/07/… –  OldProgrammer Nov 28 '13 at 19:55
1  
Agree with @OldProgrammer: you need to setup Oracle client on the db1 box to connect to db2 by altering tnsnames.ora file and after that create a dblink using a name given to db2 in tnsnames.orain using part. It's also possible to specify connection parameters directly in create database link ... without tnsnames.ora . –  ThinkJet Nov 28 '13 at 20:16
1  
The database initialisation parameter GLOBAL_NAMES can also have an effect on whether this will work as expected. It's less restrictive if GLOBAL_NAMES is set to false - it doesn't insist on matching names and you might not need to be so careful about fully qualified db names like db1.mydept.myorg.uk or whatever. See docs.oracle.com/cd/B28359_01/server.111/b28320/… –  Morbo Nov 28 '13 at 21:08

1 Answer 1

You can define the connection string via tnsnames.ora then reference the alias

remotedb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = remotedb.fqdn.com)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = ORCL))
  )

Then create a dblink referencing that alias:

CREATE DATABASE LINK remotedb
    CONNECT TO SYSTEM IDENTIFIED BY <password>
    USING 'remotedb';

Or facilitate the same inline with:

CREATE DATABASE LINK remotedb
CONNECT TO SYSTEM IDENTIFIED BY <password>
USING'(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = remotedb.fqdn.com)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = ORCL)))';

For details on syntax, check http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_5005.htm

If you want to avoid DNS resolution, just substitute the IP address for remotedb.fqdn.com instead. Of course, you'll want to check that the source can initiate TCP connections to the remote port 1521:

nc -zv remotedb.fqdn.com 1521
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.