9 Replies Latest reply: Mar 3, 2013 2:40 AM by Ivan89 RSS

    Data pump import from 11g to 10g

    Ivan89
      I have 2 database: first is 11.2.0.2.0 and second is 10.2.0.1.0
      In 10g i created database link on 11g

      CREATE DATABASE LINK "TEST.LINK"
      CONNECT TO "monservice" IDENTIFIED BY "monservice"
      USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = host)(PORT = port))) (CONNECT_DATA = (SID = sid)))';

      And execute this query for test dbLink which work fine:

      select * from v$version@TEST.LINK;

      After it i try to call open function:

      declare
      h number;
      begin
      h := dbms_datapump.open('IMPORT', 'TABLE', 'TEST.LINK', null, '10.2');
      end;

      and get exception: 39001. 00000 - "invalid argument value"
      if i remove 'TEST.LINK' from the arguments it works fine

      Edited by: 990594 on 26.02.2013 23:41
        • 1. Re: Data pump import from 11g to 10g
          Srini Chavali-Oracle
          Can you retry with a database link name that does not have a 'dot' - try with "TEST_LINK" ?

          Pl also post the complete error codes and messages - there should be additional messages after ORA-39001

          HTH
          Srini
          • 2. Re: Data pump import from 11g to 10g
            Ivan89
            Srini Chavali thanks for help, i replaced 'dot' by 'underline' and got same result
            here full error report:
            Error report:
            ORA-39001: invalid argument value
            ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
            ORA-06512: at "SYS.DBMS_DATAPUMP", line 2926
            ORA-06512: at "SYS.DBMS_DATAPUMP", line 4367
            ORA-06512: at line 4
            +39001. 00000 - "invalid argument value"+

            I think if i cannot import to 10g from 11g. Maybe i able to export from 11g to 10g
            Then i try to open datapump on 11g
            h := dbms_datapump.open('EXPORT', 'SCHEMA', 'TEST_LINK', null, '10.2');
            and get this exception:
            ORA-39006: internal error
            ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
            ORA-06512: at "SYS.DBMS_DATAPUMP", line 3507
            ORA-06512: at "SYS.DBMS_DATAPUMP", line 5296
            ORA-06512: at line 4
            +39006. 00000 - "internal error"+
            but this h := dbms_datapump.open('EXPORT', 'SCHEMA', null, null, '10.2'); work fine
            What makes me think that something is wrong with dbLink.
            • 3. Re: Data pump import from 11g to 10g
              Richard Harrison .
              Hi,
              What about if you do it from the command line with network_link=TEST_LINK - does that work?

              Does the db link exist in the same schema you are running dbms_datapumpas (sorry to ask an obvious question...)

              11g linking to 10g definitely works - I've done a number of migrations this way.

              Cheers,
              Harry
              • 4. Re: Data pump import from 11g to 10g
                Ivan89
                Hi Richard Harrison,
                result for import from 11g to 10g:
                impdp user/pass@dburl schemas=SCHEMANAME network_link=TEST_LINK version=10.2

                Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
                ORA-39001: invalid argument value
                ORA-39169: Local version of 10.2.0.1.0 cannot work with remote version of 11.2.0.2.0


                result for export from 11g to 10g:

                expdp user/pass@dburl schemas=SCHEMANAME network_link=TEST_LINK version=10.2

                Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64 bit Production
                With the Partitioning, OLAP, Data Mining and Real Application Testing option
                ORA-39006: internal error
                ORA-39065: unexpected master process exception in DISPATCH
                ORA-04052: error occurred when looking up remote object SYS.KUPM$MCP@TEST_LINK
                ORA-00604: error occurred at recursive SQL level 3
                ORA-06544: PL/SQL: internal, error, arguments: [55916], [], [], [], [], [], [], []
                ORA-06553: PLS-801: internal error [55916]
                ORA-02063: preceding 2 lines from TEST_LINK
                ORA_39097: Data Pump job encountered unexpected error -4052
                • 5. Re: Data pump import from 11g to 10g
                  Richard Harrison .
                  Hi,
                  Think it may be a bug - there are a number of issues related to this kind of thing and i think you need to be on 10.2.0.3 at least for this to work.

                  Sorry - iniitially i thought you were pulling from 10g (and not the other way round).

                  Regards,
                  Harry
                  • 6. Re: Data pump import from 11g to 10g
                    Ivan89
                    Richard Harrison, I found the same suggestion on ittoolbox forum
                    It's very bad because i can't update database, I think i can write my own client for this purpose.
                    Thanks all for help.
                    • 7. Re: Data pump import from 11g to 10g
                      Richard Harrison .
                      Hi,
                      If you are only pulling a small amount of objects you could just do

                      create table xx as select * from xx@test_link ??

                      Cheers,
                      Rich
                      • 8. Re: Data pump import from 11g to 10g
                        Dean Gagne-Oracle
                        I think it may be the version parameter it doesn't like. When you do a network Data Pump operation, you really only need the version if you are doing an export. I forgot to check what you are doing. If you are doing an import, the version will be negotiated for you. Here is how it works:

                        source is 10.2 with compat = 10.1
                        target is 11.2 with compat = 11.2

                        You job version is always going to be the lowest compatibility setting. So, in this case, it will be 10.1. If you ran your impdp command with version=10.2, this would throw an error since the compatibility setting on the source is 10.1. It does not know how to generate a 10.2 dumpfile.

                        So, try your same open call without the version. I think it will work. The reason it worked when you dropped your dblink is because your version parameter was fine for 11.2.

                        Hope this helps.

                        Dean
                        • 9. Re: Data pump import from 11g to 10g
                          Ivan89
                          Richard Harrison, thank you, i need copy all schema objects, but it's statement very saved my time on weekend. I wrote my own tools for copy the schema objects with blackjack and governess))