联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
数据库alert日志出现ORA-20011 KUP-11024等错误
Thu Sep 22 18:00:31 2016 DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file. Errors in file /u1/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_j002_2686.trc: 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.
从报错的信息看应该是数据库收集统计信息报错(GATHER_STATS_JOB)，但是报错原因是由于访问外部表导致,而该外部表很可能和data pump有关系.
查看trace日志
[oracle@xifenfei]$ more /u1/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_j002_2686.trc
Trace file /u1/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_j002_2686.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u1/oracle/pruduct/11.2.0.3
System name: Linux
Node name: xifenfei
Release: 2.6.32-220.el6.x86_64
Version: #1 SMP Wed Nov 9 08:03:13 EST 2011
Machine: x86_64
Instance name: xifenfei
Redo thread mounted by this instance: 1
Oracle process number: 356
Unix process pid: 2686, image: oracle@xifenfei (J002)
*** 2016-09-22 18:00:31.939
*** SESSION ID:(835.16363) 2016-09-22 18:00:31.939
*** CLIENT ID:() 2016-09-22 18:00:31.939
*** SERVICE NAME:(SYS$USERS) 2016-09-22 18:00:31.939
*** MODULE NAME:(DBMS_SCHEDULER) 2016-09-22 18:00:31.939
*** ACTION NAME:(ORA$AT_OS_OPT_SY_10669) 2016-09-22 18:00:31.939
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.
*** 2016-09-22 18:00:31.939
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"DWDBA"','"ET$012D00070001"','""', ...)
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.
*** 2016-09-22 18:00:31.960
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"DWDBA"','"ET$01D10D4F0001"','""', ...)
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.
通过trace文件,我们已经可以明确是由于数据库对DWDBA.ET$012D00070001和DWDBA.ET$01D10D4F0001这两个表收集统计信息时候报的上述alert日志中看到的错误.
查询数据库记录
SYS@xifenfei>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$%' 6 / OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED LAST_DDL_TIME --------- ---------------- ------------ ------- ------------------------- ---------------- DWDBA ET$012D00070001 TABLE VALID 10-mar-2016 16:32:25 10-mar-2016 16:32:25 DWDBA ET$01D10D4F0001 TABLE VALID 10-mar-2016 17:29:29 10-mar-2016 17:29:29 SYS@xifenfei> select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE 2 from dba_external_tables 3 order by 1,2 4 / OWNER TABLE_NAME DEFAULT_DIRECTORY_NAME ACCESS_ ----------- ------------------------------ ------------------------------ ------- DWDBA ET$012D00070001 EXP_FILE_DIR CLOB DWDBA ET$01D10D4F0001 EXP_FILE_DIR CLOB
到这一步,我们已经完全清楚,ET$012D00070001和ET$01D10D4F0001是两个外部表,由于他们的存在使得收集统计信息异常。
分析ET$012D00070001表
SYS@xifenfei>desc DWDBA.ET$012D00070001 Name Null? Type ----------------------------------------------------- -------- ------------------------------------ STORE_NO NUMBER(3) ITEM_NO NUMBER(6) WORK_DATE DATE DIVISION_NO NUMBER(2) SECTION_NO NUMBER(3) SUP_NO NUMBER(6) GRP_NO NUMBER(3) SUBGRP_NO NUMBER(3) USR VARCHAR2(30) TYPE NUMBER(1) ACTIVE_SELL_PRICE NUMBER(8,2) SELL_PRICE NUMBER(8,2) SELL_VAT NUMBER(1) BUY_PRICE NUMBER(10,4) BUY_VAT NUMBER(1) PROMOTION_NO NUMBER(10) PROM_CLASS VARCHAR2(1) PROM_LEVEL NUMBER(1) STOCK NUMBER(10,3) STOCK_ADJ NUMBER(10,3) RECPT NUMBER(10,3) SALES NUMBER(10,3) STOCK_ADJ_AMNT NUMBER(10,2) RECPT_AMNT NUMBER(10,2) SALES_AMNT NUMBER(10,2) PROF_AMNT NUMBER(10,2) COST_CHANGE NUMBER(10,2) DISC NUMBER(10,3) RTN_QTY NUMBER(9,3) DISC_AMNT NUMBER(10,2) RTN_AMNT NUMBER(10,2) LOSS_AMNT NUMBER(10,2) CREATED_DATE DATE COST NUMBER(10,4) NBR_PK NUMBER(5) NBR_VISIT NUMBER(5) NBR_PK_LINE NUMBER(5) N_N_PROF_AMNT NUMBER(9,2) CON_FORE NUMBER(10,2) CON_FORE_OTH NUMBER(10,2) SALES_B NUMBER(10,3) SALES_AMNT_B NUMBER(10,2) SYS@xifenfei>select count(*) from DWDBA.ET$012D00070001; select count(*) from DWDBA.ET$012D00070001 * 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.
通过对ET$012D00070001表查询重新了alert日志一样的错误,可以明确定位问题就是由于该外部表异常导致.通过查询mos,确定Bug 10327346 DBMS_WORKLOAD_CAPTURE does not drop external tables (causing ORA-20011 from DBMS_STATS)可能导致DBMS_WORKLOAD_CAPTURE无法正常清理掉Data pump的外部表导致出现Datapump出现孤立的外部表对象,从而出现该问题.解决方案就是直接drop 相关外部表.也就是这里的(ET$012D00070001和ET$01D10D4F0001)