首页 » ORACLE [C]系列, ORACLE 9i-23c » Troubleshooting ORA-600 [kkdlfjou_1] after index rebuild online session killed

Troubleshooting ORA-600 [kkdlfjou_1] after index rebuild online session killed

希望是龙年春节前的最后一个故障, 一客户Oracle 19c RAC环境中,做index rebuild online操作进行过程中,kill重建索引的会话,后面查询索引相关的表提示如下报错:ORA-00600: internal error code, arguments: [kkdlfjou_1], [], [], [], [], [], [], [], [], [], [], []

kkdlfjou_1可能是Kernel Compile Dictionary Lookup Find  JOUrnal table

取trace中的调用Stack Trace:

dbgdaExecuteAction()+354<-dbgerRunAction()+83<-dbgerRunActions()+1352<-dbgexPhaseII()+549<-dbgexProcessError()
+1867<-dbgePostErrorKGE()+1853<-dbkePostKGE_kgsf()+71<-kgeadse()+447<-kgerinv_internal()+44
<-kgerinv()+40<-kgeasnmierr()+146<-kkdlFindIotTopCkyph()+512<-kkdlfjou()+146
<-kkdl1ck()+1832<-kkdlack()+90<-kkmfcbbt()+221

之前在2021APACOUC活动中,我分享过一次oracle online操作的主题,online index rebuild会创建一个Journal table日志表(IOT),报错的原因是oracle正在尝试查找online索引构建的日志表物理信息。

Mos Internal Error ORA-600 [kkdlfjou_1] explained (Doc ID 2999697.1)中能找到一篇关于该报错的描述,通常发生的情况是ONLINE INDEX REBUILD进程由于某种原因中止,影响是19.16以后,这们这的环境是19.10 RU。系统仍然认为ALTER INDEX REBUILD ONLINE进程处于活动状态,没有进行清理。所以在ind$表中仍然设置了一个标志需要清理,之前在《ORA-08104 when online index rebuild》记录过如果online rebuid index中如果中断没有清理flag标记,再次创建索引会提示ora-8104错误,而这个是在过程中SQL 解析式数据字典问题。 MoS中提到的解决方案是:

Reboot the database. As explained this is an Internal Data Dictionary table flag stuck prior to database cleanup of a stopped process.

重启数据库是一个较大的操作,可能会影响业务连续性,那除了重启是否有其它方法,以下方法可以尝试:

1, 建议可以尝试新建索引,删除原索引的方式,如果可能。

2,   查找当前是否有中间状态的索引

SQL> select obj# from sys.ind$ where bitand(flags, 512) = 512;
SQL> select OWNER,OBJECT_NAME,OBJECT_ID from dba_objects
where object_id =
(select obj# from sys.ind$ where bitand(flags, 512) = 512);
SQL> Select Object_Name, Object_Type
       From   Dba_Objects
       Where  Object_Name Like 'SYS_JOURNAL%';

rename table <SYS_JOURNAL_nnnnn table> to xx_bak;  -- or drop --

3, 手动清理online rebuid 状态的index.

从oracle 12c 增加了一些新的JOBS:

PMO_DEFERRED_GIDX_MAINT_JOB   – deferred global index maintenance job
CLEANUP_NON_EXIST_OBJ   – scheduler job to remove non-existent objects
CLEANUP_ONLINE_IND_BUILD   – scheduler job to perform online index build cleanup
CLEANUP_TAB_IOT_PMO   – scheduler job to perform tab$ and tabpart$ cleanup
CLEANUP_TRANSIENT_TYPE   – scheduler job to cleanup transient types
CLEANUP_TRANSIENT_PKG   – scheduler job to cleanup cursor transient packages
CLEANUP_ONLINE_PMO   – scheduler job to perform online PMO cleanup
FILE_SIZE_UPD   – scheduler job to perform file size update
LOAD_OPATCH_INVENTORY   – scheduler job for Queryable Inventory

该JOB似乎是用于清理online ind_build Job,默认每1小时运行一次,

SQL> select job_name,JOB_ACTION from dba_scheduler_jobs
  2* where job_name like '%ONLINE%'

JOB_NAME                                 JOB_ACTION
---------------------------------------- ----------------------------------------------------------------------------------------------------
CLEANUP_ONLINE_IND_BUILD                 declare
                                                           myinterval number;
                                                         begin
                                                           myinterval := dbms_pdb.cleanup_task(2);
                                                           if myinterval <> 0 then
                                                             next_date := systimestamp +
                                                               numtodsinterval(myinterval, 'second');
                                                           end if;
                                                         end;




dbmspdb.sql

  function cleanup_task(task_id number)
    return number;

  -- NAME:
  -- cleanup_task - cleanup task previously done in SMON
  --
  -- DESCRIPTION:
  --   This procedure performs cleanup task previously done in SMON
  --
  -- PARAMETERS:
  -- task_id  - Task Id
  --
  -- RETURNS:
  -- 0 - if the next scheduled time for job does not need to be changed.
  -- N - if the next scheduled time for job should be N seconds from now
  --
  -- NOTE
  --   ************************************************************************
  --   ************************************************************************
  --   IMPORTANT, PLEASE READ!
  --   This procedure is not meant to be documented.  It is supplied for use by
  --   Oracle internal scripts only. Please talk to the package owners to
  --   determine if it is appropriate to use this procedure in your particular
  --   usage scenario.
  --   ************************************************************************

该job调用的dbms_pdb.cleanup_task属于内部函数,没有公开太多的内容,不过我们可以尝试手动调用该JOB.注意当Auto Scheduler Job“SYS CLEANUP_ONLINE_IND_BUILD”正在执行时,索引的已重建开始了,这导致错误消息:ORA-00054: resource busy and acquire with NOWAIT指定或超时过期,过会和重试即可。

— over —

打赏

,

目前这篇文章还没有评论(Rss)

我要评论