Impdp Resumable Mystery

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.

Advertisements

2 thoughts on “Impdp Resumable Mystery

  1. 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.

    Like

    • 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

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s