欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-03-30 11:30:58
2018年1月份第一次巡检中,某地市oracle告警ora-20011&kup-11024&ora-29913, 相关处理如下: 操作版本:rhel 2.6.39-400.17.1....

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: oracle@cis.db (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.