ORA-08102: index key not found
Issue:
In my alert log file i found several errors like below ones,
Sun Aug 20 06:22:57 2017
Errors in file /oracleSID/diag/rdbms/SID/SID/trace/SID_j000_17106.trc:
ORA-01114: IO error writing block to file (block # )
Sun Aug 20 09:00:01 2017
Errors in file /oracleSID/diag/rdbms/SID/SID/trace/SID_j000_18872.trc:
ORA-01114: IO error writing block to file (block # )
Errors in file /oracleSID/diag/rdbms/SID/SID/trace/SID_j000_18872.trc:
ORA-12012: error on auto execute of job
"SYS"."BSLN_MAINTAIN_STATS_JOB"
ORA-01114: IO error writing block to file (block # )
ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073
ORA-06512: at line 1
Sun Aug 20 11:15:42 2017
Thread 1 cannot allocate new log, sequence 12922
Wed Aug 23 22:14:22 2017
Errors in file /oracleSID/diag/rdbms/SID/SID/trace/SID_m001_26283.trc:
Wed Aug 23 22:14:27 2017
Dumping diagnostic data in directory=[cdmp_20170823221427],
requested by (instance=1, osid=26283 (M001)), summary=[abnormal process
termination].
Wed Aug 23 22:14:34 2017
Errors in file /oracleSID/diag/rdbms/SID/SID/trace/SID_m001_26283.trc:
Dumping diagnostic data in directory=[cdmp_20170823221436],
requested by (instance=1, osid=26283 (M001)), summary=[abnormal process
termination].
Wed Aug 23 22:14:38 2017
Thread 1 advanced to log sequence 12954 (LGWR switch)
Current log# 2 seq#
12954 mem# 0: /oracleSID/SID/origlog/redo02_1.log
from the trace file we found below details:
----------------------------------------------
*** KEWROCISTMTEXEC - encountered error: (ORA-08102: index
key not found, obj# 6550, file 2, block 71674 (2)
)
*** SQLSTR:
total-len=356, dump-len=240,
STR={delete from
WRH$_TABLESPACE_SPACE_USAGE tab where (:beg_snap <= tab.snap_id and tab.snap_id <= :end_snap and dbid = :dbid) and not exists (select 1 from WRM$_BASELINE
b where (tab.dbid =
b.dbid) and }
kewrpanp - Failed to purge non-partitioned table, tbid=53,
errcode=13509
DDE rules only execution for: ORA 12751
SOLUTION:
After looking at ORA error:
[oraSID@eul2534 ~]$ oerr ora 12751
12751, 00000, "cpu time or run time policy
violation"
// *Document: NO
// *Cause: A piece of code ran longer than it is supposed to
// *Action: If this error persists, contact Oracle Support
Services.
[oraSID@eul2534 ~]$
[oraSID@eul2534 ~]$
[oraSID@eul2534 ~]$ oerr ora 8102
08102, 00000, "index key not found, obj# %s, file %s,
block %s (%s)"
// *Cause: Internal
error: possible inconsistency in index
// *Action: Send
trace file to your customer support representative, along
// with
information on reproducing the error
[oraSID@eul2534 ~]$
It was clear that object related to block 71674 of file 2 is
not accessible so to resolve this we needs to find the object related to this
and rebuild or reorg that object:
Provided object id is 6550 (from error):
SQL> select
OBJECT_ID,OBJECT_NAME,OWNER,STATUS,OBJECT_TYPE from dba_objects where
OBJECT_ID=6550;
OBJECT_ID
OBJECT_NAME OWNER STATUS
OBJECT_TYPE
---------- ------------------------------ ---------- -------
-------------------
6550
WRH$_TS_SPACE_USAGE_IND SYS VALID
INDEX
______Here we need to rebuild this INDEX so that it becomes
accessible again._______
SQL> alter index WRH$_SYSMETRIC_HISTORY_INDEX rebuild
online;
alter index WRH$_SYSMETRIC_HISTORY_INDEX rebuild online
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01114: IO error writing block to file (block # )
ORA-01114: IO error writing block to file 201 (block #
414173)
ORA-27072: File I/O error
Additional information: 4
Additional information: 414173
Additional information: 221184
Process ID: 64559
Session ID: 1 Serial number: 8681
Here it is clear that the file under which it is trying to
write is full.
But you might confuse with "file 201 (block # 414173)" by understanding it as a
datafile with file_id 201.
If the value of your
init.ora parameter "db_files" is 200 then file#201 is actually
The first tempfile. You
would not see it in v$datafile (dba_data_files) but v$tempfile.
It is most likely
that the filesystem holding your tempfile is full.
For example if the
filesystem is something like a NetApp filer which keeps blocks
For snapshot space,
it prevents further writes when it is 100% full as it is no longer
Able to maintain
updated blocks.
SO after checking we found: FS holding our tempfile was
full, so we added another tempfile in our temporary tablespace and retried it.
Now it completed successfully.
Comments
Post a Comment