Create database link on Oracle Database with 2 Databases on different machines.

oracle

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

Best Answer

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
Related Question