Oracle Database Discussions

Hi,

need help on this problem if ever you encountered this before.
it's really killing our production system.\


what does this mean,ORA-01502 says that i have to rebuild an index
but i don't really know what index to rebuild?

has any of you encountered this before?
it's causing massive slowdown


thanks


Redo thread mounted by this instance: 1

Oracle process number: 26

Windows thread id: 3664, image: ORACLE.EXE (m000)


*** ACTION NAME:(Auto-Flush Slave Action) 2009-08-10 13:00:17.200
*** MODULE NAME:(MMON_SLAVE) 2009-08-10 13:00:17.200
*** SERVICE NAME:(SYS$BACKGROUND) 2009-08-10 13:00:17.200
*** SESSION ID:(504.4096) 2009-08-10 13:00:17.200
*** KEWROCISTMTEXEC - encountered error: (ORA-26026: unique index SYS.WRH$_ACTIVE_SESSION_HISTORY_PK initially in unusable state
)
*** SQLSTR: total-len=1479, dump-len=240,
STR={INSERT /*+ APPEND */ INTO WRH$_ACTIVE_SESSION_HISTORY ( snap_id, dbid, instance_number, sample_id, sample_time, session_id, session_serial#, user_id, sql_id, sql_child_number, sql_plan_hash_value, force_matching_signature, service_}
kewa_flusher: Exception handler 13509 - Will resignal!
*** KEWRAFM1: Error=13509 encountered by kewrfteh
*** KEWROCISTMTEXEC - encountered error: (ORA-01502: index 'SYS.WRH$_SEG_STAT_PK' or partition of such index is in unusable state
)
*** SQLSTR: total-len=1759, dump-len=240,
STR={insert into wrh$_seg_stat (snap_id, dbid, instance_number, ts#, obj#, dataobj#, logical_reads_total, logical_reads_delta, buffer_busy_waits_total, buffer_busy_waits_delta, db_block_changes_total, db_block_changes_delta, physical_rea}
*** KEWRAFM1: Error=13509 encountered by kewrfteh
*** KEWROCISTMTEXEC - encountered error: (ORA-01502: index 'SYS.WRH$_FILESTATXS_PK' or partition of such index is in unusable state
)
*** SQLSTR: total-len=581, dump-len=240,
STR={insert into wrh$_filestatxs (snap_id, dbid, instance_number, file#, creation_change#, phyrds, phywrts, singleblkrds, readtim, writetim, singleblkrdtim, phyblkrd, phyblkwrt, wait_count, time) select :snap_id, :dbid, :instance_num}
*** KEWRAFM1: Error=13509 encountered by kewrfteh
*** KEWROCISTMTEXEC - encountered error: (ORA-01502: index 'SYS.WRH$_SERVICE_STAT_PK' or partition of such index is in unusable state
)
*** SQLSTR: total-len=204, dump-len=204,
STR={insert into WRH$_SERVICE_STAT (snap_id, dbid, instance_number, service_name_hash, stat_id, value) select :snap_id, :dbid, :instance_number, service_name_hash, stat_id, value from v$service_stats}
*** KEWRAFM1: Error=13509 encountered by kewrfteh
*** KEWROCISTMTEXEC - encountered error: (ORA-01502: index 'SYS.WRH$_SERVICE_WAIT_CLASS_PK' or partition of such index is in unusable state
)
*** SQLSTR: total-len=294, dump-len=240,
STR={insert into WRH$_SERVICE_WAIT_CLASS (snap_id, dbid, instance_number, service_name_hash, wait_class_id, wait_class, total_waits, time_waited) select :snap_id, :dbid, :instance_number, service_name_hash, wait_class_id, wait_class,}
*** KEWRAFM1: Error=13509 encountered by kewrfteh
*** KEWROCISTMTEXEC - encountered error: (ORA-01502: index 'SYS.WRH$_SYSTEM_EVENT_PK' or partition of such index is in unusable state
)
*** SQLSTR: total-len=277, dump-len=240,
STR={insert into wrh$_system_event (snap_id, dbid, instance_number, event_id, total_waits, total_timeouts, time_waited_micro) select :snap_id, :dbid, :instance_number, event_id, total_waits, total_timeouts, time_waited_micro from }
*** KEWRAFM1: Error=13509 encountered by kewrfteh
*** KEWROCISTMTEXEC - encountered error: (ORA-01502: index 'SYS.WRH$_WAITSTAT_PK' or partition of such index is in unusable state
)
*** SQLSTR: total-len=191, dump-len=191,
STR={insert into wrh$_waitstat (snap_id, dbid, instance_number, class, wait_count, time) select :snap_id, :dbid, :instance_number, class, "COUNT", time from v$waitstat order by class}
*** KEWRAFM1: Error=13509 encountered by kewrfteh
*** KEWROCISTMTEXEC - encountered error: (ORA-01502: index 'SYS.WRH$_LATCH_PK' or partition of such index is in unusable state
)
*** SQLSTR: total-len=403, dump-len=240,
STR={insert into wrh$_latch (snap_id, dbid, instance_number, latch_hash, level#, gets, misses, sleeps, immediate_gets, immediate_misses, spin_gets, sleep1, sleep2, sleep3, sleep4, wait_time) select :snap_id, :dbid, :instance_number, }
*** KEWRAFM1: Error=13509 encountered by kewrfteh
*** KEWROCISTMTEXEC - encountered error: (ORA-01502: index 'SYS.WRH$_LATCH_MISSES_SUMMARY_PK' or partition of such index is in unusable state
)
*** SQLSTR: total-len=390, dump-len=240,
STR={insert into wrh$_latch_misses_summary (snap_id, dbid, instance_number, parent_name, where_in_code, nwfail_count, sleep_count, wtr_slp_count) select :snap_id, :dbid, :instance_number, parent_name, "WHERE", sum(nwfail_count), sum(}
*** KEWRAFM1: Error=13509 encountered by kewrfteh
*** KEWROCISTMTEXEC - encountered error: (ORA-01502: index 'SYS.WRH$_DB_CACHE_ADVICE_PK' or partition of such index is in unusable state
)
*** SQLSTR: total-len=550, dump-len=240,
STR={insert into wrh$_db_cache_advice (snap_id, dbid, instance_number, bpid, buffers_for_estimate, name, block_size, advice_status, size_for_estimate, size_factor, physical_reads, base_physical_reads, actual_physic}
*** KEWRAFM1: Error=13509 encountered by kewrfteh
*** KEWROCISTMTEXEC - encountered error: (ORA-01502: index 'SYS.WRH$_ROWCACHE_SUMMARY_PK' or partition of such index is in unusable state
)
*** SQLSTR: total-len=548, dump-len=240,
STR={insert into wrh$_rowcache_summary (snap_id, dbid, instance_number, parameter, total_usage, usage, gets, getmisses, scans, scanmisses, scancompletes, modifications, flushes, dlm_requests, dlm_conflicts, dlm_releases) select :snap}
*** KEWRAFM1: Error=13509 encountered by kewrfteh
*** KEWROCISTMTEXEC - encountered error: (ORA-01502: index 'SYS.WRH$_SGASTAT_U' or partition of such index is in unusable state
)
*** SQLSTR: total-len=370, dump-len=240,
STR={insert into wrh$_sgastat (snap_id, dbid, instance_number, pool, name, bytes) select :snap_id, :dbid, :instance_number, pool, name, bytes from (select pool, name, bytes, 100*(bytes) / (sum(bytes) over (partition by po}
*** KEWRAFM1: Error=13509 encountered by kewrfteh
*** KEWROCISTMTEXEC - encountered error: (ORA-01502: index 'SYS.WRH$_SYSSTAT_PK' or partition of such index is in unusable state
)
*** SQLSTR: total-len=176, dump-len=176,
STR={insert into wrh$_sysstat (snap_id, dbid, instance_number, stat_id, value) select :snap_id, :dbid, :instance_number, stat_id, value from v$sysstat order by stat_id}
*** KEWRAFM1: Error=13509 encountered by kewrfteh
*** KEWROCISTMTEXEC - encountered error: (ORA-01502: index 'SYS.WRH$_PARAMETER_PK' or partition of such index is in unusable state
)
*** SQLSTR: total-len=500, dump-len=240,
STR={insert into wrh$_parameter (snap_id, dbid, instance_number, parameter_hash, value, isdefault, ismodified) select :snap_id, :dbid, :instance_number, i.ksppihash hash, sv.ksppstvl, sv.ksppstdf, decode(bitand(sv.ksppstvf,7), 1, 'MO}
*** KEWRAFM1: Error=13509 encountered by kewrfteh
*** KEWROCISTMTEXEC - encountered error: (ORA-01502: index 'SYS.WRH$_TABLESPACE_STAT_PK' or partition of such index is in unusable state
)
*** SQLSTR: total-len=1107, dump-len=240,
STR={insert into wrh$_tablespace_stat (snap_id, dbid, instance_number, ts#, tsname, contents, status, segment_space_management, extent_management, is_backup) select :snap_id, :dbid, :instance_number, ts.ts#, ts.name as tsname, }
*** KEWRAFM1: Error=13509 encountered by kewrfteh
*** KEWROCISTMTEXEC - encountered error: (ORA-01502: index 'SYS.WRH$_OSSTAT_PK' or partition of such index is in unusable state
)
*** SQLSTR: total-len=155, dump-len=155,
STR={insert into wrh$_osstat (snap_id, dbid, instance_number, stat_id, value) select :snap_id, :dbid, :instance_number, osstat_id, value from v$osstat}
*** KEWRAFM1: Error=13509 encountered by kewrfteh
*** KEWROCISTMTEXEC - encountered error: (ORA-01502: index 'SYS.WRH$_SYS_TIME_MODEL_PK' or partition of such index is in unusable state
)
*** SQLSTR: total-len=190, dump-len=190,
STR={insert into wrh$_sys_time_model (snap_id, dbid, instance_number, stat_id, value) select :snap_id, :dbid, :instance_number, stat_id, value from v$sys_time_model order by stat_id}
*** KEWRAFM1: Error=13509 encountered by kewrfteh
*** 2009-08-10 13:00:22.513
*** KEWROCISTMTEXEC - encountered error: (ORA-01502: index 'SYS.WRH$_SQLSTAT_PK' or partition of such index is in unusable state
)
*** SQLSTR: total-len=2248, dump-len=240,
STR={insert into wrh$_sqlstat (snap_id, dbid, instance_number, sql_id, plan_hash_value, optimizer_cost, optimizer_mode, optimizer_env_hash_value, sharable_mem, loaded_versions, version_count, module, action, sql_profile, force_matchin}
*** KEWRAFM1: Error=13509 encountered by kewrfteh

Edited by: user6593769 on Sep 1, 2009 6:11 AM