Some indexes or index [sub]partitions of table SYS.WRH$_SQLSTAT have been marked unusable

Admin — Thu, 26 Mar 2009 01:24:26 GMT

Out of nowhere we start seeing a bunch of messages in the alert log:

Tue Mar 24 22:02:34 2009
Some indexes or index [sub]partitions of table SYS.WRH$_SQLSTAT have been marked unusable
Tue Mar 24 22:04:42 2009
Completed checkpoint up to RBA [0x2d83.2.10], SCN: 10386207811326
Tue Mar 24 22:29:46 2009
Incremental checkpoint up to RBA [0x2d83.482eef.0], current log tail at RBA [0x2d83.51930f.0]
Tue Mar 24 23:00:12 2009
Incremental checkpoint up to RBA [0x2d83.727e0d.0], current log tail at RBA [0x2d83.762401.0]
Tue Mar 24 23:09:52 2009
Beginning log switch checkpoint up to RBA [0x2d84.2.10], SCN: 10386208010755
Thread 1 advanced to log sequence 11652

Then soon after:

Wed Mar 25 22:45:07 2009
Errors in file /opt/dbms/app/oracle/admin/PROD200P/udump/prod200p_ora_28403.trc:
ORA-00600: internal error code, arguments: [kcbnew_3], [0], [8], [195539], [], [], [], []
Wed Mar 25 22:48:06 2009
statement in resumable session 'User QBISOP(146), Session 188, Instance 1' was aborted
Wed Mar 25 22:51:03 2009
Errors in file /opt/dbms/app/oracle/admin/PROD200P/udump/prod200p_ora_28309.trc:
ORA-00600: internal error code, arguments: [kcbnew_3], [0], [8], [195534], [], [], [], []
Wed Mar 25 22:51:05 2009

As per the metalink doc id 430446.1 following are the solutions that are possible

These messages should be considered a warning and not something harmful to the database.

SOLUTION:
1) This message can be ignored as it is considered as a warning.

OR

2) Disable MMON from gathering stats:

dbms_workload_repository.modify_snapshot_settings(interval=>0);

OR

3) Partition the offending table WRH$_SQLSTAT into sysaux tablespace.

Metalink link: 430446.1

Symptoms

Lots of messages in alert log file like:

Mon Apr 23 23:25:40 2007
Some indexes or index [sub]partitions of table SYS.WRH$_SQLSTAT have been marked unusable
Tue Apr 24 01:26:11 2007

Cause

These messages are generated when AWR runs a space management routine in which AWR tables are moved out from the system tablespace to sysaux tablespace.

For some AWR tables, just after table is moved, indexes are marked "unusable". Thats when these messaged are generated.

Solution
These messages should be considered a warning and not something harmful to the database.
SOLUTION:
1) This message can be ignored as it is considered as a warning.
OR

2) Disable MMON from gathering stats:
dbms_workload_repository.modify_snapshot_settings(interval=>0);

OR
3) Partition the offending table WRH$_SQLSTAT into sysaux tablespace.

Hope this helps.