Establishing a link between oracle databases

This article show how to establish a link between two Oracle databases. Here we'll establish such a link between two 'virtual' databases, called phoenix and zeus.

1. SSH Tunnel

The first step is to create a ssh tunnel between the two machines, hosting the databases. To perform that, we create a tunnel, redirecting phoenix 1522 port to zeus 1521 port (Oracle listening port).

phoenix% ssh oracle@zeus -L 1522:zeus:1521

From now, all connections on port 1522 of phoenix are redirected to zeus, on port 1521.

2. Virtual SID

Second step now, inform Oracle on phoenix of a new SID it can connect to. Of course this SID will be a redirection on zeus. For this, we need to get some informations about the target database, on zeus. Let's open the tnsnames.ora file, located usually in the folder $ORACLE_HOME/network/admin, on zeus machine. Now find the informations about your target database (here i want to connect to REMOTE_DB) :

...
REMOTE_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = zeus)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = remote_db_DGMGRL)

    )
  )
...

We find the remote_db sid definition, so let's write down the associated service name : remote_db_DGMGRL.
We are now able to create a SID on phoenix to connect to this database. So let's open phoenix's tnsnames.ora  (same location, $ORACLE_HOME/network/admin), and add this :

REMOTE_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = phoenix)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = remote_db_DGMGRL)

    )
  )

We just created an alias towards host phoenix on port 1522. But remember, we established a SSH tunnel redirecting every connection on phoenix:1522 to zeus:1521. So actually our sid will connect to zeus machine, on port 1521. Smells good :).  The service name we specified is remote_db_DGMGRL, and it's the service name on zeus associated to our target database. Smells even better :D.
Save and test with a simple tns ping :

phoenix% tnsping remote_db

TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 24-NOV-2010 12:48:58


Copyright (c) 1997,  2010, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = phoenix)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = remote_db_DGMGRL)))

OK (40 msec)

Connexion time of 40 ms shows this is not a localhost connection.
What is tested here is not the service connection, this will come later. Try to specify a wrong service name, you'll still be ok with tnsping. We just test connexion to the host/port we gave to our new sid.
If you change the sid, the hostname, the port or if you stop the ssh tunnel, tnsping will fail.
Now, on with the show, let's create our database link.

3. Database link

On phoenix, let's connect to the database, with sql*plus.
The following command creates the link :

SQL> create public database link my_far_away_db connect to db_schema identified by db_password using 'remote_db';

Database link created.

Let's check our data now :


SQL> select count(*) from my_table@my_far_away_db;

COUNT(*)
----------
     30266

To compare, assuming phoenix hosts the same database as zeus with different data (for instance a development database on phoenix, and a production database on zeus), you can check the difference without using the link :

SQL> select count(*) from db_schema.my_table;

COUNT(*)
----------
     27137

Voilà !

4. Link deletion

To delete a database link, its a one liner :

SQL> drop public database link my_far_away_db;

Database link dropped.

If you don't need this link anymore, you can remove the sid definition in tnsnames.ora, and stop your ssh tunnel.