Checklist for Slow Performance of Export Data Pump (expdp) and Import DataPump (impdp)

Oracle

4 comments to Checklist for Slow Performance of Export Data Pump (expdp) and Import DataPump (impdp)

• admin

  August 24, 2010 at 10:10 PM · Reply

• admin

  August 24, 2010 at 10:11 PM · Reply

  IMPDP Can Fail with ORA-31696 if ACCESS_METHOD=DIRECT_PATH Is Manually Specified

  Applies to:

  Oracle Server – Enterprise Edition – Version: 10.2.0.3
  Information in this document applies to any platform.

  Goal

  The following situation:
  1. export
  2. change table structure by adding/removing columns.
  3. import

  Does not work when the chosen ACCESS_METHOD is EXTERNAL_TABLES, because this method is checking the table metadata. This situation cannot be fixed in 10g and several workarounds have been indicated, such as:

  + import the data to a temporary table and then use INSERT/SELECT to include it to the actual table

  + use DIRECT_PATH for ACCESS_METHOD to import the data, as this access method does not check the table metadata before import.

  However, using the second method is sometimes failing with ORA-31696 errors. Why?

  Solution

  The error is caused by the limitations on DIRECT PATH insert with datapump, as documented at:
  http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_overview.htm#sthref17

  Situations in Which Direct Path Load Is Not Used

  If any of the following conditions exist for a table, Data Pump uses external tables rather than direct path to load the data for that table:
  * A global index on multipartition tables exists during a single-partition load. This includes object tables that are partitioned.
  * A domain index exists for a LOB column.
  * A table is in a cluster.
  * There is an active trigger on a pre-existing table.
  * Fine-grained access control is enabled in insert mode on a pre-existing table.
  * A table contains BFILE columns or columns of opaque types.
  * A referential integrity constraint is present on a pre-existing table.
  * A table contains VARRAY columns with an embedded opaque type.
  * The table has encrypted columns
  * The table into which data is being imported is a pre-existing table and at least one of the following conditions exists:

  o There is an active trigger
  o The table is partitioned
  o fgac is in insert mode
  o A referential integrity constraint exists
  o A unique index exists

  * Supplemental logging is enabled and the table has at least 1 LOB column.

  To the above, there is an additional condition which is not documented to be added:

  + if the table is pre-exitent and partitioned, there should be no index defined on it in order to be able to use direct path import.

  
  As well, one of the above limitations can be circumvented if patch 5632683 is installed, ie the table can be partitioned. The fix above allows direct path import to be used on this kind of tables, even when the table is pre-existent to the import.

  Thus, in order to be still able to workaround the error, one should try to drop or disable any of the above mentioned objects in order to be still able to import with the DIRECT PATH method.
  

   

• admin

  August 24, 2010 at 10:12 PM · Reply

  Logical Corruption Encountered After Importing Table With Long Column Using DataPump
  Applies to:
  Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 10.2.0.3
  This problem can occur on any platform.
  Symptoms

  After importing a table that contains a LONG or LONG RAW column using DataPump, logical corruptions are reported eg. as one of:

  * ORA-1498
  * using the RMAN VALIDATE DATAFILE command and querying the V$BACKUP_CORRUPTION view
  * ORA-600 [6917], [6110] (if DB_BLOCK_CHECKING is enabled)
  * DBVerify raising check code 6110

  Example

  Import dump file using access_method=direct_path (default):

  $ impdp user/pass job_name=job directory=dir dumpfile=filename …

  The imported table has eg. the following structure (note the LONG RAW column):

  SQL> desc ….

  Name Null? Type
  —————————————– ——– —————————-
  TABNAME NOT NULL VARCHAR2(10)
  VARKEY NOT NULL VARCHAR2(50)
  DATALN NOT NULL NUMBER(5)
  VARDATA NOT NULL LONG RAW

  Analyzing the table gives:

  SQL> analyze table … validate structure cascade;

  ERROR at line 1:
  ORA-01498: block check failure – see trace file

  Using RMAN to check the datafiles (see also note:283053.1):

  $ rman target / nocatalog

  RMAN> run {
  allocate channel d1 type disk;
  backup check logical validate database;
  release channel d1;
  }

  and then checking V$BACKUP_CORRUPTION gives an output like:

  SQL> select * from v$backup_corruption;
  RECID STAMP SET_STAMP SET_COUNT PIECE# FILE# BLOCK#
  ———- ———- ———- ———- ———- ———- ———-
  BLOCKS CORRUPTION_CHANGE# MAR CORRUPTIO
  ———- —————— — ———
  1 638462152 638461740 2 1 7 5909
  1 254974 YES LOGICAL

  Or using DBVerify:

  $ dbv file=… blocksize=…

  Block Checking: DBA = 29366037, Block Type = KTB-managed data block
  data header at 0xf6e9b27c
  kdbchk: the amount of space used is not equal to block size
  used=7248 fsc=0 avsp=817 dtl=8064
  Page 5909 failed with check code 6110

  Cause
  This is a case of bug:5941030.

  The situation is due to the import of a table having a LONG or LONGRAW via DataPump where access_method=direct_path (which is the default). The column count is miscalculated on import and this introduces the corruption.
  Solution

  Bug:5941030 is fixed in the 10.2.0.4 patch set and is also included in the 11g (11.1.0.7) patch. One-off patches are available for some platforms on earlier versions.

  Possible solutions are:

  1. If the original DataPump export is available:

  a. Drop the table in the database
  b. Apply the 10.2.0.4 patch set or Patch 5941030
  c. Import the original DataPump dumpfile again

  OR

  2. If the original DataPump export is NOT available:

  a. Export the table in conventional mode
  b. Drop the table
  c. Import the table in conventional mode

• yumianfeilong

  May 15, 2012 at 9:51 PM · Reply

  昨天在10203上impdp一个130G大小包括long的表，35分钟direct path mode就结束了

  今天在11202上impdp同样的到处文件，要3个小时。

Copyright © 2012 Oracle Clinic – Maclean Liu的个人技术博客 - All Rights Reserved
Powered by WordPress & Atahualpa