6 Replies Latest reply: Sep 17, 2013 1:37 PM by Richard Harrison . RSS

    Should db_link be recreated after impdp?

    user130038

      Hi

       

      I have imported schemas (impdp) from production database (10gR2, RHEL 64bit). One of the schemas has a db_link.

       

      The db_link points to a database that exists on the same server - both in production server and also on the new server where I imported the schemas.

       

      When I run a simple query in production using this db_link, it works but when I run the same query on the test server (where I imported the schemas), it gives me following error:

       

      ORA-02019: connection description for remote database not found

       

      I run this in prod database:

       

      Select count(1) from SOME_TABLE@my_link;

       

      when I run it in new database, it gives the above ORA error - even if I qualify the table and db_link with the schema owner like this:

       

      Select count(1) from the_owner.some_table@the_owner.my_link;

       

      NOTE: I am not running these queries as schema owner - I do not know the password. I am able to connect to both databases like this from the command prompt:

       

      $ sqlplus user@/password@db1

      $ sqlplus user@/password@db2

       

      Does this mean that I need to recreate the db_link - perhaps every time I import?

       

      Best regards

        • 1. Re: Should db_link be recreated after impdp?
          Srini Chavali-Oracle

          Database links use a connect descriptor ("my_link") that needs to be defined in the tnsnames.ora file in the ORACLE_HOME/nework/admin directory. Look up the definition of "my_link" in this file on the source database - does the same definition exist in the tnsnames.ora file of the target ?

           

          Srini

          • 2. Re: Should db_link be recreated after impdp?
            user130038

            I always thought that db_link uses the connect descriptor 'some_db' ( from the USING clause) and not the db_link names itself.

             

             

            Here is how the db_link has been defined:

             

             

            CREATE DATABASE LINK my_link

            CONNECT TO some_user

            IDENTIFIED BY <PWD>

            USING 'some_db';

             

             

            And "some_db" is both in the source systems tnanames.ora as well as the target system.

             

             

            I am able to connect to "some_db" using the "@" syntax which tells me that tnsnames.ora entry exists.

             

             

            Apologies for my ignorance but I do not think an entry for "my_link" has to be in the tnsnames.ora file. Required entries for the databases exist in the tnsnames.ora.

            • 3. Re: Should db_link be recreated after impdp?
              Srini Chavali-Oracle

              You are correct - I meant to state "some_db" instead of "my_link".

               

              What is the output of "tnsping some_db" from the target database ?

               

              HTH
              Srini

              • 4. Re: Should db_link be recreated after impdp?
                Richard Harrison .

                Hi,

                The database link should be cerated and work find after the import. The commonest cause of this type of problem i've found is that the value of db_domain is different in the 2 systems.

                 

                Can you give us the output of select * from dba_db_links?

                 

                Regards,

                Harry

                • 5. Re: Should db_link be recreated after impdp?
                  user130038

                  Thanks Srini.

                   

                  $ tnsping some_db

                  TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 17-SEP-2013 13:29:06

                  Copyright (c) 1997,  2010, Oracle.  All rights reserved.

                  Used parameter files:

                  Used TNSNAMES adapter to resolve the alias

                  Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <host-name>)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = some_db)))

                  OK (0 msec)

                  $

                   

                   

                  @Harry

                  select * from dba_db_links

                  owner       db_link     username    host        created

                  <owner>     my_LINK    <user_id>    some_db     8/8/2013 5:42:58 PM

                   

                  Sorry that I have to remove the actual info!

                  • 6. Re: Should db_link be recreated after impdp?
                    Richard Harrison .

                    Hi,

                    When you do the test are you logged in as the owner of the db link? Unless the database link is public only the owner of the link can use it.

                     

                    Regards,

                    Harry