Hello fellas,

Its been quite a while that I am off blogging.

Today’s article is nothing great but felt sharing as it may help others.

This article is regarding issue with unstoppable undo generation while performing impdp.

The request is to import a table on the test environment.

You startup with the import activity a simple impdp command as below.

impdp directory=EXPDP dumpfile=DW_RETL_PRD_CAT_VAL_US_24Jan.dmp logfile=DW_RETL_PRD_CAT_VAL_US_07Feb_impdp.log tables=DSS.DW_RETL_PRD_CAT_VAL_US TABLE_EXISTS_ACTION=truncate

All great until you wait for it to be completed and suddenly you come across below message.

ORA-39171: Job is experiencing a resumable wait.
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO'

So basically undo is been used during datapump import when we have indexes and primary key constraints.

Impdp will also not use the direct_path method, if indexes are applicable.

So follow below process to import in such situation.

1. Prior to import, disable the primary key constraints. For some reason if you have foreign key that reference these primary keys, disable the foriegn key as well.

alter table abc modify constraint cons1 disable novalidate;

Script to disable constraints

select 'alter table ' || owner || '.' || table_name || ' modify constraint ' || constraint_name || ' disable novalidate;'
from dba_constraints where owner='&owner'
and constraint_type='P'
order by owner, table_name,constraint_name;

'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'MODIFYCONSTRAINT'||CONSTRAINT_NAME||'DISABLE NOVALIDATE;'
------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE DSS.DW_RETL_PRD_CAT_VAL_US MODIFY CONSTRAINT DW_RPCVU_PK DISABLE NOVALIDATE;
select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_OWNER,R_CONSTRAINT_NAME,STATUS from dba_constraints where owner='DSS'
and table_name='DW_RETL_PRD_CAT_VAL_US' and constraint_type='P'
order by owner, table_name,constraint_name;

OWNER                CONSTRAINT_NAME                C TABLE_NAME                     R_OWNER              R_CONSTRAINT_NAME              STATUS
-------------------- ------------------------------ - ------------------------------ -------------------- ------------------------------ --------
DSS                  DW_RPCVU_PK                    P DW_RETL_PRD_CAT_VAL_US

You can use below query to get all constraints info.

select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_OWNER,R_CONSTRAINT_NAME,STATUS from dba_constraints where owner='DSS'
and table_name='DW_RETL_PRD_CAT_VAL_US'
order by owner, table_name,constraint_name;

2. Import data only, and create the indexes on the tables, after the import. Use parameter ACCESS_METHOD=DIRECT_PATH

While performing import came across below error.

impdp \'/ as sysdba\' directory=EXPDP dumpfile=DW_RETL_PRD_CAT_VAL_US_24Jan.dmp logfile=DW_RETL_PRD_CAT_VAL_US_07Feb_impdp.log tables=DSS.DW_RETL_PRD_CAT_VAL_US TABLE_EXISTS_ACTION=truncate ACCESS_METHOD=DIRECT_PATH
ORA-31696: unable to export/import TABLE_DATA:"DSS"."DW_RETL_PRD_CAT_VAL_US" using client specified DIRECT_PATH method

Instead of using table_exists_action=append/truncate I used table_exists_action=replace

Import: Release 11.2.0.2.0 - Production on Tue Feb 7 13:28:50 2017

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 and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" directory=EXPDP dumpfile=DW_RETL_PRD_CAT_VAL_US_24Jan.dmp logfile=DW_RETL_PRD_CAT_VAL_US_07Feb_impdp.log tables=DSS.DW_RETL_PRD_CAT_VAL_US TABLE_EXISTS_ACTION=replace ACCESS_METHOD=DIRECT_PATH                                          
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DSS"."DW_RETL_PRD_CAT_VAL_US"              10.56 GB 318780855 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at 15:13:38.

3. After import completes, enable the primary key and foreign key if applicable.

alter table abc modify constraint cons1 enable validate;

Script to enable constraints

select 'alter table ' || owner || '.' || table_name || ' modify constraint ' || constraint_name || ' enable validate;'
from dba_constraints
where owner='&owner'
and constraint_type='P'
order by owner, table_name,constraint_name;

'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'MODIFYCONSTRAINT'||CONSTRAINT_NAME||'DISABLENOVALIDATE;'
------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE DSS.DW_RETL_PRD_CAT_VAL_US MODIFY CONSTRAINT DW_RPCVU_PK ENABLE VALIDATE;
select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_OWNER,R_CONSTRAINT_NAME,STATUS from dba_constraints where owner='DSS'
and table_name='DW_RETL_PRD_CAT_VAL_US' and constraint_type='P'
order by owner, table_name,constraint_name;

OWNER                CONSTRAINT_NAME                C TABLE_NAME                     R_OWNER              R_CONSTRAINT_NAME              STATUS
-------------------- ------------------------------ - ------------------------------ -------------------- ------------------------------ --------
DSS                  DW_RPCVU_PK                    P DW_RETL_PRD_CAT_VAL_US

