Step by step to configure database link | DB LINK

DATABASE LINK / DB LINK / NETWORK LINK

- Suppose two databases(DB1 , DB2) are there on two machines (M1 and M2) respectively.

Using DATABASE link you can acess the data from host database (DB2) on Machine2 .

You can configure DATABASE link for a single user/schema.

To Configure DATABASE LINK:
if you are on DB1 (residing on machine1) and want to acess DB2(residing on machine2) database.

Configure TNS in TNSNAMES.ora file on machine1

Ex:   DB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = machine2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = emrep)
(UR=A)
    )
  )


-- In the above TNS DB2 is the tns names alias. Any name can be given. Input the correct values in HOST (the hostname of the host machine), SERVICE_NAME (service name/instance name of host database).

Test the hand shake between servers using PING utility.
EXAMPLE:   $ PING hostname/ipaddress of host server.

Check weather the listener is up and running on the host server.
EXAMPLE:   $ lsnrctl status

 Test the hand shake between databases using tnsping utility.
EXAMPLE:  $ tnsping DB2

  create DATABASE LINK

  SQL> CREATE Public Database Link <link name>
Connect To scott Identified By tiger
Using <'tns name example: DB2'>;

iff success then issue:

select * from emp@DB2;

DATAPUMP - While exporting  NETWORK_LINK = DB2

Example :  expdp system/xxxx@db2 full=y directory=Backup_folder dumpfile=dumpfile_21_11_2018.dmp logfile=logfile_21_11_2018.dmp network_link=DB2 consistent=y


Common errors faced:

•  If connection error try os level ping utility.
•  Antivirus block
•  firewall issue when tnsping. 
•  listener issue. if host machine dosent have actively running listener.
•  tns improperly configured.