5

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

Improve this question
3
  • 2
    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/… Commented Nov 28, 2013 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 . Commented Nov 28, 2013 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/… Commented Nov 28, 2013 at 21:08

1 Answer 1

Reset to default
5

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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.