1 2 3 Previous Next 35 Replies Latest reply: Aug 17, 2010 4:45 AM by 781937 RSS

    Exp / imp schema from 10g to 9i

    NPD
      Hi Guys,

      I posted this last week and from the suggestions given, niothing seem to work. Are there any new suggestions.

      exp system/manager owner='production' file='/oracle10/production.dmp' l og='/oracle10/production.log'

      --- Created a new user call test

      imp system/manager fromuser='production' touser='test' file='/oracle9/production.dmp' log='/oracle9/production.log

      Import: Release 9.2.0.8.0 - Production on Fri Aug 13 14:10:04 2010

      Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


      Connected to: Oracle9i Release 9.2.0.8.0 - 64bit Production
      JServer Release 9.2.0.8.0 - Production

      IMP-00010: not a valid export file, header failed verification
      IMP-00000: Import terminated unsuccessfully

      Thanking you in advance.
        • 1. Re: Exp / imp schema from 10g to 9i
          781937
          Its not possible to import from 10g dump into 9i database. So kindly use 9i client to export from 10g database after that import into 9i database from the exported dumpfile.
          • 2. Re: Exp / imp schema from 10g to 9i
            CKPT
            Hi,

            before exporting use version clause

            or use exp from 9i home to export
            • 3. Re: Exp / imp schema from 10g to 9i
              amardeep.sidhu
              You are exporting in 10g and importing in 9i ?

              If yes, it is not going to work. Use 9i exp utility to connect to 10g database and then export. It should work that ways.

              If not, it means the dump file is corrupt. That could have resulted from transferring it in ascii mode while ftp'ing the file or transferring using some other method.
              • 4. Re: Exp / imp schema from 10g to 9i
                mohammed_dba
                hi dear,
                it is possible ,when export at that time you specify version as under
                expdp scott/tiger version=10.2 dumpfile=scott.dmp
                • 5. Re: Exp / imp schema from 10g to 9i
                  NPD
                  before exporting use version clause : How do I do that?

                  or use exp from 9i home to export: I have tried this but correct me here if I'm wrong. This is what I've done



                  ora902@db01->prod01:/export/ora902>echo $ORACLE_HOME
                  /oracle10/app/oracle/product/10.2.0/db_1

                  ora902@db01->prod01:/u1/ora902>export ORACLE_HOME=/u1/ora902

                  ora902@db01->pll01:/u1/ora902>echo $ORACLE_HOME
                  /u1/ora902

                  ora902@db01->pll01:/u1/ora902>exp

                  Export: Release 10.2.0.1.0 - Production on Mon Aug 16 09:09:46 2010 " I still see 10g here"

                  Copyright (c) 1982, 2005, Oracle. All rights reserved.


                  Username: system
                  Password:

                  EXP-00056: ORACLE error 1034 encountered
                  ORA-01034: ORACLE not available
                  ORA-27101: shared memory realm does not exist
                  SVR4 Error: 2: No such file or directory
                  EXP-00005: all allowable logon attempts failed
                  EXP-00000: Export terminated unsuccessfully

                  Thanx.
                  • 6. Re: Exp / imp schema from 10g to 9i
                    Nicolas.Gasparotto
                    CKPT wrote:
                    Hi,

                    before exporting use version clause
                    VERSION is an option of export dump (expdp), available only between databases above 10g. A dump file created with expdp cannot be imported into a database lower than 10g.
                    To import into a 9i database, old export utility version must be used (exp from 9i) to create the dump file.

                    Nicolas.
                    • 7. Re: Exp / imp schema from 10g to 9i
                      635471
                      The correct procedures for inter-version import and export, including which client versions to use and any extra scripts required, are documented in the Utilities Guide. Consult the version of the documentation corresponding to the highest version you want to transfer date from ot to.
                      • 8. Re: Exp / imp schema from 10g to 9i
                        781937
                        Boss you need to set oracle home only. Dont export your 9i database. Instead use tns and export the 10g database from 9i home.

                        export ORACLE_HOME=9ipath
                        exp helpdesk/password@10gtns file=dump.dmp log=logfile.log

                        Edited by: Ramesh on Aug 16, 2010 1:11 PM
                        • 9. Re: Exp / imp schema from 10g to 9i
                          791023
                          Hi ,


                          For schema export from 10g to 9i is somthing you are exporting from higher version to lower version. For importing in lower version you need to export using lower version export exe(exp exe of 9i) and than import in 9i database.
                          In your case if Both db are on same server(host) made a entry in tnsnames.ora file of your 9i db home for 10g db(like tnsnames entry is "10gdb"). and export from 9i exe like


                          exp system@10gdb file=exp_10g.dmp log=exp_10g.log

                          then import this file in 9i db.

                          Regards
                          Rajinder Kumar
                          • 10. Re: Exp / imp schema from 10g to 9i
                            NPD
                            Mohammed,

                            exp with version of dd

                            exp system/manager version=10.2.0.1.0 owner='production' file='/oracle10/production.dmp' l og='/oracle10/production.log'


                            On the import do I still need to add version? if yes does it have to be 10g version or 9i

                            imp system/manager fromuser='production' touser='test' file='/oracle9/production.dmp' log='/oracle9/production.log

                            Thank you.
                            • 11. Re: Exp / imp schema from 10g to 9i
                              Nicolas.Gasparotto
                              NPD wrote:
                              Mohammed,

                              exp with version of dd

                              exp system/manager version=10.2.0.1.0 owner='production' file='/oracle10/production.dmp' l og='/oracle10/production.log'


                              On the import do I still need to add version? if yes does it have to be 10g version or 9i

                              imp system/manager fromuser='production' touser='test' file='/oracle9/production.dmp' log='/oracle9/production.log

                              Thank you.
                              That is wrong , there is no VERSION option within exp utility (type "exp help=yes" on OS prompt and check the available options). Use the export utility from your 9i oracle home, that's it.

                              Nicolas.
                              • 12. Re: Exp / imp schema from 10g to 9i
                                NPD
                                they are on different servers
                                • 13. Re: Exp / imp schema from 10g to 9i
                                  781937
                                  For that only we have posted to use tns entry.

                                  simple to follow

                                  1) set the path of oracle 9i home path
                                  2) export using tnsentry of the 10g database.
                                  3) import the dumpfile in 9i database

                                  Task done

                                  Edited by: Ramesh on Aug 16, 2010 1:28 PM
                                  • 14. Re: Exp / imp schema from 10g to 9i
                                    791023
                                    hi,

                                    If dbs are on different server:
                                    1. Login in 9i db server
                                    2. Make a entry in tnsnames.ora for 10g db in 9i db home.
                                    3. Export using given below command

                                    exp system@10gdb file=exp_file.dmp log=exp_log.log (other parameters you want table level or schema level)

                                    4. Import this exp_file.dmp in 9i db

                                    imp system file=exp_file.dmp log=imp_log.log

                                    Regards
                                    Rajinder Kumar
                                    1 2 3 Previous Next