7 Replies Latest reply: Nov 30, 2012 6:29 PM by 977281 RSS

    IMPDP stuck at 99%

    977281
      I have two Data Pump imports running that have been at 99% now for over an hour. Everything looks good, but they simply are not finishing the job. The row count has not changed in more than an hour now. No errors or trace generation in the alert log. Everything has plenty of space including UNDO and TEMP. I can, and will, open an SR, but I cant find anything in the forums or the Metalink Notes about this type of situation so throwing it to the community at large. This import completed the first table without an issue.

      Job: SYS_IMPORT_TABLE_01
      Owner: SYSTEM
      Operation: IMPORT
      Creator Privs: TRUE
      GUID: CFB5FB7B039B32FAE044001B21818314
      Start Time: Friday, 30 November, 2012 11:48:48
      Mode: TABLE
      Instance: rnrcp21
      Max Parallelism: 1
      EXPORT Job Parameters:
      CLIENT_COMMAND SERVICE_ER/******** parfile=exp_data010.par
      ESTIMATE STATISTICS
      FLASHBACK_SCN INCLUDE_METADATA 0
      IMPORT Job Parameters:
      Parameter Name Parameter Value:
      CLIENT_COMMAND system/******** parfile=imp010_8.par
      State: EXECUTING
      Bytes Processed: 20,576,810,208
      Percent Done: 2
      Current Parallelism: 1
      Job Error Count: 0
      Dump File: /ggdata/load/dp_data_010e%u.dmp
      Dump File: /ggdata/load/dp_data_010e01.dmp
      Dump File: /ggdata/load/dp_data_010e02.dmp
      Dump File: /ggdata/load/dp_data_010e03.dmp
      Dump File: /ggdata/load/dp_data_010e04.dmp
      Dump File: /ggdata/load/dp_data_010e05.dmp
      Dump File: /ggdata/load/dp_data_010e06.dmp

      Worker 1 Status:
      Process Name: DW00
      State: EXECUTING
      Object Schema: ORDER1
      Object Name: OO_LINE_SHIPMENT
      Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
      Completed Objects: 2
      Completed Rows: 136,395,047
      Completed Bytes: 18,577,233,800
      Percent Done: 99
      Worker Parallelism: 1
        • 1. Re: IMPDP stuck at 99%
          User286067
          most likely it is creating indexes, enabling constraints etc.
          • 2. Re: IMPDP stuck at 99%
            Dean Gagne-Oracle
            You can type a CTRL-c and you will get to an:

            Import>

            prompt. At this prompt, you can type status to see what is being done. Maybe it is an index, maybe it is something else. Look at the worker status rows to see what the workers are doing. Typing the ctrl-c will not stop the job. It just stops printing the status to the x-term. You can get back to the regular window by typing continue.

            Hope this helps.

            Dean
            • 3. Re: IMPDP stuck at 99%
              977281
              Forgot to mention this is a DATA_ONLY load so there are no indexes or constraints. If it were on those though the status should display it.
              • 4. Re: IMPDP stuck at 99%
                Dean Gagne-Oracle
                You can also run this query to see what data is left to import:

                select sum(dump_orig_length), processing_state
                from "SYSTEM"."SYS_IMPORT_FULL_01“ -- < your master table /job name goes here
                where process_order > 0 and duplicate = 0 and
                object_type = 'TABLE_DATA‘
                group by processing_state;

                SUM(DUMP_ORIG_LENGTH) P
                --------------------- -
                13408128 W -- already imported
                2525400 R -- to be imported
                24944 X -- excluded

                Rows with the 'W' state have already been imported.
                Rows with the 'R' state still need to be imported.
                Rows with the 'X' state have been excluded from the job (through filters and other things)

                Hope this helps.

                Dean
                • 5. Re: IMPDP stuck at 99%
                  User286067
                  is it waiting on space transaction due to no space left ? i doubt if it will wait 3+ hours but you never know.
                  • 6. Re: IMPDP stuck at 99%
                    khallas301
                    try tracing when it's 99% and see what's happening behind..

                    -- Syntax: DBMS_SYSTEM.SET_EV([SID],[SERIAL#],[EVENT],[LEVEL],'')

                    [http://dbasolutions.wikispaces.com/Expdp+-+Trace]

                    set lines 150 pages 100 numwidth 7
                    col program for a38
                    col username for a10
                    col spid for a7

                    select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid,
                    s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
                    from v$session s, v$process p, dba_datapump_sessions d
                    where p.addr=s.paddr and s.saddr=d.saddr;

                    Edited by: khallas301 on Nov 30, 2012 1:56 PM
                    • 7. Re: IMPDP stuck at 99%
                      977281
                      Thank you for the ideas. I did use a level 8 trace only to find that although I was doing a DATA_ONLY load the index objects tied to the tables rebuilt before the impdp process would 'let go' and complete. So while the data load itself did not call to rebuild/instantiate the other objects, because they existed they had to complete. It is running slow, but it is running.