expdp报ORA-01031

jeanron100

bisal 发表于 2015-5-27 11:06
hi，您好，

1. 是不是aq参数的值为0导致的？

修改字符集的操作 GBK到UTF8 不是oracle建议的方式吧。这种方式可能有些隐患，因为UTF8中汉字是3个字节，GBK是2个字节。一般都是直接重建库，导数据来做的。
你说的在另一个测试库试过，aq设为0，并没有模拟出这个错误。那个库是做过修改字符集的操作吗？
感觉你这个问题可能是bug.

你使用strace尝试了吗？看看有没有什么收获？

jieyancai

show parameter job;看看结果
alter system set job_queue_processes=1000;

bisal

jeanron100 发表于 2015-5-27 14:13
修改字符集的操作 GBK到UTF8 不是oracle建议的方式吧。这种方式可能有些隐患，因为UTF8中汉字是3个字节， ...

我在另一个测试库中按照上面修改字符集的方法做了一遍，expdp提示的信息不同，是字符集问题，还不是权限问题：
ora11g@bisal.oracle.com$expdp xxx/xxx directory=expdir dumpfile=test.dmp logfile=test.log tables=test_b

Export: Release 11.2.0.1.0 - Production on Wed May 27 09:08:32 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "BISAL"."SYS_EXPORT_TABLE_01":  xxx/******** directory=expdir dumpfile=test.dmp logfile=test.log tables=test_b
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA [INDEX_STATISTICS]
ORA-12704: character set mismatch

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8164

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x86fcabe0     19028  package body SYS.KUPW$WORKER
0x86fcabe0      8191  package body SYS.KUPW$WORKER
0x86fcabe0      2814  package body SYS.KUPW$WORKER
0x86fcabe0      8846  package body SYS.KUPW$WORKER
0x86fcabe0      1651  package body SYS.KUPW$WORKER
0x86fd1a88         2  anonymous block

Job "BISAL"."SYS_EXPORT_TABLE_01" stopped due to fatal error

trace还没时间做，回头搞下。

bisal

jieyancai 发表于 2015-5-27 15:29
show parameter job;看看结果
alter system set job_queue_processes=1000;

当前值是0

Yong Huang

I have two comments. Commands such as "ALTER DATABASE character set INTERNAL_USE..." should not be run without approval from Oracle Support. Once you do that, you're on your own.

For data pump jobs to run correctly, aq_tm_processes should not be explicitly set to 0. Note I say "explicitly". In early 11g versions such as 11.2.0.1 or ealier, the value in v$parameter (or "show parameter") may appear to be 0 but internally it's not; that 0 simply means "automatically set". In later versions, the value by default is 1. If the value has been set to 0 (see your msg #10), you should change it to some non-zero value and try data pump again.

I'm not sure if job_queue_processes is related to a data pump job.

jieyancai

bisal 发表于 2015-5-28 00:15
当前值是0

改了吗？
alter system set job_queue_processes=1000;

bisal

Yong Huang 发表于 2015-5-28 00:51
I have two comments. Commands such as "ALTER DATABASE character set INTERNAL_USE..." should not be r ...

我在mos看到一篇文章，给了一个PLSQL，执行后和您说的一样，虽然show的值是0，但PLSQL指ing执行结果显示是“explicityl set to 0”，我试过设置其值为1，但问题还是这样。

connect / as sysdba

，
declare
mycheck number;
begin
select 1 into mycheck from v$parameter where name = 'aq_tm_processes' and value = '0' and (ismodified != 'FALSE' OR isdefault = 'FALSE');
if mycheck = 1 then
dbms_output.put_line('The parameter ''aq_tm_processes'' is explicitly set to 0!');
end if;
exception when no_data_found then
dbms_output.put_line('The parameter ''aq_tm_processes'' is not explicitly set to 0.');
end;
/

bisal

jieyancai 发表于 2015-5-28 09:20
改了吗？
alter system set job_queue_processes=1000;

这个还没试过，这个参数设置后有什么副作用么？

jieyancai

bisal 发表于 2015-5-28 23:23
这个还没试过，这个参数设置后有什么副作用么？

没有副作用，本来就是1000的。
不启用的话，所有job都不能执行。

Yong Huang

jieyancai,

I have a feeling that you're confusing data pump jobs with DBMS jobs (those shown in dba_jobs or dba_jobs_running). They're different. Data pump jobs, as shown in dba_datapump_jobs, are not related to DBMS jobs. You can test by explicitly setting job_queue_processes to 0 and see if you can run a data pump job. My test in 11.2.0.3 shows it can still run. (Make sure aq_tm_processes is not 0.)