客户的测试环境已经从10g升级到11g了。但是没过几天，数据hang住了，登都登不了了，而且通过sys，system，普通用户连接的错误都不一样
SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 13 14:51:25 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
--使用system连接，显示是oracle不在状态，这和standby物理备库在apply的时候连接进来的情况类似，但是这个库压根没用dataguard。
SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 13 14:49:17 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ORA-01033: ORACLE initialization or shutdown in progress
Session ID: 0 Serial number: 0
SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 13 14:52:51 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Session ID: 0 Serial number: 0
我查看alert日志的时候就发现了如下的错误在日志中反复出现。
ORA-20011 ORA-29913 and ORA-29400 , KUP-XXXXX Errors
-----------------from alert log------------------------
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file.
Errors in file /dbccbsPT1/oracle/xxxx/oradmp/bdump/diag/rdbms/xxxx/xxxx/trace/xxxx_j000_17725.trc:
ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11010: unable to open at least one dump file for fetch
Errors in file /dbccbsPT1/oracle/xxxx/oradmp/bdump/diag/rdbms/xxxx/xxxx/trace/xxxx_j002_17729.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_SA_SPC_SY_133"
ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes
ORA-06512: at "SYS.DBMS_ADVISOR", line 201
ORA-06512: at "SYS.DBMS_SPACE", line 2465
ORA-06512: at "SYS.DBMS_SPACE", line 2538
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter
Starting background process VKRM
VKRM started with pid=31, OS id=17443
*** 2013-08-13 22:00:24.222
*** SESSION ID:(6238.93) 2013-08-13 22:00:24.222
*** CLIENT ID:() 2013-08-13 22:00:24.222
*** SERVICE NAME:(SYS$USERS) 2013-08-13 22:00:24.222
*** MODULE NAME:(DBMS_SCHEDULER) 2013-08-13 22:00:24.222
*** ACTION NAME:(ORA$AT_OS_OPT_SY_132) 2013-08-13 22:00:24.222
ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11010: unable to open at least one dump file for fetch
*** 2013-08-13 22:00:24.230
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"SYSTEM"','"TEST_TABLE_TARGET_EXT"','""', ...)
DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11010: unable to open at least one dump file for fetch
*** 2013-08-13 22:00:24.252
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"SYSTEM"','"TEST_TABLE_SOURCE_EXT"','""', ...)
DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11010: unable to open at least one dump file for fetch
这个问题很容易和其他外部的原因联系起来，首先是实例不可用的问题，想要查问题，连都连不进去。 本来打算使用Hanganalyze来分析一下。但sqlplus连不进去，
sqlplus -prelim /nolog之后再connect / as sysdba也不行 最后和Unix team的人沟通了一下，他们有完整的备份， 我记得前几天storage好像有问题了，和他们的人确认了下，他们最后发现时storage的问题，及时的修复了。
实例可以连上了。查看alert还是发现会有如上alert里面的ora 错误，我就有点凌乱了。从metalink上可以看到这个问题很可能是datapump相关的问题导致的
排除了job中有datapump相关的job，注意力集中在了外部表上
SQL> set linesize 200 trimspool on
SQL> col created form. a25
SQL> col last_ddl_time form. a25
SQL> col object_name form. a30
SQL> 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
5 where object_name like 'ET$%'
SQL> select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
2 from dba_external_tables
OWNER TABLE_NAME DEFAULT_DIRECTORY_NAME ACCESS_
------------------------------ ------------------------------ ------------------------------ -------
SYSTEM TEST_TABLE_SOURCE_EXT DATA_PUMP_DIR CLOB
SYSTEM TEST_TABLE_TARGET_EXT DATA_PUMP_DIR CLOB
SQL> select count(*)from TEST_TABLE_SOURCE_EXT;
select count(*)from TEST_TABLE_SOURCE_EXT
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11010: unable to open at least one dump file for fetch
发现 报错正式alert中的错误，仔细分析了下，是因为我的这个外部表的directory当时是用了系统中默认的data_pump_dir，升级以后ORACLE_HOME改变了，所以原本的dump所在的目录就不可用了。
因为那两个外部表是之前临时抽取数据用的，所以可以删掉了。
SQL> drop table TEST_TABLE_SOURCE_EXT;
SQL> drop table TEST_TABLE_TARGET_EXT;
隐患排除了，alert再没有报这个错。一切正常了。
metalink中对于这个问题的原因描述如下：
Doc ID 1274653.1 CAUSE
The primary cause of this issue is that an external table existed at some point in time but does not now. However, the database still believes the table exists since the dictionary information about the object has not been modified to reflect the change. When DBMS_STATS is run against the table in question, it makes a call out to the external table which fails because the object is not there.
There are many reasons that an external table may not exist including:
-
Temporary Datapump external tables have not been cleaned up properly. The dictionary information should have been dropped when the DataPump jobs completed.
-
An External table has been removed without clearing up the corresponding data dictionary information. For example: Oracle Demo Schema Tables such as the external table “SALES_TRANSACTIONS_EXT” may have been removed but the dictionary has not been updated to reflect this. The "SALES_TRANSACTIONS_EXT" table is an external table in the "SH" schema which is one of Demo Schema provided by Oracle.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-768445/，如需转载，请注明出处，否则将追究法律责任。