oracle expdp作业外表报错ORA-20011&KUP-11024&ORA-29913实例

2018年1月份第一次巡检中，某地市oracle告警ora-20011&kup-11024&ora-29913，

相关处理如下：

操作版本：rhel 2.6.39-400.17.1.el6uek.x86_64

操作系统内核：rhel 2.6.39-400.17.1.el6uek.x86_64

数据库版本：oracle 11.2.0.4

问题描述：数据库对数据泵作业残余外部表收集统计信息报错：ora-20011&kup-11024&ora-29913

告警日志报错信息如下：

09/01/2018 22:00:10 dbms_stats: gather_stats_job encountered errors. check the trace file.

ora-20011: approximate ndv failed: ora-29913: error in executing odciexttableopen callout

tue jan 09 22:00:10 2018

kup-11024: this external table can only be accessed from within a data pump job.

errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j003_333.trc

查看告警日志提示的trc跟踪文件orcl_j003_333.trc

trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j003_333.trc

oracle database 11g enterprise edition release 11.2.0.4.0 - 64bit production

with the partitioning, olap, data mining and real application testing options

oracle_home = /u01/app/oracle/product/11.2.0/db_1

system name: linux

node name: cis.db

release: 2.6.39-400.17.1.el6uek.x86_64

version: #1 smp fri feb 22 18:16:18 pst 2013

machine: x86_64

instance name: orcl

redo thread mounted by this instance: 1

oracle process number: 57

unix process pid: 333, image: [e-mail] (j003)

*** 2018-01-09 22:00:10.612

*** session id:(634.22947) 2018-01-09 22:00:10.612

*** client id:() 2018-01-09 22:00:10.612

*** service name:(sys$users) 2018-01-09 22:00:10.612

*** module name:(dbms_scheduler) 2018-01-09 22:00:10.612

*** action name:(ora$at_os_opt_sy_152) 2018-01-09 22:00:10.612

ora-20011: approximate ndv failed: ora-29913: error in executing odciexttableopen callout

kup-11024: this external table can only be accessed from within a data pump job.

*** 2018-01-09 22:00:10.612

dbms_stats: gather_stats_job: gather_table_stats('"expdp"','"et$01b7017f0001"','""', ...)

dbms_stats: ora-20011: approximate ndv failed: ora-29913: error in executing odciexttableopen callout

kup-11024: this external table can only be accessed from within a data pump job.

*** 2018-01-09 22:00:10.656

dbms_stats: gather_stats_job: gather_table_stats('"expdp"','"et$01c303530001"','""', ...)

dbms_stats: ora-20011: approximate ndv failed: ora-29913: error in executing odciexttableopen callout

kup-11024: this external table can only be accessed from within a data pump job.

*** 2018-01-09 22:00:10.686

dbms_stats: gather_stats_job: gather_table_stats('"expdp"','"et$01982c090001"','""', ...)

dbms_stats: ora-20011: approximate ndv failed: ora-29913: error in executing odciexttableopen callout

kup-11024: this external table can only be accessed from within a data pump job.

*** 2018-01-09 22:00:10.711

dbms_stats: gather_stats_job: gather_table_stats('"expdp"','"et$01d400370001"','""', ...)

dbms_stats: ora-20011: approximate ndv failed: ora-29913: error in executing odciexttableopen callout

kup-11024: this external table can only be accessed from within a data pump job.

*** 2018-01-09 22:00:10.735

dbms_stats: gather_stats_job: gather_table_stats('"expdp"','"et$01d3186f0001"','""', ...)

dbms_stats: ora-20011: approximate ndv failed: ora-29913: error in executing odciexttableopen callout

kup-11024: this external table can only be accessed from within a data pump job.

*** 2018-01-09 22:00:10.759

dbms_stats: gather_stats_job: gather_table_stats('"expdp"','"et$00251aef0001"','""', ...)

dbms_stats: ora-20011: approximate ndv failed: ora-29913: error in executing odciexttableopen callout

kup-11024: this external table can only be accessed from within a data pump job.

处理方法：

1、dbms_stats job 运行时，没有datapump job运行

sql> !ps -ef|grep expdp

oracle 28440 28067 0 09:49 pts/0 00:00:00 /bin/bash -c ps -ef|grep expdp

oracle 28442 28440 0 09:49 pts/0 00:00:00 grep expdp

2、确定外部表属于datapump job

set linesize 200 trimspool on

set pagesize 2000

col owner form a30

col created form a25

col last_ddl_time form a25

col object_name form a30

col object_type form a25

sql> select owner,object_name,object_type, status,

2 to_char(created,'dd-mon-yyyy hh24:mi:ss') created,

3 to_char(last_ddl_time, 'dd-mon-yyyy hh24:mi:ss') last_ddl_time

4 from dba_objects

5 where object_name like 'et$%';

owner object_name object_type status created last_ddl_time

------------------------------ ------------------------------ ------------------------- ------- ------------------------- -------------------------

expdp et$01d400370001 table valid 09-oct-2015 23:11:41 09-oct-2015 23:11:41

expdp et$01d3186f0001 table valid 18-dec-2015 02:20:04 18-dec-2015 02:20:04

expdp et$01c303530001 table valid 15-mar-2015 00:45:46 15-mar-2015 00:45:46

expdp et$01b7017f0001 table valid 15-mar-2015 00:42:03 15-mar-2015 00:42:03

expdp et$01982c090001 table valid 08-oct-2015 23:51:06 08-oct-2015 23:51:06

expdp et$00251aef0001 table valid 29-dec-2015 00:50:20 29-dec-2015 00:50:20

6 rows selected.

sql> select owner, table_name, default_directory_name, access_type

from dba_external_tables

order by 1,2;

owner table_name default_directory_name access_

------------------------------ ------------------------------ ------------------------------ -------

expdp et$00251aef0001 expdata clob

expdp et$01982c090001 expdata clob

expdp et$01b7017f0001 expdata clob

expdp et$01c303530001 expdata clob

expdp et$01d3186f0001 expdata clob

expdp et$01d400370001 expdata clob

sys alert_log_view bdump clob

--查询外部表，报的错误和alert日志一样

sql> select count(*) from expdp.et$00251aef0001;

select count(*) from expdp.et$00251aef0001

*

error at line 1:

ora-29913: error in executing odciexttableopen callout

kup-11024: this external table can only be accessed from within a data pump job.

sql>

--查看外部表定义

sql> desc et$00251aef0001

error:

ora-04043: object et$00251aef0001 does not exist

3、清理datapump jobs。

sql> select 'drop table '||owner||'.'||object_name||' purge ;' from dba_objects where object_name like 'et$%';

'droptable'||owner||'.'||object_name||'purge;'

----------------------------------------------------

drop table expdp.et$01d400370001 purge ;

drop table expdp.et$01d3186f0001 purge ;

drop table expdp.et$01c303530001 purge ;

drop table expdp.et$01b7017f0001 purge ;

drop table expdp.et$01982c090001 purge ;

drop table expdp.et$00251aef0001 purge ;

6 rows selected.

sql> drop table expdp.et$01d400370001 purge ;

drop table expdp.et$01d3186f0001 purge ;

drop table expdp.et$01c303530001 purge ;

drop table expdp.et$01b7017f0001 purge ;

drop table expdp.et$01982c090001 purge ;

drop table expdp.et$00251aef0001 purge ;

table dropped.

sql>

table dropped.

sql>

table dropped.

sql>

table dropped.

sql>

table dropped.

sql>

table dropped.