Resumable operations are operations that can be suspended instead of failing, allowing us to fix the problem and resume the operation. When importing data using impdp, if (for example) a tablespace gets full, instead of failing the entire import (and force us to restart it from the beginning), we can identify that and add some space to the tablespace while the import is waiting.
The resumable operations feature is managed by the RESUMABLE_TIMEOUT parameter. In an import I executed, I got the “ORA-39171: Job is experiencing a resumable wait” error, while my RESUMABLE_TIMEOUT parameter was 0 (that means that the feature is turned off).
In this case I preferred the import to fail and not wait (because it was running overnight, and it would fail anyway because I wasn’t connected to fix the problem on time). But this is where things started to get strange.
First, I couldn’t find anywhere a parameter to change the resumable option in impdp. I couldn’t find anything in the impdp documentation or in the output of “impdp help=y”. So I did what I had to do and turned on SQL tracing. Then I found this in the trace file:
ALTER SESSION ENABLE RESUMABLE NAME ‘SYS.SYS_IMPORT_FULL_01’
This means that the impdp itself turns the resumable feature on. OK, but what is the timeout? I checked the “alter session” documentation chapter where they show that you can configure the timeout or not, but wouldn’t say the default. So in the end I found it in the resumable operation chapter where they say that the default is 2 hours.
What’s left now is to understand how to control it, and according to my research, we can’t. There are “resumable” and “resumable_timeout” parameters, but they exist to support a legacy mode and are being ignored. So I tried to change my resumable_timeout parameter in the database to a value of 1. According to Oracle, impdp should take this value, but apparently it doesn’t and DBA_RESUMABLE showed me that the timeout for the import is still 7200 seconds.
The only thing I can think of is logon trigger, and I really don’t want to go there…
By the way, this is 11.2.0.4 database. If you have any idea about that, please add a comment to this post.
Impdp Resumable Mystery
Categories:
Resumable_timeout Cannot be Set Beyond 2 Hours With Data Pump (Doc ID 871885.1)
If resumable_timeout parameter is changed, for example in init.ora and the instance is bounced, the data pump will continue having a value of 2 hours which is hard coded.
If the parameter is changed dynamically, with ‘alter system’ command, while no data pump export or import job is running, the next data pump operation will not take the new value set, but still the hard code value of 2 hours.
Only if the value of resumable_timeout is changed dynamically while the data pump operation is currently running, data pump will take the new value, but the rest of data pump jobs started after
the change will have again the hard coded value of 2 hours.
Thanks John,
That does look like what I’ve seen. However, I don’t agree with this approach. I think it should take the parameter value, or have a command line parameter for impdp.
Liron