{"id":2244,"date":"2011-07-20T10:06:00","date_gmt":"2011-07-20T08:06:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-11g-r2-catupgrdsql-running-for-hours\/"},"modified":"2011-07-20T10:06:00","modified_gmt":"2011-07-20T08:06:00","slug":"oracle-11g-r2-catupgrdsql-running-for-hours","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-11g-r2-catupgrdsql-running-for-hours\/","title":{"rendered":"Oracle 11g R2: catupgrd.sql running for hours"},"content":{"rendered":"<p>During the last weeks, I experienced an uncommon bug during upgrade of security sensible databases: The upgrade process was sticking for hours on the script c1101000.sql which tried to fill the column DBID with the database DBID of the tables AUD$ and\/or FGA_LOG$ (auditing and fine grained auditing tables).<\/p>\n<p>As expected this bug was already published on My Oracle Support Note 11.2.0.1 Catupgrd.sql Hangs While Running Procedure POPULATE_DBID_AUDIT [ID 1062993.1]. In simple words, when you have many rows in these audit tables, the catupgrd.sql may be stuck until all rows are processed.<\/p>\n<p>The announced workaround:<\/p>\n<ul>\n<li>exporting the AUD$ and\/or FGA_LOG$ tables<\/li>\n<li>cleaning up these two tables<\/li>\n<li>finally launching catupgrd.sql<\/li>\n<li>importing the data<\/li>\n<\/ul>\n<p>But this solution can be bypassed by a much more &#8220;customer friendly&#8221; way as it does not require any delete\/truncate operation on the audit logs!<br \/>\nMy solution was to fill the column AUD$.DBID shortly before the catalog upgrade catupgrd.sql script. You have to use the following query:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">DECLARE\u00a0\u00a0\n\u00a0\u00a0 cur_dbid NUMBER;\nBEGIN\u00a0\u00a0\n\u00a0\u00a0 SELECT dbid INTO cur_dbid FROM v$database;\n\u00a0\u00a0 UPDATE sys.aud$ SET dbid=cur_dbid WHERE dbid is NULL;\u00a0\u00a0\n\u00a0\u00a0 COMMIT;\nEND;\n\/<\/pre>\n<p>This short PL\/SQL code was running about 10min (depending on the number of rows). Afterwards, the catupgrd.sql was running in an acceptable timeframe and the rows appended to the audit tables in the meantime were updated without any pain during the catalogue upgrade.<\/p>\n<p>I hope this information will help some of you in the upgrade of your Oracle databases to Oracle 11gR2!<\/p>\n<p>J\u00e9r\u00f4me<\/p>\n","protected":false},"excerpt":{"rendered":"<p>During the last weeks, I was confronted to an uncommon bug during the database upgrade from security sensibledatabases.<\/p>\n<p>The upgrade was sticking for hours on the script c1101000.sql.&nbsp; which tried to fill the column DBID with the database DBID of the tables AUD$ and\/or FGA_LOG$.<\/p>\n","protected":false},"author":25,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[197],"tags":[275,276,17,24],"type_dbi":[],"class_list":["post-2244","post","type-post","status-publish","format-standard","hentry","category-application-integration-middleware","tag-aud","tag-catupgrd-sql","tag-oracle-11g","tag-pl-sql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.5) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Oracle 11g R2: catupgrd.sql running for hours - dbi Blog<\/title>\n<meta name=\"description\" content=\"During the last weeks, I experienced an uncommon bug: The upgrade process was sticking for hours on the script c1101000.sql.\" \/>\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\/oracle-11g-r2-catupgrdsql-running-for-hours\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle 11g R2: catupgrd.sql running for hours\" \/>\n<meta property=\"og:description\" content=\"During the last weeks, I experienced an uncommon bug: The upgrade process was sticking for hours on the script c1101000.sql.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-11g-r2-catupgrdsql-running-for-hours\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2011-07-20T08:06:00+00:00\" \/>\n<meta name=\"author\" content=\"J\u00e9r\u00f4me Witt\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"J\u00e9r\u00f4me Witt\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-11g-r2-catupgrdsql-running-for-hours\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-11g-r2-catupgrdsql-running-for-hours\\\/\"},\"author\":{\"name\":\"J\u00e9r\u00f4me Witt\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/a2d3ecddaf732850101a39b9d62c31b7\"},\"headline\":\"Oracle 11g R2: catupgrd.sql running for hours\",\"datePublished\":\"2011-07-20T08:06:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-11g-r2-catupgrdsql-running-for-hours\\\/\"},\"wordCount\":245,\"commentCount\":0,\"keywords\":[\"AUD$\",\"catupgrd.sql\",\"Oracle 11g\",\"PL\\\/SQL\"],\"articleSection\":[\"Application integration &amp; Middleware\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-11g-r2-catupgrdsql-running-for-hours\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-11g-r2-catupgrdsql-running-for-hours\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-11g-r2-catupgrdsql-running-for-hours\\\/\",\"name\":\"Oracle 11g R2: catupgrd.sql running for hours - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2011-07-20T08:06:00+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/a2d3ecddaf732850101a39b9d62c31b7\"},\"description\":\"During the last weeks, I experienced an uncommon bug: The upgrade process was sticking for hours on the script c1101000.sql.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-11g-r2-catupgrdsql-running-for-hours\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-11g-r2-catupgrdsql-running-for-hours\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-11g-r2-catupgrdsql-running-for-hours\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle 11g R2: catupgrd.sql running for hours\"}]},{\"@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\\\/a2d3ecddaf732850101a39b9d62c31b7\",\"name\":\"J\u00e9r\u00f4me Witt\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/17095c081578ca53f52ec4030ba2bde72cc18badb325cd2ba1ee2831106507ad?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/17095c081578ca53f52ec4030ba2bde72cc18badb325cd2ba1ee2831106507ad?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/17095c081578ca53f52ec4030ba2bde72cc18badb325cd2ba1ee2831106507ad?s=96&d=mm&r=g\",\"caption\":\"J\u00e9r\u00f4me Witt\"},\"description\":\"J\u00e9rome Witt started his Consultant career a few years ago. He is specialized in database and infrastructure management, engineering, and optimization. He is very skilled in Oracle high availability, backup &amp; recovery, and tuning technologies. His expertise also includes the open source field (Linux\\\/Unix), advanced Perl, Shell, Windows PowerShell programming, and Automation tools (UC4). J\u00e9r\u00f4me Witt is Oracle Certified Professional 11g (OCP 11g), Oracle Certified Expert Tuning (OCE), and ITIL V3 Foundation certified. Prior to joining dbi services, J\u00e9r\u00f4me Witt was Consultant at Trivadis in Basel. He also worked as a Junior Automation specialist at Selmoni AG in Basel. J\u00e9r\u00f4me Witt holds a BTS degree in Information Systems and Industrial Networks from France. His branch-related experience covers Pharma, Health Care, Banking &amp; Financial Services, Energy, Automotive etc.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/jerome-witt\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle 11g R2: catupgrd.sql running for hours - dbi Blog","description":"During the last weeks, I experienced an uncommon bug: The upgrade process was sticking for hours on the script c1101000.sql.","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\/oracle-11g-r2-catupgrdsql-running-for-hours\/","og_locale":"en_US","og_type":"article","og_title":"Oracle 11g R2: catupgrd.sql running for hours","og_description":"During the last weeks, I experienced an uncommon bug: The upgrade process was sticking for hours on the script c1101000.sql.","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-11g-r2-catupgrdsql-running-for-hours\/","og_site_name":"dbi Blog","article_published_time":"2011-07-20T08:06:00+00:00","author":"J\u00e9r\u00f4me Witt","twitter_card":"summary_large_image","twitter_misc":{"Written by":"J\u00e9r\u00f4me Witt","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-11g-r2-catupgrdsql-running-for-hours\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-11g-r2-catupgrdsql-running-for-hours\/"},"author":{"name":"J\u00e9r\u00f4me Witt","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/a2d3ecddaf732850101a39b9d62c31b7"},"headline":"Oracle 11g R2: catupgrd.sql running for hours","datePublished":"2011-07-20T08:06:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-11g-r2-catupgrdsql-running-for-hours\/"},"wordCount":245,"commentCount":0,"keywords":["AUD$","catupgrd.sql","Oracle 11g","PL\/SQL"],"articleSection":["Application integration &amp; Middleware"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-11g-r2-catupgrdsql-running-for-hours\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-11g-r2-catupgrdsql-running-for-hours\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-11g-r2-catupgrdsql-running-for-hours\/","name":"Oracle 11g R2: catupgrd.sql running for hours - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2011-07-20T08:06:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/a2d3ecddaf732850101a39b9d62c31b7"},"description":"During the last weeks, I experienced an uncommon bug: The upgrade process was sticking for hours on the script c1101000.sql.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-11g-r2-catupgrdsql-running-for-hours\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-11g-r2-catupgrdsql-running-for-hours\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-11g-r2-catupgrdsql-running-for-hours\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle 11g R2: catupgrd.sql running for hours"}]},{"@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\/a2d3ecddaf732850101a39b9d62c31b7","name":"J\u00e9r\u00f4me Witt","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/17095c081578ca53f52ec4030ba2bde72cc18badb325cd2ba1ee2831106507ad?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/17095c081578ca53f52ec4030ba2bde72cc18badb325cd2ba1ee2831106507ad?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/17095c081578ca53f52ec4030ba2bde72cc18badb325cd2ba1ee2831106507ad?s=96&d=mm&r=g","caption":"J\u00e9r\u00f4me Witt"},"description":"J\u00e9rome Witt started his Consultant career a few years ago. He is specialized in database and infrastructure management, engineering, and optimization. He is very skilled in Oracle high availability, backup &amp; recovery, and tuning technologies. His expertise also includes the open source field (Linux\/Unix), advanced Perl, Shell, Windows PowerShell programming, and Automation tools (UC4). J\u00e9r\u00f4me Witt is Oracle Certified Professional 11g (OCP 11g), Oracle Certified Expert Tuning (OCE), and ITIL V3 Foundation certified. Prior to joining dbi services, J\u00e9r\u00f4me Witt was Consultant at Trivadis in Basel. He also worked as a Junior Automation specialist at Selmoni AG in Basel. J\u00e9r\u00f4me Witt holds a BTS degree in Information Systems and Industrial Networks from France. His branch-related experience covers Pharma, Health Care, Banking &amp; Financial Services, Energy, Automotive etc.","url":"https:\/\/www.dbi-services.com\/blog\/author\/jerome-witt\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2244","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\/25"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=2244"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2244\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=2244"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=2244"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=2244"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=2244"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}