A couple of weeks ago I upgraded an Oracle Standard Edition 2 test database from 12.1.0.2 to 12.2.0.1 (with the April 2020 Patch Bundle) on Windows. Recently I upgraded the production database. Both upgrades were done with the Database Upgrade Assistant DBUA. I didn’t use AUTOUPGRADE because I had to upgrade only 1 database and the DBUA handles everything for me (including changing the necessary Windows services and update the timezone file).<\/p>\n
Both upgrades did hang at the finalizing phase of the components upgrade.<\/p>\n
So I checked what the upgrade process is waiting for in the DB:<\/p>\n
\nSQL> select sid, sql_id, event,p1,p2,p3 from v$session \n 2 where status='ACTIVE' and type='USER' and sid not in \n 3 (select sid from v$mystat);\n\n SID SQL_ID EVENT P1 P2 P3\n---------- ------------- -------------------------------------------------- ----- ---------- ----------\n 1142 fgus25bx1md8q Streams AQ: waiting for messages in the queue 17409 1.4072E+14 2147483647\n\nSQL> set long 400000 longchunksize 200\nSQL> select sql_fulltext from v$sqlarea where sql_id='fgus25bx1md8q';\n\nSQL_FULLTEXT\n---------------------------------------------------------------------------------\nDECLARE\n cursor table_name_cursor is\n select x.name table_name\n from sys.x$krvxdta x\n where bitand(x.flags, 12) != 0;\n filter_lst DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();\n obj_lst DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();\n ind number := 1;\nBEGIN\n for rec in table_name_cursor loop\n begin\n filter_lst.extend(1);\n filter_lst(ind).ownname := 'SYSTEM';\n filter_lst(ind).objname := 'LOGMNR_'|| rec.table_name||'';\n ind := ind + 1;\n end;\n end loop;\n DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'SYSTEM', objlist=>obj_lst, obj_filter_list=>filter_lst);\nEND;\n<\/code><\/pre>\nSo obviously the upgrade process tried to gather stats on LOGMNR-tables owned by SYSTEM and waits for messages in the scheduler queue SCHEDULER$_EVENT_QUEUE (Object ID 17409). I.e. this is something similar as documented in MOS Note 1559487.1.<\/p>\n
The upgrade was stuck at this point. So what to do?<\/p>\n
Fortunately I remembered a blog about DBUA being restartable in 12.2. from Mike Dietrich:<\/p>\n
https:\/\/mikedietrichde.com\/2017\/01\/25\/restarting-a-failed-database-upgrade-with-dbua-12-2\/<\/p>\n
So I killed the waiting session:<\/p>\n
\nSQL> select serial# from v$session where sid=1142;\n\n SERIAL#\n----------\n 59722\n\nSQL> alter system kill session '1142,59722';\n\nSystem altered.\n<\/code><\/pre>\nThen I let the DBUA run into tons of errors and let it finish his work. To restart it I just clicked on “Retry” in the GUI. After some time DBUA went into an error again. I quickly checked the log-files and clicked again on “Retry”. That time it went through without issues. Checking the log-files and the result of the upgrade showed all components migrated correctly.<\/p>\n
So in summary: A failed upgrade (crashed or hanging) with DBUA is not such a bad thing anymore as it was before 12.2. You can just let DBUA (or AUTOUPGRADE) retry its work. Of course, usually you have to fix the reason for the failure before restarting\/retrying.<\/p>\n
REMARK: See Mike Dietrich’s Blog about resumability and restartability of Autoupgrade here:<\/p>\n
https:\/\/mikedietrichde.com\/2020\/04\/08\/troubleshooting-restoring-and-restarting-autoupgrade\/<\/p>\n","protected":false},"excerpt":{"rendered":"
A couple of weeks ago I upgraded an Oracle Standard Edition 2 test database from 12.1.0.2 to 12.2.0.1 (with the April 2020 Patch Bundle) on Windows. Recently I upgraded the production database. Both upgrades were done with the Database Upgrade Assistant DBUA. I didn’t use AUTOUPGRADE because I had to upgrade only 1 database and […]<\/p>\n","protected":false},"author":35,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[59],"tags":[],"type_dbi":[],"class_list":["post-14276","post","type-post","status-publish","format-standard","hentry","category-oracle"],"acf":[],"yoast_head":"\n
DB-Upgrade hangs in SE2 waiting on Streams AQ while gathering statistics on LOGMNR-Tables - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/db-upgrade-hangs-in-se2-waiting-on-streams-aq-while-gathering-statistics-on-logmnr-tables\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"DB-Upgrade hangs in SE2 waiting on Streams AQ while gathering statistics on LOGMNR-Tables\" \/>\n<meta property=\"og:description\" content=\"A couple of weeks ago I upgraded an Oracle Standard Edition 2 test database from 12.1.0.2 to 12.2.0.1 (with the April 2020 Patch Bundle) on Windows. Recently I upgraded the production database. Both upgrades were done with the Database Upgrade Assistant DBUA. I didn’t use AUTOUPGRADE because I had to upgrade only 1 database and […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/db-upgrade-hangs-in-se2-waiting-on-streams-aq-while-gathering-statistics-on-logmnr-tables\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-06-05T17:51:01+00:00\" \/>\n<meta name=\"author\" content=\"Clemens Bleile\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@ifgtxD2SrQ8r!YuXj\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Clemens Bleile\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/db-upgrade-hangs-in-se2-waiting-on-streams-aq-while-gathering-statistics-on-logmnr-tables\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/db-upgrade-hangs-in-se2-waiting-on-streams-aq-while-gathering-statistics-on-logmnr-tables\\\/\"},\"author\":{\"name\":\"Clemens Bleile\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0ac04011f60f2e93c115358d0789c2da\"},\"headline\":\"DB-Upgrade hangs in SE2 waiting on Streams AQ while gathering statistics on LOGMNR-Tables\",\"datePublished\":\"2020-06-05T17:51:01+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/db-upgrade-hangs-in-se2-waiting-on-streams-aq-while-gathering-statistics-on-logmnr-tables\\\/\"},\"wordCount\":310,\"commentCount\":0,\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/db-upgrade-hangs-in-se2-waiting-on-streams-aq-while-gathering-statistics-on-logmnr-tables\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/db-upgrade-hangs-in-se2-waiting-on-streams-aq-while-gathering-statistics-on-logmnr-tables\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/db-upgrade-hangs-in-se2-waiting-on-streams-aq-while-gathering-statistics-on-logmnr-tables\\\/\",\"name\":\"DB-Upgrade hangs in SE2 waiting on Streams AQ while gathering statistics on LOGMNR-Tables - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2020-06-05T17:51:01+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0ac04011f60f2e93c115358d0789c2da\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/db-upgrade-hangs-in-se2-waiting-on-streams-aq-while-gathering-statistics-on-logmnr-tables\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/db-upgrade-hangs-in-se2-waiting-on-streams-aq-while-gathering-statistics-on-logmnr-tables\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/db-upgrade-hangs-in-se2-waiting-on-streams-aq-while-gathering-statistics-on-logmnr-tables\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"DB-Upgrade hangs in SE2 waiting on Streams AQ while gathering statistics on LOGMNR-Tables\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0ac04011f60f2e93c115358d0789c2da\",\"name\":\"Clemens Bleile\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"caption\":\"Clemens Bleile\"},\"description\":\"Clemens Bleile has more than 30 years of IT experience, thirteen in Oracle Support and fifteen in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.\",\"sameAs\":[\"https:\\\/\\\/www.dbi-services.com\",\"https:\\\/\\\/x.com\\\/ifgtxD2SrQ8r!YuXj\"],\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/clemens-bleile\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"DB-Upgrade hangs in SE2 waiting on Streams AQ while gathering statistics on LOGMNR-Tables - dbi Blog","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:\/\/www.dbi-services.com\/blog\/db-upgrade-hangs-in-se2-waiting-on-streams-aq-while-gathering-statistics-on-logmnr-tables\/","og_locale":"en_US","og_type":"article","og_title":"DB-Upgrade hangs in SE2 waiting on Streams AQ while gathering statistics on LOGMNR-Tables","og_description":"A couple of weeks ago I upgraded an Oracle Standard Edition 2 test database from 12.1.0.2 to 12.2.0.1 (with the April 2020 Patch Bundle) on Windows. Recently I upgraded the production database. Both upgrades were done with the Database Upgrade Assistant DBUA. I didn’t use AUTOUPGRADE because I had to upgrade only 1 database and […]","og_url":"https:\/\/www.dbi-services.com\/blog\/db-upgrade-hangs-in-se2-waiting-on-streams-aq-while-gathering-statistics-on-logmnr-tables\/","og_site_name":"dbi Blog","article_published_time":"2020-06-05T17:51:01+00:00","author":"Clemens Bleile","twitter_card":"summary_large_image","twitter_creator":"@ifgtxD2SrQ8r!YuXj","twitter_misc":{"Written by":"Clemens Bleile","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/db-upgrade-hangs-in-se2-waiting-on-streams-aq-while-gathering-statistics-on-logmnr-tables\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/db-upgrade-hangs-in-se2-waiting-on-streams-aq-while-gathering-statistics-on-logmnr-tables\/"},"author":{"name":"Clemens Bleile","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"headline":"DB-Upgrade hangs in SE2 waiting on Streams AQ while gathering statistics on LOGMNR-Tables","datePublished":"2020-06-05T17:51:01+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/db-upgrade-hangs-in-se2-waiting-on-streams-aq-while-gathering-statistics-on-logmnr-tables\/"},"wordCount":310,"commentCount":0,"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/db-upgrade-hangs-in-se2-waiting-on-streams-aq-while-gathering-statistics-on-logmnr-tables\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/db-upgrade-hangs-in-se2-waiting-on-streams-aq-while-gathering-statistics-on-logmnr-tables\/","url":"https:\/\/www.dbi-services.com\/blog\/db-upgrade-hangs-in-se2-waiting-on-streams-aq-while-gathering-statistics-on-logmnr-tables\/","name":"DB-Upgrade hangs in SE2 waiting on Streams AQ while gathering statistics on LOGMNR-Tables - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-06-05T17:51:01+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/db-upgrade-hangs-in-se2-waiting-on-streams-aq-while-gathering-statistics-on-logmnr-tables\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/db-upgrade-hangs-in-se2-waiting-on-streams-aq-while-gathering-statistics-on-logmnr-tables\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/db-upgrade-hangs-in-se2-waiting-on-streams-aq-while-gathering-statistics-on-logmnr-tables\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"DB-Upgrade hangs in SE2 waiting on Streams AQ while gathering statistics on LOGMNR-Tables"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da","name":"Clemens Bleile","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","caption":"Clemens Bleile"},"description":"Clemens Bleile has more than 30 years of IT experience, thirteen in Oracle Support and fifteen in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.","sameAs":["https:\/\/www.dbi-services.com","https:\/\/x.com\/ifgtxD2SrQ8r!YuXj"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/clemens-bleile\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14276","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/35"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=14276"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14276\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=14276"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=14276"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=14276"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=14276"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}