expdp Hit bug( Bug 5879865)

     最近计划升级公司运行N年的一套10g数据库，计划升级到11.2.0.4；

测试expdp/impdp 升级方案的过程中遇到如下问题：

Export: Release 10.2.0.3.0 - 64bit Production on Tuesday, 24 March, 2015 13:33:15

Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_FULL_03": system/******** full=y directory=expdp_dir dumpfile=dn2prod201_fspdc_%U.dmp logfile=dn2prod201_fspdc.log content=all filesize=15000M
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 502.9 GB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/PROXY
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/CONTEXT
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/CROSS_SCHEMA/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW01" prematurely terminated
ORA-31672: Worker process DW01 died unexpectedly.
Job "SYSTEM"."SYS_EXPORT_FULL_03" stopped due to fatal error at 13:37:21 ===>error at 13:37:21
ORA-39014: One or more workers have prematurely exited.



分析追踪日志，在此异常之后并未影响生产库正常运行。该异常问题是可重复模拟：
Tue Mar 24 13:36:17 2015
Errors in file d:\oracle\product\10.2.0\admin\fspdc\bdump\fspdc_dw01_13868.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [evaopn2+2896] [PC:0x239EB3E] [ADDR:0x0] [UNABLE_TO_READ] []

Tue Mar 24 13:36:20 2015
Errors in file d:\oracle\product\10.2.0\admin\fspdc\bdump\fspdc_dw01_13868.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [evaopn2+2896] [PC:0x239EB3E] [ADDR:0x0] [UNABLE_TO_READ] []

Tue Mar 24 13:36:24 2015
Errors in file d:\oracle\product\10.2.0\admin\fspdc\bdump\fspdc_dw01_13868.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [evaopn2+2896] [PC:0x239EB3E] [ADDR:0x0] [UNABLE_TO_READ] []

Tue Mar 24 13:38:43 2015
Beginning log switch checkpoint up to RBA [0x80312.2.10], SCN: 13985706256174
Thread 1 advanced to log sequence 525074
Current log# 3 seq# 525074 mem# 0: E:\FSPDC\REDO3A.LOG
Current log# 3 seq# 525074 mem# 1: F:\FSPDC\REDO3B.LOG

========== Dump file d:\oracle\product\10.2.0\admin\fspdc\bdump\fspdc_dw01_13868.trc

Dump file d:\oracle\product\10.2.0\admin\fspdc\bdump\fspdc_dw01_13868.trc
Tue Mar 24 13:33:21 2015
ORACLE V10.2.0.3.0 - 64bit Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Windows NT Version V5.2 Service Pack 2
CPU : 8 - type 8664, 4 Physical Cores
Process Affinity : 0x0000000000000000
Memory (Avail/Total): Ph:16594M/34814M, Ph+PgF:22079M/41881M
Instance name: fspdc

Redo thread mounted by this instance: 1
Oracle process number: 2421
Windows thread id: 13868, image: ORACLE.EXE (DW01)

*** 2015-03-24 13:36:17.686
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [evaopn2+2896] [PC:0x239EB3E] [ADDR:0x0] [UNABLE_TO_READ] []
Current SQL statement for this session:
SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('PACKAGE_BODY_T', '7')), KU$.OBJ_NUM ,KU$.SCHEMA_OBJ.NAME ,KU$.SCHEMA_OBJ.NAME ,'PACKAGE_BODY' ,KU$.SCHEMA_OBJ.OWNER_NAME FROM SYS.KU$_EXP_PKG_BODY_VIEW KU$ WHERE KU$.BASE_OBJ_NUM IN (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(200001)))

----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
evaopn2+2896 0000000000000000 644CBB820 000000003 000000102
000000000
evaopn2+1304 CALL??? evaopn2+2896 248FBB3F8 5A1000000000
3662C2F48 00001DB70
evaopn2+3586 CALL??? evaopn2+1304 248FBAD08 003286D99 000000000
004C4CC54
opifcr+5568 CALL??? evaopn2+3586 248FBADC0 000000001 600000003
4FB900010
__PGOSF568_qervwRow CALL??? opifcr+5568 673300002 673301A10 248FBC3C8
Procedure+244 002CC3B02
qersoFetch+2991 CALL??? __PGOSF568_qervwRow 000000000 000000000 000000000
Procedure+244 000000000
qervwFetch+142 CALL??? qersoFetch+2991 000000006 000000010 6743C04C8
682A16978
kpofrws+474 CALL??? qervwFetch+142 000000001 000000006 000000006
64E9E2230


Data Pump Export Fails With ORA-39014, ORA-39029 and ORA-31672, Alert Log Shows ORA-07445 [_evaopn2+578] (Doc ID 473426.1)

Solution

1. The bug is fixed is 11g.  If available, download the one off for specific os.
2. Or use one of following workaround:

•  set both of the following in the init/spfile:
  "_complex_view_merging" = false  
  event="38066 trace name context forever, level 1"  or level 2

  
  OR

• Set following parameter:

• "_optimizer_cost_based_transformation"=off

Any of these workarounds can be done at the system level or set in the init or spfile file.


EXAMPLE:
===============
AT SYSTEM LEVEL:

SQL> alter system set events '38066 trace name context forever, level 2';
SQL> alter system set "_complex_view_merging" = false;
SQL> alter system set "_optimizer_cost_based_transformation"=off

IN THE INIT.ORA:

event="38066 trace name context forever, level 2"
_complex_view_merging=false
_optimizer_cost_based_transformation=off

IN THE SPFILE (requires restart of instance):

SQL> alter system set event= '38066 trace name context forever, level 2' scope=spfile;
SQL> alter system set "_complex_view_merging" = false scope=spfile;
SQL> alter system set "_optimizer_cost_based_transformation"=off scope=spfile;

3.  Flush the shared pool or restart the database:

SQL>alter system flush shared_pool;

4.  Perform the Data Pump export again.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24867586/viewspace-1473410/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24867586/viewspace-1473410/