ORA-00604: error occurred at recursive SQL level 1 ORA-08102: index key not found, obj# 348, file 3, block 508 (2)

Following Error comes from alert log

Errors in file /u01/app/oracle/diag/rdbms/backupbaedw/backupedw/trace/backupedw_smon_33054.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 348, file 3, block 508 (2)
Wed Nov 28 13:33:23 2018
Dumping diagnostic data in directory=[cdmp_20181128133323], requested by (instance=1, osid=33054 (SMON)), summary=[abnormal process termination].
Wed Nov 28 13:38:24 2018
Errors in file /u01/app/oracle/diag/rdbms/backupbaedw/backupedw/trace/backupedw_smon_33054.trc:
Wed Nov 28 13:38:24 2018
Errors in file /u01/app/oracle/diag/rdbms/backupbaedw/backupedw/trace/backupedw_smon_33054.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 348, file 3, block 508 (2)
Wed Nov 28 13:38:24 2018
Dumping diagnostic data in directory=[cdmp_20181128133824], requested by (instance=1, osid=33054 (SMON)), summary=[abnormal process termination].
/u01/app/oracle/diag/rdbms/backupbaedw/backupedw/trace/backupedw_smon_33054.trc


Solution

[oracle@BACKUP-BAEDW ~]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 28 13:41:11 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> analyze table SMON_SCN_TIME validate structure;

Table analyzed.

SQL> Select index_name,index_type,owner from dba_indexes where table_name='SMON_SCN_TIME' ;

INDEX_NAME INDEX_TYPE OWNER
--------------------------------------------------------------------------------
SMON_SCN_TIME_SCN_IDX NORMAL SYS
SMON_SCN_TIME_TIM_IDX NORMAL SYS


SQL> Set long 10000000
SQL> Select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_TIM_IDX','SYS') FROM DUAL ;

DBMS_METADATA.GET_DDL('INDEX','SMON_SCN_TIME_TIM_IDX','SYS')
--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "SYS"."SMON_SCN_TIME_TIM_IDX" ON "SYS"."SMON_SCN_TIME" ("T
IME_MP")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX"


SQL>

SQL> Select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_SCN_IDX','SYS') FROM DUAL ;

DBMS_METADATA.GET_DDL('INDEX','SMON_SCN_TIME_SCN_IDX','SYS')
--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "SYS"."SMON_SCN_TIME_SCN_IDX" ON "SYS"."SMON_SCN_TIME" ("S
CN")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX"


SQL>

Once you have the required DDL for the Index.Drop the index


SQL> Drop index sys.SMON_SCN_TIME_TIM_IDX ;

Index dropped.

SQL> Drop index sys.SMON_SCN_TIME_SCN_IDX ;

Index dropped.

Re-run Analyze Command


SQL> Analyze table SMON_SCN_TIME validate structure cascade online ;

Table analyzed.

Re-run Analyze Command

SQL> Analyze table SMON_SCN_TIME validate structure cascade online ;

Table analyzed.

SQL>

Now check the alert log, hope the errors are gone!!!!!!!!!!!!!!!

Reference:  How to Resolve ORA-08102 Reported on Table SMON_SCN_TIME (Doc ID 978502.1)