6 Replies Latest reply: Oct 9, 2012 1:53 PM by orausern RSS

    Import handing since several hours

    orausern
      Hi,
      I am facing an issue while copying a schema using import. The source schema is on Oracle 11.2.0.2 and the target is on 11.2.0.1. So I took export using 11.2.0.2 and also doing importing using 11.2.0.2 version of import utility (to avoid version related issues). The schema dump file is just 150 mb. The target db server is in different country than the source db server and I added tns-entry of the target db and I am running import from source database using tns entry

      like:
      imp <system/pwd@targetdb> fromuser=db1 touser=db1 file=db1.dmp log=db1_imp.log
      However the import is hanging since more than 4 hours. There is a table with about 450k rows and the import is stuck on that table since then. Must I run impdp and kill this imp session or is there something else that can help?

      Thanks,

      Edited by: orausern on Oct 9, 2012 5:49 AM
        • 1. Re: Import handing since several hours
          964511
          Hi..
          Please copy the dumpfile to target server and start import form target server, it will be much faster
          • 2. Re: Import handing since several hours
            Fran
            select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
            rows_processed,
            round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
            trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
            from sys.v_$sqlarea
            where sql_text like 'INSERT %INTO "%'
            and command_type = 2
            and open_versions > 0;

            if you get 0 rows, the import is stop, you should stop it and try with data pump.

            Also, you are using version 11.2.0.1, you must use 11.2.0.3, less bugs..
            • 3. Re: Import handing since several hours
              orausern
              Hi Fran,

              I got 0 rows as output of your query so I am going ahead with the data pump solution. Now the issue is the source and target db are at different versions -source is at 11.2.0.2 and target is at 11.2.0.1. So do I try to get it done using network_link, will that work? Are there some ways to tune the import session where the import is being done in different a server in a different country than the source server so a lots of network is involved?

              Thanks,
              • 4. Re: Import handing since several hours
                Fran
                You can use data pump in different databases and different versions:
                http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_overview.htm#CEGFCFFI

                just remember to user VERSION parameter when you run the expdp:
                http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_export.htm

                when the expdp finish just move the dumpfile to the server where the target database is and run the impdp.
                • 5. Re: Import handing since several hours
                  EmaxG
                  You should also check there are no triggers being fired while you do the import which might corrupt/generate innecesary data.
                  • 6. Re: Import handing since several hours
                    orausern
                    Awesome help!!! My issue got resolved once I tried with expdp. Thanks a LOT experts!!!!