6 Replies Latest reply: May 1, 2012 8:34 AM by User286067 RSS

    Import seems to be stuck

    user3636719
      I am doing schema level export, using below command
      nohup expdp system/password@LTRN SCHEMAS=CONTRACT dumpfile= DATA_PUMP1:ltrn_srm_%u.dmp, DATA_PUMP2:ltrn_srm_%u.dmp, DATA_PUMP3:ltrn_srm_03.dmp, DATA_PUMP4:ltrn_srm_%u.dmp logfile = expdp_ltrn_srm.log PARALLEL=9 &
      And importing using below command
      nohup impdp system/password@LTORN SCHEMAS= CONTRACT dumpfile= DATA_PUMP3:ltrn_srm_01.dmp, DATA_PUMP3:ltrn_srm_02.dmp, DATA_PUMP3:ltrn_srm_03.dmp, DATA_PUMP4:ltrn_srm_04.dmp DATA_PUMP4:ltrn_srm_05.dmp,DATA_PUMP4:ltrn_srm_06.dmp,DATA_PUMP4:ltrn_srm_07.dmp, DATA_PUMP4:ltrn_srm_08.dmp, DATA_PUMP4:ltrn_srm_09.dmp logfile = imp_ltorn_srm.log PARALLEL=9 TABLE_EXISTS_ACTION=REPLACE &
      Import seems to be stuck, I started import 5:30AM in the morning and it’s still running and I don’t see growth in tablespace.

      I have plenty of space on tablespace level. Right now import seems to be on index level I have 10Gig space available.

      So I did below to see the status
      /oracle/data_02/tjiveapp> impdp system/LTORN_dragon1b@LTORN attach='SYS_IMPORT_SCHEMA_02'
      
      Import: Release 11.2.0.2.0 - Production on Mon Apr 30 12:28:11 2012
      
      Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
      
      Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      
      Job: SYS_IMPORT_SCHEMA_02
        Owner: SYSTEM
        Operation: IMPORT
        Creator Privs: TRUE
        GUID: BEE32F002D4302A6E0430A2A46A702A6
        Start Time: Monday, 30 April, 2012 5:36:12
        Mode: SCHEMA
        Instance: LTORN
        Max Parallelism: 9
        EXPORT Job Parameters:
           CLIENT_COMMAND        system/********@LTRN SCHEMAS=CONTRACT dumpfile= DATA_PUMP1:ltrn_srm_%u.dmp,DATA_PUMP2:ltrn_srm_%u.dmp, DATA_PUMP3:ltrn_srm_%u.dmp,DATA_PUMP4:ltrn_srm_%u.dmp FILESIZE= 10G logfile = DATA_PUMP1:imp_ditsm_srm.log PARALLEL=8
        IMPORT Job Parameters:
        Parameter Name      Parameter Value:
           CLIENT_COMMAND        system/********@LTORN SCHEMAS=CONTRACT dumpfile= dumpfile DATA_PUMP3:ltrn_srm_03.dmp, dumpfile DATA_PUMP4:ltrn_srm_05.dmp,DATA_PUMP4:ltrn_srm_06.dmp,DATA_PUMP4:ltrn_srm_07.dmp, dumpfile DATA_PUMP4:ltrn_srm_09.dmp logfile=imp_ltrn_srm.log PARALLEL=9 TABLE_EXISTS_ACTION=REPLACE
           TABLE_EXISTS_ACTION   REPLACE
        State: EXECUTING
        Bytes Processed: 45,432,497,576
        Percent Done: 99
        Current Parallelism: 9
        Job Error Count: 0
        Dump File: /oracle/data_02/tjiveapp/ltrn_srm_01.dmp
        Dump File: /oracle/data_01/tjiveapp/ltrn_srm_04.dmp
        Dump File: /oracle/data_01/tjiveapp/ltrn_srm_08.dmp
        Dump File: /oracle/data_01/tjiveapp/ltrn_srm_06.dmp
        Dump File: /oracle/data_02/tjiveapp/ltrn_srm_02.dmp
        Dump File: /oracle/data_01/tjiveapp/ltrn_srm_05.dmp
        Dump File: /oracle/data_01/tjiveapp/ltrn_srm_09.dmp
        Dump File: /oracle/data_01/tjiveapp/ltrn_srm_07.dmp
        Dump File: /oracle/data_02/tjiveapp/ltrn_srm_03.dmp
      
      Worker 1 Status:
        Process Name: DW00
        State: EXECUTING
        Object Schema: CONTRACT
        Object Name: ACTIVITYM12CCFC172
        Object Type: SCHEMA_EXPORT/TABLE/INDEX/INDEX
        Completed Objects: 7
        Worker Parallelism: 2
      
      Worker 2 Status:
        Process Name: DW01
        State: WORK WAITING
      
      Worker 3 Status:
        Process Name: DW02
        State: WORK WAITING
      
      Worker 4 Status:
        Process Name: DW03
        State: WORK WAITING
      
      Worker 5 Status:
        Process Name: DW04
        State: WORK WAITING
      
      Worker 6 Status:
        Process Name: DW05
        State: WORK WAITING
      
      Worker 7 Status:
        Process Name: DW06
        State: WORK WAITING
      
      Worker 8 Status:
        Process Name: DW07
        State: WORK WAITING
      
      Worker 9 Status:
        Process Name: DW08
        State: WORK WAITING
      
      Import> status
      
      Job: SYS_IMPORT_SCHEMA_02
        Operation: IMPORT
        Mode: SCHEMA
        State: EXECUTING
        Bytes Processed: 45,432,497,576
        Percent Done: 99
        Current Parallelism: 9
        Job Error Count: 0
        Dump File: /oracle/data_02/tjiveapp/ltrn_srm_01.dmp
        Dump File: /oracle/data_01/tjiveapp/ltrn_srm_04.dmp
        Dump File: /oracle/data_01/tjiveapp/ltrn_srm_08.dmp
        Dump File: /oracle/data_01/tjiveapp/ltrn_srm_06.dmp
        Dump File: /oracle/data_02/tjiveapp/ltrn_srm_02.dmp
        Dump File: /oracle/data_01/tjiveapp/ltrn_srm_05.dmp
        Dump File: /oracle/data_01/tjiveapp/ltrn_srm_09.dmp
        Dump File: /oracle/data_01/tjiveapp/ltrn_srm_07.dmp
        Dump File: /oracle/data_02/tjiveapp/ltrn_srm_03.dmp
      
      Worker 1 Status:
        Process Name: DW00
        State: EXECUTING
        Object Schema: CONTRACT
        Object Name: ACTIVITYM12CCFC172
        Object Type: SCHEMA_EXPORT/TABLE/INDEX/INDEX
        Completed Objects: 7
        Worker Parallelism: 2
      
      Worker 2 Status:
        Process Name: DW01
        State: WORK WAITING
      
      Worker 3 Status:
        Process Name: DW02
        State: WORK WAITING
      
      Worker 4 Status:
        Process Name: DW03
        State: WORK WAITING
      
      Worker 5 Status:
        Process Name: DW04
        State: WORK WAITING
      
      Worker 6 Status:
        Process Name: DW05
        State: WORK WAITING
      
      Worker 7 Status:
        Process Name: DW06
        State: WORK WAITING
      
      Worker 8 Status:
        Process Name: DW07
        State: WORK WAITING
      
      Worker 9 Status:
        Process Name: DW08
        State: WORK WAITING
      This is after 30 minutes
      Import> status
      
      Job: SYS_IMPORT_SCHEMA_02
        Operation: IMPORT
        Mode: SCHEMA
        State: EXECUTING
        Bytes Processed: 45,432,497,576
        Percent Done: 99
        Current Parallelism: 9
        Job Error Count: 0
        Dump File: /oracle/data_02/tjiveapp/ltrn_srm_01.dmp
        Dump File: /oracle/data_01/tjiveapp/ltrn_srm_04.dmp
        Dump File: /oracle/data_01/tjiveapp/ltrn_srm_08.dmp
        Dump File: /oracle/data_01/tjiveapp/ltrn_srm_06.dmp
        Dump File: /oracle/data_02/tjiveapp/ltrn_srm_02.dmp
        Dump File: /oracle/data_01/tjiveapp/ltrn_srm_05.dmp
        Dump File: /oracle/data_01/tjiveapp/ltrn_srm_09.dmp
        Dump File: /oracle/data_01/tjiveapp/ltrn_srm_07.dmp
        Dump File: /oracle/data_02/tjiveapp/ltrn_srm_03.dmp
      
      Worker 1 Status:
        Process Name: DW00
        State: EXECUTING
        Object Schema: CONTRACT
        Object Name: ACTIVITYM12CCFC172
        Object Type: SCHEMA_EXPORT/TABLE/INDEX/INDEX
        Completed Objects: 8
        Worker Parallelism: 1
      
      Worker 2 Status:
        Process Name: DW01
        State: WORK WAITING
      
      Worker 3 Status:
        Process Name: DW02
        State: WORK WAITING
      
      Worker 4 Status:
        Process Name: DW03
        State: WORK WAITING
      
      Worker 5 Status:
        Process Name: DW04
        State: WORK WAITING
      
      Worker 6 Status:
        Process Name: DW05
        State: WORK WAITING
      
      Worker 7 Status:
        Process Name: DW06
        State: WORK WAITING
      
      Worker 8 Status:
        Process Name: DW07
        State: WORK WAITING
      
      Worker 9 Status:
        Process Name: DW08
        State: WORK WAITING
      
      Import>
      Is this mean import stuck, what can do to move it forward.


      Source version
      Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
      PL/SQL Release 10.2.0.5.0 - Production
      "CORE     10.2.0.5.0     Production"
      TNS for HPUX: Version 10.2.0.5.0 - Production
      NLSRTL Version 10.2.0.5.0 - Production
      Destination version
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      PL/SQL Release 11.2.0.2.0 - Production
      "CORE     11.2.0.2.0     Production"
      TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
      NLSRTL Version 11.2.0.2.0 - Production
        • 1. Re: Import seems to be stuck
          sb92075
          other session can NOT see uncommitted data.

          what do you see when doing repeated invocations of SQL below

          SELECT * FROM V$SESS_IO;

          is one session doing lots & more I/O between each SELECT?
          • 2. Re: Import seems to be stuck
            user3636719
            Here is the result for
            SELECT * FROM V$SESS_IO;
            *SID     BLOCK_GETS     CONSISTENT_GETS     PHYSICAL_READS     BLOCK_CHANGES     CONSISTENT_CHANGES     OPTIMIZED_PHYSICAL_READS*
            3     1014411     119617     4     304268     2     0
            13     78     2430     161     7     7     0
            16     668721     125594     741     186940     312     0
            25     0     0     0     0     0     0
            26     1166     3615     83     1492     82     0
            27     957328     106357     166     131480     4     0
            37     71492     492020     55     78557     0     0
            38     0     0     0     0     0     0
            39     891310     128361     178     146550     6     0
            49     0     0     0     0     0     0
            50     0     141062     0     2     0     0
            61     0     0     0     0     0     0
            62     33363     106101969     104595072     36078     2     0
            73     0     0     0     0     0     0
            74     153     164472     21449     171     0     0
            85     0     373     21     0     0     0
            87     1171882     83770322     59023777     1036918     25     0
            97     0     0     0     0     0     0
            98     0     139176     1     2     0     0
            109     0     0     0     0     0     0
            110     169     168557     21442     178     0     0
            121     0     0     0     0     0     0
            122     33931     1137213     7253     11198     200     0
            133     0     0     0     0     0     0
            134     725     197985     418     709     20     0
            145     0     0     0     0     0     0
            146     47465     252570     7     52143     4     0
            157     165713     294840     35875     136756     4     0
            158     0     28224     0     2     0     0
            169     0     311     3     0     0     0
            170     0     0     0     0     0     0
            181     35611     167062     594     2858     1     0
            183     0     0     0     0     0     0
            193     4948     276622     239     5513     42     0
            194     0     0     0     0     0     0
            205     880     2640     0     0     0     0
            207     12034     76952     524     6682     11     0
            217     0     139176     0     2     0     0
            218     0     19     0     0     0     0
            229     431933     1503127     344     462331     3     0
            230     73     77     2     87     0     0
            241     1511160     130012     14     333250     2     0
            242     0     0     0     0     0     0
            253     208     14736487     13927569     210     4     0
            254     97048     43264     7     115793     0     0
            265     25683     409124     102     27680     3     0
            266     552     84103     67     589     22     0
            277     442483     32336062     1161     418215     26     0
            279     0     135     0     0     0     0
            289     306620     69924119     61541125     338269     2     0
            290     8809167     3098214     1378491     5628540     40564     0
            292     0     31     0     0     0     0
            301     0     6     0     0     0     0
            303     1116146     100348     14     188435     2     0
            313     156     163996     21514     152     0     0
            314     767269     83302     6     135732     2     0
            325     188     171620     21467     192     1     0
            326     911864     73869     11     160368     2     0
            after sometime
            *SID     BLOCK_GETS     CONSISTENT_GETS     PHYSICAL_READS     BLOCK_CHANGES     CONSISTENT_CHANGES     OPTIMIZED_PHYSICAL_READS*
            1     0     35     0     0     0     0
            3     1014411     122109     4     304268     2     0
            13     78     2459     161     7     7     0
            16     668721     126218     741     186940     312     0
            25     0     0     0     0     0     0
            26     1258     3837     87     1598     82     0
            27     957328     108853     166     131480     4     0
            37     71754     536426     57     78816     0     0
            38     0     0     0     0     0     0
            39     891310     130857     178     146550     6     0
            49     0     0     0     0     0     0
            50     0     154218     0     2     0     0
            61     0     0     0     0     0     0
            62     33435     114145661     112089855     36151     2     0
            73     0     0     0     0     0     0
            74     165     179347     21449     183     0     0
            85     0     373     21     0     0     0
            87     1173120     88358730     63456906     1038149     25     0
            97     0     0     0     0     0     0
            98     0     152447     2     2     0     0
            109     0     0     0     0     0     0
            110     169     181713     21442     178     0     0
            121     0     0     0     0     0     0
            122     34242     1148303     7442     11307     205     0
            133     0     0     0     0     0     0
            134     761     200669     442     745     20     0
            145     0     0     0     0     0     0
            146     47627     274580     7     52304     4     0
            157     169157     299410     37398     140254     11     0
            158     0     31007     0     2     0     0
            169     0     313     3     0     0     0
            170     0     0     0     0     0     0
            181     36055     171700     647     2990     1     0
            183     0     0     0     0     0     0
            193     4948     279118     239     5513     42     0
            194     0     0     0     0     0     0
            205     889     2667     0     0     0     0
            207     12034     78473     528     6682     11     0
            217     0     152332     0     2     0     0
            218     0     19     0     0     0     0
            229     432789     1622901     362     463181     3     0
            230     73     77     2     87     0     0
            241     1511160     132504     17     333250     2     0
            242     0     0     0     0     0     0
            253     282     18386347     17067355     282     4     0
            254     97053     43270     7     115799     0     0
            265     25783     445327     103     27780     3     0
            277     442483     32349218     1161     418215     26     0
            289     307687     74731437     65756172     339312     2     0
            290     8818509     3098214     1378493     5628540     40564     0
            301     0     6     0     0     0     0
            303     1116146     102848     14     188435     2     0
            313     174     178872     21514     169     0     0
            314     767269     85802     6     135732     2     0
            325     201     186555     21467     205     1     0
            326     911864     76369     11     160368     2     0
            • 3. Re: Import seems to be stuck
              user3636719
              Now can I do below to EXCLUDE indexs
              Import> EXCLUDE=INDEX
              • 4. Re: Import seems to be stuck
                krramnik
                First check the alert log for errors. That will throw some light.

                There are bugs reported in impdp with PARALLEL >1 . Check the following metalnk Id 1221495.1

                the import should probably work with PARALLEL=1
                • 5. Re: Import seems to be stuck
                  krramnik
                  check this link too

                  http://www.dba-oracle.com/t_export_import_different_releases_of_oracle.htm
                  • 6. Re: Import seems to be stuck
                    User286067
                    Rather than status, what does the logfile says?