3 Replies Latest reply: Apr 27, 2012 12:10 PM by 933273 RSS

    Link from Oracle DB server to Oracle DB server

    933273
      Hi,
      I need some help how to link oracle db server A to Oracle db server B. It is so difficult to set it up. I kept getting error 'Could not resolve the connect identified...'

      when I execute this query statement that links to B server: select COUNT(*) from table1@SAFESTAGING.US.MYCOMP.COM

      I am currently on server A and here is the information server of server B.

      Server B
      DB Name: 'SafeStaging'
      Domain: 'us.mycomp.com'
      global Name: true
      Name of db link: SAFESTAGING.US.MYCOMP.COM

      From the query screen, I used the query syntax to establish the link as following:

      create database link SAFESTAGING.US.MYCOMP.COM
      connect to "login" identified by "password"
      using 'service_staging'

      In the TNSNAMES.ORA file on the server desktop, I also add these lines
      service_staging =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ###.###.#.##)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =SAFESTAGING.US.MYCOMP.COM)
      )
      )

      select COUNT(*) from table1@SAFESTAGING.US.MYCOMP.COM --> get error: 'Could not resolve the connect identified...'
        • 1. Re: Link from Oracle DB server to Oracle DB server
          hitgon
          Hi,

          #Please conform the global_name

          select * from global_name;
          show parameter db_name

          Note: You can do the Global Name should be equal to the DB_NAME

          #Recreate the DB Link
          create database link service_staging connect to "login" identified by "password" using 'service_staging';

          #Check the DB Link
          select COUNT(*) from table1@service_staging

          #Check the Service
          tnsping service_staging;

          I hope your problem will resolved now,

          you can also check the below document
          http://www.akadia.com/services/ora_database_links.html

          best of luck :)

          Regards
          Hitgon

          Edited by: hitgon on Apr 26, 2012 9:22 AM

          Edited by: hitgon on Apr 26, 2012 10:07 AM
          • 2. Re: Link from Oracle DB server to Oracle DB server
            933273
            Skip

            Edited by: 930270 on Apr 27, 2012 10:12 AM
            • 3. Re: Link from Oracle DB server to Oracle DB server
              933273
              Thanks for the reply. But, the issue is not resolved. I still get error: 'Could not resolve the connect identifier specified'


              The TNSNAMES.ORA contains this information

              QSAFESTG.US.MyComp.COM =
              (DESCRIPTION =
              (ADDRESS_LIST =
              (ADDRESS = (PROTOCOL = TCP)(HOST = ###.##.###.##)(PORT = 1521))
              )
              (CONNECT_DATA =
              (SERVICE_NAME = qsafestg)
              )
              )

              Oracle uses this information to connect this db server is fine from the UI.  But, the issue when I create the database link.

              create database link QSAFESTG.US.MyComp.COM connect to "login" identified by "password" using 'qsafestg'

              SELECT COUNT(*)
              FROM table1@QSAFESTG.US.MyCom.COM

              -----> I got an error 'could not resolve the connect identifier specifier'

              on DOS command - tnsping qsafestg --> get error: 'Failed to resolve name'. --why it failed?
              -------------

              Then, I tried creating another link

              create database link qsafestg connect to "login" identified by "password" using 'qsafestg'

              SELECT COUNT(*)
              FROM table1@qsafestg

              -----> I got an error 'could not resolve the connect identifier specifier'

              ON DOS Command: tnsping QSAFESTG.US.MyComp.COM --> Return: OK. (100ms) ?

              Why do I keep getting this error: could not resolve the connect identifier specifier