• Oracle-expdp报错ORA-08103: object no longer exists

    问题:

            用户的expdp备份任务，不定期出现执行报错的情况，报错ORA-08103: object no longer exists

    1. Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
    2. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
    3. ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [REF_CONSTRAINT:"OWNER"."FK_TABLENAME"] 
    4. ORA-08103: object no longer exists
    5. ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
    6. ORA-06512: at "SYS.KUPW$WORKER", line 9715
    7. ----- PL/SQL Call Stack -----
    8.   object      line  object
    9.   handle    number  name
    10. 0x103697dc58     21979  package body SYS.KUPW$WORKER
    11. 0x103697dc58      9742  package body SYS.KUPW$WORKER
    12. 0x103697dc58     11838  package body SYS.KUPW$WORKER
    13. 0x103697dc58      2808  package body SYS.KUPW$WORKER
    14. 0x103697dc58     10422  package body SYS.KUPW$WORKER
    15. 0x103697dc58      1824  package body SYS.KUPW$WORKER
    16. 0x1027151600         2  anonymous block
    17. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

    问题分析:

            expdp导出期间发生ORA-08103: object no longer exists的错误，通常原因为导出任务操作的对象发生了DDL操作，引发object_data_id发生了改变，导致操作的对象object_data_id不一致，出现找不到对象的情况，按照这个思路对问题开始进行分析

            首先，每次报错日志显示的对象都是在外键约束对象REF_CONSTRAINT:"OWNER"."FK_TABLENAME"，所以检查这个外键约束的最近一次DDL时间以及约束所在表的最近一次DDL时间，都在2018年6月，不在报错发生的时间点

    1. ​​SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
    2. 1 select constraint_name,constraint_type,status,table_name,owner,LAST_CHANGE
    3. 2 from dba_constraints
    4. 3 where constraint_name='FK_TABLENAME';
    5. ​
    6. ​
    7. CONSTRAINT_NAME                CON STATUS                   TABLE_NAME                     OWNER                          LAST_CHANGE
    8. ------------------------------ --- ------------------------ ------------------------------ ------------------------------ -------------------
    9. FK_TABLENAME                R   ENABLED                      TABLENAME                     OWNER                    2018-06-21 16:32:26
    10. ​
    11. ​
    12. SQL> 
    13.   1  select owner,object_name,CREATED,last_ddl_time
    14.   2  from dba_objects
    15.   3* where object_name='TABLENAME'
    16. ​
    17. OWNER                                    OBJECT_NAME                    CREATED             LAST_DDL_TIME
    18. ---------------------------------------- ------------------------------ ------------------- -------------------
    19. OWNER                                    TABLENAME                   2018-06-21 16:11:00 2018-06-25 14:40:29
    20. ​
    21. SQL>

            由于报错的日志无法确认具体是哪个对象导致的，所以我们需要通过开启errorstack 定位到具体8103错误的操作语句对象

    1. --使用sys用户在导出备份开始之前设置事件对ORA-8103发生错误时，dump出报错详细信息
    2. alter system set events '8103 trace name errorstack level 3';
    3. --报错结束之后，关闭8103事件
    4. alter system set events '8103 trace name errorstack off';

            通过开启errorstack，在expdp再次发生报错时我们定位到了错误发生时的sql语句，从trace里面的执行sql语句里面我们提取了可能触发报错的表backupuser.backup_table_tmp

    1. *** 2023-10-12 21:06:16.594
    2. dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
    3. ----- Error Stack Dump -----
    4. ORA-08103: object no longer exists
    5. ----- Current SQL Statement for this session (sql_id=2qw01kxr5vgh0) -----
    6. SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('T_STAT_T', '7')), 0 ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,'TABLE_STATISTICS' 
    7. FROM SYS.KU$_TAB_STATS_VIEW KU$ 
    8. WHERE NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0 AND   KU$.OBJ_NUM IN (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(200001))) 
    9. AND  NOT (KU$.BASE_OBJ.NAME  IN(select distinct segment_name from  backupuser.BACKUP_TABLE_TMP))
    10. ----- PL/SQL Stack -----
    11. ----- PL/SQL Call Stack -----
    12.   object      line  object
    13.   handle    number  name
    14. 0x1044c4e078      3665  package body SYS.DBMS_METADATA
    15. 0x1044c4e078      4269  package body SYS.DBMS_METADATA
    16. 0x1044c4e078      4581  package body SYS.DBMS_METADATA
    17. 0x1044c4e078      8160  package body SYS.DBMS_METADATA
    18. 0x103697dc58     11566  package body SYS.KUPW$WORKER
    19. 0x103697dc58      2808  package body SYS.KUPW$WORKER
    20. 0x103697dc58     10422  package body SYS.KUPW$WORKER
    21. 0x103697dc58      1824  package body SYS.KUPW$WORKER
    22. 0x1027151600         2  anonymous block
    23.   object      line  object

            通过logmnr对报错时间点归档日志进行挖掘，确认表是否发生了DDL操作

    1. --查询问题时间点涉及的归档日志
    2.   1  select name,FIRST_TIME,NEXT_TIME
    3.   2  from v$archived_log
    4.   3  where FIRST_TIME between to_date('2023-10-12 21:00:00','yyyy-mm-dd hh24::mi:ss') and to_date('2023-10-12 21:10:00','yyyy-mm-dd hh24:mi:ss')
    5.   4* order by 3
    6. ​
    7. NAME                                                                                                 FIRST_TIME          NEXT_TIME
    8. ---------------------------------------------------------------------------------------------------- ------------------- -------------------
    9. +DATA2/xxdb/archivelog/2023_10_12/thread_2_seq_1173870.1779.1150059883                               2023-10-12 21:01:45 2023-10-12 21:04:42
    10. +DATA2/xxdb/archivelog/2023_10_12/thread_1_seq_1051584.3520.1150059963                               2023-10-12 21:02:39 2023-10-12 21:06:03
    11. +DATA2/xxdb/archivelog/2023_10_12/thread_2_seq_1173871.1649.1150060057                               2023-10-12 21:04:42 2023-10-12 21:07:37
    12. +DATA2/xxdb/archivelog/2023_10_12/thread_1_seq_1051585.1715.1150060161                               2023-10-12 21:06:03 2023-10-12 21:09:21
    13. +DATA2/xxdb/archivelog/2023_10_12/thread_2_seq_1173872.654.1150060229                                2023-10-12 21:07:37 2023-10-12 21:10:28
    14. +DATA2/xxdb/archivelog/2023_10_12/thread_1_seq_1051586.1560.1150060361                               2023-10-12 21:09:21 2023-10-12 21:12:40
    15. ​
    16. --进行logmnr挖掘
    17. SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '+DATA2/xxdb/archivelog/2023_10_12/thread_2_seq_1173870.1779.1150059883', OPTIONS => DBMS_LOGMNR.NEW);
    18. SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '+DATA2/xxdb/archivelog/2023_10_12/thread_1_seq_1051584.3520.1150059963', OPTIONS => DBMS_LOGMNR.ADDFILE);
    19. SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

            在日志里面，发现了backupuser.backup_table_tmp在问题时间点确实发生了DDL：truncate table BACKUP_TABLE_TMP的操作

    1. alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
    2. SQL> SELECT TIMESTAMP,
    3.    OPERATION,
    4.      SQL_REDO
    5.      FROM V$LOGMNR_CONTENTS 
    6.      WHERE username IN ('backupuser');
    7. TIMESTAMP           OPERATION            SQL_REDO
    8. ------------------- -------------------- --------------------------------------------------------------------------------
    9. 2023-10-12 21:05:16 DDL                  truncate table BACKUP_TABLE_TMP;
    10. 2023-10-12 21:05:16 INSERT               insert into "backupuser"."BACKUP_TABLE_TMP"("OWNER","SEGMENT_NAME","PARTITIO
    11.                                          N_NAME","BYTES") values ('TESTUSER','XXX_PT_LOG','-1','-1');

    问题解决:

            跟用户进一步确认表的DDL操作逻辑，用户反馈表是备份作业任务的配置表，在备份开始后会truncate表重新插入要备份的配置信息，根据这个操作流程，想要触发ORA-08103: object no longer exists的错误，需要备份程序在同一时间执行两个expdp备份作业，一查备份的日志果然在问题时间段有两个备份作业在执行

            跟用户沟通，将两个备份作业配置在不同时间段执行，后面expdp报错不再发生，问题得以解决。

  • 相关阅读:
    简单介绍24种设计模式
    Swan学院社团招新
    Python实现ANN与KNN的图像分类
    Postman(2): postman发送带参数的GET请求
    统计学习方法P54中位数脚注
    JavaScript学习--Day04
    13.webpack4之语法检查eslint
    【Presto Profile系列】Timeline使用
    【阿旭机器学习实战】【13】决策树分类模型实战：泰坦尼克号生存预测
    C# 中的Async 和 Await 的用法详解
  • 原文地址：https://blog.csdn.net/sinat_36757755/article/details/133964086