impdp导入速度缓慢问题,专家帮忙会诊

wanglixue2012

http://www.itpub.net/forum.php?mod=viewthread&tid=1628017&page=1#pid19734095

是否是因为IO问题导致，可有优化的空间

http://www.itpub.net/forum.php?mod=viewthread&tid=1628017&page=1#pid19734095

Maclean Liu(刘相兵

action plan:


查询 target 上的 gv$session_longops 视图   确认impdp 还需要多久

建议 impdp时 exclude=index 排除建索引的步骤 ，之后手动建立索引 ， direct path write temp可能由index build引起

wanglixue2012

也许我真的需要放弃导入索引。导出日志如下：耗费了了(21:39:53-18:58:05) 2个半小时。

算了一下2M/s的整体速度。




YY-JFJK-HW:/opt/oracle/admin/jingfen/dpdump # more imp_billlog_t_P_R_09302010.log
;;;
Import: Release 11.1.0.7.0 - 64bit Production on Sunday, 24 June, 2012 18:58:05

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "PINTEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "PINTEST"."SYS_IMPORT_FULL_01":  pintest/******** table_exists_action=append remap_schema=PIN:PINTEST directory=PARTITION_B
ACKUP_DIR dumpfile=billlog_t_P_R_09302010.dmp logfile=DATA_PUMP_DIR:imp_billlog_t_P_R_09302010.log
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "PINTEST"."BILLLOG_T" exists. Data will be appended to existing table but all dependent metadata will be skipped du
e to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "PINTEST"."BILLLOG_T":"P_R_09302010"        17.25 GB 48326149 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "PINTEST"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 21:39:53

wanglixue2012

SYS_IMPORT_FULL_01: IMPORT : 0 out of 22794 MB done

这个东东在作祟，目前还在进行(exclude=index,已经使用了,看来IO问题比较验证，NAS存储性能的确烂的很)

START_TIME 2012-6-24 22:20:39
LAST_UPATE_TIME 2012-6-24 22:31:43
SOFAR 0
TOTALWORK 22794

wanglixue2012

DBM上DMP文件的导出仅使用了9分钟,23G的文件

-rwxrwxrwx 1 oracle oinstall  23G Jun 24 22:17 billlog_t_P_R_10302010.dmp

Maclean Liu(刘相兵

direct path write 单次 12ms 还不算最差

考虑 是否采用 parallel impdp ， 是否有lob、long字段可能影响速度

wanglixue2012

这个表及其简单，分区表。只有INTEGER、VARCHAR2两个类型。

今晚使用参数parallel，提高并行度。试一下效果。

wanglixue2012

我晕倒，计算速度似乎计算错误了。

文件大小：18524393472Bytes
导入时间 9708S

1.8197608447929542645241038318912 MB/s

wanglixue2012

增大并大量的问题，晕~~~~~~~~~~~~~

oracle@YY-JFJK-HW:~> impdp pintest/pintest  PARALLEL=3 exclude=index table_exists_action=append remap_schema=PIN:PINTEST directory=PARTITION_BACKUP_DIR dumpfile=billlog_t_P_R_11302010.dmp logfile=DATA_PUMP_DIR:imp_billlog_t_P_R_11302010.log;

Import: Release 11.1.0.7.0 - 64bit Production on Monday, 25 June, 2012 10:32:50

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "PINTEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "PINTEST"."SYS_IMPORT_FULL_01":  pintest/******** PARALLEL=3 exclude=index table_exists_action=append remap_schema=PIN:PINTEST directory=PARTITION_BACKUP_DIR dumpfile=billlog_t_P_R_11302010.dmp logfile=DATA_PUMP_DIR:imp_billlog_t_P_R_11302010.log
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "PINTEST"."BILLLOG_T" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

ORA-31693: Table data object "PINTEST"."BILLLOG_T":"P_R_11302010" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "PINTEST"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 10:33:13

seedog123

Impdp Fails with orA-31693 orA-29913 orA-29400 KUP-11010 if Parallel > 1 is Used in RAC Database [ID 762475.1]
    修改时间 17-APR-2012     类型 PROBLEM     状态 PUBLISHED     
In this Document
Symptoms
Cause
Solution
References
Applies to:

oracle Server - Enterprise Edition - Version 10.2.0.4 to 11.1.0.7 [Release 10.2 to 11.1]
Information in this document applies to any platform.
*** Checked for relevance on 17-Apr-2012 ***
oracle Server Enterprise Edition - Version: 10.2.0.4 to 11.1.0.7


Symptoms

In a RAC database Data Pump import generates the following errors if PARALLEL > 1 is used:

orA-31693: Table data object ""."" failed to load/unload and is being skipped due to error:
orA-29913: error in executing ODCIEXTTABLEOPEN callout
orA-29400: data cartridge error
KUP-11010: unable to open at least one dump file for load

The directory where Data Pump dump file is stored is *not* accessible from all instances in RAC.

Cause

This issue was analyzed in:

Bug 6990731 - IMPDP 10.2.0.4 ERROR WHEN PARALLEL > 1 orA-31693 orA-29913 orA-29400 KUP-11010
currently suspended.

In this bug there was referenced the "Note" from Bug 5472417 - EXPDP on RAC
fails with orA-39014 / orA-12801 (error signaled in parallel query server) fixed in 10.2.0.4 and 11.1.0.6 (see Note 5472417.8).

This note specifies:
"In order for a Data Pump job start up and work properly in parallel a RAC environment make sure the specified directory object points to shared storage that is accessible from all instances in the RAC cluster".

In 10.2.0.4 and 11g RAC databases this is mandatory for a proper behavior of parallel (parallel>1) Data Pump export/import jobs that the specified directory is on a shared disk accessible from all instances in the RAC cluster.



Solution

1. Create the Data Pump directory on a shared storage accessible from all the instances in the RAC cluster.

or

2. Run the Data Pump import job with parallel=1 (default)

or

3. Run the Data Pump import job if only one instance is started.

References

BUG:5472417 - EXPDP ON RAC ABORTS DUE TO orA-12801 (ERROR SIGNALED IN PARALLEL QUERY SERVER)
NOTE:5472417.8 - Bug 5472417 - EXPDP on RAC fails with orA-39014 / orA-12801 (error signaled in parallel query server)
BUG:6990731 - IMPDP 10.2.0.4 ERROR WHEN PARALLEL > 1: orA-31693 orA-29913 orA-29400 KUP-11010

wanglixue2012

这个帖子看过，我是IMPDP时报错。

我导入的目标机器是单机，似乎三个solution都不太适合我。看来我只能并发1来导入了