oracle 10.2.0.5 expdp ORA-39097、ORA-39065、ORA-06502

os环境：CentOS release 5.10

应用环境：10.2.0.1 -> 10.2.0.5

错误摘要:

Starting "POPUPUSER"."SYS_EXPORT_TABLE_01":  *******/******** dumpfile=51other.dmp directory=MOVEDATA logfile=51other.log tables=T_IP，T_IP_JX，T_IP_MY，A_SCHEDULER_LOG，IPXX，MYTABLE，STATIC_AGG_10MIN_TEST，STATIC_AGG_HOUR_2011090813，STATIC_AGG_HOUR_TEST，STATIC_IP_QUALITY，T10MIN，T3，T4，T5，T6，TAB_TESTIP，TAB_TESTIP61304，TAB_TESTIPHOUR，TAGG10MIN，TAGGHOUR，TEST，TMP_61304_IP，T_CS，T_FEE_IP_CITY，T_FEE_IP_COUNTRY，T_FEE_IP_COUNTRY_CODE，T_FEE_IP_D，T_IP_QQWRY，T_MYIP，T_POP_UID_LOG，T_QX，T_SF，T1，STATIC_DATA_CHECK，A_COMPRESS_LOG，DATA_CHECK exclude=STATISTICS,INDEX 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-39097: Data Pump job encountered unexpected error -6502
ORA-39065: unexpected master process exception in DISPATCH
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

metalink信息：

APPLIES TO:

Oracle Database - Enterprise Edition -Version 10.1.0.2 to 10.2.0.3 [Release 10.1 to 10.2]
Information in this document applies to any platform.
***Checked for relevance on 21-Oct-2013*** 

SYMPTOMS

During DataPump export of large list of tables getfollowing errors:

ORA-39125: Workerunexpected fatal error in KUPW$WORKER.CONFIGURE_METADATA_UNLOAD while callingDBMS_METADATA.SET_FILTER [ESTIMATE_PHASE] 
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

CAUSE

This is produced by bugs:

4053129 EXPDP FAILS WITH ERROR ORA-39125 ORA-6502 ON LARGE LIST OF TABLE NAMES(not published)
Bug 5714205 DATAPUMP IS NOT UPWARD COMPATIBLE TO EXP 

When expdp creates an internal metadata filter to access the needed data, itbuilds a SQL statement with a WHERE clause. In this case, the length of suchwhere clause (+3000) might be too large for the processing resulting in"ORA-06502: PL/SQL: numeric or value error: character string buffer toosmall".

This error can be seen in following forms during datapump export:

ORA-39006:internal error 
ORA-39065: unexpected master process exception in DISPATCH 
ORA-6502: PL/SQL: numeric or value error: character string buffer toosmall 
ORA-39097: Data Pump job encountered unexpected error -6502

or:

ORA-39125: Workerunexpected fatal error in KUPW$WORKER.CONFIGURE_METADATA_UNLOAD while callingDBMS_METADATA.SET_FILTER [ESTIMATE_PHASE] 
ORA-6502: PL/SQL: numeric or value error: character string buffer toosmall 
ORA-6512: at "SYS.DBMS_SYS_ERROR", line 105 
ORA-6512: at "SYS.KUPW$WORKER", line 6129

or:

ORA-39125: Workerunexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while callingDBMS_METADATA.FETCH_XML_CLOB [] 
ORA-6502: PL/SQL: numeric or value error: character string buffer too small
ORA-6512: at "SYS.DBMS_SYS_ERROR", line 95 
ORA-6512: at "SYS.KUPW$WORKER", line 6123

SOLUTION

4053129 is fixed in the 10.1.0.5 release and 10.2releases.
Bug 5714205 is fixed in the 10.2.0.4 release and 11.1 releases.

The following workarounds can be done:

1. Run datapump export (expdp) jobs with smaller list of objects/tables.

2. Create a work table to hold the names (in upper case)of tables that have to be included/excluded

connectscott/passwd

-- create additional table that stores table names to be included/excluded
create table tt 
(
   name varchar2(20)
);

-- populate table
insert into tt ('TABLE1'); 
insert into tt ('TABLE2');
...
insert into tt ('TABLE<n>');
commit;

Now use expdp parfile with syntax for exclude/include ofthe form:

INCLUDE=TABLE:"IN(select name from scott.tt)"

This avoids explicitly creating a long list or a filterexpression containing many object names.

3. Use traditional exp instead of expdp.

REFERENCES

BUG:5714205 - DATAPUMP IS NOT UPWARD COMPATIBLE TO EXP

看到metalink提供的信息，是由于bug导致的问题

解决方法：

1、缩小table list长度

2、用INCLUDE=TABLE:"IN(select <table_name>from temptable)"的方式expdp。

3、Bug5714205 

相关文档：

====================================================================

============ora-6502 in 10.2.0.4 and later&11.2.0.2 and later=============

====================================================================

APPLIES TO(Version 10.2.0.4 and later):                                                                                                                                                                        

OracleDatabase - Enterprise Edition -Version 10.2.0.4 and later
Information in this document applies to any platform.
***Checked for relevance on 14-Mar-2013***

SYMPTOMS

DataPump export (expdp) encountered unexpected errorsORA-39097 ORA-6502 ORA-39065:

expdp tc1/tc1DIRECTORY=test_dp DUMPFILE=export_schemas.dmp 

Export: Release 10.2.0.4.0 - Production on Tuesday, 28 April, 2009 16:25 

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 
Production 
With the Partitioning, OLAP, Data Mining and Real Application Testingoptions 
ORA-39006: internal error 
ORA-39065: unexpected master process exception in DISPATCH 
ORA-06502: PL/SQL: numeric or value error: character to number conversionerror 

ORA-39097: Data Pump job encountered unexpected error -6502

The same error messages also occur duringDataPump import (impdp).

When executing DBMS_METADATA.GET_DDL ORA-31600 and ORA-6512 are encountered,too:

set long100000 
set linesize 100 
select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual; 

ERROR: 
ORA-31600: invalid input value COMPATIBLE for parameter VERSION infunction 
GET_DDL 
ORA-06512: at "SYS.DBMS_METADATA", line 2682 
ORA-06512: at "SYS.DBMS_METADATA", line 2733 
ORA-06512: at "SYS.DBMS_METADATA", line 4333 
ORA-06512: at line 1

CAUSE

Both issues are caused by a corruption in SPFILE.

A hex dump of SPFILE confirms, the value of parameter "compatible" iscorrupted due to a linefeed (0xA) character:

SPFILE 
------ 
00000340h: 70 27 0A 2A 2E 63 6F 6D 70 61 74 69 62 6C 65 3D ;p'.*.compatible= 
00000350h: 27 31 30 2E 32 2E 30 2E 34 2E 30 0A 27 0A 2A2E ; '10.2.0.4.0.'.*. 
                                           -- 
                          There's a linefeed 0x0A inside 
                          compatible specification! 

Another method to confirm the SPFILE corruption is to run a query againstv$spparameter: 

SQL> select dump(value,16) from v$spparameter where name='compatible'; 

DUMP(VALUE,16) 
-------------------------------------------------------------------------- 
Typ=1 Len=11: 31,30,2e,32,2e,30,2e,34,2e,30,a <==

Expdp/Impdp and DBMS_METADATA.GET_DDL are misinterpreting thisinformation and therefore terminate with errors.

SOLUTION

Reset the compatible parameter and restart instance:

SQL> altersystem set compatible='10.2.0.4.0' scope=spfile;

SQL> -- This paramter can't be changed dynamically in memory. 
SQL> shutdown immediate 
SQL> startup

Once the compatible parameter has been reset, both IMPDP/EXPDP andDBMS_METADATA.GET_DDL work as expected.

APPLIES TO(Version11.2.0.2 and later):

Oracle Server - Enterprise Edition -Version11.2.0.2 and later
Information in this document applies to any platform.

GOAL

Expdp errors out with:

ORA-39097: DataPump job encountered unexpected error -6502
ORA-39065: unexpected master process exception in DISPATCH
ORA-06502: PL/SQL: numeric or value error

when the parameter TABLES=table1,table2... is larger than32K.

The fix for bug 11655916 was installed but the errors are still raised.

Expdp succeeds, if the table name are included like thebelow format:

INCLUDE=TABLE:"IN (select name fromscott.tt)" 

FIX

 There are 2 ways to resolve this error.

1. Install patch 11655916. But this is successful only when expdpgenerates ORA-6502 error, tables list is very large and database character setis AL32UTF8.

Note: This patch requires to execute the followingpackage once the patch has been applied:

SQL> connect / as sysdba
SQL> @?/rdbms/admin/prvtbpm.plb

2. Install patch 10647999, if expdp errors out with ORA-6502 error andtables list is larger than 32K.

Note: This patch requires to execute the followingpackage once the patch has been applied:

SQL> connect / as sysdba
SQL> @?/rdbms/admin/prvtstat.plb

Note: Do not apply both the patchessimultaneously. Only one patch has to be applied at a time.