{"id":2945,"date":"2019-09-22T17:27:51","date_gmt":"2019-09-22T17:27:51","guid":{"rendered":"https:\/\/nenadnoveljic.com\/blog\/?p=2945"},"modified":"2019-09-22T17:27:53","modified_gmt":"2019-09-22T17:27:53","slug":"wrh_sysmetric_history_index","status":"publish","type":"post","link":"https:\/\/nenadnoveljic.com\/blog\/wrh_sysmetric_history_index\/","title":{"rendered":"WRH$_SYSMETRIC_HISTORY_INDEX UNUSABLE After 18c Upgrade"},"content":{"rendered":"

The index WRH$_SYSMETRIC_HISTORY_INDEX became UNUSABLE a couple of days after an Oracle 18c upgrade. Rebuilding the index helped only temporarily, because it became UNUSABLE again. Interestingly, while I’ve been observing this problem on every database upgraded from 12c, none of the databases created on 18c were affected.<\/p>\n

The first clue came from the alert log:<\/p>\n

2019-09-13T00:28:11.009410+02:00\nSome indexes or index [sub]partitions of table SYS.WRH$_SYSMETRIC_HISTORY have been marked unusable<\/code><\/pre>\n
V$ACTIVE_SESSION_HISTORY showed only some MMAN_SLAVE activity around this time, but the captured statements weren’t really related to this problem. Therefore, I set up the SQL trace for MMAN_SLAVE:<\/p>\n
begin \n  dbms_monitor.serv_mod_act_trace_enable( \n    service_name=>'SYS$BACKGROUND', module_name=>'MMON_SLAVE', binds=>TRUE\n  ); \nend; \n\/ \n\nbegin \n  dbms_monitor.serv_mod_act_trace_disable(\n    service_name=>'SYS$BACKGROUND', module_name=>'MMON_SLAVE'\n  ); \nend; \n\/<\/code><\/pre>\n
The trace containing the following statements, among others, indeed revealed the problem:<\/p>\n
alter table WRH$_SYSMETRIC_HISTORY drop partition WRH$_SYSMETRIC_HISTORY_1896533961_17248\nLOCK TABLE \"WRH$_SYSMETRIC_HISTORY\" PARTITION (\"WRH$_SYSMETRIC_HISTORY_1896533961_17439\")  IN EXCLUSIVE MODE  NOWAIT\nalter table WRH$_SYSMETRIC_HISTORY split partition WRH$_SYSMETRIC_HISTORY_1896533961_17439 at (1896533961,17464) into (partition WRH$_SYSMETRIC_HISTORY_1896533961_17439, partition WRH$_SYSMETRIC_HISTORY_1896533961_17464 tablespace SYSAUX) update indexes<\/code><\/pre>\n
It was the first statement (that is, ALTER TABLE DROP PARTITION) that invalidated the index. That happened because the index WRH$_SYSMETRIC_HISTORY_INDEX is global:<\/p>\n
select partitioned from dba_indexes \n  where index_name = 'WRH$_SYSMETRIC_HISTORY_INDEX' ;\n\nPAR\n---\nNO<\/code><\/pre>\n
A global index will become UNUSABLE when DROP PARTITION is issued without UPDATE INDEXES (or at least UPDATE GLOBAL INDEXES) [McDonald]<\/a>.<\/p>\n
In contrast, the same index is local in a newly created 18c database:<\/p>\n
column owner format a3\ncolumn name format a30\ncolumn column_name format a7\n\nselect owner,name,column_name from dba_part_key_columns \n  where name in ('WRH$_SYSMETRIC_HISTORY', 'WRH$_SYSMETRIC_HISTORY_INDEX')\n  order by 1,2,3;\n\nOWN NAME                           COLUMN_\n--- ------------------------------ -------\nSYS WRH$_SYSMETRIC_HISTORY         DBID\nSYS WRH$_SYSMETRIC_HISTORY         SNAP_ID\nSYS WRH$_SYSMETRIC_HISTORY_INDEX   DBID\nSYS WRH$_SYSMETRIC_HISTORY_INDEX   SNAP_ID<\/code><\/pre>\n
For local indexes, DROP PARTITION doesn’t require UPDATE INDEXES, because ALTER TABLE DROP PARTITION drops the related index partition as well. It is precisely the reason why this problem doesn’t occur on a new 18c database.<\/p>\n
In my opinion, it’s a bug in the upgrade script, which doesn’t replace the global index with the local one. Until there’s an official procedure to fix this problem you can safely ignore it, unless your queries on WRH$_SYSMETRIC_HISTORY get slow. In this case, you can rebuild the index, which usually remains VALID for a couple of days:<\/p>\n
alter index SYS.WRH$_SYSMETRIC_HISTORY_INDEX rebuild online ;<\/code><\/pre>\n
Since the problem recurs, I automatically rebuild this index whenever it becomes UNUSABLE.<\/p>\n
Lastly, I dropped the global and created the local index on a test database:<\/p>\n
drop index sys.wrh$_sysmetric_history_index ;\n\ncreate index sys.wrh$_sysmetric_history_index on sys.wrh$_sysmetric_history (\n  dbid, snap_id, instance_number, group_id, metric_id, begin_time, con_dbid\n) local tablespace sysaux ;<\/code><\/pre>\n
The index haven’t become UNUSABLE any more and I haven’t observed any side-effects. Mind, however, that this is unsupported and, therefore, should be only done after getting clearance from the Oracle support.<\/p>\n
References:<\/p>\n
[McDonald]<\/a> Conor McDonald. (September 20, 2017). Updating indexes with partition maintenance<\/p>\n","protected":false},"excerpt":{"rendered":"
The index WRH$_SYSMETRIC_HISTORY_INDEX became UNUSABLE a couple of days after the Oracle 18c upgrade Continue Reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[29,5],"tags":[],"class_list":["post-2945","post","type-post","status-publish","format-standard","hentry","category-18c","category-oracle"],"yoast_head":"\nWRH$_SYSMETRIC_HISTORY_INDEX UNUSABLE After 18c Upgrade - All-round Database Topics<\/title>\n<meta name=\"description\" content=\"The index WRH$_SYSMETRIC_HISTORY_INDEX became UNUSABLE a couple of days after the Oracle 18c upgrade\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/nenadnoveljic.com\/blog\/wrh_sysmetric_history_index\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"WRH$_SYSMETRIC_HISTORY_INDEX UNUSABLE After 18c Upgrade - All-round Database Topics\" \/>\n<meta property=\"og:description\" content=\"The index WRH$_SYSMETRIC_HISTORY_INDEX became UNUSABLE a couple of days after the Oracle 18c upgrade\" \/>\n<meta property=\"og:url\" content=\"https:\/\/nenadnoveljic.com\/blog\/wrh_sysmetric_history_index\/\" \/>\n<meta property=\"og:site_name\" content=\"All-round Database Topics\" \/>\n<meta property=\"article:published_time\" content=\"2019-09-22T17:27:51+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-09-22T17:27:53+00:00\" \/>\n<meta name=\"author\" content=\"Nenad Noveljic\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@NenadNoveljic\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nenad Noveljic\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/wrh_sysmetric_history_index\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/wrh_sysmetric_history_index\\\/\"},\"author\":{\"name\":\"Nenad Noveljic\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"headline\":\"WRH$_SYSMETRIC_HISTORY_INDEX UNUSABLE After 18c Upgrade\",\"datePublished\":\"2019-09-22T17:27:51+00:00\",\"dateModified\":\"2019-09-22T17:27:53+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/wrh_sysmetric_history_index\\\/\"},\"wordCount\":345,\"commentCount\":7,\"articleSection\":[\"18c\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/wrh_sysmetric_history_index\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/wrh_sysmetric_history_index\\\/\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/wrh_sysmetric_history_index\\\/\",\"name\":\"WRH$_SYSMETRIC_HISTORY_INDEX UNUSABLE After 18c Upgrade - All-round Database Topics\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\"},\"datePublished\":\"2019-09-22T17:27:51+00:00\",\"dateModified\":\"2019-09-22T17:27:53+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"description\":\"The index WRH$_SYSMETRIC_HISTORY_INDEX became UNUSABLE a couple of days after the Oracle 18c upgrade\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/wrh_sysmetric_history_index\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/wrh_sysmetric_history_index\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/wrh_sysmetric_history_index\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"WRH$_SYSMETRIC_HISTORY_INDEX UNUSABLE After 18c Upgrade\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\",\"name\":\"All-round Database Topics\",\"description\":\"Nenad Noveljic\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\",\"name\":\"Nenad Noveljic\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"caption\":\"Nenad Noveljic\"},\"sameAs\":[\"nenad-noveljic-9b746a6\",\"https:\\\/\\\/x.com\\\/NenadNoveljic\"],\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/author\\\/nenad\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"WRH$_SYSMETRIC_HISTORY_INDEX UNUSABLE After 18c Upgrade - All-round Database Topics","description":"The index WRH$_SYSMETRIC_HISTORY_INDEX became UNUSABLE a couple of days after the Oracle 18c upgrade","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/nenadnoveljic.com\/blog\/wrh_sysmetric_history_index\/","og_locale":"en_US","og_type":"article","og_title":"WRH$_SYSMETRIC_HISTORY_INDEX UNUSABLE After 18c Upgrade - All-round Database Topics","og_description":"The index WRH$_SYSMETRIC_HISTORY_INDEX became UNUSABLE a couple of days after the Oracle 18c upgrade","og_url":"https:\/\/nenadnoveljic.com\/blog\/wrh_sysmetric_history_index\/","og_site_name":"All-round Database Topics","article_published_time":"2019-09-22T17:27:51+00:00","article_modified_time":"2019-09-22T17:27:53+00:00","author":"Nenad Noveljic","twitter_card":"summary_large_image","twitter_creator":"@NenadNoveljic","twitter_misc":{"Written by":"Nenad Noveljic","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/nenadnoveljic.com\/blog\/wrh_sysmetric_history_index\/#article","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/wrh_sysmetric_history_index\/"},"author":{"name":"Nenad Noveljic","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"headline":"WRH$_SYSMETRIC_HISTORY_INDEX UNUSABLE After 18c Upgrade","datePublished":"2019-09-22T17:27:51+00:00","dateModified":"2019-09-22T17:27:53+00:00","mainEntityOfPage":{"@id":"https:\/\/nenadnoveljic.com\/blog\/wrh_sysmetric_history_index\/"},"wordCount":345,"commentCount":7,"articleSection":["18c","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/nenadnoveljic.com\/blog\/wrh_sysmetric_history_index\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/nenadnoveljic.com\/blog\/wrh_sysmetric_history_index\/","url":"https:\/\/nenadnoveljic.com\/blog\/wrh_sysmetric_history_index\/","name":"WRH$_SYSMETRIC_HISTORY_INDEX UNUSABLE After 18c Upgrade - All-round Database Topics","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#website"},"datePublished":"2019-09-22T17:27:51+00:00","dateModified":"2019-09-22T17:27:53+00:00","author":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"description":"The index WRH$_SYSMETRIC_HISTORY_INDEX became UNUSABLE a couple of days after the Oracle 18c upgrade","breadcrumb":{"@id":"https:\/\/nenadnoveljic.com\/blog\/wrh_sysmetric_history_index\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/nenadnoveljic.com\/blog\/wrh_sysmetric_history_index\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/nenadnoveljic.com\/blog\/wrh_sysmetric_history_index\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/nenadnoveljic.com\/blog\/"},{"@type":"ListItem","position":2,"name":"WRH$_SYSMETRIC_HISTORY_INDEX UNUSABLE After 18c Upgrade"}]},{"@type":"WebSite","@id":"https:\/\/nenadnoveljic.com\/blog\/#website","url":"https:\/\/nenadnoveljic.com\/blog\/","name":"All-round Database Topics","description":"Nenad Noveljic","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/nenadnoveljic.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa","name":"Nenad Noveljic","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","caption":"Nenad Noveljic"},"sameAs":["nenad-noveljic-9b746a6","https:\/\/x.com\/NenadNoveljic"],"url":"https:\/\/nenadnoveljic.com\/blog\/author\/nenad\/"}]}},"_links":{"self":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/2945","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/comments?post=2945"}],"version-history":[{"count":1,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/2945\/revisions"}],"predecessor-version":[{"id":2958,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/2945\/revisions\/2958"}],"wp:attachment":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/media?parent=2945"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/categories?post=2945"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/tags?post=2945"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}