How to Re-create database links

Re-create database links

To check for the existence of database links, use SQL*Plus on the database server node to connect to the Applications database instance as APPS and run the following query:

$ sqlplus apps/<apps password>
SQL> select db_link from dba_db_links; 

If the EDW_APPS_TO_WH and APPS_TO_APPS database links exist, use the following commands to drop and re-create them:

$ sqlplus apps/<apps password>
SQL> alter session set global_names=false;
SQL> drop database link EDW_APPS_TO_WH;
SQL> drop database link APPS_TO_APPS;

SQL> create database link EDW_APPS_TO_WH connect to apps
     identified by <apps password> using
     '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<host name>)
     (PORT=<port number>))(CONNECT_DATA=(SID=<ORACLE_SID>)))';

SQL> create database link APPS_TO_APPS connect to apps
     identified by <apps password> using
      '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<host name>)
     (PORT=<port number>))(CONNECT_DATA=(SID=<ORACLE_SID>)))';

where <host name> is the host name of the database server node, <port number> is the port number of the new Net8 listener for the database instance, and <ORACLE_SID> is the ORACLE_SID of the database instance. If you have custom self-referential database links in the database instance, use the following commands to drop and re-create them:

$ sqlplus apps/<apps password>
SQL> drop database link <custom database link>;
SQL> create database link <custom database link> connect to
     <user> identified by <password> using
     '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<host name>)
     (PORT=<port number>))(CONNECT_DATA=(SID=<ORACLE_SID>)))';

where <custom database link>, <user>, <password>, and <ORACLE_SID> are all as they were in the original database link definition, and <port number> is the port number of the new Net8 listener for the database instance.

Regards

Srini