You can also use below query to get sql script.

impdp directory=EXPDP dumpfile=DW_RETL_PRD_CAT_VAL_US_24Jan.dmp logfile=DW_RETL_PRD_CAT_VAL_US_07Feb_impdp.log tables=DSS.DW_RETL_PRD_CAT_VAL_US sqlfile=DW_RETL_PRD_CAT_VAL_US_07Feb_impdp.sql

Important Note when using DIRECT_PATH: reference Export/Import DataPump Parameter ACCESS_METHOD – How to Enforce a Method of Loading and Unloading Data ? (Doc ID 552424.1)

EXPDP will use DIRECT_PATH mode if:

2.1. The structure of a table allows a Direct Path unload, i.e.:
     - The table does not have fine-grained access control enabled for SELECT.
     - The table is not a queue table.
     - The table does not contain one or more columns of type BFILE or opaque, or an object type containing opaque columns.
     - The table does not contain encrypted columns.
     - The table does not contain a column of an evolved type that needs upgrading.
     - If the table has a column of datatype LONG or LONG RAW, then this column is the last column.
2.2. The parameters QUERY, SAMPLE, or REMAP_DATA parameter were not used for the specified table in the Export Data Pump job.
2.3. The table or partition is relatively small (up to 250 Mb), or the table or partition is larger, but the job cannot run in parallel because the parameter PARALLEL was not specified (or was set to 1).

Note that with an unload of data in Direct Path mode, parallel I/O execuation Processes (PX processes) cannot be used to unload the data in parallel (paralllel unload is not supported in Direct Path mode).

IMPDP will use DIRECT_PATH if:

4.1. The structure of a table allows a Direct Path load, i.e.:
     - A global index does not exist on a multipartition table during a single-partition load. This includes object tables that are partitioned.
     - A domain index does not exist for a LOB column.
     - The table is not in a cluster.
     - The table does not have BFILE columns or columns of opaque types.
     - The table does not have VARRAY columns with an embedded opaque type.
     - The table does not have encrypted columns.
     - Supplemental logging is not enabled or supplemental logging is enabled and the table does not have a LOB column.
     - The table into which data is being imported is a pre-existing table and:
        – There is not an active trigger, and:
        – The table is not partitioned, and:
        – Fine-grained access control for INSERT mode is not enabled, and:
        – A constraint other than table check does not exist, and:
        – A unique index does not exist.
4.2 The parameters QUERY, REMAP_DATA parameter were not used for the specified table in the Import Data Pump job.
4.3. The table or partition is relatively small (up to 250 Mb), or the table or partition is larger, but the job cannot run in parallel because the parameter PARALLEL was not specified (or was set to 1).

How to enforce a specific load/unload method ?

In very specific situations, the parameter ACCESS_METHOD can be used to enforce a specific method to unload or load the data. Example:

%expdp system/manager ... ACCESS_METHOD=DIRECT_PATH 
or:
%impdp system/manager ... ACCESS_METHOD=DIRECT_PATH

Important Need-To-Know’s when the parameter ACCESS_METHOD is specified for a job:

In Oracle 11gR1 and before, the parameter ACCESS_METHOD should only be used when requested by Oracle Support.

Note:

With Oracle 11gR2, the parameter ACCESS_METHOD has been documented.

Please refer to Oracle® Database Utilities

11g Release 2 (11.2)

E22490-05

If the parameter is not specified, then Data Pump will automatically choose the best method to load or unload the data.

If import Data Pump cannot choose due to conflicting restrictions, an error will be reported:

ORA-31696: unable to export/import TABLE_DATA:”SCOTT”.”EMP” using client specified AUTOMATIC method

The parameter can only be specified when the Data Pump job is initially started (i.e. the parameter cannot be specified when the job is restarted).

If the parameter is specified, the method of loading or unloading the data is enforced on all tables that need to be loaded or unloaded with the job.

Enforcing a specific method may result in a slower performance of the overall Data Pump job, or errors such as:

...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31696: unable to export/import TABLE_DATA:"SCOTT"."MY_TAB" using client specified DIRECT_PATH method
...

To determine which access method is used, a Worker trace file can be created, e.g.:

%expdp system/manager DIRECTORY=my_dir \
DUMPFILE=expdp_s.dmp LOGFILE=expdp_s.log \
TABLES=scott.my_tab TRACE=400300

The Worker trace file shows the method with which the data was loaded (or unloaded for Import Data Pump):

...
KUPW:14:57:14.289: 1: object: TABLE_DATA:"SCOTT"."MY_TAB"
KUPW:14:57:14.289: 1: TABLE_DATA:"SCOTT"."MY_TAB" external table, parallel: 1
Advertisements