8 Replies Latest reply: Jan 30, 2013 5:45 PM by 988202 RSS

    impdp fails via database link

    667901
      Hi,

      I am trying to import a particular schema from another instance into my instance over a database link using Data Pump.

      Source instance: Solaris on Sparc 10.2.0.4.0 64bit
      Target instance: Linux on x32, 10.2.0.4.0

      I created a database link on the target database and verified that the link works, i.e. I could query tables in the remote database using the user which should receive the data.

      I created a directory 'exp' on the target database and granted appropriate permissions to 'mh03'.

      Then I tried the import and got an error:

      -----

      oracle@padsw7ora01 imp$ impdp mh03/*** NETWORK_LINK=padsol25 SCHEMAS=test_bas_bkrus_d123120 REMAP_SCHEMA=test_bas_bkrus_d123120:mh03 DIRECTORY=exp LOGFILE=imp.log

      Import: Release 10.2.0.4.0 - Production on Tuesday, 20 January, 2009 14:47:30

      Copyright (c) 2003, 2007, Oracle. All rights reserved.

      Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      ORA-39006: internal error
      ORA-39113: Unable to determine database version
      ORA-02083: database name has illegal character '-'

      ORA-39097: Data Pump job encountered unexpected error -2083

      oracle@padsw7ora01 imp$

      -----

      I tried searching metalink and web but could not find any meaningful information. I tried to use parameter VERSION='10.2.0.4.0' but this did not help either (error message stays as is).

      I found this which seemed slightly related:

      ORA-39113:     Unable to determine database version
      Cause:     The Data Pump was unable to determine the compatibility level and version of the current database using SYS.DBMS_UTILITY.DB_VERSION.
      Action:     Make sure access to the DBMS_UTILITY package is granted to you. If this is a network job, be sure that access to the DBMS_UTILITY package is granted to you on the remote instance.

      But even doing "grant execute on SYS.DBMS_UTILITY to mh03" did not help at all.

      I do realize though that the target database is set compatible to 10.2.0.3.0:

      -----

      1 declare
      2 v varchar2(1000 char);
      3 c varchar2(1000 char);
      4 begin
      5 DBMS_UTILITY.DB_VERSION(v,c);
      6 dbms_output.put_line(v);
      7 dbms_output.put_line(c);
      8* end;
      SQL> /

      PL/SQL procedure successfully completed.

      SQL> set serverout on
      SQL> /
      10.2.0.4.0
      10.2.0.3.0

      PL/SQL procedure successfully completed.

      SQL>

      -----

      Any ideas what went wrong and how I can remedy this? Thanks a lot!

      Kind regards

      robert

      Edited by: rklemme on Jan 20, 2009 6:16 AM
        • 1. Re: impdp fails via database link
          Dean Gagne
          Hi,

          You need to do this on both instances. So, for your other instance do this:

          declare
          v varchar2(1000 char);
          c varchar2(1000 char);
          begin
          DBMS_UTILITY.DB_VERSION@padsol25(v,c);
          dbms_output.put_line(v);
          dbms_output.put_line(c);
          end;
          /

          Post those results.

          Dean
          • 2. Re: impdp fails via database link
            667901
            Hi Dean,

            thank you for replying!

            On the source machine
            -----
            SQL> ed
            Wrote file afiedt.buf
            147
            ^D
            1 declare
            2 v varchar2(1000 char);
            3 c varchar2(1000 char);
            4 begin
            5 DBMS_UTILITY.DB_VERSION(v,c);
            6 dbms_output.put_line(v);
            7 dbms_output.put_line(c);
            8* end;
            SQL> /
            10.2.0.4.0
            10.2.0.1.0

            PL/SQL procedure successfully completed.
            -----
            So settings do actually differ.

            Someone also mentioned that it might be related to charsets.
            -----
            Target:

            SQL> column parameter format a30
            SQL> column value format a30
            SQL> select * from V$NLS_PARAMETERS where parameter like '%CHAR%SET%' order by parameter;

            PARAMETER VALUE

            NLS_CHARACTERSET WE8ISO8859P1
            NLS_NCHAR_CHARACTERSET UTF8
            -----
            Source:

            SQL> column parameter format a30
            SQL> column value format a30
            SQL> select * from V$NLS_PARAMETERS where parameter like '%CHAR%SET%' order by parameter;

            PARAMETER VALUE

            NLS_CHARACTERSET UTF8
            NLS_NCHAR_CHARACTERSET AL16UTF16
            -----
            They are actually different...

            What do you think?

            Kind regards

            robert
            • 3. Re: impdp fails via database link
              Dean Gagne
              Robert,

              Can you run the plsql from the target machine pointing to the source machine. This is how the code is being run, so I'm wondering if it is some strange character being returned that is not recognized. I don't know what it could be, but since the error has to do with an unreconized character or something like that, I wonder if it might have something to do with the different character sets. Just run the command with the dblink after the procedure call.

              DBMS_UTILITY.DB_VERSION(v,c) -- gets the information from the local node


              DBMS_UTILITY.DB_VERSION@your_db_link_here(v,c) -- gets the information on the remote node, and sends it over the dblink
              for the local node to translaste and print out.

              Thanks

              Dean
              • 4. Re: impdp fails via database link
                679289
                Hello rklemme,

                I am not sure this is your problem but I had once this error when trying to import through database link:

                ORA-02083: database name has illegal character '-'

                It was because in the global name of the instance I had this character in my domain name "-" (something like sid.my-company.com)
                I removed the "-" character from global name, recreated the database link and it worked.

                Hope it helps,

                PY
                • 5. Re: impdp fails via database link
                  667901
                  Dean:
                  Can you run the plsql from the target machine pointing to the source machine. This is how the code is being run, so I'm wondering if it is some strange character being returned that is not recognized. I don't know what it could be, but since the error has to do with an unreconized character or something like that, I wonder if it might have something to do with the different character sets. Just run the command with the dblink after the procedure call.
                  Good point! It produces the same error:
                  SQL> set serverout on
                  SQL> /
                  declare
                  *
                  ERROR at line 1:
                  ORA-02083: database name has illegal character '-'
                  
                  
                  SQL> list
                    1  declare
                    2  v varchar2(1000 char);
                    3  c varchar2(1000 char);
                    4  begin
                    5  DBMS_UTILITY.DB_VERSION@padsol25(v,c);
                    6  dbms_output.put_line(v);
                    7  dbms_output.put_line(c);
                    8* end;
                  SQL> 
                  PY:
                  I am not sure this is your problem but I had once this error when trying to import through database link:
                  ORA-02083: database name has illegal character '-'

                  It was because in the global name of the instance I had this character in my domain name "-" (something like sid.my-company.com)
                  I removed the "-" character from global name, recreated the database link and it worked.>
                  Well, that's the exact error I see. The FQDN does actually contain a dash padsol25.pad.my-company.net. Apparently Oracle does some name resolution here because I do not use the full name in the connect string and the error persists if I replace the non full qualified host name with the IP address. The weird thing is that querying works without issues, it's just the version check that fails.

                  Do I have any other option as to fiddle with name resolution, e.g. put an entry in +/etc/hosts+ that renames the host to not have a FQDN with a dash?

                  Thanks!

                  robert
                  • 6. Re: impdp fails via database link
                    679289
                    rklemme

                    In my case it had nothing to do with the hostname of the server or name resolution. It was related to the global_name of the instance.
                    I could query using the link but not perform a remote import.

                    Try to modify temporarly your global_name by removing the dash in the domain name and test your import:
                    sql> alter database rename global_name to <your sid>.pad.*mycompany*.net;

                    PY
                    • 7. Re: impdp fails via database link
                      667901
                      In my case it had nothing to do with the hostname of the server or name resolution. It was related to the global_name of the instance.
                      I could query using the link but not perform a remote import.

                      Try to modify temporarly your global_name by removing the dash in the domain name and test your import:
                      sql> alter database rename global_name to <your sid>.pad.mycompany.net;>
                      Changing the global name is not an option for me right now as I first have to get an overview about the dependencies and other systems that might be affected. I'll keep your solution in mind and try it out as soon as I get the time.

                      Thank you and Dean for all the help!

                      robert
                      • 8. Re: impdp fails via database link
                        988202
                        In My case it had everything to do with the hostname.

                        Import: Release 11.2.0.1.0 - Production on Wed Jan 30 23:10:39 2013

                        Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

                        Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                        With the Partitioning, OLAP, Data Mining and Real Application Testing options
                        ORA-39006: internal error
                        ORA-39065: unexpected master process exception in DISPATCH
                        ORA-02083: database name has illegal character '-'

                        ORA-39097: Data Pump job encountered unexpected error -2083


                        After, I read this thread. I adviced the dba with this change.

                        alter database rename global_name to <sid>.<hostname_without_fancy_characters>;


                        and voila!!!! We all went home on time :)