Monday, February 06, 2012

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.

Sukhwinder Singh
Apps DBA