INEWUP'S BLOG

遇到一个oracle10g impdp bug

inewup-oracle-12c

情况是这样的，客户需建议一个测试系统，导入正式数据

impdp xxxxx  directory=xxxxx dumpfile=xxxxx.dmp remap_tablespace=xxxxxx:cccccc remap_schema=xxx:ccc logfile=xxx.log

导入的过程中使用了impdp attach=SYS_IMPORT_FULL_01 修改了parallel 并行度

在导入到TABLE_STATISTICS的时候出现问题

处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

ORA-39097: 数据泵作业出现意外的错误 -21780

ORA-39065: DISPATCH 中出现意外的主进程异常错误

ORA-21780: 已超过对象持续时间的最大值。

ORA-39014: 一个或多个 worker 进程已过早地退出。

作业 "xxxxx"."SYS_IMPORT_FULL_01" 因致命错误于 14:30:43 停止

ORA-39014: 一个或多个 worker 进程已过早地退出。

查询后发现Data Pump Export (expdp) or Data Pump Import (impdp) reports ORA-21780 at the end of export completion. (文档 ID 1357811.1)

CHANGES

Changed number of parallel processes while Data Pump Export or Import was running.

Export> parallel=1 Export> exit

CAUSE

This is caused by Bug:10198387 which is a confirmed duplicate of Bug:16298145

SOLUTION

FIX

- 11.2.0.4

- 12.1

WORKAROUND

Don't change the number of parallel processes while Data Pump Export is running.

Bug 10198387 : DATA PUMP EXPORT REPORTS ORA-21780 WHEN PARALLELISM IS MODIFIED

DIAGNOSTIC ANALYSIS:

WORKAROUND:

Don't execute "parallel=n" while export is running.

RELATED BUGS:

-

REPRODUCIBILITY:

Permanently reproducible:

  • 11g Release 1
  • 11g Release 2

Not verified on 10g but most probable would reproduce there too.

TEST CASE:

The following test case will try to export the SYSTEM schema by using PARALLEL=4

  1. Prerequisites

connect / as sysdba

-- create test user create user tc1 identified by tc1 default tablespace users; grant dba to tc1;

-- Create Data Pump directory create or replace directory DP_TEST as '/tmp';

-- Create a parameter file (test.par) to include the following parameters: directory=DP_TEST job_name=TEST LOGFILE=test.log SCHEMAS=SYSTEM parallel=4

  1. Run the test case. You need 2 separate sessions:

-- session #1: initiates the export rm /tmp/*.dmp expdp tc1/tc1 parfile=test.par

-- session #2: Changes parallel to 1. expdp tc1/tc1 attach=TEST Export> parallel=1 Export> exit

NOTE

It is essential to execute the "parallel=1" command after the "total estimation" message has been reported:

Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 320 KB <== don't execute "parallel=1" before this message appeared. before this message

The issue would not occur if "parallel=1" is specified before the estimate process completed.

← Back to Home