Update 2016-04-26: this is new bug 25954054 – WRH$_SGASTAT_U BECOMING UNUSABLE STATE IN UPGRADED DB.
After upgrading our OMR and AWRW instances from 12.1 to 12.2, our monitoring revealed that index SYS.WRH$_SGASTAT_U was becoming unusable at regular intervals.
The first couple of times, we just rebuilt it in the hope that the problem would just go away(!); of course it didn’t, so I had to do a little bit of investigation.
Reviewing the alert log identified the exact time that the problem had occurred:
2017-04-12T08:11:43.674485+08:00 Some indexes or index [sub]partitions of table SYS.WRH$_SGASTAT have been marked unusable
Grepping for “SGASTAT” in the trace directory, also revealed that the index being unusable was causing inserts into AWR had been failing:
*** 2017-04-12T12:00:18.338061+08:00 *** SESSION ID:(289.8864) 2017-04-12T12:00:18.338092+08:00 *** CLIENT ID:() 2017-04-12T12:00:18.338103+08:00 *** SERVICE NAME:(SYS$BACKGROUND) 2017-04-12T12:00:18.338108+08:00 *** MODULE NAME:(MMON_SLAVE) 2017-04-12T12:00:18.338119+08:00 *** ACTION NAME:(Auto-Flush Slave Action) 2017-04-12T12:00:18.338125+08:00 *** CLIENT DRIVER:() 2017-04-12T12:00:18.338132+08:00 *** KEUUXS - encountered error: (ORA-01502: index 'SYS.WRH$_SGASTAT_U' or partition of such index is in unusable state ) *** SQLSTR: total-len=464, dump-len=464, STR={insert into wrh$_sgastat (dbid, per_pdb, con_dbid, snap_id, instance_number, pool, name, bytes) select distinct :dbid, 0 per_pdb, :srcdbid con_dbid, :snap_id, :instance_number, pool, name, bytes FROM (select con_id, pool, name, bytes, 100*(bytes) / (sum(bytes) over (partition by pool)) part_pct from v$sgastat ) sgastat WHERE (1 = 1) and part_pct >= 1 or pool is null or name = 'free memory' or name = 'row cache' } *** KEWRAFM1: Error=13509 encountered by kewrfteh
As we still had the redo logs for the period of time identified by the alert log, I fired up logminer, and right after a bunch of inserts into WRH$_SGASTAT, I found the following statement:
alter table WRH$_SGASTAT drop partition WRH$_SGASTA_1521710113_17057;
I am guessing this is part of the standard mechanism for purging AWR data based on configured retention settings.
A quick check reveals that of the partitioned AWR base tables, 3 have indexes that are not partitioned:
SQL> SELECT 2 partitioned, 3 COUNT(*) 4 FROM 5 dba_indexes 6 WHERE 7 index_type = 'NORMAL' 8 AND 9 table_name IN ( 10 SELECT 11 table_name 12 FROM 13 dba_tables 14 WHERE 15 table_name LIKE 'WRH$\_%' ESCAPE '\' 16 AND 17 partitioned = 'YES' 18 ) 19 GROUP BY 20 partitioned; PAR COUNT(*) --- ---------- NO 3 YES 35 SQL> SELECT 2 table_owner, 3 table_name, 4 index_name 5 FROM 6 dba_indexes 7 WHERE 8 index_type = 'NORMAL' 9 AND 10 table_name IN ( 11 SELECT 12 table_name 13 FROM 14 dba_tables 15 WHERE 16 table_name LIKE 'WRH$\_%' ESCAPE '\' 17 AND 18 partitioned = 'YES' 19 ) 20 AND partitioned = 'NO'; TABLE_OWNER TABLE_NAME INDEX_NAME ------------------------------ ------------------------------ ------------------------------ SYS WRH$_SGASTAT WRH$_SGASTAT_U SYS WRH$_CON_SYSMETRIC_HISTORY WRH$_CON_SYSMET_HISTORY_INDEX SYS WRH$_SYSMETRIC_HISTORY WRH$_SYSMETRIC_HISTORY_INDEX
Running the same query on both 11.2 and 12.1 instances reveals no such mis-configured indexes We are following up with Oracle Support, but I would be interested to know if anyone else can check my findings on their 12.2 instances.
Working with Oracle Support we have worked around the issue using the following script:
ALTER TABLE wrh$_sgastat DROP CONSTRAINT wrh$_sgastat_u; ALTER TABLE wrh$_sgastat ADD CONSTRAINT wrh$_sgastat_u UNIQUE ( dbid,snap_id,instance_number,name,pool,con_dbid ) USING INDEX LOCAL TABLESPACE sysaux;
In addition, I believe I have found the bug in file $ORACLE_HOME/rdbms/admin/c1201000.sql at line 3036:
alter table wrh$_sgastat drop constraint wrh$_sgastat_u; alter table wrh$_sgastat add constraint wrh$_sgastat_u unique (dbid, snap_id, instance_number, name, pool, con_dbid, stattype);
Pingback: A Consolidated List of 12cR2 Issues | hkpatora
I had the same problem, and can’t find anything on MOS yet, so this post saved me some time. Cheers.
LikeLike
No worries, I thought other people would start to be hitting this soon
LikeLike
Thank you for sharing!
LikeLike
same issue here
LikeLiked by 1 person
Cheers.
LikeLike
Cheers.
LikeLike
Had the same issue after an upgrade to 12.2.
Thanks
LikeLike
appartenlty fixed in RU Jan2018
LikeLiked by 1 person