问题:
用户的expdp备份任务，不定期出现执行报错的情况，报错ORA-08103: object no longer exists
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [REF_CONSTRAINT:"OWNER"."FK_TABLENAME"]
ORA-08103: object no longer exists
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 9715
----- PL/SQL Call Stack -----
object line object
handle number name
0x103697dc58 21979 package body SYS.KUPW$WORKER
0x103697dc58 9742 package body SYS.KUPW$WORKER
0x103697dc58 11838 package body SYS.KUPW$WORKER
0x103697dc58 2808 package body SYS.KUPW$WORKER
0x103697dc58 10422 package body SYS.KUPW$WORKER
0x103697dc58 1824 package body SYS.KUPW$WORKER
0x1027151600 2 anonymous block
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月，不在报错发生的时间点
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
1 select constraint_name,constraint_type,status,table_name,owner,LAST_CHANGE
2 from dba_constraints
3 where constraint_name='FK_TABLENAME';
CONSTRAINT_NAME CON STATUS TABLE_NAME OWNER LAST_CHANGE
------------------------------ --- ------------------------ ------------------------------ ------------------------------ -------------------
FK_TABLENAME R ENABLED TABLENAME OWNER 2018-06-21 16:32:26
SQL>
1 select owner,object_name,CREATED,last_ddl_time
2 from dba_objects
3* where object_name='TABLENAME'
OWNER OBJECT_NAME CREATED LAST_DDL_TIME
---------------------------------------- ------------------------------ ------------------- -------------------
OWNER TABLENAME 2018-06-21 16:11:00 2018-06-25 14:40:29
SQL>由于报错的日志无法确认具体是哪个对象导致的，所以我们需要通过开启errorstack 定位到具体8103错误的操作语句对象
--使用sys用户在导出备份开始之前设置事件对ORA-8103发生错误时，dump出报错详细信息
alter system set events '8103 trace name errorstack level 3';
--报错结束之后，关闭8103事件
alter system set events '8103 trace name errorstack off';通过开启errorstack，在expdp再次发生报错时我们定位到了错误发生时的sql语句，从trace里面的执行sql语句里面我们提取了可能触发报错的表backupuser.backup_table_tmp
*** 2023-10-12 21:06:16.594
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-08103: object no longer exists
----- Current SQL Statement for this session (sql_id=2qw01kxr5vgh0) -----
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'
FROM SYS.KU$_TAB_STATS_VIEW KU$
WHERE NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0 AND KU$.OBJ_NUM IN (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(200001)))
AND NOT (KU$.BASE_OBJ.NAME IN(select distinct segment_name from backupuser.BACKUP_TABLE_TMP))
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x1044c4e078 3665 package body SYS.DBMS_METADATA
0x1044c4e078 4269 package body SYS.DBMS_METADATA
0x1044c4e078 4581 package body SYS.DBMS_METADATA
0x1044c4e078 8160 package body SYS.DBMS_METADATA
0x103697dc58 11566 package body SYS.KUPW$WORKER
0x103697dc58 2808 package body SYS.KUPW$WORKER
0x103697dc58 10422 package body SYS.KUPW$WORKER
0x103697dc58 1824 package body SYS.KUPW$WORKER
0x1027151600 2 anonymous block
object line object通过logmnr对报错时间点归档日志进行挖掘，确认表是否发生了DDL操作
--查询问题时间点涉及的归档日志
1 select name,FIRST_TIME,NEXT_TIME
2 from v$archived_log
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')
4* order by 3
NAME FIRST_TIME NEXT_TIME
---------------------------------------------------------------------------------------------------- ------------------- -------------------
+DATA2/xxdb/archivelog/2023_10_12/thread_2_seq_1173870.1779.1150059883 2023-10-12 21:01:45 2023-10-12 21:04:42
+DATA2/xxdb/archivelog/2023_10_12/thread_1_seq_1051584.3520.1150059963 2023-10-12 21:02:39 2023-10-12 21:06:03
+DATA2/xxdb/archivelog/2023_10_12/thread_2_seq_1173871.1649.1150060057 2023-10-12 21:04:42 2023-10-12 21:07:37
+DATA2/xxdb/archivelog/2023_10_12/thread_1_seq_1051585.1715.1150060161 2023-10-12 21:06:03 2023-10-12 21:09:21
+DATA2/xxdb/archivelog/2023_10_12/thread_2_seq_1173872.654.1150060229 2023-10-12 21:07:37 2023-10-12 21:10:28
+DATA2/xxdb/archivelog/2023_10_12/thread_1_seq_1051586.1560.1150060361 2023-10-12 21:09:21 2023-10-12 21:12:40
--进行logmnr挖掘
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '+DATA2/xxdb/archivelog/2023_10_12/thread_2_seq_1173870.1779.1150059883', OPTIONS => DBMS_LOGMNR.NEW);
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '+DATA2/xxdb/archivelog/2023_10_12/thread_1_seq_1051584.3520.1150059963', OPTIONS => DBMS_LOGMNR.ADDFILE);
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);在日志里面，发现了backupuser.backup_table_tmp在问题时间点确实发生了DDL：truncate table BACKUP_TABLE_TMP的操作
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> SELECT TIMESTAMP,
OPERATION,
SQL_REDO
FROM V$LOGMNR_CONTENTS
WHERE username IN ('backupuser');
TIMESTAMP OPERATION SQL_REDO
------------------- -------------------- --------------------------------------------------------------------------------
2023-10-12 21:05:16 DDL truncate table BACKUP_TABLE_TMP;
2023-10-12 21:05:16 INSERT insert into "backupuser"."BACKUP_TABLE_TMP"("OWNER","SEGMENT_NAME","PARTITIO
N_NAME","BYTES") values ('TESTUSER','XXX_PT_LOG','-1','-1');问题解决:
跟用户进一步确认表的DDL操作逻辑，用户反馈表是备份作业任务的配置表，在备份开始后会truncate表重新插入要备份的配置信息，根据这个操作流程，想要触发ORA-08103: object no longer exists的错误，需要备份程序在同一时间执行两个expdp备份作业，一查备份的日志果然在问题时间段有两个备份作业在执行
跟用户沟通，将两个备份作业配置在不同时间段执行，后面expdp报错不再发生，问题得以解决。
498
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
