9 Replies Latest reply: Jun 7, 2013 4:28 PM by Dean Gagne-Oracle RSS

    Datpump Export,Import across lower and higher versions

    T.Boyd
      Versions involved : 10.2.0.4, 11.2.0.3
      Operating System  : AIX 6.1
      +++++++++++++++++++++++++++++++++
      Lets say I export a schema in a 11.2 Database using 11.2 expdp .

      Can I import the above dump (11.2) to a 10.2 Database using 10.2 impdp ?
        • 1. Re: Datpump Export,Import across lower and higher versions
          Marcus Rangel
          No, impdp version 10.2 will not recognize the file:
          [oracle@taurus ~]$ impdp system/******** dumpfile=teste.dmp
          
          Import: Release 10.2.0.5.0 - 64bit Production on Thursday, 06 June, 2013 13:55:06
          
          Copyright (c) 2003, 2007, Oracle.  All rights reserved.
          
          Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
          With the Partitioning, OLAP, Data Mining and Real Application Testing options
          ORA-39001: invalid argument value
          ORA-39000: bad dump file specification
          ORA-39142: incompatible version number 3.1 in dump file "/opt/oracle/product/Ora10gR2/rdbms/log/teste.dmp"
          • 2. Re: Datpump Export,Import across lower and higher versions
            T.Boyd
            Thank you Marcus.

            Can I set VERSION=10.2 as a workaround ? I don't have a 10.2 DB to test now . Its for one of our customers.

            What about original export , Import ?

            Lets say I export a schema in a 11.2 Database using 11.2 exp .

            Can I import the above dump (11.2) to a 10.2 Database using 10.2 imp ?
            • 3. Re: Datpump Export,Import across lower and higher versions
              Marcus Rangel
              What about original export , Import ?

              Lets say I export a schema in a 11.2 Database using 11.2 exp .

              Can I import the above dump (11.2) to a 10.2 Database using 10.2 imp ?
              This will not work as well.
              Can I set VERSION=10.2 as a workaround ? I don't have a 10.2 DB to test now . Its for one of our customers.
              Let me try this one...

              It worked. Well, at least, the dump file was accepted and most objects were imported. Of course there may be side effects, ask your customer for the import log and check for errors.

              Edited by: Marcus Rangel on Jun 6, 2013 3:02 PM
              • 4. Re: Datpump Export,Import across lower and higher versions
                Srini Chavali-Oracle
                Use the VERSION parameter in expdp to specify the target database release - http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#sthref150

                expdp/impdp are recommeded over original exp/imp

                HTH
                Srini
                • 5. Re: Datpump Export,Import across lower and higher versions
                  T.Boyd
                  Setting VERSION=10.2 worked fine for me.

                  Since I was curious, I tested taking a table level export dump from 11.2.0.3 Schema using 11.2.0.3 expdp without VERSION parameter. I succesfully managed to import those tables to a 10.2.0.2 schema needless to say using 10.2.0.2 impdp.
                  I don't know how this worked . Maybe export,importing from higher to lower version at schema level might fail. Need to test it when I have time.


                  This is contrary to Oracle documentation (11.2 DB Utilities guide)

                  Data Pump Import cannot read dump file sets created by a database release that is
                  newer than the current database release, unless those dump file sets were created
                  with the VERSION parameter set to the release of the target database. Therefore,
                  the best way to perform a downgrade is to perform your Data Pump export with
                  the VERSION parameter set to the release of the target database


                  But, anyway I am glad that Oracle's superior coding has enabled me to export,import from higher to lower version.
                  • 6. Re: Datpump Export,Import across lower and higher versions
                    Dean Gagne-Oracle
                    If you don't specify a version, then Data Pump uses the compatibility version. So...

                    If you export from 11.2.0.3 but your compatibility is set to 10.2 and if you don't use a version parameter then you will get a dumpfile that is importable into 10.2 and later.

                    If you export from 11.2.0.3 but your compatibility is set to 10.2 and if you use a version parameter set to 11.1 then you will get a dumpfile that is importable into 10.2 and later. In this case, it ignores the version since compatibility is set less than the version parameter.

                    So in your case, I'm guessing that the compatibility parameter is set low enough to be imported into your version 10 database.

                    Dean
                    • 7. Re: Datpump Export,Import across lower and higher versions
                      T.Boyd
                      Thank You Dean.

                      But my source DB had the compatible parameter set as 11.2 . Here is the output
                      SQL > show parameter compatible
                      
                      NAME                                 TYPE        VALUE
                      ------------------------------------ ----------- ------------------------------
                      compatible                           string      11.2.0.3
                      • 8. Re: Datpump Export,Import across lower and higher versions
                        Paul M.
                        Lets say I export a schema in a 11.2 Database using 11.2 expdp .

                        Can I import the above dump (11.2) to a 10.2 Database using 10.2 impdp ?
                        You can use 10.2 expdp and NETWORK_LINK parameter.
                        • 9. Re: Datpump Export,Import across lower and higher versions
                          Dean Gagne-Oracle
                          T. Boyd.

                          Not sure why that worked. It should not have worked. The reasoning behind that is because you can have objects in 11.2 that are unknown in 10.2. When you specify the version, it tells Data Pump to only export things from 11.2 that 10.2 know about.

                          Dean