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)

ORA-31693 ORA-02354: error in exporting/importing data ORA-01555: snapshot too old

Error:

ORA-31693: Table data object "MIS"."MASTER_MISDB" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 4 with name "_SYSSMU4_4250244621$" too small


[oracle@said]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 26 10:15:18 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> show parameter undo

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled      boolean FALSE
undo_management      string AUTO
undo_retention      integer 7200
undo_tablespace      string UNDOTBS1
SQL>

SQL> select max(maxquerylen) from v$undostat;

MAX(MAXQUERYLEN)
----------------
  204662
 
Solution:
 
1. Modify the current UNDO_RETENTION for the database:
 

SQL> ALTER SYSTEM SET UNDO_RETENTION = 205000 scope=both sid='*';

SQL> show parameter undo

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled      boolean FALSE
undo_management      string AUTO
undo_retention      integer 205000
undo_tablespace      string UNDOTBS1

2. Perform the export again.

Step by Step Enable Archive Log in Oracle 12c

SQL> archive log list
Database log mode        No Archive Mode
Automatic archival        Disabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     244249
Current log sequence

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> show parameter db_name

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name      string miscdb
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1.0100E+10 bytes
Fixed Size     3721224 bytes
Variable Size 1778386936 bytes
Database Buffers 8287944704 bytes
Redo Buffers    29831168 bytes
Database mounted.
SQL>


Space Assign for Fast Recovery Area:

SQL> alter system set db_recovery_file_dest_size = 200G;

System altered.

Choose the destination:

SQL> alter system set db_recovery_file_dest='/u02/app/oracle/FRA/miscdb';

System altered.

SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL>

SQL> alter database open;

Database altered.

SQL> show parameter recovery

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest      string /u02/app/oracle/FRA/miscdb
db_recovery_file_dest_size      big integer 200G
recovery_parallelism      integer 0
SQL>