2 Replies Latest reply on May 5, 2014 10:35 AM by elmousa68

    Database link between Oracle 10g database and Oracle 11g database

    elmousa68

      We have an Oracle 10g database to which we log on using SID=mod

       

      The TNS names file that is present in the Oracle NETWORK\ADMIN directory on the development machine contains the following two entries among others

       

      MOD =

        (DESCRIPTION =

          (ADDRESS_LIST =

           

            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.70.10.23)(PORT = 1521))

           

            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.70.10.21)(PORT = 1521))

          )

          (CONNECT_DATA =

            (SERVICE_NAME = mod)

          )

        )

       

      NEWMOD =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.70.10.7)(PORT = 1521))

            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.70.10.9)(PORT = 1521))

            (LOAD_BALANCE = yes)

          )

          (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SERVICE_NAME = MOD)

          )

        )

       

      In the SQL client we can log on to both mod and newmod each with its own username and password.

       

      Upon creating a database link as follows

       

      CREATE Public DATABASE LINK newmod CONNECT TO user1 IDENTIFIED BY password1 USING 'newmod';

       

      the database link is created without problems.

       

      however when trying to retrieve data from tables in the database which uses 10.70.10.7 and 10.70.10.9  using a command such as

       

      select column1 from table1@newmod;

       

      we get the following error:

       

       

      ORA-12154: TNS:could not resolve the connect identifier specified

       

      How can we access the tables in the newmod tns entry given that it resides on an 11g server while the mod tns entry is on a  10g server? (both use the same service name but are found on different ip addresses)

       

      Your help is much appreciated.