Home » RDBMS Server » Server Utilities » Cancel impdp before import finishes (Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.12.0.0.0 ( AWS RDS ) )
Cancel impdp before import finishes [message #684959] Sun, 03 October 2021 01:00 Go to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Hi all,
I have exported multiple schemas using oracle expdp and now importing with impdp.
It seems stuck on "Processing object type SCHEMA_EXPORT/STATISTICS/MARKER" and looks like I am hitting one of the variations of "Data pump Import Job Is Hanging On STATISTICS/MARKER (Doc ID 2135702.1)"

My impdp looks like this

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/VIEW/TRIGGER
Processing object type SCHEMA_EXPORT/EVENT/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Now, I am familiar with the workaround of excluding the statistics now, but I am already many hours deep and 99.9% done:

SQL> col units for a12
SQL> col message for a25
SQL> set num 15
SQL> col start_time for a30
SQL> col DONE for a10
SQL> set lines 300 pages 400
SQL> --
SQL> select
  2  sid,
  3  message,
  4  sofar,
  5  totalwork,
  6  substr((sofar/totalwork * 100),1,5)||'%' DONE,
  7   units,start_time,elapsed_seconds
  8  from gv$session_longops
  9  where sofar!=totalwork
 10  and totalwork != 0 ;

            SID MESSAGE                             SOFAR       TOTALWORK DONE       UNITS        START_TIME                     ELAPSED_SECONDS
--------------- ------------------------- --------------- --------------- ---------- ------------ ------------------------------ ---------------
            674 SYS_IMPORT_SCHEMA_01: IMP          124877          124878 99.99%     MB           02.10.2021 20:56:58                      35884
                ORT : 124877 out of 12487
                8 MB done

Question(s):

1. Is there a way to "skip" or "soft abort" this step after it has already begun ( and stuck) ?
2. If I kill the import job at this point, will it be equivalent to me not just excluding statistics for the import? or there are more severe implications around the area ?

I am asking because re-starting a multiple-hour-long import is a lot of extended unplanned downtime.


Many thanks in advance,
Andrey
Re: Cancel impdp before import finishes [message #684960 is a reply to message #684959] Sun, 03 October 2021 02:35 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I just did a little test, interrupting the import at that stage, and there were no adverse effects that I could see. So I think you could take a chance, if it really is urgent. In the meantime, how about raising a TAR? You could reasonably request P1, it is potentially a "production system down" issue.
Sorry I can't give any definitive advice.
Re: Cancel impdp before import finishes [message #684961 is a reply to message #684959] Sun, 03 October 2021 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

McDP can help you in this task.
See especially this post: you can kill the worker with SKIP option to skip the current step and execute the next ones.

Re: Cancel impdp before import finishes [message #684964 is a reply to message #684961] Sun, 03 October 2021 11:40 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Thank you both.

The impdp finished just when I started considering some panic measures.
The McDP is an interesting tool - who is the vendor of it ?


Re: Cancel impdp before import finishes [message #684965 is a reply to message #684964] Sun, 03 October 2021 12:48 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Me. Proud
It is a freeware, you can download it at the bottom of our Datapump wiki page.

The forum page I pointed you to is quite long to read but it is worth for a DBA.
I maintain it at each Oracle version including new options/transforms and adding new commands.
I have quite a lot of stuff in my TODO list but it is not the only tool I develop or maintain.

Previous Topic: ORA-31694: master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" failed to load/unload
Next Topic: Update multiple table and rows in one go
Goto Forum:
  


Current Time: Thu Mar 28 14:59:45 CDT 2024