Index WRH$_SYSMETRIC_HISTORY_INDEX Status Unusable After Database Upgrade From 12c To 19c

Upgrading Oracle database 12.2 to 19.10 came with some surprises, and one of them was the invalid status of the index WRH$_SYSMETRIC_HISTORY_INDEX owned by the user SYS. Rebuilding the index helped only temporarily, so I decided to recreate the index by following the procedure below:

— Confirmation of the invalid index:

SQL> set pages 2000
SQL> set lines 2000
SQL> SELECT owner, index_name, tablespace_name
FROM   dba_indexes
WHERE  status = 'UNUSABLE';  2    3

OWNER                                                                                                                            INDEX_NAME                                                   TABLESPACE_NAME
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------
SYS                                                                                                                              WRH$_SYSMETRIC_HISTORY_INDEX                                 SYSAUX
SYS                                                                                                                              WRH$_SGASTAT_U   

— Action plan:

## ONLY ONCE YOUR REGULAR FULL DATABASE BACKUP is successfully completed, proceed with below action plan 

a) Get this mentioned index DDL:

-----------------
sqlplus "/as sysdba"

set pages 0 lines 0

select DBMS_METADATA.GET_DDL('INDEX','WRH$_SYSMETRIC_HISTORY_INDEX') from DUAL;

exit
-----------------


SQL> set pages 2000
SQL>set long 99999
SQL>select DBMS_METADATA.GET_DDL('INDEX','WRH$_SYSMETRIC_HISTORY_INDEX') from DUAL;

DBMS_METADATA.GET_DDL('INDEX','WRH$_SYSMETRIC_HISTORY_INDEX')
--------------------------------------------------------------------------------

  CREATE INDEX "SYS"."WRH$_SYSMETRIC_HISTORY_INDEX" ON "SYS"."WRH$_SYSMETRIC_HIS
TORY" ("DBID", "SNAP_ID", "INSTANCE_NUMBER", "GROUP_ID", "METRIC_ID", "BEGIN_TIM
E", "CON_DBID")
  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"
  ALTER INDEX "SYS"."WRH$_SYSMETRIC_HISTORY_INDEX"  UNUSABLE

b) Drop this index:

drop index SYS.WRH$_SYSMETRIC_HISTORY_INDEX;

Drop:

SQL> drop index SYS.WRH$_SYSMETRIC_HISTORY_INDEX; 

c) Re-create this above dropped index using Metadata collected in the above step a) and additionally adding LOCAL clause, you can refer Doc ID 2426391.1 solution section to get an idea

Recreated:


SQL>CREATE INDEX "SYS"."WRH$_SYSMETRIC_HISTORY_INDEX" ON "SYS"."WRH$_SYSMETRIC_HIS
TORY" ("DBID", "SNAP_ID", "INSTANCE_NUMBER", "GROUP_ID", "METRIC_ID", "BEGIN_TIM
E", "CON_DBID") 
  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)
  local tablespace SYSAUX TABLESPACE "SYSAUX";

d) Now check if this index is partitioned and in USABLE state.

e) Finally gather the stats for the underlying table:

exec DBMS_STATS.GATHER_TABLE_STATS('SYS','WRH$_SYSMETRIC_HISTORY', cascade => true);

Statistics:
SQL>   exec DBMS_STATS.GATHER_TABLE_STATS('SYS','WRH$_SYSMETRIC_HISTORY', cascade => true);

For more information, please check the following MOS notes below:

Index WRH$_SYSMETRIC_HISTORY_INDEX Status Unusable (Doc ID 2426391.1)
WRH$_SYSMETRIC_HISTORY_INDEX Index Does Not Get Partitioned After Database Upgrade From 12c To 19c (Doc ID 2777641.1)
Index WRH$_SYSMETRIC_HISTORY_INDEX Status Unusable (Doc ID 2426391.1)

brunors

Hi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)