4 Replies Latest reply on May 16, 2017 7:36 PM by User594210-OC

    re running impdp on failed import

    GlenStrom

      Database: 12.1.0.2 on Solaris.

       

      We have a new server set up and I am creating some databases & populating them from development databases on the old server. Because space is limited on the source server I thought I would try an import over dblink to avoid creating a large dumpfile on the source server.

       

      I extracted create tablespace DDL from the source database and pre-created all of the tablespaces. Then I was going to pre-create all of the users (my usual practice) and thought first I would try just importing a single schema across the network link and see if it worked without pre creating the user, which it did just fine. 

       

      I was then going to run the impdp using the exclude schema: in (system, sysaux, etc..) to exclude non user schemas but I could not get that to work. There are hundreds of users to bring across so I didn't want to use the "schemas="  clause either.

       

      So since bringing one schema across worked fine, I decided to just use the impdp over dblink with the full=y option. First off I got a bunch of errors from the source tables that had long datatypes, then this error & the import aborted:

       

      ORA-39126: Worker unexpected fatal error in KUPW$WORKER.STATS_LOAD [MARKER]

      MARKER

      ORA-30926: unable to get a stable set of rows in the source tables

       

      ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105

      ORA-06512: at "SYS.KUPW$WORKER", line 11265

       

      ----- PL/SQL Call Stack -----

        object      line  object

        handle    number  name

      3dfa59780     27116  package body SYS.KUPW$WORKER

      3dfa59780     11286  package body SYS.KUPW$WORKER

      3dfa59780     24286  package body SYS.KUPW$WORKER

      3dfa59780     24415  package body SYS.KUPW$WORKER

      3dfa59780     20692  package body SYS.KUPW$WORKER

      3dfa59780     10206  package body SYS.KUPW$WORKER

      3dfa59780     13381  package body SYS.KUPW$WORKER

      3dfa59780      3173  package body SYS.KUPW$WORKER

      3dfa59780     12035  package body SYS.KUPW$WORKER

      3dfa59780      2081  package body SYS.KUPW$WORKER

      3cdcd7758         2  anonymous block

       

      DBMS_STATS.REMAP_STAT_TABLE

      DBMS_STATS.REMAP_STAT_TABLE

      DBMS_STATS.REMAP_STAT_TABLE

      DBMS_STATS.REMAP_STAT_TABLE

      DBMS_STATS.REMAP_STAT_TABLE

      ORA-39126: Worker unexpected fatal error in KUPW$WORKER.LOOK_FOR_OBJECT [MARKER]

      SELECT process_order, duplicate, completion_time, in_progress FROM "SYSTEM"."SYS_IMPORT_FULL_01" WHERE  partition_name IS NULL AND subpartition_name IS            NULL AND object_type = :1 AND object_schema IS NULL AND :2 IS NULL AND object_name IS NULL AND :3 IS NULL AND object_long_name IS NULL AND :4 IS NULL AN           D base_object_type IS NULL AND :5 IS NULL AND base_object_schema IS NULL AND :6 IS NULL AND base_object_name IS NULL AND :7 IS NULL AND grantor IS NULL            AND :8 IS NULL

      ORA-01422: exact fetch returns more than requested number of rows

       

      ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105

      ORA-06512: at "SYS.KUPW$WORKER", line 11265

       

      ----- PL/SQL Call Stack -----

        object      line  object

        handle    number  name

      3dfa59780     27116  package body SYS.KUPW$WORKER

      3dfa59780     11286  package body SYS.KUPW$WORKER

      3dfa59780     18542  package body SYS.KUPW$WORKER

      3dfa59780     15758  package body SYS.KUPW$WORKER

      3dfa59780      9645  package body SYS.KUPW$WORKER

      3dfa59780     13381  package body SYS.KUPW$WORKER

      3dfa59780      3173  package body SYS.KUPW$WORKER

      3dfa59780     12035  package body SYS.KUPW$WORKER

      3dfa59780      2081  package body SYS.KUPW$WORKER

      3cdcd7758         2  anonymous block

       

      Tables being remapped looking for

      Tables being remapped looking for

      Tables being remapped looking for

      Tables being remapped looking for

      Tables being remapped looking for

      Tables being remapped looking for

      Tables being remapped looking for

      In procedure BUILD_OBJECT_STRINGS

      In function LOOK_FOR_OBJECT

      In procedure DETERMINE_FATAL_ERROR with ORA-01422: exact fetch returns more than requested number of rows

      Job "SYSTEM"."SYS_IMPORT_FULL_01" stopped due to fatal error at Mon Apr 24 20:09:11 2017 elapsed 0 04:31:28

       

      There was about 10 tables the import failed on because of the long datatypes, so rather than export just those tables to a dumpfile and re importing them I thought I'd try the SQL Developer database copy feature - which (to my surprise, since I thought it probably used impdp) worked flawlessly and fast to copy the tables from the source to the target database.

       

      All my users were created, and it looks like all the users objects are now all across.

       

      But since the import aborted, I am now wondering if I need to re run the import now to make sure the target is "all there"?

       

      I am more just curious about this at this point, since I am going to probably at some point redo the entire import using a dumpfile this time, because I like to have a nice clean procedure with as few steps as possible ready to go when it comes time to do the real switch over.

       

      Thanks in advance.

       

       

        

        • 1. Re: re running impdp on failed import
          jr-phDBA

          Check Hitting ORA-30926 During DataPump Imprort Job When Processing The Statistics (Doc ID 2162936.1)

          • 2. Re: re running impdp on failed import
            MsJ

             

            ORA-39126: Worker unexpected fatal error in KUPW$WORKER.STATS_LOAD [MARKER]

            MARKER

            ORA-30926: unable to get a stable set of rows in the source tables

             

            ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105

            ORA-06512: at "SYS.KUPW$WORKER", line 11265

             

            ..

            SELECT process_order, duplicate, completion_time, in_progress FROM "SYSTEM"."SYS_IMPORT_FULL_01" WHERE partition_name IS NULL AND subpartition_name IS NULL AND object_type = :1 AND object_schema IS NULL AND :2 IS NULL AND object_name IS NULL AND :3 IS NULL AND object_long_name IS NULL AND :4 IS NULL AN D base_object_type IS NULL AND :5 IS NULL AND base_object_schema IS NULL AND :6 IS NULL AND base_object_name IS NULL AND :7 IS NULL AND grantor IS NULL AND :8 IS NULL

            ORA-01422: exact fetch returns more than requested number of rows

             

            ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105

            ORA-06512: at "SYS.KUPW$WORKER", line 11265

             

            ----- PL/SQL Call Stack -----

            object line object

            handle number name

            3dfa59780 27116 package body SYS.KUPW$WORKER

             

            Tables being remapped looking for

            In procedure BUILD_OBJECT_STRINGS

            In function LOOK_FOR_OBJECT

            In procedure DETERMINE_FATAL_ERROR with ORA-01422: exact fetch returns more than requested number of rows

            Job "SYSTEM"."SYS_IMPORT_FULL_01" stopped due to fatal error at Mon Apr 24 20:09:11 2017 elapsed 0 04:31:28

             

             

             

            Errors ORA-39126 ORA-1422 Raised When Using DataPump Import (IMPDP) (Doc ID 403950.1)

            1 person found this helpful
            • 3. Re: re running impdp on failed import
              Dean Gagne-Oracle

              You could restart the import job.  There is a keyword I can't remember right now that says to skip the current object, which would be all of your statistics.  You could just rebuild/gather statistics after the job is done.

               

              When you restart a job, it is done like this:

               

              impdp user/password job_name=job_name_of_failed_job

               

              This brings you to a prompt like:

               

              IMPDP>

               

              You can type help and it should give you the available commands.  One of the is like:

               

              start=skip_current

               

              or something like that.  This will skip the current object that you were on.  In your case, it would be the statistic.

               

              Hope this helps.

               

              Dean

              1 person found this helpful
              • 4. Re: re running impdp on failed import
                User594210-OC

                I received this error too and was able to re-run the import using exclude=table_statistics without errors.

                 

                Ron