Ads by Smowtion

Monday, 17 December 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

Ads by Smowtion

Ads by Smowtion