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