<button id="kgao8"></button>
  • <td id="kgao8"><li id="kgao8"></li></td>
  • <td id="kgao8"><sup id="kgao8"></sup></td>
    <table id="kgao8"><li id="kgao8"></li></table>
    <table id="kgao8"><li id="kgao8"></li></table>
  • <table id="kgao8"></table>
  • <table id="kgao8"></table>
  • <table id="kgao8"><li id="kgao8"></li></table>
  • <li id="kgao8"></li>
    <table id="kgao8"><li id="kgao8"></li></table>
  • <table id="kgao8"><li id="kgao8"></li></table>
    <table id="kgao8"></table>
    13883183259 023-68037655 咨詢熱線:
    當前位置: 主頁 > 關于思莊 > 技術分享 >

    ORA-20011 ORA-29913 and ORA-29400 with Associated KUP-XXXXX

    發布時間:2021-10-10
     
    ORA-20011 ORA-29913 and ORA-29400 with Associated KUP-XXXXX Errors from DBMS_STATS.GATHER_STATS_JOB (Doc ID 1274653.1)
    APPLIES TO:

    Oracle Database - Standard Edition - Version 11.2.0.2 and later

    Oracle Database - Enterprise Edition - Version 11.2.0.2 and later

    Oracle Database - Personal Edition - Version 11.2.0.2 and later

    Oracle Database Cloud Schema Service - Version N/A and later

    Oracle Database Exadata Cloud Machine - Version N/A and later

    Information in this document applies to any platform.

    SYMPTOMS

    The key symptoms in these cases are typically the following:


    DBMS_STATS is being executed in some form

    ORA-20011, ORA-29913 and ORA-29400 Errors are seen, ORA-29913 specifying external function "ODCIEXTTABLEOPEN" signifying that there was an error in the callout by the external function "ODCIEXTTABLEOPEN " while trying to open an external table.

    Some specific examples follow:


    When attempting to run GATHER_STATS_JOB the following errors occur:


    ORA-20011: Approximate NDV failed:ORA-29913: error in executing ODCIEXTTABLEOPEN callout

    ORA-29400: data cartridge error



    usually accompanied with varying other errors such as:


    KUP-11024: This external table can only be accessed from within a Data Pump job



    and others.


    The alert log may also show following errors:


    Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x5400000000] [PC:0xFFFFFFFF7CB19EFC, memcpy%sun4u-opl()+236] [flags: 0x0, count: 1]

    ERROR: Unable to normalize symbol name for the following short stack (at offset 200):

    dbgexProcessError()+176<-dbgePostErrorKGE()+1348<-dbkePostKGE_kgsf()+48<-kgeade()+




    NOTE: With cases where the file was not found in the directory specified raising:


    KUP-04040: file %s in %s not found

    See:


    Document 150737.1 ORA-29913, ORA-29400, KUP-04040 While Selecting from External Table

    Document 1290722.1 "KUP-04040: file timezdif.csv in TIMEZDIF_DIR not found " logged by GATHER_STATS_JOB in alert.log after upgrade

    Document 1305127.1 KUP-04040: file TRCA_CONTROL.txt in TRCA$STAGE not found




    Alternatively you may get text in the trace file like:


    *** 2012-09-10 05:00:11.921

    DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"SH"','"SALES_TRANSACTIONS_EXT"','""', ...)

    DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

    ORA-29400: data cartridge error

    error opening file ../demo/schema/log/ext_1v3.log


    CHANGES



    CAUSE

    The primary cause of this issue is that an OS file for an "external table" existed at some point in time but does not now. However, the database still believes the OS file for 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 OS file for 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.


    SOLUTION

    Essentially the solution to this issue is to clean up the orphaned dictionary entries. Different solutions are appropriate dependent on the exact nature of the primary cause and these are outlined below:


    Temporary Datapump External Table

    Ensure that there are no DataPump jobs running at the same time as the DBMS_STATS job (this is to avoid any potential complications associated with cleaning up at the same time as something else is running).

    Check and clean up orphaned DataPump jobs. The following article addresses a case where DBMS_WORKLOAD_CAPTURE does not drop external tables (causing ORA-20011 from DBMS_STATS):


    Document 10327346.8 Bug 10327346 - DBMS_WORKLOAD_CAPTURE does not drop external tables (causing ORA-20011 from DBMS_STATS)


    Both above steps can be done by following Document:


    Document 336014.1 How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ?

    Identify external tables. To do this, Run the following as SYSDBA in SQL*Plus


    spool obj.out

    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


    select OWNER,OBJECT_NAME,OBJECT_TYPE, status,

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

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

    from dba_objects

    where object_name like 'ET$%'

    /


    select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE

    from dba_external_tables

    order by 1,2

    /


    spool off


    Correlate the information from DBA_OBJECTS and DBA_EXTERNAL TABLES above to identify the temporary external tables that belong to the DataPump.

    Drop the temporary external tables that belong to the DataPump. eg:


    SQL> drop table system.&1 purge;

    Enter value for 1: ET$00654E1E0001

    old 1: drop table system.&1 purge

    new 1: drop table system.ET$00654E1E0001 purge

    Other External Table

    With cases where specific External tables (be they Demo Schema tables or other tables) are missing, the process for handling them is much the same and can be resolved by following the procedures below. For example, if the additional error is 'error opening file ../demo/schema/log/ext_1v3.log', then this indicates that there is a problem opening or locating the log file from the demo schema directory. The implication is that the demo tables have not been cleared up correctly:


    Try to locate the files for these tables in their directory.


    To confirm if a file is present at the expected location for the external file, the following query might be helpful.

    undefine owner

    undefine table_pattern


    select el.table_name, el.owner, dir.directory_path||'/'||dir.directory_name "path"

    from dba_external_locations el

    , dba_directories dir

    where el.table_name like '%&&table_pattern%'

    and el.owner like '%&&owner%'

    and el.directory_owner = dir.owner

    and el.directory_name = dir.directory_name

    order by 1, 2;


    It may be that the files still exist but they have just been renamed or re-located. If that is the case you can correct the location to avoid the problem. If the file has been removed then follow either the following steps:


    Lock the statistics on these tables by using the following command:

    DBMS_STATS.LOCK_TABLE_STATS ('ownname','tabname');

    (This step prevents DBMS_STATS from gathering against the missing table)


    AND/OR


    Remove the dictionary object for the external table.  For example if the os file for the "SALES_TRANSACTIONS_EXT" Demo table is missing you would use :

    DROP TABLE SALES_TRANSACTIONS_EXT;





    REFERENCES

    NOTE:336014.1 - How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ?

    NOTE:150737.1 - ORA-29913, ORA-29400, KUP-04040 While Selecting from External Table

    BUG:9387797 - ORA-20011 / ORA-1870 EXECUTING GATHER_FIXED_OBJECTS_STATS AFTER UPGRADE TO 11.2


    NOTE:1305127.1 - KUP-04040: file TRCA_CONTROL.txt in TRCA$STAGE not found

    NOTE:1290722.1 - "KUP-04040: file timezdif.csv in TIMEZDIF_DIR not found " Logged by GATHER_STATS_JOB in Alert.log After Upgrade

     

     
    ?
    聯系我們 重慶思莊科技有限公司
    023-68037655
    重慶思莊科技有限公司 版權所有? 備案號:渝ICP備12004239號-4

    渝公網安備 50010702502184號

    <<