oracle 10.2.0.5 expdp ORA-39097、ORA-39065、ORA-06502
来源:互联网 发布:淘宝联通话费充值便宜 编辑:程序博客网 时间:2024/05/17 02:05
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
相关文档:
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
- oracle 10.2.0.5 expdp ORA-39097、ORA-39065、ORA-06502
- expdp 导出报错 ORA-39006: ORA-39065: ORA-04063: ORA-06508: ORA-39097: ORA-01403: ORA-39097:
- ORA-39006,ORA-39213,ORA-06512,ORA-01114,ORA-39065,ORA-39097,ORA-01403 EXPDP报错问题处理
- expdp时遇到ORA-39097&ORA-39065&ORA-39079&ORA-06512&ORA-24033
- [Oracle] expdp ORA-39006, ORA-39065 的解决办法
- Oracle expdp ORA-39006, ORA-39065的解决办法
- expdp遭遇ORA-39006、ORA-39065、ORA-01403、ORA-39097错误
- expdp ORACLE:ORA-12560解决办法
- ORACLE EXPDP ORA-31626 问题
- Orace expdp ORA ORA-39167
- expdp ora-31640 ora-19505
- ORACLE impdp或expdp与ORA-31693&ORA-31640&ORA-19505&ORA-27037
- Oracle expdp 时遭遇ORA-39125 ORA-04063
- ORA-39097 ORA-39065 ORA-01427
- expdp错误ORA-39029
- ORA-39126: expdp
- expdp ORA-39002
- EXPDP ORA-39083
- 【cocos2d-x】 之 模态对话框
- 致加西亚的信读后感
- C++ Assert()函数
- 任意改变参数的64位Thunk
- jQuery学习第六课(jquery中的动画)
- oracle 10.2.0.5 expdp ORA-39097、ORA-39065、ORA-06502
- js设置css样式对照表
- Hand in Hand 并查集 同构图
- LCA在线算法(hdu2586)
- error: No resource identifier found for attribute 'backgroud' in package 'android'
- 图像处理中像素点的问题:double、long、unsigned、int、char类型数据所占字节数
- SQLServer 2012 已成功与服务器建立连接,但是在登录前的握手期间发生错误。 (provider: SSL Provider, error: 0 - 等待的操作过时。
- C++类所占内存大小计算
- Mysql理解之连接